Home Man
Today's Posts

Linux & Unix Commands - Search Man Pages

RedHat 9 (Linux i386) - man page for pg_dump (redhat section 1)

PG_DUMP(1)			  PostgreSQL Client Applications		       PG_DUMP(1)

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

       pg_dump [ options... ] [ dbname ]

       pg_dump	is  a  utility for saving a PostgreSQL database into a script or an archive file.
       The script files are in plain-text format and contain the SQL commands required to  recon-
       struct  the  database  to  the  state it was in at the time it was saved. To restore these
       scripts, use psql(1). They can be used to reconstruct the database even on other  machines
       and other architectures, with some modifications even on other SQL database products.

       Furthermore,  there  are  alternative  archive file formats that are meant to be used with
       pg_restore(1) to rebuild the database, and they 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 save the information necessary to re-generate all user-defined  types,  func-
       tions, tables, indexes, aggregates, 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.

       When  used with one of the archive file formats and combined with pg_restore, pg_dump pro-
       vides 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 com-
       pressed 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 manipu-
       lated with other 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.

       pg_dump	makes consistent backups even if the database is being used concurrently. pg_dump
       does not block other users accessing the database (readers or writers).

       The following command-line options are used to control the output format.

       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 speci-
	      fied for the connection is used.


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

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


	      Include large objects in dump.


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

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


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


	      Dump data as INSERT commands (rather than COPY). This will  make	restoration  very
	      slow, but it makes the archives more portable to other SQL database packages.



	      Dump data as INSERT commands with explicit column names (INSERT INTO table (column,
	      ...) VALUES ...). This will make restoration very slow, but it is necessary if  you
	      desire to rearrange column ordering.

       -f file

	      Send output to the specified file. If this is omitted, the standard output is used.

       -F format

	      Selects the format of the output.  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 elements 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 mismatch between pg_dump and the database server.

	      pg_dump can handle databases from previous releases of  PostgreSQL,  but	very  old
	      versions are not supported anymore (currently 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).


       --oids Dump object identifiers (OIDs) for every table. 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.


	      Do  not output commands to set the object ownership to match the original database.
	      Typically, pg_dump issues (psql-specific) \connect statements to set  ownership  of
	      schema  elements. See also under -R and -X use-set-session-authorization. Note that
	      -O does not prevent all reconnections to the  database,  only  the  ones	that  are
	      exclusively used for ownership adjustments.

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


	      Prohibit pg_dump from outputting a script that would require reconnections  to  the
	      database	while being restored. An average restoration script usually has to recon-
	      nect several times as different  users  to  set  the  original  ownerships  of  the
	      objects.	This  option  is  a rather blunt instrument because it makes pg_dump lose
	      this ownership information, unless you  use  the	-X  use-set-session-authorization

	      One possible reason why reconnections during restore might not be desired is if the
	      access to the database requires manual interaction (e.g., passwords).

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


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

       -S username

	      Specify the superuser user name to use when disabling triggers.  This is only rele-
	      vant if --disable-triggers is used.  (Usually, it's better  to  specify  --use-set-
	      session-authorization, and then start the resulting script as superuser.)

       -t table

	      Dump data for table only.


	      Specifies verbose mode. This will cause pg_dump to print progress messages to stan-
	      dard error.



	      Prevent dumping of access privileges (grant/revoke commands).

       -X use-set-session-authorization

	      Normally, if a (plain-text mode) script generated by pg_dump must alter the current
	      database	user  (e.g., to set correct object ownerships), it uses the psql \connect
	      command.	This command actually opens a new connection, which might require  manual
	      interaction  (e.g.,  passwords).	If  you  use the -X use-set-session-authorization
	      option, then pg_dump  will  instead  output  SET	SESSION  AUTHORIZATION	[set_ses-
	      sion_authorization(7)] commands. This has the same effect, but it requires that the
	      user restoring the database from the generated  script  be  a  database  superuser.
	      This option effectively overrides the -R option.

	      Since  SET  SESSION  AUTHORIZATION [set_session_authorization(7)] is a standard SQL
	      command, whereas \connect only works in psql, this option also enhances  the  theo-
	      retical portability of the output script.

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

       -X 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  trig-
	      gers 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 specify  --use-
	      set-session-authorization  and  then  be careful to start the resulting script as a
	      superuser. If you give neither option, the entire script must be run as superuser.

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

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

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

       -p port

	      Specifies  the  Internet	TCP/IP port or local Unix domain socket file extension on
	      which the server is listening for connections. The port number defaults to 5432, or
	      the value of the PGPORT environment variable (if set).

       -U username
	      Connect as the given user

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

       Long option forms are only available on some platforms.




       PGUSER Default connection parameters

       Connection to database 'template1' failed.
       connectDBStart() -- connect() failed: No such file or directory
	       Is the postmaster running locally
	       and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?

       pg_dump could not attach to the PostgreSQL server on the specified host and port.  If  you
       see  this  message, ensure that the server is running on the proper host and that you have
       specified the proper port.

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

       If your installation 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  with-
       out any local additions, copy from template0 not template1, for example:


       pg_dump has a few limitations:

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

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

       Members	of tar archives are limited to a size less than 8 GB.  (This is an inherent limi-
       tation of the tar file format.) Therefore this format cannot be used if the textual repre-
       sentation  of  a  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.

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

       $ pg_dump -Ft -b mydb > db.tar

       To reload this database (with large objects) to an existing database called newdb:

       $ pg_restore -d newdb db.tar

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

       pg_dumpall(1), pg_restore(1), psql(1), PostgreSQL Administrator's Guide

Application				    2002-11-22				       PG_DUMP(1)

All times are GMT -4. The time now is 03:43 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
Show Password