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 audit tables which adds up a lot of space, I needed an easier way to ignore these tables when restoring the database.
Apparently pg_restore can generate a list of tables 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 PostgreSQL.sql | 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 mydb_development PostgreSQL.sql
Ref
pg restore options: https://www.postgresql.org/docs/10/static/app-pgrestore.html