Man Pages

pg_dump(1) - phpMan pg_dump(1) - phpMan

Command: man perldoc info search(apropos)  


PG_DUMP(1)              PostgreSQL Client Applications              PG_DUMP(1)



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 com-
                     pressed 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.  Multi-
              ple  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  multi-
              ple 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 mul-
              tiple -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-ta-
              ble 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  other-
              wise 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 use-
              ful for making dumps that can be loaded into non-PostgreSQL databases.  Also, since this  option  gener-
              ates 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 col-
              umn 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 cer-
              tainly require superuser privileges to restore correctly, whereas ALTER  OWNER  requires  lesser  privi-
              leges.


       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 con-
       tain 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)



Application                       2014-02-17                        PG_DUMP(1)