pg_dump

Name

pg_dump --  Extract a Postgres database into a script file or other archive file

Synopsis

pg_dump [-a | -s] [-b] [-c] [-C] [-d | -D] [-f file] [-F format] [-i] [-n | -N] [-o] [-O] [-R] [-S] [-t table] [-v] [-x] [-Z 0...9] [-h host] [-p port] [-u] dbname

Description

pg_dump is a utility for dumping out a Postgres database into a script or archive file containing query commands. The script files are in text format and can be used to reconstruct the database, even on other machines and other architectures. The archive files, new with version 7.1, contain enough information for pg_restore to rebuild the database, but also allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive files are also designed to be portable across architectures.

pg_dump will produce the queries necessary to re-generate all user-defined types, functions, tables, indices, aggregates, and operators. In addition, all the data is copied out in text format so that it can be readily copied in again, as well as imported into tools for editing.

pg_dump is useful for dumping out the contents of a database to move from one Postgres installation to another. After running pg_dump, one should examine the output for any warnings, especially in light of the limitations listed below.

When used with one of the alternate file formats and combined with pg_restore, it provides a flexible archival and transfer mechanism. pg_dump can be used to backup an entire database, then pg_restore can be used to examine the archive and/or select which parts of the database are to be restored. See the pg_restore documentation for details.

Options

pg_dump accepts the following command line arguments. (Long option forms are only available on some platforms.)

dbname

Specifies the name of the database to be extracted.

-a, --data-only

Dump only the data, not the schema (definitions).

-b, --blobs

Dump data and BLOB data.

-c, --clean

Dump commands to clean (drop) the schema prior to (the commands for) creating it.

-C, --create

For plain text (script) output, include commands to create the database itself.

-d, --inserts

Dump data as proper INSERT commands (not COPY). This will make restoration very slow.

-D, --attribute-inserts

Dump data as INSERT commands with explicit column names. This will make restoration very slow.

-f file, --file=file

Send output to the specified file.

-F format, --format=format

Format can be one of the following:

p

output a plain text SQL script file (default)

t

output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of schema elements at the time the database is restored. It is also possible to limit which data is reloaded at restore time.

c

output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of data load as well as schema elements. This format is also compressed by default.

-i, --ignore-version

Ignore version mismatch between pg_dump and the database server. Since pg_dump knows a great deal about system catalogs, any given version of pg_dump is only intended to work with the corresponding release of the database server. Use this option if you need to override the version check (and if pg_dump then fails, don't say you weren't warned).

-n, --no-quotes

Suppress double quotes around identifiers unless absolutely necessary. This may cause trouble loading this dumped data if there are reserved words used for identifiers. This was the default behavior for pg_dump prior to version 6.4.

-N, --quotes

Include double quotes around identifiers. This is the default.

-o, --oids

Dump object identifiers (OIDs) for every table.

-O, --no-owner

In plain text output mode, do not set object ownership to match the original database. Typically, pg_dump issues (psql-specific) \connect statements to set ownership of schema elements.

-R, --no-reconnect

In plain text output mode, prohibit pg_dump from issuing any \connect statements.

-s, --schema-only

Dump only the schema (definitions), no data.

-S username, --superuser=username

Specify the superuser user name to use when disabling triggers and/or setting ownership of schema elements.

-t table, --table=table

Dump data for table only.

-v, --verbose

Specifies verbose mode.

-x, --no-acl

Prevent dumping of ACLs (grant/revoke commands) and table ownership information.

-Z 0..9, --compress=0..9

Specify the compression level to use in archive formats that support compression (currently only the custom archive format supports compression).

pg_dump also accepts the following command line arguments for connection parameters:

-h host, --host=host

Specifies the host name of the machine on which the postmaster is running. If host begins with a slash, it is used as the directory for the Unix domain socket.

-p port, --port=port

Specifies the Internet TCP/IP port or local Unix domain socket file extension on which the postmaster is listening for connections. The port number defaults to 5432, or the value of the PGPORT environment variable (if set).

-u

Use password authentication. Prompts for username and password.

Diagnostics

Connection to database 'template1' failed.
connectDBStart() -- connect() failed: No such file or directory
        Is the postmaster running locally
        and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
pg_dump could not attach to the postmaster process on the specified host and port. If you see this message, ensure that the postmaster is running on the proper host and that you have specified the proper port.
dumpSequence(table): SELECT failed
You do not have permission to read the database. Contact your Postgres site administrator.

Note: pg_dump internally executes SELECT statements. If you have problems running pg_dump, make sure you are able to select information from the database using, for example, psql.

Notes

pg_dump has a few limitations. The limitations mostly stem from difficulty in extracting certain meta-information from the system catalogs.

Examples

To dump a database:

$ pg_dump mydb > db.out

To reload this database:

$ psql -d database -f db.out

To dump a database called mydb that contains BLOBs to a tar file:

$ pg_dump -Ft -b mydb > db.tar

To reload this database (with BLOBs) to an existing database called newdb:

$ pg_restore -d newdb db.tar

See Also

pg_dumpall, pg_restore , psql, PostgreSQL Administrator's Guide