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');
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. For more information on AWS SCT, please refer to the link below :
 Migrating an Oracle Database to PostgreSQL - https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html  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.
Using DMS CDC for Oracle to Postgresasked 6 days ago
does AWS DMS support partition tables from postgresasked 6 months ago
Does DMS support updating value of sequences in the target for postgres to postgres data migration?asked a month ago
DMS Migration MongoDB source and RDS Postgres as destinationAccepted Answerasked 20 days ago
AWS DMS Postgres to OpenSearch LOB handlingasked 6 months ago
Resolution for Fatal error when using DMS for on-going replication from RDS Postgres to S3Accepted Answerasked a year ago
How to create SSL dblink connection from RDS to postgres on premise?asked 5 months ago
Applying column filters in DMS Task for source MongoDB and target Postgresasked 19 days ago
Postgres RDS Schema Upgrade (migration) with no downtimeasked 3 months ago
AWS DMS - Migration fails when column in column filter is in camel caseasked 2 months ago