Is there a way using PG_DUMP
or any other commands to export only sequences from an existing database?
2 Answers
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
pg_dump -h localhost -p 5432 -d namedb -U postgres -t '*_id_seq' > dump-seq.sql
-
You may want to add short description of what this command does and why it answers the question. Nov 24, 2021 at 16:31
-
The
-t
parameter in pg_dump is normally used for tables. Will this really work for sequences? 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