w3resource logo


PostgreSQL

PostgreSQL: Backup and Restore

Secondary Nav

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:

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.

Go Top

Complete syntax and other options of pg_dump

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.

Go Top

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

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.

Go Top

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.


Join our Question Answer community to learn and share your programming knowledge.

Help the community:

PHP: Loop

JavaScript: Delete duplicates in an array

Oracle: Round the number