Does DMS support updating value of sequences in the target for postgres to postgres data migration?
We are migrating our database from on-premise postgres to AWS RDS ( we prefer serverless v2). I set up a full load to load data from source to target. The table data was copied. We also have sequences, which we use as default IDs for our tables. But sequence values were not updated after migration. We plan to migrate data to AWS and then make the DB on AWS RDS serverless v2 as our DB for our application.
Now that sequence values are not updated in the target, what is the general practise? Is it to have a downtime between the switch and update all sequence values in the target ? That is inconvenient and introduces a manual process before we switch the serverless v2 as source of truth. Any recommendations here? Or am I doing something wrong and sequences values are supposed to update? I did not put in any special exclusion rules in the DMS task configuration.
DMS while migrating from postgresql to postgresql wont migrate the sequence property
post the data is migrated you need to setup sequences manually on target and also set the nextval
example script where table is foo and foo_a_seq is sequence
CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
Hello,
From my understanding, you want to know if DMS support updating value of sequences in the target for postgres to postgres data migration.
AWS DMS doesn’t migrate your secondary indexes, sequences, default values, stored procedures, triggers, synonyms, views, and other schema objects not specifically related to data migration. To migrate these objects to your PostgreSQL target, use AWS SCT[1]. For more information on AWS SCT, please refer to the link[2] below :
[1] Migrating an Oracle Database to PostgreSQL - https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html [2] What is the AWS Schema Conversion Tool? - https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html
Based on the the post, sequences is being used for ID columns in the PostgreSQL onprem database. Prior to initiating data migration, I would recommend that you consider manually creating the sequence on the target database. Once created, set the next values higher than they are on the source database(PostgreSQL onprem). I would suggest that you give enough gap to make sure that the values are higher than for the source database at the migration cutover date. This will avoid collisions in sequence IDs after the migration.
I hope this information is helpful.
Relevant questions
Using DMS CDC for Oracle to Postgres
asked 6 days agodoes AWS DMS support partition tables from postgres
asked 6 months agoDoes DMS support updating value of sequences in the target for postgres to postgres data migration?
asked a month agoDMS Migration MongoDB source and RDS Postgres as destination
Accepted Answerasked 20 days agoAWS DMS Postgres to OpenSearch LOB handling
asked 6 months agoResolution for Fatal error when using DMS for on-going replication from RDS Postgres to S3
Accepted Answerasked a year agoHow to create SSL dblink connection from RDS to postgres on premise?
asked 5 months agoApplying column filters in DMS Task for source MongoDB and target Postgres
asked 19 days agoPostgres RDS Schema Upgrade (migration) with no downtime
asked 3 months agoAWS DMS - Migration fails when column in column filter is in camel case
asked 2 months ago