postgresqlpostgresql-13

How to do a pg_dump for only tables only and not triggers and functions?

What I want:
I want a pg_dump of a database (let's call the database as 'test'). In this pg_dump I want only the tables without the following: data, triggers, functions, sequences, etc.

What I am doing to get what I want:
The command I run is as follows:

pg_dump -U postgres -s test > dump_test.sql

What I am observing:
Then when I try to restore this dump on another server as follows:

pg_dump -U postgres new_amazing_test < dump_test.sql

I notice that part of the output of running the above command says the following:

CREATE TRIGGER
CREATE FUNCTION
CREATE SEQUENCE
CREATE INDEX

What I actually want:
All I want is the table itself and not these triggers, functions, sequence and indexes. How do I only get the tables only?

Other things I have tried/considered:

  1. I have tried doing this:

    pg_dump -U postgres -s -schema=\dtmvE test > dump_test.sql

    but it didn't work because the pattern needs to be a name not a \d pattern.
    See here: https://www.postgresql.org/docs/13/app-pgdump.html for information on -n pattern option.

  2. One thing that may solve it is to use multiple switches like this:

    pgdump -t mytable1 -t mytable2 -t mytable3 ... -t mytableN > dump_test.sql

    However, the above solution is impractical because I have some 70+ tables on my database.

Other relevant info:
PostgreSQL version is 13.1
Ubuntu version v16.04 (I have also tried this on Ubuntu v18.04)

Solution

  • I would dump everything with a custom format schema-only dump (-F c -s) and run pg_restore -l on the resulting dump. That gives you a table of contents. Delete everything except the tables from that file and use it as input to pg_restore -L to restore exactly those items from the archive that you need.

    This may not be as simple as you have hoped for, but it is certainly simpler than writing tons of -t options, and you may be able to automatize it.