Export Postgres sequences only

Question

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

Answer 1

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

Answer 2

pg_dump -h localhost -p 5432 -d namedb -U postgres -t '*_id_seq' > dump-seq.sql