CODEWITHSUNDEEP
postgresqlpg-dump
Matt Hampel
Matt Hampel

Reputation: 5237

Excluding sequences from pg_dump

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

Upvotes: 10

Views: 9142

Answers (3)

Bill Kingsley
Bill Kingsley

Reputation: 1

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.

Upvotes: -1

Isaac Philip
Isaac Philip

Reputation: 565

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

Upvotes: 11

Michael Powers
Michael Powers

Reputation: 2050

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

Upvotes: 9

Related Questions