Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Using pg_dumpall

PostgreSQL supplies a supplementary wrapper command to the pg_dump application called pg_dumpall . The primary use of this application is to allow the entire cluster of PostgreSQL databases on a system to be dumped at once, rather than having to perform a pg_dump for each database, one at a time.

Here is the syntax for pg_dumpall , displayed from the - -help flag:

  pg_dumpall [ -c ] [ -h 
 ] [ -p 
 ] [ -g ]

The pg_dumpall command accepts the same connection parameters available to pg_dump . The following are the pg_dumpall -specific parameters:


Specifies that SQL statements to drop existing global objects will precede the SQL statements to create those objects.


Specifies that HOSTNAME should be connected to, rather than the localhost, or the host defined by the PGHOST environment variable. Use this when the target database is on another server.

-p PORT , - -port= PORT

Specifies that the database connection should be made on port PORT , rather than the default port (usually 5432).

-g, - -globals-only

Specifies that only global objects will be dumped. This is primarily useful for recreating just users and groups, or for duplicating them on another machine (by taking the dump to another machine, and executing it). The -g flag implicitly causes all users to be deleted from the pg_shadow table prior to the CREATE statements. Exercise caution with the output from this command!


Do not pass the -? flag to pg_dumpall , as it will result in passing that flag to pg_dump for each database, which may result in quite a few more help screens than you would expect. If you wish to view pg_dumpall 's help, use the - -help flag.

Note that as of PostgreSQL 7.1.x, the use of the pg_dumpall script does have some practical limitations over the normal use of pg_dump . For example, the -u flag cannot be used to provide a different username and password, and the -F flag may not be used to specify a format other than plain text (your backups will be stored as plain text, regardless of chosen format). This means that the -b flag cannot be used with pg_dumpall either, as it requires a format other than plain-text.

While the -f flag can be used to pass a filename through to pg_dump , doing so does not create a complete dump, as pg_dumpall 's global data is still sent to stdout . To solve the problem of not having the -f flag available to pg_dumpall , the shell re-direction operator (>) can be used to redirect the output of pg_dumpall to a file.

A simple workaround to the lack of the -u flag is to set the PGUSER environment variable. You can also set the PGPASSWORD environment variable in this fashion if you are connecting to a system which requires password authentication, and you do not wish to provide a password for each database that it connects to and dumps.

Example 9-22 demonstrates a simple bash-shell shorthand for a temporary environment variable (PGUSER) when invoking any PostgreSQL client. While not usually necessary, it can be a handy trick to know for exceptions such as the pg_dumpall script. Example 9-22 uses this technique to create one dump file for all databases.

Example 9-22. Using pg_dumpall

[jworsley@booktown ~]$ 
PGUSER=postgres pg_dumpall > all.sql

The first part of the shell statement demonstrated in Example 9-22 sets a temporary environment variable named PGUSER to the value of postgres . This variable is set for the duration of the pg_dumpall command, and expires when the command has finished.

Note: The pg_dumpall command generally requires that the user executing the script be a PostgreSQL superuser. This is because the pg_dumpall command requires access to the PostgreSQL system catalogs, as it dumps global objects as well as database objects.

You can also run the pg_dumpall command remotely, though be sure to set any environment variables as needed. These will depend greatly on the remote host configuration in the pg_hba.conf file.

You should use the pg_dumpall command if it is especially inconvenient to backup all your existing databases individually, or if you have any kind of complex user and group system in place. If you are inhibited by the limitations of pg_dumpall for data output (particularly if your database makes use of large objects), the simplest solution is to use pg_dumpall with the -g flag to keep a backup of all user and group data, and to subsequently use pg_dump for each database which needs to be backed up, individually.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire