Back to Commands

pg_restore

databaseLinux/Unix
The pg_restore command is one of the most frequently used commands in Linux/Unix-like operating systems. pg_restore Restore a PostgreSQL database from an archive file created by pg_dump
See ExamplesView Syntax

Quick Reference

Command Name:

pg_restore

Category:

database

Platform:

Linux/Unix

Basic Usage:

pg_restore [options] [arguments]

Common Use Cases

    Syntax

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

    Options

    Option Description
    -a, --data-only Restore only the data, not the schema
    -c, --clean Clean (drop) database objects before recreating
    -C, --create Create the database before restoring into it
    -d, --dbname=NAME Connect to database NAME
    -e, --exit-on-error Exit if an error is encountered
    -f, --file=FILENAME Output file name (for scripts) or directory (for directory format)
    -F, --format=c|d|t Backup file format (should be automatic)
    -I, --index=NAME Restore named index
    -j, --jobs=NUM Use this many parallel jobs to restore
    -l, --list List the contents of the archive
    -L, --use-list=FILENAME Restore only objects in list file
    -n, --schema=SCHEMA Restore only objects in the named schema
    -N, --exclude-schema=SCHEMA Do not restore objects in the named schema
    -O, --no-owner Skip restoration of object ownership
    -P, --function=NAME(args) Restore named function
    -s, --schema-only Restore only the schema, no data
    -S, --superuser=NAME Superuser user name to use for disabling triggers
    -t, --table=TABLE Restore named relation (table, view, etc.)
    -T, --trigger=NAME Restore named trigger
    -v, --verbose Verbose mode
    -x, --no-privileges Skip restoration of access privileges (grant/revoke)
    --disable-triggers Disable triggers during data-only restore
    --no-data-for-failed-tables Do not restore data of tables that could not be created
    --no-security-labels Do not restore security labels
    --no-tablespaces Do not restore tablespace assignments
    --section=SECTION Restore named section (pre-data, data, or post-data)
    --use-set-session-authorization Use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands

    Connection Options:

    Option Description
    -h, --host=HOSTNAME Database server host or socket directory
    -p, --port=PORT Database server port number
    -U, --username=NAME Connect as specified database user
    -w, --no-password Never prompt for password
    -W, --password Force password prompt
    --role=ROLENAME Do SET ROLE before restore

    Examples

    How to Use These Examples

    The examples below show common ways to use the pg_restore command. Try them in your terminal to see the results. You can copy any example by clicking on the code block.

    # Basic Examples Basic
    pg_restore -d mydb mydb.dump
    Restore a database from a custom format dump file.
    pg_restore -U username -h hostname -d dbname backup.dump
    Restore a remote database with specified username and host.
    # Advanced Examples Advanced
    pg_restore -C -d postgres mydb.dump Create the database before restoring into it. pg_restore -l mydb.dump > restore.list List the contents of a dump file without restoring. pg_restore -L restore.list -d mydb mydb.dump Restore only the objects specified in restore.list. pg_restore -t mytable -d mydb mydb.dump Restore only a specific table. pg_restore -n myschema -d mydb mydb.dump Restore only a specific schema. pg_restore -j 4 -d mydb mydb.dump Restore using 4 parallel jobs for faster restoration. pg_restore -c -d mydb mydb.dump Clean (drop) database objects before recreating them. pg_restore -e -d mydb mydb.dump Exit with error code 1 if any errors occur during restore.

    Try It Yourself

    Practice makes perfect! The best way to learn is by trying these examples on your own system with real files.

    Understanding Syntax

    Pay attention to the syntax coloring: commands, options, and file paths are highlighted differently.

    Notes

    The pg_restore command is a specialized utility for restoring PostgreSQL databases from archive files created by pg_dump in one of the non-plain-text formats (custom, directory, or tar format). It provides significant advantages over using plain SQL dumps, including parallel restoration, selective restoration of database objects, and the ability to reorder operations for more efficient processing. Developed by the PostgreSQL Global Development Group, pg_restore is designed to complement pg_dump, offering flexible restoration options that are particularly valuable for large databases or complex recovery scenarios. While plain text SQL dumps from pg_dump can be directly fed into psql, the custom, directory, and tar formats require pg_restore for proper restoration. Key features of pg_restore include: 1. Selective Restoration: One of pg_restore's most powerful features is its ability to restore only specific database objects (tables, schemas, functions, etc.), making it useful for partial restores or merging data across databases. 2. Parallel Processing: For large databases, pg_restore can use multiple parallel processes to restore data, significantly reducing restoration time on multi-processor systems. 3. Reordering: pg_restore can automatically reorder restoration operations to maximize efficiency, such as creating indexes after loading data rather than before. 4. Format Support: The utility works with all non-plain-text formats produced by pg_dump, including the custom format (.backup or .dump files), directory format, and tar format. 5. Preview Capability: Users can list the contents of an archive without actually restoring anything, useful for examining what's in a backup before proceeding. 6. Script Generation: pg_restore can output a script that can be reviewed or modified before execution, rather than directly restoring to a database. 7. Flexible Connection Options: The command supports various connection parameters to connect to local or remote PostgreSQL servers. Common use cases for pg_restore include: - Restoring databases after a system failure or data corruption - Migrating databases between servers or PostgreSQL versions - Creating development or testing copies of production databases - Selectively restoring specific database objects - Performing faster restores of large databases using parallelism - Merging data or structures from one database to another When using pg_restore, it's important to understand the interaction between various options. For example, combining the --clean option (which drops objects before recreating them) with --if-exists can prevent errors when restoring to a database that doesn't already contain the objects being dropped. Additionally, pg_restore can be used in two primary modes: connecting directly to a database or generating SQL scripts. When no database connection is specified, pg_restore produces SQL commands to stdout, which can be captured to a file and reviewed before execution. This script-generation capability provides an extra level of control over the restoration process. For the most efficient restores, especially of large databases, users often combine several options like --jobs for parallelism, --no-owner to avoid ownership issues in different environments, and --section to control exactly which parts of the database are restored in what order.

    Related Commands

    These commands are frequently used alongside pg_restore or serve similar purposes:

    pg_dump
    pg_dumpall
    psql
    createdb
    dropdb
    Explore All Commands

    Use Cases

    Learn By Doing

    The best way to learn Linux commands is by practicing. Try out these examples in your terminal to build muscle memory and understand how the pg_restore command works in different scenarios.

    See ExamplesTry Lab Exercises
    $ pg_restore
    View All Commands