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

Solution 1

There are two cases:

1. 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.

2. 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

Solution 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

Comments

[Graham]

It is a sequence that is not owned by the table, so the exclude-table option works great.

[Abel Callejo]

-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.

[Igor Pomaranskiy]

Wouldn't it exclude both sequence AND the table from dump?

[AlexDev]

@IgorPomaranskiy yes