CODEWITHSUNDEEP
postgresqlpostgresql-13
theQuestionMan
theQuestionMan

Reputation: 1562

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)

Upvotes: 3

Views: 11565

Answers (3)

tanius
tanius

Reputation: 16839

As you observed, -t switches for all your tables would indeed be a solution, as it will cause pg_dump to dump only tables and not functions and triggers:

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

When done manually, this is impractical for databases with many tables. However, when generating these -t switches automatically it is a viable option:

  1. Generate the list of -t switches for all tables in one or more schemas of your database: (the following assumes you use the Bash shell)

    TABLES=$(
  psql -h example.com -p 5432 -d db_name -U user_name --tuples-only -c \
    "SELECT CONCAT(table_schema, '.', table_name) 
     FROM information_schema.tables 
     WHERE table_type='BASE TABLE' AND table_schema IN ('public','auth');" | 
  xargs -I{} echo -n " -t {}"
)

  2. Make sure your list of -t switches looks as intended:

    $ echo $TABLES
-t public.mytable1 -t public.mytable2 ... -t public.mytablen 
-t auth.mytable1 -t auth.mytable2 ... -t auth.mytablen

  3. Run pg_dump with the generated list of table switches:

    pg_dump \
  -h example.com -p 5432 -d db_name -U user_name \
  --schema-only --no-comments --no-owner --no-privileges $TABLES \
  > tables.sql

Upvotes: 0

Bruno
Bruno

Reputation: 641

you can use the flag --section as described in the postgres documentation

--section=sectionname
Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

example:

pg_dump --schema-only --section=pre-data

Upvotes: -1

Laurenz Albe
Laurenz Albe

Reputation: 247675

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.

Upvotes: 3

Related Questions