CODEWITHSUNDEEP
postgresql
code-ninja-54321
code-ninja-54321

Reputation: 539

Ignoring a table in pg_dump and restore

This is what I currently do to copy a database from my local machine to a remote server.

Dump local database:
pg_dump dbname --clean -U postgres > dumpfile

Restore remote database:
psql --single-transaction dbname -U postgres < dumpfile

This does a perfect replication.

How do I modify this to ignore particular table names, both in the source and destination?

This will be useful for tables that log website visits. I want to retain my existing remote visit logs, while ignoring my local "visit" logs (which is just me visiting my own website locally).

Upvotes: 12

Views: 12101

Answers (1)

Hambone
Hambone

Reputation: 16397

Using the -t switch, you can be selective about what tables to include:

pg_dump <your switches> -t my_schema.awesome* -f backup.bat postgres

Which will only include those tables.

Likewise, the -T switch will do the opposite -- dump everything but the tables you specified:

pg_dump <your switches> -T my_schema.lame* -f backup.bat postgres

You can use each switch multiple times as well:

pg_dump <your switches> \
    -t my_schema.awesome* \
    -t my_schema.amazing* \
    -t my_schema.great -f backup.bat postgres

When you restore, it won't bother trying to restore something it didn't back up, so that part should be handled somewhat natively.

Upvotes: 24

Related Questions