pg_dumpall

PG_DUMPALL(1)		PostgreSQL Client Applications		PG_DUMPALL(1)



NAME
       pg_dumpall - extract a PostgreSQL database cluster into a script file


SYNOPSIS
       pg_dumpall [ option... ]

DESCRIPTION
       pg_dumpall  is  a utility for writing out (‘‘dumping’’) all PostgreSQL
       databases of a cluster into one script file. The script file  contains
       SQL  commands  that  can	 be  used  as input to psql(1) to restore the
       databases. It does this by calling pg_dump(1) for each database	in  a
       cluster.	  pg_dumpall also dumps global objects that are common to all
       databases.  (pg_dump does not save  these  objects.)   This  currently
       includes	 information about database users and groups, and access per-
       missions that apply to databases as a whole.

       Thus, pg_dumpall	 is  an	 integrated  solution  for  backing  up	 your
       databases.  But	note  a limitation: it cannot dump ‘‘large objects’’,
       since pg_dump cannot dump such objects into text files.	If  you	 have
       databases containing large objects, they should be dumped using one of
       pg_dump’s non-text output modes.

       Since pg_dumpall reads tables from all databases you will most  likely
       have to connect as a database superuser in order to produce a complete
       dump. Also you will need superuser privileges  to  execute  the	saved
       script  in  order to be allowed to add users and groups, and to create
       databases.

       The SQL script will be written to the standard output. Shell operators
       should be used to redirect it into a file.

       pg_dumpall needs to connect several times to the PostgreSQL server and
       might be asking for a password each time. It is convenient to  have  a
       $HOME/.pgpass file in such cases.

OPTIONS
       The following command-line options are used to control the content and
       format of the output.

       -a

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

       -c

       --clean
	      Include SQL commands  to	clean  (drop)  the  databases  before
	      recreating them.

       -d

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

       -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,	but  it is necessary if you desire to
	      rearrange column ordering.

       -g

       --globals-only
	      Dump only global objects (users and groups), no databases.

       -i

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

	      pg_dumpall 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_dumpall then fails, don’t say  you
	      weren’t warned).

       -o

       --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).  Otherwise, this option
	      should not be used.

       -s

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

       -v

       --verbose
	      Specifies verbose mode. This will	 cause	pg_dumpall  to	print
	      progress messages to standard error.

       -x

       --no-privileges

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


       The  following  command-line  options  control the database connection
       parameters.

       -h host
	      Specifies the host name of the machine on	 which	the  database
	      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
	      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	 com-
	      piled-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
       PGHOST

       PGPORT

       PGUSER Default connection parameters

NOTES
       Since pg_dumpall calls pg_dump internally,  some	 diagnostic  messages
       will refer to pg_dump.

       Once restored, it is wise to run ANALYZE on each database so the opti-
       mizer has useful statistics. You can also run vacuumdb -a -z  to	 ana-
       lyze all databases.

EXAMPLES
       To dump all databases:

       $ pg_dumpall > db.out


       To reload this database use, for example:

       $ psql -f db.out template1

       (It  is	not  important	to  which database you connect here since the
       script file created by pg_dumpall will contain  the  appropriate	 com-
       mands to create and connect to the saved databases.)

SEE ALSO
       pg_dump(1). Check there for details on possible error conditions.



Application			  2008-01-03			PG_DUMPALL(1)