Next: pg_dumpall
Up: Reference Manual
Previous: pg_ctl
Subsections
pg_dump
pg_dump -- Extract a Postgres database into a script file
pg_dump [ dbname ]
pg_dump [ -h host ] [ -p port ] [ -t table ]
[ -a ] [ -c ] [ -d ] [ -D ] [ -i ] [ -n ] [ -N ]
[ -o ] [ -s ] [ -u ] [ -v ] [ -x ] [ dbname ]
pg_dump accepts the following command line arguments:
- dbname
- Specifies the name of the database to be extracted. dbname
defaults to the value of the USER environment variable.
- -a
- Dump out only the data, no schema (definitions).
- -c
- Clean (drop) schema prior to create.
- -d
- Dump data as proper insert strings.
- -D
- Dump data as inserts with attribute names
- -i
- 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
- 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 v6.4.
- -N
- Include double quotes around identifiers. This is the default.
- -o
- Dump object identifiers (OIDs) for every table.
- -s
- Dump out only the schema (definitions), no data.
- -t table
- Dump data for table only.
- -u
- Use password authentication. Prompts for username and password.
- -v
- Specifies verbose mode.
- -x
- Prevent dumping of ACLs (grant/revoke commands) and table ownership information.
pg_dump also accepts the following command line arguments for connection parameters:
- -h host
- Specifies the hostname of the machine on which the postmaster
is running. Defaults to using a local Unix domain socket rather than an IP connection.
- -p 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.
pg_dump will create a file or write to stdout.
- Connection to database 'template1' failed. connectDB() failed:
- Is the
postmaster running and accepting connections at 'UNIX Socket' on port 'port'?
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. If your site uses an
authentication system, ensure that you have obtained the required authentication
credentials.
- Connection to database 'dbname' failed. FATAL 1:
- SetUserId:
user 'username' is not in 'pg_shadow' You do not
have a valid entry in the relation pg_shadow and and will not be allowed to
access Postgres. Contact your Postgres administrator.
- 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.
pg_dump is a utility for dumping out a Postgres database into a script 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.
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 script file for any warnings, especially in light of the limitations
listed below.
pg_dump has a few limitations. The limitations mostly stem from difficulty
in extracting certain meta-information from the system catalogs.
- pg_dump does not understand partial indices. The reason is the same as above;
partial index predicates are stored as plans.
- pg_dump does not handle large objects. Large objects are ignored and must be
dealt with manually.
- 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.
To dump a database of the same name as the user:
$ pg_dump > db.out
To reload this database:
$ psql -e database < db.out
Next: pg_dumpall
Up: Reference Manual
Previous: pg_ctl
Bruce Momjian
2001-05-09