Sometimes when restoring a multi-schema database from a backup file, you may want to exclude one or more schemas, for one reason or the other.
For example, if you are restoring a huge database for development purposes, you may not want to restore a schema that you know has lots of data, which might cause the restoration process to take so long.
pg_restore the command-line utility allows for excluding one or more schemas when restoring a database from a backup file created by pg_dump. You can use its -N or --exclude-schema option to restore objects in the named schema.
PostgreSQL supports various useful database backup and restores features. In this guide, we will show how to exclude a schema while restoring a PostgreSQL multi-schema database from a backup file.
Exclude a Schema While Restoring a PostgreSQL Database
Before you start the restoration process, in case you are performing it on a new server, make sure that the owner or user of the database as defined in the backup is created on the server:
$ pg_restore -d myappdb -N schema_name myappdb.dump OR $ pg_restore -d myappdb --exclude-schema=schema_name myappdb.dump
In the command above, the flag:
-d– is used to specify the target database name.-N– specifies the name of the schema to exclude during the restoration process.myappdb.dump– is the database backup file name. Note that the file should be in one of the non-plain-text formats as created by pg_dump.
To exclude multiple schemas, use multiple -N flags as shown.
$ pg_restore -d myappdb -N schema_name1 -N schema_name2 -N schema_name3 myappdb.dump OR $ pg_restore -d myappdb --exclude-schema=schema_name1 --exclude-schema=schema_name2 --exclude-schema=schema_name3 myappdb.dump
You can tell pg_restore to create the database specified by the -d option if it doesn’t exist, by using the -C or --create switch as follows:
$ pg_restore -d myappdb -C -N schema_name myappdb.dump OR $ pg_restore -d myappdb --create -N schema_name myappdb.dump
You should note that in case the -C switch is used, as in the previous command, the database name myappdb specified using the -d switch is only employed to run the initial “DROP DATABASE myappdb” and “CREATE DATABASE myappdb” commands, all the data is restored into the database name that exists in the backup file.
To drop or clean and recreate the target database before connecting to it, use the --clean option as shown.
$ pg_restore --clean -d testdb -n schema_name myappdb.dump
By default, if there is an error encountered while running the SQL command during the restoration process, pg_restore will continue with the process and simply display the errors.
You can let pg-restore terminate when an error is encountered by adding the -e or --exit-on-error option:
$ pg_restore -e -d testdb -n schema_name myappdb.dump OR $ pg_restore --exit-on-error -d testdb -n schema_name myappdb.dump
To make the restoration process faster, you can use the -j or --number-of-jobs to run concurrently. This option ensures that steps such as creating indexes, creating constraints, or loading data will be executed concurrently using concurrent sessions of up to the specified number of jobs.
However, it highly depends on the number of CPUs core and disk configuration on the client, server, and network:
$ pg_restore -j 4 --clean -d testdb -n schema_name myappdb.dump OR $ pg_restore --number-of-jobs=4 --clean -d testdb -n schema_name myappdb.dump
You can read more about pg_restore by viewing its man page as shown.
$ man pg_restore
That’s all we had for you in this short guide. If you have any comments or questions, use the feedback form below to reach us.
