3.6. Backing up and restoring PostgreSQL data with logical dumps

• An SQL dump is the only PostgreSQL backup method that is not server version-specific. The output of the pg_dump utility can be reloaded into later versions of PostgreSQL, which is not possible for file system level backups or continuous archiving.
• An SQL dump is the only method that works when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server.
• An SQL dump provides internally consistent dumps. A dump represents a snapshot of the database at the time pg_dump began running.
• The pg_dump utility does not block other operations on the database when it is running.

• An SQL dump takes more time compared to a file system level backup.

Prerequisites

• You are logged in as the postgres superuser or a user with database administrator privileges.

Procedure

• Dump a database without cluster-wide information:

  $ pg_dump <db_name> > <dump_file>

  To specify which database server pg_dump will contact, use the following command-line options:

  • The -h option to define the host.

    The default host is either the local host or what is specified by the PGHOST environment variable.

  • The -p option to define the port.

    The default port is indicated by the PGPORT environment variable or the compiled-in default.

Prerequisites

• You are logged in as the postgres superuser or a user with database administrator privileges.

Prerequisites

• You are logged in as the postgres superuser or a user with database administrator privileges.

Procedure

• Dump all databases in the database cluster and preserve cluster-wide data:

  $ pg_dumpall > <dump_file>

  To specify which database server pg_dumpall will contact, use the following command-line options:

  • The -h option to define the host.

    The default host is either the local host or what is specified by the PGHOST environment variable.

  • The -p option to define the port.

    The default port is indicated by the PGPORT environment variable or the compiled-in default.

  • The -l option to define the default database.

    This option enables you to choose a default database different from the postgres database created automatically during initialization.

Prerequisites

• You are logged in as the postgres superuser or a user with database administrator privileges.

Prerequisites

• You are logged in as the postgres superuser or a user with database administrator privileges.

Procedure

• Make psql exit with an exit status of 3 if an SQL error occurs by setting the ON_ERROR_STOP variable:

  $ psql --set ON_ERROR_STOP=on <db_name> < <dump_file>

• Specify that the whole dump is restored as a single transaction so that the restore is either fully completed or canceled.

  • When restoring a text file dump by using the psql utility:

    $ psql -1

  • When restoring a non-text file dump by using the pg_restore utility:

    $ pg_restore -e

  Note that when you use this approach, even a minor error can cancel a restore operation that has already run for many hours.