Identity columns sequences were not migrated

[Matthias64]

Hello,

I just want to give the information to whoever that could help.
I was trying to use DMS for Postgres to Postgres migration and even after migrating the sequences with the provided script, I was missing a few sequences. It took me quite a long time to find out that they were not migrated.

I did not figure out fully why they behave differently but I think this is because they are linked to identity columns.

I modified the query to use pg_class instead of information_schema.sequences and that indeed gave me a few more sequences to update :

SELECT d.refobjid::regclass as schema, a.attname as field, seq.relname as sequence_name
FROM pg_depend d
     INNER JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
     INNER JOIN  pg_class as seq ON seq.relkind = 'S' and d.objid = ('"' || seq.relnamespace::regnamespace::text || '"."' || seq.relname || '"')::regclass
WHERE d.refobjsubid > 0
  AND d.classid = 'pg_class'::regclass
ORDER BY seq.relname ;

The ALTER table does not work with these identity columns but the setval is still required.

Hope this can help :)