PG_DUMP(1)		PostgreSQL 9.2.24 Documentation		    PG_DUMP(1)



NAME
       pg_dump - extract a PostgreSQL database into a script file or other
       archive file

SYNOPSIS
       pg_dump [connection-option...] [option...] [dbname]

DESCRIPTION
       pg_dump is a utility for backing up a PostgreSQL database. It makes
       consistent backups even if the database is being used concurrently.
       pg_dump does not block other users accessing the database (readers or
       writers).

       Dumps can be output in script or archive file formats. Script dumps are
       plain-text files containing the SQL commands required to reconstruct
       the database to the state it was in at the time it was saved. To
       restore from such a script, feed it to psql(1). Script files can be
       used to reconstruct the database even on other machines and other
       architectures; with some modifications, even on other SQL database
       products.

       The alternative archive file formats must be used with pg_restore(1) to
       rebuild the database. They allow pg_restore to be selective about what
       is restored, or even to reorder the items prior to being restored. The
       archive file formats are designed to be portable across architectures.

       When used with one of the archive file formats and combined with
       pg_restore, pg_dump 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. The most flexible output file
       format is the "custom" format (-Fc). It allows for selection and
       reordering of all archived items, and is compressed by default.

       While running pg_dump, one should examine the output for any warnings
       (printed on standard error), especially in light of the limitations
       listed below.

OPTIONS
       The following command-line options control the content and format of
       the output.

       dbname
	   Specifies the name of the database to be dumped. If this is not
	   specified, the environment variable PGDATABASE is used. If that is
	   not set, the user name specified for the connection is used.

       -a, --data-only
	   Dump only the data, not the schema (data definitions). Table data,
	   large objects, and sequence values are dumped.

	   This option is similar to, but for historical reasons not identical
	   to, specifying --section=data.

       -b, --blobs
	   Include large objects in the dump. This is the default behavior
	   except when --schema, --table, or --schema-only is specified. The
	   -b switch is therefore only useful to add large objects to dumps
	   where a specific schema or table has been requested. Note that
	   blobs are considered data and therefore will be included when
	   --data-only is used, but not when --schema-only is.

       -c, --clean
	   Output commands to clean (drop) database objects prior to
	   outputting the commands for creating them. (Restore might generate
	   some harmless error messages, if any objects were not present in
	   the destination database.)

	   This option is only meaningful for the plain-text format. For the
	   archive formats, you can specify the option when you call
	   pg_restore.

       -C, --create
	   Begin the output with a command to create the database itself and
	   reconnect to the created database. (With a script of this form, it
	   doesn't matter which database in the destination installation you
	   connect to before running the script.) If --clean is also
	   specified, the script drops and recreates the target database
	   before reconnecting to it.

	   This option is only meaningful for the plain-text format. For the
	   archive formats, you can specify the option when you call
	   pg_restore.

       -E encoding, --encoding=encoding
	   Create the dump in the specified character set encoding. By
	   default, the dump is created in the database encoding. (Another way
	   to get the same result is to set the PGCLIENTENCODING environment
	   variable to the desired dump encoding.)

       -f file, --file=file
	   Send output to the specified file. This parameter can be omitted
	   for file based output formats, in which case the standard output is
	   used. It must be given for the directory output format however,
	   where it specifies the target directory instead of a file. In this
	   case the directory is created by pg_dump and must not exist before.

       -F format, --format=format
	   Selects the format of the output.  format can be one of the
	   following:

	   p, plain
	       Output a plain-text SQL script file (the default).

	   c, custom
	       Output a custom-format archive suitable for input into
	       pg_restore. Together with the directory output format, this is
	       the most flexible output format in that it allows manual
	       selection and reordering of archived items during restore. This
	       format is also compressed by default.

	   d, directory
	       Output a directory-format archive suitable for input into
	       pg_restore. This will create a directory with one file for each
	       table and blob being dumped, plus a so-called Table of Contents
	       file describing the dumped objects in a machine-readable format
	       that pg_restore can read. A directory format archive can be
	       manipulated with standard Unix tools; for example, files in an
	       uncompressed archive can be compressed with the gzip tool. This
	       format is compressed by default.

	   t, tar
	       Output a tar-format archive suitable for input into pg_restore.
	       The tar format is compatible with the directory format:
	       extracting a tar-format archive produces a valid
	       directory-format archive. However, the tar format does not
	       support compression. Also, when using tar format the relative
	       order of table data items cannot be changed during restore.

       -i, --ignore-version
	   A deprecated option that is now ignored.

       -n schema, --schema=schema
	   Dump only schemas matching schema; this selects both the schema
	   itself, and all its contained objects. When this option is not
	   specified, all non-system schemas in the target database will be
	   dumped. Multiple schemas can be selected by writing multiple -n
	   switches. Also, the schema parameter is interpreted as a pattern
	   according to the same rules used by psql's \d commands (see
	   Patterns), so multiple schemas can also be selected by writing
	   wildcard characters in the pattern. When using wildcards, be
	   careful to quote the pattern if needed to prevent the shell from
	   expanding the wildcards; see EXAMPLES.

	       Note
	       When -n is specified, pg_dump makes no attempt to dump any
	       other database objects that the selected schema(s) might depend
	       upon. Therefore, there is no guarantee that the results of a
	       specific-schema dump can be successfully restored by themselves
	       into a clean database.

	       Note
	       Non-schema objects such as blobs are not dumped when -n is
	       specified. You can add blobs back to the dump with the --blobs
	       switch.

       -N schema, --exclude-schema=schema
	   Do not dump any schemas matching the schema pattern. The pattern is
	   interpreted according to the same rules as for -n.  -N can be given
	   more than once to exclude schemas matching any of several patterns.

	   When both -n and -N are given, the behavior is to dump just the
	   schemas that match at least one -n switch but no -N switches. If -N
	   appears without -n, then schemas matching -N are excluded from what
	   is otherwise a normal dump.

       -o, --oids
	   Dump object identifiers (OIDs) as part of the data for every table.
	   Use this option if your application references the OID columns in
	   some way (e.g., in a foreign key constraint). Otherwise, this
	   option should not be used.

       -O, --no-owner
	   Do not output commands to set ownership of objects to match the
	   original database. By default, pg_dump issues ALTER OWNER or SET
	   SESSION AUTHORIZATION statements to set ownership of created
	   database objects. These statements will fail when the script is run
	   unless it is started by a superuser (or the same user that owns all
	   of the objects in the script). To make a script that can be
	   restored by any user, but will give that user ownership of all the
	   objects, specify -O.

	   This option is only meaningful for the plain-text format. For the
	   archive formats, you can specify the option when you call
	   pg_restore.

       -R, --no-reconnect
	   This option is obsolete but still accepted for backwards
	   compatibility.

       -s, --schema-only
	   Dump only the object definitions (schema), not data.

	   This option is the inverse of --data-only. It is similar to, but
	   for historical reasons not identical to, specifying
	   --section=pre-data --section=post-data.

	   (Do not confuse this with the --schema option, which uses the word
	   "schema" in a different meaning.)

	   To exclude table data for only a subset of tables in the database,
	   see --exclude-table-data.

       -S username, --superuser=username
	   Specify the superuser user name to use when disabling triggers.
	   This is only relevant if --disable-triggers is used. (Usually, it's
	   better to leave this out, and instead start the resulting script as
	   superuser.)

       -t table, --table=table
	   Dump only tables (or views or sequences or foreign tables) matching
	   table. Multiple tables can be selected by writing multiple -t
	   switches. Also, the table parameter is interpreted as a pattern
	   according to the same rules used by psql's \d commands (see
	   Patterns), so multiple tables can also be selected by writing
	   wildcard characters in the pattern. When using wildcards, be
	   careful to quote the pattern if needed to prevent the shell from
	   expanding the wildcards; see EXAMPLES.

	   The -n and -N switches have no effect when -t is used, because
	   tables selected by -t will be dumped regardless of those switches,
	   and non-table objects will not be dumped.

	       Note
	       When -t is specified, pg_dump makes no attempt to dump any
	       other database objects that the selected table(s) might depend
	       upon. Therefore, there is no guarantee that the results of a
	       specific-table dump can be successfully restored by themselves
	       into a clean database.

	       Note
	       The behavior of the -t switch is not entirely upward compatible
	       with pre-8.2 PostgreSQL versions. Formerly, writing -t tab
	       would dump all tables named tab, but now it just dumps
	       whichever one is visible in your default search path. To get
	       the old behavior you can write -t '*.tab'. Also, you must write
	       something like -t sch.tab to select a table in a particular
	       schema, rather than the old locution of -n sch -t tab.

       -T table, --exclude-table=table
	   Do not dump any tables matching the table pattern. The pattern is
	   interpreted according to the same rules as for -t.  -T can be given
	   more than once to exclude tables matching any of several patterns.

	   When both -t and -T are given, the behavior is to dump just the
	   tables that match at least one -t switch but no -T switches. If -T
	   appears without -t, then tables matching -T are excluded from what
	   is otherwise a normal dump.

       -v, --verbose
	   Specifies verbose mode. This will cause pg_dump to output detailed
	   object comments and start/stop times to the dump file, and progress
	   messages to standard error.

       -V, --version
	   Print the pg_dump version and exit.

       -x, --no-privileges, --no-acl
	   Prevent dumping of access privileges (grant/revoke commands).

       -Z 0..9, --compress=0..9
	   Specify the compression level to use. Zero means no compression.
	   For the custom archive format, this specifies compression of
	   individual table-data segments, and the default is to compress at a
	   moderate level. For plain text output, setting a nonzero
	   compression level causes the entire output file to be compressed,
	   as though it had been fed through gzip; but the default is not to
	   compress. The tar archive format currently does not support
	   compression at all.

       --binary-upgrade
	   This option is for use by in-place upgrade utilities. Its use for
	   other purposes is not recommended or supported. The behavior of the
	   option may change in future releases without notice.

       --column-inserts, --attribute-inserts
	   Dump data as INSERT commands with explicit column names (INSERT
	   INTO table (column, ...) VALUES ...). This will make restoration
	   very slow; it is mainly useful for making dumps that can be loaded
	   into non-PostgreSQL databases. However, since this option generates
	   a separate command for each row, an error in reloading a row causes
	   only that row to be lost rather than the entire table contents.

       --disable-dollar-quoting
	   This option disables the use of dollar quoting for function bodies,
	   and forces them to be quoted using SQL standard string syntax.

       --disable-triggers
	   This option is only relevant when creating a data-only dump. It
	   instructs pg_dump to include commands to temporarily disable
	   triggers on the target tables while the data is reloaded. Use this
	   if you have referential integrity checks or other triggers on the
	   tables that you do not want to invoke during data reload.

	   Presently, the commands emitted for --disable-triggers must be done
	   as superuser. So, you should also specify a superuser name with -S,
	   or preferably be careful to start the resulting script as a
	   superuser.

	   This option is only meaningful for the plain-text format. For the
	   archive formats, you can specify the option when you call
	   pg_restore.

       --exclude-table-data=table
	   Do not dump data for any tables matching the table pattern. The
	   pattern is interpreted according to the same rules as for -t.
	   --exclude-table-data can be given more than once to exclude tables
	   matching any of several patterns. This option is useful when you
	   need the definition of a particular table even though you do not
	   need the data in it.

	   To exclude data for all tables in the database, see --schema-only.

       --inserts
	   Dump data as INSERT commands (rather than COPY). This will make
	   restoration very slow; it is mainly useful for making dumps that
	   can be loaded into non-PostgreSQL databases. However, since this
	   option generates a separate command for each row, an error in
	   reloading a row causes only that row to be lost rather than the
	   entire table contents. Note that the restore might fail altogether
	   if you have rearranged column order. The --column-inserts option is
	   safe against column order changes, though even slower.

       --lock-wait-timeout=timeout
	   Do not wait forever to acquire shared table locks at the beginning
	   of the dump. Instead fail if unable to lock a table within the
	   specified timeout. The timeout may be specified in any of the
	   formats accepted by SET statement_timeout. (Allowed values vary
	   depending on the server version you are dumping from, but an
	   integer number of milliseconds is accepted by all versions since
	   7.3. This option is ignored when dumping from a pre-7.3 server.)

       --no-security-labels
	   Do not dump security labels.

       --no-tablespaces
	   Do not output commands to select tablespaces. With this option, all
	   objects will be created in whichever tablespace is the default
	   during restore.

	   This option is only meaningful for the plain-text format. For the
	   archive formats, you can specify the option when you call
	   pg_restore.

       --no-unlogged-table-data
	   Do not dump the contents of unlogged tables. This option has no
	   effect on whether or not the table definitions (schema) are dumped;
	   it only suppresses dumping the table data. Data in unlogged tables
	   is always excluded when dumping from a standby server.

       --quote-all-identifiers
	   Force quoting of all identifiers. This option is recommended when
	   dumping a database from a server whose PostgreSQL major version is
	   different from pg_dump's, or when the output is intended to be
	   loaded into a server of a different major version. By default,
	   pg_dump quotes only identifiers that are reserved words in its own
	   major version. This sometimes results in compatibility issues when
	   dealing with servers of other versions that may have slightly
	   different sets of reserved words. Using --quote-all-identifiers
	   prevents such issues, at the price of a harder-to-read dump script.

       --section=sectionname
	   Only dump the named section. The section name can be pre-data,
	   data, or post-data. This option can be specified more than once to
	   select multiple sections. The default is to dump all sections.

	   The data section contains actual table data, large-object contents,
	   and sequence values. Post-data items include definitions of
	   indexes, triggers, rules, and constraints other than validated
	   check constraints. Pre-data items include all other data definition
	   items.

       --serializable-deferrable
	   Use a serializable transaction for the dump, to ensure that the
	   snapshot used is consistent with later database states; but do this
	   by waiting for a point in the transaction stream at which no
	   anomalies can be present, so that there isn't a risk of the dump
	   failing or causing other transactions to roll back with a
	   serialization_failure. See Chapter 13, Concurrency Control, in the
	   documentation for more information about transaction isolation and
	   concurrency control.

	   This option is not beneficial for a dump which is intended only for
	   disaster recovery. It could be useful for a dump used to load a
	   copy of the database for reporting or other read-only load sharing
	   while the original database continues to be updated. Without it the
	   dump may reflect a state which is not consistent with any serial
	   execution of the transactions eventually committed. For example, if
	   batch processing techniques are used, a batch may show as closed in
	   the dump without all of the items which are in the batch appearing.

	   This option will make no difference if there are no read-write
	   transactions active when pg_dump is started. If read-write
	   transactions are active, the start of the dump may be delayed for
	   an indeterminate length of time. Once running, performance with or
	   without the switch is the same.

       --use-set-session-authorization
	   Output SQL-standard SET SESSION AUTHORIZATION commands instead of
	   ALTER OWNER commands to determine object ownership. This makes the
	   dump more standards-compatible, but depending on the history of the
	   objects in the dump, might not restore properly. Also, a dump using
	   SET SESSION AUTHORIZATION will certainly require superuser
	   privileges to restore correctly, whereas ALTER OWNER requires
	   lesser privileges.

       -?, --help
	   Show help about pg_dump command line arguments, and exit.

       The following command-line options control the database connection
       parameters.

       -h host, --host=host
	   Specifies the host name of the machine on which the server is
	   running. If the value begins with a slash, it is used as the
	   directory for the Unix domain socket. The default is taken from the
	   PGHOST environment variable, if set, else a Unix domain socket
	   connection is attempted.

       -p port, --port=port
	   Specifies the TCP port or local Unix domain socket file extension
	   on which the server is listening for connections. Defaults to the
	   PGPORT environment variable, if set, or a compiled-in default.

       -U username, --username=username
	   User name to connect as.

       -w, --no-password
	   Never issue a password prompt. If the server requires password
	   authentication and a password is not available by other means such
	   as a .pgpass file, the connection attempt will fail. This option
	   can be useful in batch jobs and scripts where no user is present to
	   enter a password.

       -W, --password
	   Force pg_dump to prompt for a password before connecting to a
	   database.

	   This option is never essential, since pg_dump will automatically
	   prompt for a password if the server demands password
	   authentication. However, pg_dump will waste a connection attempt
	   finding out that the server wants a password. In some cases it is
	   worth typing -W to avoid the extra connection attempt.

       --role=rolename
	   Specifies a role name to be used to create the dump. This option
	   causes pg_dump to issue a SET ROLErolename command after connecting
	   to the database. It is useful when the authenticated user
	   (specified by -U) lacks privileges needed by pg_dump, but can
	   switch to a role with the required rights. Some installations have
	   a policy against logging in directly as a superuser, and use of
	   this option allows dumps to be made without violating the policy.

