Yolinux.com

pg_dump manpage

Search topic Section
Get manual page for the search topic
List all commands matching the search topic
List all topics in the manpage index

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

       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	mecha-
       nism. 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 may specify the option when you call
	      pg_restore.

       -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 may 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 may specify the option when you call
	      pg_restore.

       -d

       --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.  Note  that  the
	      restore may fail altogether if you have rearranged column order.
	      The -D option is safer, though even slower.   Also,  while  this
	      option  generates	 errors	 for  invalid  data,  it  allows other
	      INSERTs to continue loading data into the table.

       -D

       --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, while this option
	      generates errors for invalid data, it allows  other  INSERTs  to
	      continue loading data into the table.

       -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 envi-
	      ronment variable to the desired dump encoding.)

       -f file

       --file=file
	      Send output to the specified file. If this is omitted, the stan-
	      dard output is used.

       -F format

       --format=format
	      Selects the format of the output.	 format can be one of the fol-
	      lowing:

	      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 exclu-
		     sion of database objects 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 loading data as well as object defi-
		     nitions. This format is also compressed by default.


       -i

       --ignore-version
	      Ignore version mismatch between pg_dump and the database server.

	      pg_dump can handle databases from	 previous  releases  of	 Post-
	      greSQL,  but  very  old versions are not supported anymore (cur-
	      rently prior to 7.0).  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 schema

       --schema=schema
	      Dump the contents of schema only. If this option is  not	speci-
	      fied,  all  non-system  schemas  in  the target database will be
	      dumped.

	      Note: In this mode, pg_dump makes no attempt to dump  any	 other
	      database	objects that objects in the selected schema may depend
	      upon. Therefore, there is no guarantee that  the	results	 of  a
	      single-schema  dump  can	be successfully restored by themselves
	      into a clean database.


       -o

       --oids Dump object identifiers (OIDs) as part of the data for every ta-
	      ble.  Use	 this  option  if  your application references the OID
	      columns in some way (e.g., in a foreign key constraint).	Other-
	      wise, 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 may specify the option when you call
	      pg_restore.

       -R

       --no-reconnect
	      This option is obsolete but still accepted for backwards compat-
	      ibility.

       -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 data for table only. It is possible for there to be	multi-
	      ple  tables  with the same name in different schemas; if that is
	      the case, all matching  tables  will  be	dumped.	 Specify  both
	      --schema and --table to select just one table.

	      Note:  In	 this mode, pg_dump makes no attempt to dump any other
	      database objects that the selected table may depend upon. There-
	      fore,  there  is no guarantee that the results of a single-table
	      dump can be successfully restored by  themselves	into  a	 clean
	      database.


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

       -X disable-dollar-quoting

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

       -X disable-triggers

       --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 may specify the option  when  you  call
	      pg_restore.

       -X use-set-session-authorization

       --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, may  not  restore  properly.
	      Also,  a	dump  using  SET  SESSION AUTHORIZATION will certainly
	      require superuser privileges to restore correctly, whereas ALTER
	      OWNER requires lesser privileges.

       -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 com-
	      pressed, 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.


       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 exten-
	      sion on which the server is listening for connections.  Defaults
	      to  the  PGPORT  environment  variable, if set, or a compiled-in
	      default.

       -U username
	      Connect as the given user

       -W     Force a password prompt. This should happen automatically if the
	      server requires password authentication.


ENVIRONMENT
       PGDATABASE

       PGHOST

       PGPORT

       PGUSER Default connection parameters.

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

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;


       pg_dump has a few limitations:

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


       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.

       Because pg_dump is used to tranfer data	to  newer  versions  of	 Post-
       greSQL,	the  output  of	 pg_dump  can  be loaded into newer PostgreSQL
       databases. It also can read older  PostgreSQL  databases.  However,  it
       usually	cannot	read newer PostgreSQL databases or produce dump output
       that can be loaded into older database versions.	 To  do	 this,	manual
       editing of the dump file might be required.

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 to a tar file:

       $ pg_dump -Ft mydb > db.tar


       To reload this dump into an existing database called newdb:

       $ pg_restore -d newdb db.tar


HISTORY
       The pg_dump utility first appeared in Postgres95 release 0.02. The non-
       plain-text output formats were introduced in PostgreSQL release 7.1.

SEE ALSO
       pg_dumpall(1), pg_restore(1), psql(1), Environment Variables (the docu-
       mentation)



Application			  2010-12-14			    PG_DUMP(1)
YoLinux.com Home Page
YoLinux Tutorial Index
Privacy Policy | Advertise with us | Feedback Form |
Unauthorized copying or redistribution prohibited.
    Bookmark and Share