PureDevOps Community

How to exclude some tables during pg_restore or postgress restore

Storage And Backup
#1

pg_restore command does not have option to exclude tables but there is a option to include the tables. This will get complex when there are quite lot of tables out there. Below is the solution which really worked for me ,

pg_restore -O -x -L <(pg_restore -l ./evil-plans-database.dump | grep -ivE 'TABLE DATA public (event_log|search_log)') -d postgres://localhost/postgres ./evil-plans-database.dump

Explanation:

  1. pg_restore -l ./evil-plans-database.dump: Dumping the tables that are in the db dump file
  2. grep -ivE 'TABLE DATA public (event_log|search_log): Piping the list into grep and excluding lines that match our table names
  3. pg_restore -O -x -L <(PREVIOUS_COMMAND) -d postgres://localhost/postgres ./evil-plans-database.dump: Piping the updated list to -L flag of our restore command.

Ref: Exclude tables when restoring a PostgreSQL Database dump