Is there a way to get pg_dump to exclude a specific sequence?

Question

I want to exclude a sequence from my pg_dump command which is putting the output into a plain file.

Command: /Library/PostgreSQL/8.4/bin/pg_dump --host localhost --port 5433 --username xxx --format plain --clean --inserts --verbose --file /Users/xxx/documents/output/SYSTEM_admin_20131126015325.sql --exclude-table public.table1 --exclude-table public.table2 mydatabase

I know there are switches for tables which i am using above and that you can enable/disable database objects in the tar format in combination with pg_restore as stated in the pg_dump documentation but I will not be using pg_restore.

Many Thanks

Graham

score 32 · Accepted Answer · edited May 23 '17 at 10:31

32

There are two cases:

The sequence to exclude is owned by a table you're also dumping (typical case: SERIAL column).
See: Dump a table without sequence table in postgres
Short answer: no, the sequence can't be left aside.
The sequence is not owned by a dumped table. Then it can be excluded with the --exclude-table switch as if it was a table.

From pg_dump documentation:

-T table --exclude-table=table
Do not dump any tables matching the table pattern.
The pattern is interpreted according to the same rules as for -t

And about -t:

-t table
--table=table

Dump only tables (or views or sequences or foreign tables) matching table

edited May 23 '17 at 10:31

Community

1
1

answered Nov 26 '13 at 13:41

Daniel Vérité

58,074
15
129
156

It is a sequence that is not owned by the table, so the exclude-table option works great. – Graham Nov 26 '13 at 14:43
1

`-T table` statement is **very important**. Do not let your intuitions fool you on that part. By CLI arguments standards, you would think that the `-T table` is no longer needed since it was already stated in the `--exclude-table=table`. It would seem argument-redundant but it is really needed for a successful exclusion. – Abel Callejo Apr 17 '19 at 09:41

score 2 · Answer 2 · answered Dec 19 '19 at 19:35

2

If the sequence is owned by a table you can exclude both the sequence and the table using -T, such as:

pg_dump -T table -T table_id_seq

answered Dec 19 '19 at 19:35

AlexDev

4,049
31
36

Wouldn't it exclude both sequence AND the table from dump? – Ihor Pomaranskyy Jul 06 '21 at 11:29
@IgorPomaranskiy yes – AlexDev Jul 06 '21 at 15:52

Is there a way to get pg_dump to exclude a specific sequence?

2 Answers2

Linked