PG_DUMP(1) PostgreSQL Client Applications PG_DUMP(1)NAME
pg_dump - Extract a Postgres database into a script file or other ar‐
chive 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(1) to rebuild the data‐
base, 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 data‐
base are to be restored. See the pg_restore(1) 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 exclu‐
sion 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 ele‐
ments. 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 cor‐
responding 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 nec‐
essary. 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 owner‐
ship 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 connec‐
tion 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 connec‐
tions. 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(1).
NOTES
pg_dump has a few limitations. The limitations mostly stem from diffi‐
culty in extracting certain meta-information from the system catalogs.
· When dumping a single table or as plain text, pg_dump does not handle
large objects. Large objects must be dumped in their entirety using
one of the binary archive formats.
· When doing a data only dump, pg_dump emits queries to disable trig‐
gers on user tables before inserting the data and queries to re-
enable them after the data has been inserted. If the restore is
stopped in the middle, the system catalogs may be left in the wrong
state.
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 ALSOpg_dumpall(1), pg_restore(1), psql(1), PostgreSQL Administrator's Guide
Application 2001-03-06 PG_DUMP(1)