intro
Let’s learn how to import a PostgreSQL database backup with the pg_restore command-line utility.
As you may already know, pg_dump is a versatile tool for creating database backups in PostgreSQL.
If there is a tool helping you to create backups, there must also be a tool to restore these backups. This is exactly the purpose of the pg_restore command line utility!
In this guide packed with numerous examples, you will learn what pg_restore is, how it allows you to restore a PostgreSQL backup, and what options it supports.
Become a backup restoration master in PostgreSQL!
What Is pg_restore?
pg_restore is a command-line utility for restoring a PostgreSQL database from an archive in a non-plain-text format created with pg_dump. Specifically, it launches all the commands required to reconstruct a database to the state it was in at the time of the dump.
The restore process changes based on whether a database name is specified or not:
Keep in mind that pg_restore can only follow the instructions contained in the archive file generated with pg_dump. This means if the dump contains INSERT statements, the data restoration tool will not be able to load the data using COPY statements.
How to Use the PostgreSQL pg_restore Utility: Syntax and Options
The pg_restore syntax in the command line is:
1
pg_restore [options] [filename]
The optional parameters are:
Here is a list of the most important and commonly used pg_restore options:
For the full list of options available, read the official documentation. Note that these options are case sensitive.
Tip: You can specify the -I, -n, and -T options multiple times to restore multiple indexes, schemas, and tables at once, respectively.
Now, take a look at the pg_restore example below:
1
pg_restore -U admin -d organization db_dump.tar
The above CLI command will connect to the organization database in the local PostgreSQL server. It will log in as the admin user, and you will be prompted for the user's password. Then, it will try to import the db_dump.tar archive into organization.
pg_restore Example List
Now that you know how to use the PostgreSQL restore utility, it is time to see a complete pg_restore example list!
Note: In the following CLI sample commands, the user will always be admin, the database name company, and the file name db_dump.sql. Modify these fields accordingly to make the instructions below work in your specific scenario.
Import Data Only
1
pg_restore -U admin -d company --data-only db_dump.sql
The company database will be filled with the data contained in db_dump.tar. To see what is going on, enable the verbose mode:
1
pg_restore -U admin -d company -v --data-only db_dump.sql
Import Schema Only
1
pg_restore -U admin -d company --schema-only db_dump.sql
This pg_restore example command only creates the schema in the company database. For a clean import, specify the -c flag:
1
pg_restore -U admin -d company -c --schema-only db_dump.sql
Import Only a Few Schemas
Given a PostgreSQL backup, suppose you only want to load the schemas sales and finance. You can achieve that with:
1
pg_restore -U admin -d company -n 'sales' -n 'finance' db_dump.sql
Note that -n can be specified multiple times in the same pg_restore command.
Import Only Some Tables
Assume you want to import only the orders table from the sales schema. Achieve that goal with:
1
pg_restore -U admin -d company -n 'sales' -t 'orders' db_dump.sql
Now we will get into the limitations that will dawn upon you after you start using pg_restore. The limitations are also listed in the PostgreSQL documentation, but we will summarize everything below.
Main pg_restore Limitations
This is a list of the main limitations of pg_restore:
Conclusion
In this guide, you understood what pg_restore is and what options it supports. This PostgreSQL command-line utility helps you restore backups generated via the pg_dump command. There are a lot of pg_restore options, and here you had the opportunity to see the most important ones with examples.
Creating and restoring database backups is essential but not easy to deal with. That is why you should opt for an advanced database client solution like DbVisualizer! In addition to supporting dozens of DBMS technologies, DbVisualizer enables you to export your databases and then import them with just a few clicks. It also provides advanced query optimization capabilities and can generate ERD-like schemas. Try DbVisualizer for free today!
FAQ
How long does pg_restore take to restore a PostgreSQL backup?
The time pg_restore takes to restore a PostgreSQL backup varies based on factors such as database size, server resources, and the restore method used. However, it usually ranges from a few minutes to several hours for large databases.
What is the path to pg_restore.exe?
On Windows, you can typically find the pg_restore.exe file in the bin folder of your PostgreSQL installation. The complete path to pg_restore.exe should be:
1
C:\Program Files\PostgreSQL\<version>\bin\pg_restore.exe
Replace <version> with the version number of your PostgreSQL local installation.
Is it possible to run pg_restore on a remote server?
Yes, you can run pg_restore on a remote server by following this procedure:
Make also sure that the PostgreSQL server allows remote connections. Otherwise, adjust the firewall settings. To connect to the server, you will need to provide a password or use SSH tunneling.
How to make pg_restore ignore duplicates?
By default, pg_restore restores data even if the creation command for a table fails (e.g. because it already exists). By setting the --no-data-for-failed-tables option, data for such tables will be skipped. This behavior prevents duplicate or obsolete data from being loaded into existing tables in the database.
How to perform a parallel PostgreSQL backup restore with pg_restore?
To perform a parallel PostgreSQL backup restore, pass the -j or --jobs option followed by the number of parallel jobs to pg_restore. This will run most of the most time-consuming steps of a restore, such as loading data, creating indexes, and creating constraints, concurrently. In particular, it will use up to the number of concurrent sessions passed to the option.
