Export and import PostgreSQL databases

This article describes how to back up a PostgreSQL database using pg_dump and pg_dumpall and how to restore PostgreSQL database using pg_restore.

PostgreSQL provides pg_dump and pg_dumpall tools to help you easily back up your database, and PostgreSQL provides pg_restore tool to help you easily restore your database.

Backup and recovery are must-have skills as a database administrator. PostgreSQL provides us with many convenient tools or commands to do this.

Tools or commands to backup databases:

  • The pg_dump tool is used to back up a single PostgreSQL database
  • The pg_dumpall tool is used to backup all databases in the PostgreSQL server.

Tools or commands to restore databases:

  • The pg_restore tool is used to restore tar archives and catalog files produced by the pg_dump tool.
  • The psql tool can import SQL script files generated by the pg_dump and pg_dumpall tool.
  • The \i command can import SQL script files generated by the pg_dump and pg_dumpall tools.

Back up a database using pg_dump

PostgreSQL provides the pg_dump tool for backing up a single PostgreSQL database. The following is a commonly used backup command:

pg_dump -U username -W -F t db_name > output.tar

Explanation:

  • -U username: Specify the user to connect to the PostgreSQL database server. You can use your own username at the username location.

  • -W: Force prompt for password before connecting to PostgreSQL database server. After pressing Enter, you will be prompted to enter the user password.

  • -F: Specify the format of the output file, which can be one of the following:

    • c: Custom format
    • d: Directory archive format
    • t: tar archive file
    • p: SQL script file

  • db_name is the name of the database to be backed up.

  • output.tar is the path to the output file.

If you run the command in the command line or terminal tool and you are prompted that the pg_dump tool is not found, please navigate to the PostgreSQL bin folder first. E.g:

C:\>cd C:\Program Files\PostgreSQL\14\bin

Backup all databases using pg_dumpall

In addition to the pg_dump tool, PostgreSQL also provides the pg_dumpall tool for backing up all databases. The usage of the pg_dumpall tool is as follows:

pg_dumpall -U username > output.sql

Restore a database using pg_restore

PostgreSQL provides the pg_restore tool for restoring tar files and catalog files produced by the pg_dump tool.

The usage of the pg_restore tool is as follows:

pg_restore [option...] file_path

Explanation:

  • The file_path is the path of the file or directory to restore.
  • The option are some options used when restoring data, such as database, host, port, etc. You can use options like:
    Option Description
    -a --data-only Only restore data, not the table schema (data definition).
    -c --clean Clean up (delete) database objects before creating them.
    -C --create Create the database before restoring it.
    -d dbname --dbname=dbname Connect to the dbname database and restore directly into that database.
    -e --exit-on-error Exits if an error is encountered while sending the SQL command to the database. The default is to continue execution and display an error count at the end of recovery.
    -f filename --file=filename Declare the output file of the generated script, or the file used for listing when the -l option is present, defaults to standard output.
    -F format --format=format Declare the format of the backup file.
    -i --ignore-version Ignore database version checks.
    -I index --index=index Only named indexes are restored.
    -l --list List the contents of the backup. The output of this operation can be limited and rearranged with the -L option to restore items.
    -L list-file --use-list=list-file Restore only the elements in list-file, in the order they appear in the file.
    -n namespace --schema=schema Only restore definitions and/or data in the named schema. Not to be confused with the -s option. This option can be used with the -t option.
    -O --no-owner Do not output a command that sets the object’s permissions to match the original database.
    -s --schema-only Only the table structure (data definition) is restored. Without restoring the data, the sequence value will be reset.
    -S username --superuser=username The username that declares the superuser when setting the close trigger. Only useful if –disable-triggers is set.
    -t table --table=table Restores only the definition and/or data of the table specified by the table.
    -T trigger --trigger=trigger Only the specified trigger is restored.
    -v --verbose Declare redundant mode.
    -x --no-privileges --no-acl Avoid ACL recovery (grant/revoke commands)
    -X use-set-session-authorization --use-set-session-authorization Outputs the SQL standard SET SESSION AUTHORIZATION command, not the OWNER TO command.
    -X disable-triggers --disable-triggers This option is only relevant when performing restore-only data.
    -h host --host=host Declare the hostname of the machine on which the server runs.
    -p port --port=port Declares the TCP port or local Unix domain socket file extension on which the server listens.
    -U username Connect as the given user.
    -W Force a password prompt. This should happen automatically if the server requires password authentication.

The most common usage of pg_restore is as follows:

pg_restore -d db_name path_to_db_backup_file.tar

How to restore a database using psql

You can use the psql tool to restore data from a sql file. The following is a basic usage of using psql to restore data from a sql file:

psql -U username -f path_to_db_backup_file.sql

Import the sql file using the \i command

You can also use \i command import sql files. The following demonstrates the steps to import the sakila sample database:

  1. Start the psql tool and connect to the PostgreSQL server:

    .\psql.exe -U postgres

    Enter the password for the postgres user when prompted and press Enter.

  2. Create the sakila database

    CREATE DATABASE sakila;

  3. Connect to sakila database

    \c sakila;

  4. Use the following two statements to import the two files postgres-sakila-schema.sql and postgres-sakila-insert-data.sql:

    \i C:/Users/Adam/Downloads/postgres-sakila-schema.sql
\i C:/Users/Adam/Downloads/postgres-sakila-insert-data.sql

    Note that replace \ in the file path with /.

Conclusion

This article discussed several methods for backing up and restoring PostgreSQL databases.