pg_restore(1)
Restore a PostgreSQL database from an archive file created by pg_dump.
Synopsis
pg_restore [OPTION]... [FILE]Description
pg_restore is a utility for restoring a PostgreSQL database from an archive file. It reads the archive produced by pg_dump and reconstructs the database, restoring tables, indexes, triggers, functions, and other database objects. It supports both custom binary format and tar format archives.
Unlike psql which executes SQL scripts, pg_restore works with pg_dump's special archive formats, allowing selective restoration of database objects and parallel processing for faster recovery.
Common options
| Flag | What it does |
|---|---|
-d, --dbname=DBNAME | Connect to database DBNAME and restore directly into it |
-f, --file=FILENAME | Write output to file FILENAME instead of stdout |
-j, --jobs=NUM | Execute NUM restore jobs in parallel (faster for large databases) |
-l, --list | List the table of contents of the archive and exit |
-t, --table=TABLENAME | Restore only named table; can be used multiple times |
-T, --exclude-table=TABLENAME | Exclude specified table from restore |
-s, --schema-only | Restore only schema (no data rows) |
-a, --data-only | Restore only data, not schema objects |
-c, --clean | Drop database objects before recreating them (use before restore) |
-e, --exit-on-error | Exit on first error instead of continuing |
-h, --host=HOSTNAME | Database server hostname or socket directory |
-U, --username=USERNAME | Connect as PostgreSQL user USERNAME |
Examples
Restore archive backup.dump directly into database mydb
pg_restore -d mydb backup.dumpConvert custom format archive to SQL script and save to restore.sql
pg_restore -f restore.sql backup.dumpList the table of contents of the archive to preview what will be restored
pg_restore -l backup.dump | head -20Restore with 4 parallel jobs, dropping existing objects first
pg_restore -d mydb -j 4 -c backup.dumpRestore only the users and orders tables from the archive
pg_restore -d mydb -t users -t orders backup.dumpRestore only the schema (tables, indexes, etc.) without data
pg_restore -d mydb -s backup.dumpRestore only data into existing schema (useful for refreshing data)
pg_restore -d mydb -a backup.dumpRestore to remote PostgreSQL server with specified user
pg_restore -h db.example.com -U postgres -d mydb backup.dump