pg_dump man page on Peanut

Man page or keyword search:  
man Server   7435 pages
apropos Keyword Search (all sections)
Output format
Peanut logo
[printable version]

PG_DUMP(1)		PostgreSQL Client Applications		    PG_DUMP(1)

NAME
       pg_dump - extract a PostgreSQL database into a script file or other ar‐
       chive 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.

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

       -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 col‐
	      umns  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 in archive formats that
	      support compression. (Currently only the custom  archive	format
	      supports compression.)

       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 data‐
       base,  be  careful  to restore the output of pg_dump into a truly empty
       database; otherwise you are likely to get errors due to duplicate defi‐
       nitions	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.

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			  2005-11-05			    PG_DUMP(1)
[top]

List of man pages available for Peanut

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net