How To Restore a Schema from a PostgreSQL Database

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