PostgreSQL: Backup and Restore

Last update on November 13 2023 13:01:29 (UTC/GMT +8 hours)

Introduction

A backup is a copy of data from your database that can be used to reconstruct that data. Backups are backups of the physical files used in storing and recovering your databases, such as datafiles, control files, and others. Every physical backup is a copy of files storing database information to some other location, whether on disk or some offline storage such as tape.

Benefits of backup:

With valid backups of a database, you can recover your data from many failures, such as :

Media failure : A media failure is the failure of a read or write of a disk file required to run the database, due to a physical problem with the disk such as a head crash.
Hardware failures, for example, a damaged disk drive.
User errors, for example, dropping a table by mistake.
Natural disasters.

The PostgreSQL server backup and restore component provide an essential safeguard for protecting critical data stored in server databases. To minimize the risk of data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss. Test your strategy by restoring a set of backups and then recovering your database.

Table of contents

Backup PostgreSQL database

pg_dump

Restore PostgreSQL database

pg_restore

Backup a PostgreSQL Database

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

You can use the pg_dump command line program, or you can use phpPgAdmin to backup a PostgreSQL database to a file.

Access the command line on the computer where the database is stored. If you have physical access to the computer, you can open a DOS or terminal window to access the command line.

Type the following command, and then press ENTER. Replace USERNAME with your username, and DBNAME with the name of the database that you want to export:

Syntax:

pg_dump -U USERNAME DBNAME > Mybackup.pgsql

Type your password at the Password prompt.

The Mybackup.pgsql file now contains all of the data for the DBNAME database. If the Mybackup.pgsql file is on a remote computer, download the file to your local computer.

Complete syntax and other options of pg_dump

Syntax:

pg_dump [connection-option...] [option...] [dbname]

Option	Description
dbname	Name of the database to be dumped.
-a --data-only	Dump only the data (table data, large objects, and sequence values), not the schema (data definitions) This option is similar to, but for historical reasons not identical to, specifying --section=data.
-b --blobs	Include large objects in the dump (default behavior).
-c --clean	Output commands to clean (drop) database objects prior to outputting the commands for creating them. This option is only meaningful for the plain-text format.
-C --create	Begin the output with a command to create the database itself and reconnect to the created database. This option is only meaningful for the plain-text format.
-E encoding --encoding=encoding	Create the dump in the specified character set encoding (default, database encoding).
-f file --file=file	Send output to the specified file.
-F format --format=format	Selects the format of the output. The format can be one of the following : p plain : Output a plain-text SQL script file (the default). c custom : Output a custom-format archive suitable for input into pg_restore. d directory : Output a directory-format archive suitable for input into pg_restore. t tar : Output a tar-format archive suitable for input into pg_restore.
-j njobs --jobs=njobs	Run the dump in parallel by dumping njobs tables simultaneously.
-n schema --schema=schema	Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped.
-N schema --exclude-schema=schema	Do not dump any schemas matching the schema pattern. When both -n and -N are given, the behavior is to dump just the schemas that match at least one -n switch but no -N switches. If -N appears without -n, then schemas matching -N are excluded from what is otherwise a normal dump.
-o --oids	Dump object identifiers (OIDs) as part of the data for every table.
-O --no-owner	Do not output commands to set ownership of objects to match the original database. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.
-R --no-reconnect	This option is obsolete but still accepted for backward compatibility.
-s --schema-only	Dump only the object definitions (schema), not data.
-S username --superuser=username	Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used.
-t table --table=table	Dump only tables (or views or sequences or foreign tables) matching table. Multiple tables can be selected by writing multiple -t switches.
-T table --exclude-table=table	Do not dump any tables matching the table pattern. When both -t and -T are given, the behavior is to dump just the tables that match at least one -t switch but no -T switches.
-v --verbose	Specifies verbose mode. This will cause pg_dump to output detailed object comments and start/stop times to the dump file, and progress messages to standard error.
-V --version	Print the pg_dump version and exit.
-x --no-privileges --no-acl	Prevent dumping of access privileges (grant/revoke commands).
-Z 0..9 --compress=0..9	Specify the compression level to use. Zero means no compression.
--binary-upgrade	This option is for use by in-place upgrade utilities.
--column-inserts --attribute-inserts	Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases.
--disable-dollar-quoting	This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax.
--disable-triggers	This option is relevant only when creating a data-only dump. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.
--exclude-table-data=table	This option is useful when you need the definition of a particular table even though you do not need the data in it. To exclude data for all tables in the database, see --schema-only.
--if-exists	Use conditional commands (i.e. add an IF EXISTS clause) when cleaning database objects. This option is not valid unless --clean is also specified.
--inserts	Dump data as INSERT commands (rather than COPY).
--lock-wait-timeout=timeout	Do not wait forever to acquire shared table locks at the beginning of the dump.
--no-security-labels	Do not dump security labels.
--no-synchronized-snapshots	This option allows running pg_dump -j against a pre-9.2 server, see the documentation of the -j parameter for more details.
--no-tablespaces	Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.
--no-unlogged-table-data	Do not dump the contents of unlogged tables.
--quote-all-identifiers	Force quoting of all identifiers. This may be useful when dumping a database for migration to a future version that may have introduced additional keywords.
--section = sectionname	Only dump the named section (default is to dump all sections).
--serializable-deferrable	Use a serializable transaction for the dump, to ensure that the snapshot used is consistent with later database states. This option is not beneficial for a dump which is intended only for disaster recovery.
--use-set-session-authorization	Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership.
-? --help	Show help about pg_dump command line arguments, and exit.

