How to Use pg_restore in PostgreSQL: Syntax and Examples

Use pg_restore to restore a PostgreSQL database from a backup file created by pg_dump. The basic command is pg_restore -d database_name backup_file, which loads the backup into the specified database. You can customize options like host, user, and format to control the restore process.

Syntax

The basic syntax of pg_restore is:

• pg_restore [options] backup_file
• -d database_name: Specifies the target database to restore into.
• -h host: The database server host (optional).
• -U username: The user to connect as (optional).
• -F format: Format of the backup file (custom, tar, directory).
• -v: Verbose mode to show detailed output.

This command reads the backup file and restores the database objects and data.

bash

pg_restore -d database_name backup_file

Example

This example restores a backup file named mydb.backup into a database called mydb. It connects as user postgres on localhost and shows verbose output.

bash

pg_restore -U postgres -h localhost -d mydb -v mydb.backup

Output

pg_restore: connecting to database for restore
pg_restore: creating TABLE public.users
pg_restore: creating TABLE public.orders
pg_restore: finished item processing
pg_restore: finished restore

Common Pitfalls

Common mistakes when using pg_restore include:

• Trying to restore into a database that does not exist. You must create the database first.
• Using the wrong format option if the backup is not in the default format.
• Not having proper permissions or connection settings, causing authentication errors.
• Restoring into a non-empty database without cleaning it first can cause conflicts.

Always check the backup format and create the target database before restoring.

bash

/* Wrong: restoring without creating database */
pg_restore -d mydb mydb.backup

/* Right: create database first, then restore */
createdb mydb
pg_restore -d mydb mydb.backup

Quick Reference

Option	Description
-d database_name	Target database to restore into
-U username	Database user name
-h host	Database server host
-F format	Backup file format (c=custom, t=tar, d=directory)
-v	Verbose mode to show detailed progress
-C	Create the database before restoring
-j jobs	Number of parallel jobs for faster restore

Key Takeaways

Always create the target database before running pg_restore unless using -C option.

Use the correct backup format option (-F) matching your backup file.

Run pg_restore with -v for detailed progress and easier troubleshooting.

Ensure you have proper permissions and connection details to the PostgreSQL server.

Use parallel jobs (-j) to speed up restore for large backups.