Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug

NAME

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

SYNOPSIS

       pg_dump [ 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. The tar format (-Ft) is not compressed and it is not possible to
       reorder data when loading, but it  is  otherwise  quite  flexible;  moreover,  it  can  be
       manipulated with standard Unix tools such as tar.

       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).

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

       -b

       --blobs
              Include  large  objects  in  the  dump.  This  is  the default behavior except when
              --schema, --table, or --schema-only is specified, so the -b switch is  only  useful
              to add large objects to selective dumps.

       -c

       --clean
              Output  commands  to  clean  (drop)  database  objects  prior to (the commands for)
              creating them.

              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 you
              connect to before running the script.)

              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. If this is omitted, the standard output is used.

       -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 archive suitable for input into pg_restore. This is the most
                     flexible  format  in  that  it  allows reordering of loading data as well as
                     object definitions. This format is also compressed by default.

              t

              tar    Output a tar archive suitable for input into pg_restore. Using this  archive
                     format  allows  reordering  and/or exclusion of database objects at the time
                     the database is restored. It  is  also  possible  to  limit  which  data  is
                     reloaded at restore time.

       -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 [psql(1)]), 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.

              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.

       -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) 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
              [psql(1)]),  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.

              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.

       -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.

       --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.   Also,  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.

       --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.  Also, 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.

       --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-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.

       --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.

       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 ROLE rolename 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 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.

       Members of tar archives are limited to a size less  than  8  GB.   (This  is  an  inherent
       limitation  of  the  tar file format.) Therefore this format cannot be used if the textual
       representation of any one table exceeds that size. The total size of a tar archive and any
       of the other output formats is not limited, except possibly by the operating system.

       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 good performance; see in the documentation and 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.

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 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 [psql(1)]). 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)