pg_dump  -  Extract a Postgres database into a script file
       or other archive file


SYNOPSIS

       pg_dump [ -a | -s ] [ -b ] [ -c ] [ -C ] [ -d | -D ] [  -f
       file ] [ -F format ] [ -i ] [ -n | -N ] [ -o ] [ -O ] [ -R
       ] [ -S ] [ -t table ] [ -v ] [ -x ] [ -Z 0...9 ] [ -h host
       ] [ -p port ] [ -u ] dbname


DESCRIPTION

       pg_dump  is  a utility for dumping out a Postgres database
       into a script or archive 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.   The  archive files, new with version 7.1,
       contain enough information for  pg_restore(1)  to  rebuild
       the  database,  but  also allow pg_restore to be selective
       about what is restored, or even to reorder the items prior
       to  being restored. The archive files are also designed to
       be portable across architectures.

       pg_dump will produce the queries necessary to  re-generate
       all user-defined types, functions, tables, indices, aggre­
       gates, 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  for any warnings, especially in light of the limi­
       tations listed below.

       When used with one of the alternate file formats and  com­
       bined with pg_restore, it 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.  See the pg_restore(1) documentation for
       details.

   OPTIONS
       pg_dump accepts  the  following  command  line  arguments.
       (Long  option forms are only available on some platforms.)

       dbname Specifies the name of the database to be extracted.

       -a

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

       -b

              Dump data and BLOB data.

       -c

       --clean
              Dump  commands  to clean (drop) the schema prior to
              (the commands for) creating it.

       -C

       --create
              For plain text (script) output, include commands to
              create the database itself.

       -d

       --inserts
              Dump  data  as  proper  INSERT commands (not COPY).
              This will make restoration very slow.

       -D

       --attribute-inserts
              Dump data as INSERT commands with  explicit  column
              names. This will make restoration very slow.

       -f file

       --file=file
              Send output to the specified file.

       -F format

       --format=format
              Format can be one of the following:

              p      output   a   plain   text  SQL  script  file
                     (default)

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

              c      output  a  custom archive suitable for input
                     into pg_restore. This is the  most  flexible
                     format  in that it allows reordering of data
                     load as well as schema elements. This format
                     is also compressed by default.


       --ignore-version
              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

       --no-quotes
              Suppress double quotes  around  identifiers  unless
              absolutely necessary.  This may cause trouble load­
              ing this dumped data if there  are  reserved  words
              used  for identifiers.  This was the default behav­
              ior for pg_dump prior to version 6.4.

       -N

       --quotes
              Include double quotes around identifiers.  This  is
              the default.

       -o

       --oids Dump object identifiers (OIDs) for every table.

       -O

       --no-owner
              In plain text output mode, do not set object owner­
              ship to match  the  original  database.  Typically,
              pg_dump  issues (psql-specific) \connect statements
              to set ownership of schema elements.

       -R

       --no-reconnect
              In plain text output mode,  prohibit  pg_dump  from
              issuing any \connect statements.

       -s

       --schema-only
              Dump only the schema (definitions), no data.

       -S username

       --superuser=username
              Specify  the  superuser  user name to use when dis­
              abling triggers and/or setting ownership of  schema


       -t table

       --table=table
              Dump data for table only.

       -v

       --verbose
              Specifies verbose mode.

       -x

       --no-acl
              Prevent dumping of ACLs (grant/revoke commands) and
              table ownership information.

       -Z 0..9

       --compress=0..9
              Specify the compression level  to  use  in  archive
              formats  that  support  compression (currently only
              the custom archive format supports compression).

       pg_dump also accepts the following command line  arguments
       for connection parameters:

       -h host

       --host=host
              Specifies the host name of the machine on which the
              postmaster is running. If host begins with a slash,
              it  is  used  as  the directory for the Unix domain
              socket.

       -p port

       --port=port
              Specifies the Internet TCP/IP port  or  local  Unix
              domain  socket file extension on which the postmas­
              ter is listening for connections. The  port  number
              defaults  to 5432, or the value of the PGPORT envi­
              ronment variable (if set).

       -u     Use password authentication.  Prompts for  username
              and password.


DIAGNOSTICS

       Connection to database 'template1' failed.
       connectDBStart() -- connect() failed: No such file or directory
               Is the postmaster running locally


       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.

       dumpSequence(table): SELECT failed

       You do not have permission to read the database.   Contact
       your Postgres site administrator.

              Note:  pg_dump  internally  executes  SELECT state­
              ments. If you have problems running  pg_dump,  make
              sure  you  are  able to select information from the
              database using, for example, psql(1).


NOTES

       pg_dump has a few  limitations.   The  limitations  mostly
       stem  from  difficulty in extracting certain meta-informa­
       tion from the system catalogs.

       · When dumping a single table or as  plain  text,  pg_dump
         does  not  handle  large  objects. Large objects must be
         dumped in their entirety using one of the binary archive
         formats.

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


EXAMPLES

       To dump a database:

       $ pg_dump mydb > db.out

       To reload this database:

       $ psql -d database -f db.out

       To  dump  a  database called mydb that contains BLOBs to a
       tar file:

       $ pg_dump -Ft -b mydb > db.tar

       database called newdb:

       $ pg_restore -d newdb db.tar


SEE ALSO

       pg_dumpall(1), pg_restore(1), psql(1), PostgreSQL Adminis­
       trator's Guide


Man(1) output converted with man2html