Creating a database dump for specific tables and entries Postgres

Question

I have a database with hundreds of tables, what I need to do is export specified tables and insert statements for the data to one sql file.

The only statement I know can achieve this is

pg_dump -D -a -t zones_seq interway > /tmp/zones_seq.sql

Should I run this statement for each and every table or is there a way to run a similar statement to export all selected tables into one big sql big. The pg_dump above does not export the table schema only inserts, I need both

Any help will be appreciated.

Mike T · Accepted Answer · 2019-12-01 09:26:31Z

280

Right from the manual: "Multiple tables can be selected by writing multiple -t switches"

So you need to list all of your tables

pg_dump --column-inserts -a -t zones_seq -t interway -t table_3 ... > /tmp/zones_seq.sql

Note that if you have several table with the same prefix (or suffix) you can also use wildcards to select them with the -t parameter:

"Also, the table parameter is interpreted as a pattern according to the same rules used by psql's \d commands"

edited Dec 1, 2019 at 9:26

Mike T

44.3k18 gold badges166 silver badges213 bronze badges

answered Sep 9, 2011 at 9:57

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Scott Marlowe Over a year ago

the -T switch is the same way but used to exclude tables. Might be useful if you need all but one or two tables etc.

Epigene Over a year ago

The -D option seems to have disappeared in PG 9.1 onwards

mlovic Over a year ago

The -D option was removed in version 8.4. You have to use --column-inserts now instead.

Tharindu Jayasuriya Over a year ago

Try pg_dump --host 127.0.0.1 --port 5432 --username "<username>" --column-inserts --verbose --file "/tmp/<filename.sql>" --table "^<regex>*" "<tablename>"

Lev Lukomskyi Over a year ago

Beware -a options skips tables schema export – so remove that option if you need to export these tables on fresh database

|

ThatsJustCheesy · Accepted Answer · 2022-12-03 19:01:18Z

42

If those specific tables match a particular pattern, you can use that with the -t option in pg_dump.

pg_dump -D -a -t zones_seq -t interway -t "<pattern>" -f /tmp/zones_seq.sql <DBNAME>

For example to dump tables which start with "test", you can use

pg_dump -D -a -t zones_seq -t interway -t "^test*" -f /tmp/zones_seq.sql <DBNAME>

edited Dec 3, 2022 at 19:01

ThatsJustCheesy

1,5491 gold badge17 silver badges24 bronze badges

answered Dec 6, 2013 at 11:51

Jothikanth

5114 silver badges3 bronze badges

3 Comments

msrd0 Over a year ago

if it was a regex, shouldn't it be ^test.* instead of ^test*?

elliotcm Over a year ago

It's not a regex, it uses postgres's "patterns", so it should really be "test*": postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

Peter Krauss Over a year ago

Not trivial to use regex, generating invalid regular expression: quantifier operand invalid error. But regular expression is correct... Only very simple regex are valid

Collectives™ on Stack Overflow

Creating a database dump for specific tables and entries Postgres

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related