Viswamitra's blog

Some times we might need to copy the sequences from one database to another. Below is the query which is used to create pg_dump statement to take backup of all sequences in the database.

SELECT 'pg_dump -h <host> -d <database> -U <username> '||string_agg('-t ' || n.nspname||'.'||c.relname, ' ')
 FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('S','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname <> 'information_schema'
       AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid);

replace the variables host , username with actual values.

Example

postgres=# SELECT 'pg_dump -h localhost -d postgres -U postgres -f sequence_dump.sql '||string_agg('-t ' || n.nspname||'.'||c.relname, ' ')
 as pg_dump_statement
 FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('S','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname <> 'information_schema'
       AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid);
                                                                                       pg_dump_statement

-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------
 pg_dump -h localhost -d postgres -U postgres -f sequence_dump.sql -t public.branch_id_seq -t public.test_sq -t public.master_id_seq -t public.test_seq -t public.pgbench_te
st_id_seq -t public.test_seq_1
(1 row)

Execute the output of the sql statement (pg_dump command) and it will take the backup of all sequences into sequence_dump.sql which can be restored in other databases.

Reference:

https://www.postgresql.org/docs/9.6/app-pgdump.html