Excluding sequences from pg_dump

Question

I'm creating an export of a postgres database (10.1) with some tables excluded. I followed the instructions in Is there a way to get pg_dump to exclude a specific sequence?. However, sequences for the excluded tables are still included. Is there a way to make sure they're kept out?

To isolate the problem, I created a small sample database with a table called include and exclude, added a single row to each table, and used this command to export the database:

pg_dump --host=localhost --no-owner --no-acl --verbose --schema=public --dbname=export_test --exclude-table=exclude --file=exclude_table_only.dump

The dump did not include the exclude table, but it did include the sequence:

...
--
-- TOC entry 198 (class 1259 OID 3818320)
-- Name: exclude_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE exclude_id_seq
...

Hexapartite

I don't think this is possible in general. You'll either have to edit the .sql script , either manually, or using sed, or you could submit the script to a staging database, drop the sequences there (automated via a script using the catalogs) and dump the temp DB again into a new script. – Panties

Answer 1

You should be able to exclude the sequence explicitly using another --exclude-table:

--exclude-table=exclude_id_seq

Which should end up looking like this:

$ pg_dump --host=localhost --no-owner --no-acl --verbose --schema=public --dbname=export_test --exclude-table=exclude --exclude-table=exclude_id_seq --file=exclude_table_only.dump

Valona

Instead of adding multiple --exclude-table you can try --exclude-table=<schema>.* to exclude all tables from that specific schema – Organization

Answer 2

For excluding table(s),

--exclude-table

will work.

For excluding sequence(s),

--exclude-table-data 

would be needed.

Tested on Postgres 12

I could not get the --exclude-table to exclude sequences i.e. table data.

example to dump excluding sequence,

$ pg_dump -v -C -Fp -O -x -h employee.us-east-1.rds.amazonaws.com \ 
          -U user1 -d emp -n empschema \ 
          --exclude-table="empschema.employee_history_id_seq" \ 
          -f dump-test.sql

where,
-v : verbose
-C : create Database commands in dump
-Fp : output file in plain Text
-O : no owner details in dump
-x : no privileges details in dump
-h : hostname
-U : username
-d : database
-n : schema
--exclude-table-data : excluding the sequence
-f : file to be written into

Please comment if otherwise.

https://www.postgresql.org/docs/12/app-pgdump.html

Vise

To clarify so for example: --exclude-table-data=my_sequence – Kessinger

Answer 3

the below of pg_dump.exe will give you exclusive pattern of tables in a given database. pg_dump.exe -h localhost -d database -W -U username -p 5XX2 -T m_19* -T m_200* -T m_201* -f dbsave.sql

where the tables excluded are m_19*, m_200* and m_201 etc.

Pock

The question is not about excluding tables. – Christianchristiana