The following command-line options control the database connection parameters

Option	Description
-d dbname --dbname=dbname	Specifies the name of the database to connect to.
-h host --host=host	Specifies the host name of the machine on which the server is running.
-p port --port=port	Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections (defaults to the PGPORT environment variable).
-U username --username=username	User name.
-w --no-password	Never issue a password prompt.
-W --password	Force pg_dump to prompt for a password before connecting to a database. This option is never essential, since pg_dump will automatically prompt for a password if the server demands password authentication.
--role=rolename	Specifies a role name to be used to create the dump.

Restore a PostgreSQL Database

Text files created by pg_dump are intended to be read in by the psql program. Type the following command, and then press ENTER. Replace USERNAME with your username and DBNAME with the name of the database that you want to restore the data into

Syntax:

psql -U USERNAME DBNAME < Mybackup.pgsql

where Mybackup.pgsql is the file output by the pg_dump command. The database dbname will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). psql supports options similar to pg_dump for specifying the database server to connect to and the user name to use. Non-text file dumps are restored using the pg_restore utility.

Note: Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions.

pg_restore

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats.

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump. Some of the options controlling the output are therefore analogous to pg_dump options.

Syntax:

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

pg_restore accepts the following command line arguments into the table.

Options	Description
filename	Location of the archive file (or directory, for a directory-format archive) to be restored. If not specified, the standard input is used.
-a --data-only	Restore only the data, not the schema (data definitions). This option is similar to, but for historical reasons not identical to, specifying --section=data.
-c --clean	Clean (drop) database objects before recreating them.
-C --create	Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it. When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.
-d dbname --dbname=dbname	Connect to database dbname and restore directly into the database.
-e --exit-on-error	Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration.
-f filename --file=filename	Specify output file for generated script, or for the listing when used with -l. The default is the standard output.
-F format --format=format	Specify format of the archive. It is not necessary to specify the format since pg_restore will determine the format automatically. If specified, it can be one of the following: c custom : The archive is in the custom format of pg_dump. d directory : The archive is a directory archive. t tar : The archive is a tar archive.
-I index --index=index	Restore definition of named index only.
-j number-of-jobs --jobs=number-of-jobs	Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.
-l --list	List the contents of the archive.
-L list-file --use-list=list-file	Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line.
-n namespace --schema=schema	Restore only objects that are in the named schema. This can be combined with the -t option to restore just a specific table.
-O --no-owner	Do not output commands to set ownership of objects to match the original database. By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements.
-P function-name(argtype [, ...]) --function=function-name(argtype [, ...])	Restore the named function only.
-R --no-reconnect	This option is obsolete but still accepted for backwards compatibility.
-s --schema-only	Restore only the schema (data definitions), not data, to the extent that schema entries are present in the archive. This option is the inverse of --data-only.
-S username --superuser=username	Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used.
-t table --table=table	Restore definition and/or data of named table only. This can be combined with the -n option to specify a schema.
-T trigger --trigger=trigger	Restore named trigger only.
-v --verbose	Specifies verbose mode.
-V --version	Print the pg_dump version and exit.
-x --no-privileges --no-acl	Prevent restoration of access privileges (grant/revoke commands).
-1 --single-transaction	Execute the restore as a single transaction.
--disable-triggers	This option is only relevant when performing a data-only restore.
--no-data-for-failed-tables	By default, table data is restored even if the creation command for the table failed (e.g., because it already exists). With this option, data for such a table is skipped. This behavior is useful if the target database already contains the desired table contents. This option is effective only when restoring directly into a database, not when producing SQL script output.
--no-security-labels	Do not output commands to restore security labels, even if the archive contains them.
--no-tablespaces	Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore.
--section=sectionname	Only restore the named section.
--use-set-session-authorization	Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership.
-? --help	Show help about pg_restore command line arguments, and exit.

The following command-line options control the database connection parameters

Option	Description
-h host --host=host	Specifies the host name of the machine on which the server is running.
-p port --port=port	Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections (defaults to the PGPORT environment variable).
-U username --username=username	User name.
-w --no-password	Never issue a password prompt.
-W --password	Force pg_dump to prompt for a password before connecting to a database. This option is never essential since pg_dump will automatically prompt for a password if the server demands password authentication.
--role=rolename	Specifies a role name to be used to create the dump.

Previous: PostgreSQL Privileges
Next: PostgreSQL Exercises Introduction