3

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?

2 Answers 2

3

I'm not sure what you mean by "export sequences".

I suppose that you mean to extract all statements as:

CREATE SEQUENCE [sequence name] ....;

ALTER SEQENCE [sequence name] OWNED BY [table name]

If that's so than I would do pg_dump of db and then parse extract all sequence related DDL-statements.

E.g.

# CREATE SEQUENCE
$  pg_restore db.dump --section=pre-data  -f - | awk  '/CREATE SEQUENCE/ {print}' FS="\n" RS=""
CREATE SEQUENCE public.address_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
...

# ALTER SEQUENCE
$ pg_restore db.dump --section=pre-data  -f - | awk  '/ALTER SEQUENCE/ {print}' FS="\n"       
ALTER SEQUENCE public.address_id_seq OWNED BY public.address.id;
....

Note: gawk 5.1.0 was used.

Possible alterations:

  • db dump file is not required - redirect output of pg_dump directly to awk
  • no need to dump data. pg_dump --schema-only will be enough

Hope this helps

2
pg_dump -h localhost -p 5432 -d namedb -U postgres -t '*_id_seq' > dump-seq.sql
3
  • You may want to add short description of what this command does and why it answers the question.
    – mustaccio
    Nov 24, 2021 at 16:31
  • The -t parameter in pg_dump is normally used for tables. Will this really work for sequences?
    – John K. N.
    Nov 26, 2021 at 9:47
  • Dump only tables with names matching table. For this purpose, "table" includes views, materialized views, sequences, and foreign tables. postgresql.org/docs/9.6/app-pgdump.html Nov 26, 2021 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.