PostgreSQL: Backup and Restore
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 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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics