How To Exclude a Schema While Restoring a PostgreSQL Database

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
$ 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
$ 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
$ 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
$ 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
$ 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.

Hey TecMint readers,

Exciting news! Every month, our top blog commenters will have the chance to win fantastic rewards, like free Linux eBooks such as RHCE, RHCSA, LFCS, Learn Linux, and Awk, each worth $20!

Learn more about the contest and stand a chance to win by sharing your thoughts below!

Aaron Kili
Aaron Kili is a Linux and F.O.S.S enthusiast, an upcoming Linux SysAdmin, web developer, and currently a content creator for TecMint who loves working with computers and strongly believes in sharing knowledge.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.