How To Restore a Particular Schema from a PostgreSQL Database Backup

If you intend to restore only one or a few schemas from a PostgreSQL backup file, you can use the pg_restore command, which is used for restoring a particular PostgreSQL database from an archive created by pg_dump in non-plain-text formats.

In this guide, we will show how to restore a particular schema from a PostgreSQL database backup file using the pg_restore command-line tool.

Restoring Database Schema from PostgreSQL Database

Here is an example pg_restore command that restores a selected schema from a PostgreSQL database backup file:

Let’s look at the meaning of each option in the above command:

  • -d – defines the target database name which must exist on the server, pg_restore connects to it and restores directly into the database.
  • -n or --schema – defines the name of the schema to be restored, it instructs pg_restore to restore only objects that are in the named schema.
  • backup.dump – the name of the database backup file. In this case, the backup is in a custom format, one of the formats supported by the pg_dump tool.

Restoring Multiple Schemas from PostgreSQL Database

To restore multiple schemas, use multiple -n as shown.

$ pg_restore -d testdb -n schema_name1 -n schema_name2 -n schema_name3 backup.dump
$ pg_restore -d testdb --schema=schema_name1 --schema=schema_name2 --schema=schema_name3 backup.dump

If you are restoring the backup file on a new server, ensure that the owner or user of the database as defined in the backup is created on the server before the restoration process is initiated.

pg_restore Command Options

There are several other valuable pg_restore command-line options that you can use while performing a database restoration, we will cover a few below.

One useful option is the -C or --create option which you can use to instruct pg_restore to create the database (specified using the -d option) in case it doesn’t exist on the cluster before restoring it.

Here is an example command:

$ pg_restore -d testdb -C -n schema_name  backup.dump
$ pg_restore -d testdb --create -n schema_name backup.dump

Note: When the -C option is employed, the database name testdb (in the above command) is only used to run the initial “DROP DATABASE testdb” and “CREATE DATABASE testdb” commands, but the data is restored into the database name that appears in the backup file.

Furthermore, if you use the --clean option, pg_restore will clean (drop) and recreate the target database before connecting to it.

$ pg_restore --clean -d testdb -n schema_name backup.dump

Additionally, you can also specify the number of jobs to run concurrently while performing the restoration, using the -j or --number-of-jobs. This flag tells pg_restore to run time-consuming steps such as loading data, creating indexes, or creating constraints concurrently using concurrent sessions of up to the specified number of jobs:

$ pg_restore -j 4 --clean -d testdb -n schema_name backup.dump
$ pg_restore --number-of-jobs=4 --clean -d testdb -n schema_name backup.dump

The above option is affected by hardware factors such as the number of CPU cores and disk setup on the server, client, and network. Besides, it only supports the custom and directory archive formats.

For more information, check out the pg_restore man page as shown.

$ man pg_restore

That’s all! pg_restore is one of the handy command-line tools for the PostgreSQL database management system. In this article, we have looked at how to restore a particular schema from a PostgreSQL database backup file.

For any queries or comments concerning this guide, 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.