|
 |
|
| |
The
pg_dump
(which is short for "PostgreSQL dump") application is run from a command line, and
creates a list of SQL commands. These commands, executed in the order provided, re-create the database from
scratch.
Here is the syntax for
pg_dump
:
pg_dump [
options
]
dbname
In this syntax,
dbname
is the name of the database that you want to "dump" SQL instructions for.
The available
options
are similar in format to those of the other database management utilities
included with PostgreSQL, such as
createdb
. The most common flag specified in the
options
to
pg_dump
is the
-f
flag, which specifies the file
to store the dumped SQL statements within.
Note: If the
-f
flag is not specified to
pg_dump
, the dumped SQL will
be written to
stdout
rather than stored in a file.
The complete list of
pg_dump
options follow:
- -a, - -data-only
-
Forces only COPY or INSERT SQL statements to be
dumped (depending on whether or not the
-d
flag is used). This results in a backup of data, and
not database objects (or
schema
). If the
-d
flag is
not
passed along with this flag, the dumped COPY commands are
used to copy all data from
stdin
(i.e., the rows are stored literally within the dumped file as
COPY commands from
stdin
). Otherwise, each row is represented
as sequential INSERT statements.
- -b, - -blobs
-
Causes any large objects to be dumped as well as normal data. This option also requires that the
-F
flag be provided with either the
t
or
c
format.
By default, large object data is not dumped.
- -c, - -clean
-
Specifies that SQL statements to drop all existing objects will precede the SQL statements to create those objects.
This option is useful in re-initializing an existing database, rather than dropping it and
recreating it from scratch.
- -C, - -create
-
Specifies that the SQL statement to create the database (CREATE DATABASE)
should be included in the dump.
- -d, - -inserts
-
Causes INSERT statements to be dumped for each row of data, rather than the default
COPY statements. This can be safer, as a single corrupted row will cause a
COPY statement to fail, though it is a much slower process to add a single row at a time during restoration.
- -D, - -attribute-inserts
-
Like the
-d
flag, causes INSERT statements to be dumped;
however, with this flag, each INSERT statement is created with an explicit column
target list in parentheses, immediately preceding the VALUES.
- -f
FILENAME
, - -file=FILENAME
-
Directs the output of
pg_dump
to a file named
FILENAME
, rather
than to
stdout
. The user executing
pg_dump
must have system permissions
to write to this file.
- -F { c | t | p }, - -format { c | t | p }
-
Determines the file format of the output:
-
c
(
gzip
compressed)
-
A format of
c
creates a
gzip
-compressed
tar
file
(i.e., a
.tar.gz
file).
-
t
(
tar
)
-
A value of
t
creates a
tar
file (i.e., a
.tar
file).
-
p
(plain text)
-
The default value of
p
causes plain text output.
Note that
pg_restore
is typically used to handle files created with the
c
or
t
(
gzip
-compressed or
tar
)
formats.
- -h
HOSTNAME
, - -host=
HOSTNAME
-
Specifies that
HOSTNAME
should be connected to, rather than the localhost. Use this when the target
database is on another server.
- -i, - -ignore-version
-
Overrides the check between the version of
pg_dump
and the version of PostgreSQL
running. This option is not recommended unless there is no other alternative, as it most likely will
produce errors due to changes in the system catalogs between versions. Normally, you should use the version of
pg_dump
matching the database that you are backing up.
- -n, - -no-quotes
-
Suppresses any double-quotes surrounding identifiers unless there are normally illegal characters in
the identifier (e.g., spaces, or uppercase characters).
- -N, - -quotes
-
Explicitly indicates that double-quotes should be used around all identifiers. This has been the default behavior
of
pg_dump
since PostgreSQL 6.4.
- -o, - -oid
-
Causes OIDs (object identifiers) for each row of data to be dumped as well. This can be vital if any of your
applications based around the data in PostgreSQL use OIDs in any kind of meaningful or associative way.
- -O, - -no-owner
-
Causes ownership to not be taken into account in the dump. A restore with suppressed ownership will cause all
re-created objects to belong to the user performing the restore.
- -p
PORT
, - -port=
PORT
-
Specifies that the database connection should be made on port
PORT
, rather than the
default port (usually 5432, though it may have been configured differently when PostgreSQL was
compiled, by the
- -with-pgport
flag).
- -R, - -no-reconnect
-
Suppresses any \connect statements, which are usually used to enforce currently
reflected ownerships when a backup is restored. This is similar in practice to the
-O
flag,
but also precludes the ability to use the
-C
flag, as a reconnect is required after creation
of a new database.
- -s, - -schema-only
-
Causes only the
schema-related
(database objects such as tables, sequences, indices and views)
SQL statements to be dumped, ignoring re-creation of the data. This can be useful in moving a general database
structure from a development machine to a production machine.
- -t
TABLE
, - -table=
TABLE
-
Causes only
TABLE
to be dumped from the specified database, rather than all tables.
- -u, - -password
-
Provides a prompt for a username and password. As of PostgreSQL 7.1.x, this is the only method to provide an
alternate username. If the user's password is unset (NULL), you may simply press enter
when prompted for it.
- -v, - -verbose
-
Causes verbose output from the
pg_dump
functions being performed to be displayed to
stderr
(not
stdout
).
- -x, - -no-acl
-
Suppresses any GRANT or REVOKE statements, which
are usually used to preserve the rights set at the time of the dump. Use this flag if you do not wish to enforce
any existing rights or restrictions when re-creating a database from this dump.
- -Z, - -compress { 0 - 9 }
-
Sets the degree of compression (0 for the least compression, 9 for the most) when used with the
-F c
argument.
Any system user may run
pg_dump
by default, but the user with which you connect to PostgreSQL
must have SELECT rights for every object in the database being dumped.
Example 9-20 demonstrates the use of
pg_dump
on the
booktown database, with the manager PostgreSQL user. The
-C
flag passed causes the CREATE DATABASE command to be
included in the SQL dump as well. This command is not always included, as you may prefer to create the database
beforehand with non-standard options.
Example 9-20. Using pg_dump
[jworsley@booktown ~]$
pg_dump -u -C -f booktown.sql booktown
Username:
manager
Password:
[jworsley@booktown ~]$
ls -l booktown.sql
-rw-rw-r- - 1 jworsley jworsley 46542 Sep 13 16:42 booktown.sql
Note that since
pg_dump
provides the standard connection options for specifying a host
connection (e.g., the
-h
,
-u
and
-p
flags), it can be used
to perform remote backups from any host allowed to make a remote connection (according to the
pg_hba.conf
file: see Chapter 8 for more on this subject). Example 9-21 shows a user on a remote server specifying a connection to the
booktown.commandprompt.com
server, in order to create a backup file in
compressed
format (with the
-F c
flag) called
booktown.sql.tar.gz
.
Example 9-21. Using pg_dump remotely
[jworsley@cmd ~]$
pg_dump -u -h booktown.commandprompt.com \
>
-F c -f booktown.sql.tar.gz booktown
Username:
manager
Password:
[jworsley@cmd ~]$
ls -l booktown.sql.tar.gz
-rw-rw-r- - 1 jworsley jworsley 45909 Sep 13 17:12 booktown.sql.tar.gz
If you wish to use large objects in your dumped file, it is necessary that you use either the
tar
(
t
) or
gzip
-compressed (
c
) format,
since the plain-text format cannot include large objects. Otherwise, normal plain-text formatted dumps are suitable for
most users.
Creating a dump in
tar
format can often result in a backup file more than twice the size of its
plain-text counterpart, even without large objects. This is because the
tar
format involves a
hierarchy of TOC (table of contents)
.dat
files which represent information on how to be used by the
corresponding
pg_restore
command; thus, more disk space is taken up by the extra instructions. Since
tar
is not a compression utility, the
gzip
-compressed (
c
)
format exists as well to compress the tarred file into
gzipped
format automatically.
|
|
|
