Invalid number of partitions error while migrating PostgreSQL

Not specified by the original author.

TL;DR

Default is the number of data_nodes. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4 for number of partitions requires good reasoning. [krus (accepted answer)] The error is not related to migration of data.

The error

Invalid number of partitions error while migrating PostgreSQL

[krus (accepted answer)] The error is not related to migration of data. It is due to specified space dimension deviceid without specifying the number of dimensions. From the documentation of createhypertable : | `partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes`. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4 for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributedhypertable. The rare cases in which space partitions may be useful for non-distributed hypertables are described in the adddimension section. So it might be better to use the following statement without specifying space partition: SELECT createhypertable('mytable', 'start', migrate_data => true);

Context: Vectle thread pstVyY7LLTPrEHiU5JlMuGIFQ (agent query): Invalid number of partitions error while migrating PostgreSQL Researched from Stack Overflow (Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB, accepted answer, score 5): The error is not related to migration of data. It is due to specified space dimension deviceid without specifying the number of dimensions. From the documentation of createhypertable : | `partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes`. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4 for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributedhypertable. The rare cases in which space partitions may be useful for non-distributed hypertables are described in the adddimension section. So it might be better to use the following statement without specifying space partition: SELECT createhypertable('mytable', 'start', migrate_data => true);

Matched thread

Source: Vectle search thread Original query: "Invalid number of partitions error while migrating PostgreSQL" Also searched as: "Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB" Key terms: error, invalid, migrating, number, partitions, postgresql, while

When to use

• You hit this exact issue.
• The symptom matches: Invalid number of partitions error while migrating PostgreSQL.

When NOT to use

• A different error or symptom; the cause here is specific to this one.

Compatibility

Per the linked source.

Edge cases

• If your error message differs even slightly, this is probably a different issue; search the exact text.
• If the fix does not help, capture the full error output and check the source link for updates.

Source

Vectle search thread

Source: Vectle thread pstVyY7LLTPrEHiU5JlMuGIFQ (agent query): Invalid number of partitions error while migrating PostgreSQL Researched from Stack Overflow (Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB, accepted answer, score 5): The error is not related to migration of data. It is due to specified space dimension deviceid without specifying the number of dimensions. From the documentation of createhypertable : | `partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes`. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4 for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributedhypertable. The rare cases in which space partitions may be useful for non-distributed hypertables are described in the adddimension section. So it might be better to use the following statement without specifying space partition: SELECT createhypertable('mytable', 'start', migrate_data => true);

Verified resolution: [krus (accepted answer)] The error is not related to migration of data. It is due to specified space dimension deviceid without specifying the number of dimensions. From the documentation of createhypertable : | `partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes`. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4 for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributedhypertable. The rare cases in which space partitions may be useful for non-distributed hypertables are described in the adddimension section. So it might be better to use the following statement without specifying space partition: SELECT createhypertable('mytable', 'start', migrate_data => true);

Source URL: https://stackoverflow.com/questions/66885740/invalid-number-of-partitions-error-while-migrating-postgresql-table-to-timescale

Not specified by the original author.

TL;DR

For "but I get an error:": from the documentation of create\hypertable: `` | partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn.

but I get an error:

Steps

1. The error is not related to migration of data. It is due to specified space dimension device_id without specifying the number of dimensions. From the documentation of create_hypertable:

2. For example, the create hypertable statement can be fixed to `SELECT create_hypertable('mytable', 'start','device_id', 4, migrate_data => true); ` Where the magic number `4` for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see create_distributed_hypertable.

``` CREATE TABLE mytable ( device_id bigint NOT NULL, start TIMESTAMP WITHOUT TIME ZONE, level varchar(255) NOT NULL, amount integer ); ```

3. and I want to migrate it to a hypertable like that:

``` SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true); ```

``` ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 ```

4. What am I doing wrong? I had similar tables, where it worked without an issue. Using PostgreSQL 11 and TimescaleDB...

## When to use

You are seeing this: The error is not related to migration of data. Use this skill when you run into "Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB".

## When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

## Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

## Variants

Other phrasings of the same problem that show up in reports:

### `` ERROR: invalid number of partitions for dimension "device\_id" HINT: A closed (space) dimension must specify between 

```text
``` ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 ```

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Stack Overflow #66885740 (accepted answer, 4 votes): I have a table: `` CREATE TABLE mytable ( device_id bigint NOT NULL, start TIMESTAMP WITHOUT TIME ZONE, level varchar(255) NOT NULL, amount integer ); and I want to migrate it to a hypertable like that: SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true); but I get an error: ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 `` What am I doing wrong? I had similar tables, where it worked without an issue. Using PostgreSQL 11 and TimescaleDB...

Verified resolution: The error is not related to migration of data. It is due to specified space dimension device_id without specifying the number of dimensions. From the documentation of create_hypertable: `` | partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4` for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributed_hypertable.

Source URL: https://stackoverflow.com/questions/66885740/invalid-number-of-partitions-error-while-migrating-postgresql-table-to-timescale

Not specified by the original author.

TL;DR

[adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. 23.2.16, migrating to Postgres 16/17

Steps

1. [adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. Two reporters confirmed the migration worked with no issues.

When to use

You are seeing this: Issue zitadel/zitadel#9042 (closed, 26 comments): ### Preflight Checklist - [x] I could not find a solution in the documentation, the existing issues or discussions - [ ] I have joined the ZITADEL chat Use this skill when you run into "Error while migrating from Cockroach to Postgres".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: 2.65.1, 23.2.16. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://zitadel.com/chat
• https://zitadel.com/docs/self-hosting/manage/cli/mirror
• https://github.com/zitadel/zitadel/issues/9042

In gh:zitadel/zitadel#9042, the reporter ran into: Issue zitadel/zitadel#9042 (closed, 26 comments): ### Preflight Checklist

• [x] I could not find a solution in the documentation, the existing issues or discussions
• [ ] I have joined the ZITADEL chat

Environment

Self-hosted

Version

2.65.1

Database

CockroachDB

Database Version

23.2.16, migrating to Postgres 16/17

Describe the problem caused by this bug

There seems to be an issue when migrating from CockroachDB to Postgres on this version.

Following the instruction from here (https://zitadel.com/docs/self-hosting/manage/cli/mirror)... The verified answer was: [adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. Two reporters confirmed the migration worked with no issues. Source issue: https://github.com/zitadel/zitadel/issues/9042