info

Restoring databases with pg_restore works the same on any PostgreSQL deployment, so you can apply these steps wherever your Postgres runs. If you're an enterprise team looking for managed Postgres that's built for the AI era, Lakebase delivers high performance, strong security, and deep integration with the Lakehouse. If you're a developer or startup that needs to ship quickly and scale without friction, Neon is the Postgres platform designed for that pace.

Summary: in this tutorial, you will learn how to restore a database by using a PostgreSQL restore tool called pg_restore.

Introduction to the PostgreSQL pg_restore tool

To perform a logical backup of a PostgreSQL database, you use the pg_dump tool. To back up all the databases on a PostgreSQL cluster, you use the pg_dumpall tool.

Both pg_dump and pg_dumpall tools create a snapshot of one or all databases at the time the command starts running.

To restore a database created by the pg_dump or pg_dumpall tools, you can use the pg_restore tool.

The pg_restore tool allows you to restore the PostgreSQL database from an archive file.

Here’s the syntax of the pg_restore command:

pg_restore [connection-option] [option] [filename]

The following table presents the most commonly used command-line options for the pg_restore utility:

OptionDescription
-U, –username=USERNAMESpecifies the username that you use to connect to the PostgreSQL server.
-h, –host=HOSTNAMESpecifies the hostname of the server on which the PostgreSQL server is running.
-p, –port=PORTSpecifies the port number on which the PostgreSQL server is listening.
-d, –dbname=DBNAMESpecifies the name of the database that you want to restore into.
-t, –table=TABLESpecifies one or more tables that you want to restore. If you restore multiple tables, you need to separate them by commas.
-v, –verboseShows verbose output that including information about the objects being restored.
-c, –cleanDrops existing database objects from the database before restoring the dump.
-C, –createCreates the database before restoring it.
-e, –exit-on-errorStops the restore process in case of an error.
-F, –format=FORMATSpecifies the format of the input file (e.g., custom, directory, tar).
-j, –jobs=NUMSpecifies the number of parallel jobs to use when restoring data.
-n, –schema=SCHEMASpecifies one or more schema of the database objects that you want to restore the objects. If you restore objects from multiple schemas, you need to separate them by commas.
-L, –use-list=FILENAMESpecifies a file containing a list of files that you want to restore.
-t, –tablespace=TABLESPACESpecifies the tablespace for the tables that you want to restore.
-v, –versionShows the version of pg_restore and exits.
-?, –helpShows help and usage information.

PostgreSQL Restore Database example

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, back up the dvdrental database to a directory such as D:\backup\

pg_dump -U postgres -d dvdrental -F tar -f d:\backup\dvdrental.tar

It’ll prompt you to enter the password for the user postgres. After entering a valid password, the pg_dump will create an archive file dvdrental.tar in the D:\backup file.

Third, connect to the PostgreSQL server:

psql -U postgres

Fourth, drop the dvdrental database:

drop database dvdrental;

Fifth, create a new empty dvdrental database:

create database dvdrental;

Sixth, exit the psql:

exit

Seven, restore the dvdrental database from the backup file using the pg_restore tool:

pg_restore -U postgres -d dvdrental D:/backup/dvdrental.tar

Eight, connect to the dvdrental database:

psql -U postgres -d dvdrental

Ninth, show the tables:

\dt

It returns all the tables in the dvdrental database.

Finally, exit the psql:

exit

Summary

  • Use the pg_restore tool to restore a PostgreSQL database from an archive file.