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(1) 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, aggre
gates, 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 limi
tations listed below.
When used with one of the alternate file formats and com
bined 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(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
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 ele
ments 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.
--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 load
ing this dumped data if there are reserved words
used for identifiers. This was the default behav
ior 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 owner
ship 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 dis
abling triggers and/or setting ownership of schema
-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 postmas
ter is listening for connections. The port number
defaults to 5432, or the value of the PGPORT envi
ronment 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
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 state
ments. 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 difficulty in extracting certain meta-informa
tion 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 triggers 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
database called newdb:
$ pg_restore -d newdb db.tar
SEE ALSO
pg_dumpall(1), pg_restore(1), psql(1), PostgreSQL Adminis
trator's Guide
Man(1) output converted with
man2html