How to ignore PostgreSQL tables when restoring a database

Tagged As:

At my current workplace, we use a copy of the backup database which is created by pg_dump to restore in our development machines. Since the database has a lot of code>audit files which adds up a lot of space, I needed an easier way to ignore these files when restoring the database.

Apparently pg_restore can generate a list of the content in the dump file archive. The advantage is that we can use the same list to specify what content we need to restore.

pg_restore options

Here are the pg_restore options we are going to use for this.

-l --list
List the contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed.

-L list-file --use-list=list-file
Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

Steps

Let’s generate the list while ignoreing the content for the tables we don’t need. Note that I use an inverse grep statement to ignore the content of the tables where the table names that ending with _audits and create the output in restore.list file.

pg_restore -l <dump_file.data> | grep -v 'TABLE DATA public .*_audits' > restore.list

Then simply use -L to tell pg_restore to use the restore.list file.

pg_restore -U #{USERNAME} -O -L restore.list -v -c -d atlas_development PostgreSQL.sql

 

Ref
pg restore options: https://www.postgresql.org/docs/10/static/app-pgrestore.html

Leave a Reply

Be the First to Comment!

avatar
  Subscribe  
Notify of