ENVIRONMENT
       PGDATABASE, PGHOST, PGOPTIONS, PGPORT, PGUSER
	   Default connection parameters.

       This utility, like most other PostgreSQL utilities, also uses the
       environment variables supported by libpq (see Section 31.14,
       "Environment Variables", in the documentation).

DIAGNOSTICS
       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). Also, any default connection
       settings and environment variables used by the libpq front-end library
       will apply.

       The database activity of pg_dump is normally collected by the
       statistics collector. If this is undesirable, you can set parameter
       track_counts to false via PGOPTIONS or the ALTER USER command.

NOTES
       If your database cluster has any local additions to the template1
       database, be careful to restore the output of pg_dump into a truly
       empty database; otherwise you are likely to get errors due to duplicate
       definitions of the added objects. To make an empty database without any
       local additions, copy from template0 not template1, for example:

	   CREATE DATABASE foo WITH TEMPLATE template0;

       When a data-only dump is chosen and the option --disable-triggers is
       used, pg_dump emits commands to disable triggers on user tables before
       inserting the data, and then commands to re-enable them after the data
       has been inserted. If the restore is stopped in the middle, the system
       catalogs might be left in the wrong state.

       The dump file produced by pg_dump does not contain the statistics used
       by the optimizer to make query planning decisions. Therefore, it is
       wise to run ANALYZE after restoring from a dump file to ensure optimal
       performance; see Section 23.1.3, "Updating Planner Statistics", in the
       documentation and Section 23.1.6, "The Autovacuum Daemon", in the
       documentation for more information. The dump file also does not contain
       any ALTER DATABASE ... SET commands; these settings are dumped by
       pg_dumpall(1), along with database users and other installation-wide
       settings.

       Because pg_dump is used to transfer data to newer versions of
       PostgreSQL, the output of pg_dump can be expected to load into
       PostgreSQL server versions newer than pg_dump's version.	 pg_dump can
       also dump from PostgreSQL servers older than its own version.
       (Currently, servers back to version 7.0 are supported.) However,
       pg_dump cannot dump from PostgreSQL servers newer than its own major
       version; it will refuse to even try, rather than risk making an invalid
       dump. Also, it is not guaranteed that pg_dump's output can be loaded
       into a server of an older major version -- not even if the dump was
       taken from a server of that version. Loading a dump file into an older
       server may require manual editing of the dump file to remove syntax not
       understood by the older server. Use of the --quote-all-identifiers
       option is recommended in cross-version cases, as it can prevent
       problems arising from varying reserved-word lists in different
       PostgreSQL versions.

