Does DMS support updating value of sequences in the target for postgres to postgres data migration?

Question

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.

subhashr · Answer

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'); 
```

Somto_M · Answer

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.

Does DMS support updating value of sequences in the target for postgres to postgres data migration?

Relevant content