Migrating partitioned table from postgres to Redshift with pglogical

Question

I've created a DMS task of CDC and Full Load, migrating data from postgres 14 to Redshift. According to the documentation, when using pglogical and creating postgres publication with 'publish_via_partition_root' parameter of my partitioned table, changes should be published to the parent table and to to child tables. However, the data is still migrated to the child tables in Redshift and not to the parent table. Am I missing something thats needs to be configured or is it just not possible in DMS?

Answer

Hi,

There are currently some points that should be taken into consideration when using PostgreSQL as a source database in DMS. For the moment, when a partitioned table is detected, the following occurs:

* The endpoint reports a list of parent and child tables.
* AWS DMS creates the table on the target as a regular table with the same properties as the selected tables.
* If the parent table in the source database has the same primary key value as its child tables, a "duplicate key" error is generated.

You can find the full list of limitations when using PostgreSQL as a DMS source on https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations

Thanks.

Migrating partitioned table from postgres to Redshift with pglogical

관련 콘텐츠