How to backup Sequences in Postgresql?

Question

Can we take the backup of sequences and use it in other databases?

Answer 1

Below SQL will return the list of SQL statement that can be executed on target to Reset value of sequence

SELECT 'ALTER SEQUENCE ' || c.relname || ' RESTART WITH ' || nextval(c.relname::regclass) || ';' 
FROM pg_class c WHERE c.relkind = 'S';

Answer 2

SELECT 'ALTER SEQUENCE ' || s.relname || ' RESTART WITH ' || t.reltuples + 1 || ';'
FROM pg_class s 
  JOIN pg_depend d ON d.objid = s.oid 
  JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid 
  JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
  JOIN pg_namespace n ON n.oid = s.relnamespace 
WHERE s.relkind = 'S' AND n.nspname = 'public'