EXAMPLES
       To dump a database called mydb into a SQL-script file:

	   $ pg_dump mydb > db.sql

       To reload such a script into a (freshly created) database named newdb:

	   $ psql -d newdb -f db.sql

       To dump a database into a custom-format archive file:

	   $ pg_dump -Fc mydb > db.dump

       To dump a database into a directory-format archive:

	   $ pg_dump -Fd mydb -f dumpdir

       To reload an archive file into a (freshly created) database named
       newdb:

	   $ pg_restore -d newdb db.dump

       To dump a single table named mytab:

	   $ pg_dump -t mytab mydb > db.sql

       To dump all tables whose names start with emp in the detroit schema,
       except for the table named employee_log:

	   $ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

       To dump all schemas whose names start with east or west and end in gsm,
       excluding any schemas whose names contain the word test:

	   $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

       The same, using regular expression notation to consolidate the
       switches:

	   $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

       To dump all database objects except for tables whose names begin with
       ts_:

	   $ pg_dump -T 'ts_*' mydb > db.sql

       To specify an upper-case or mixed-case name in -t and related switches,
       you need to double-quote the name; else it will be folded to lower case
       (see Patterns). But double quotes are special to the shell, so in turn
       they must be quoted. Thus, to dump a single table with a mixed-case
       name, you need something like

	   $ pg_dump -t '"MixedCaseName"' mydb > mytab.sql

SEE ALSO
       pg_dumpall(1), pg_restore(1), psql(1)



PostgreSQL 9.2.24		  2017-11-06			    PG_DUMP(1)