Up: Reference Manual
pg_dumpall -- Extract all Postgres databases into a script file
pg_dumpall [ -h host ] [ -p port ] [ -a ]
[ -d ] [ -D ] [ -O ] [ -s ] [ -u ] [ -v ]
[ -x ]
pg_dumpall accepts the following command line arguments:
pg_dumpall also accepts the following command line arguments for
- Dump out only the data, no schema (definitions).
- Dump data as proper insert strings.
- Dump data as inserts with attribute names
- Suppress double quotes around identifiers unless absolutely necessary.
This may cause trouble loading this dumped data if there are reserved
words used for identifiers.
- Dump object identifiers (OIDs) for every table.
- Dump out only the schema (definitions), no data.
- Use password authentication. Prompts for username and password.
- Specifies verbose mode.
- Prevent dumping ACLs (grant/revoke commands) and table ownership
- -h host
- Specifies the hostname of the machine on which the
postmaster is running. Defaults to using a local Unix domain socket
rather than an IP connection.
- -p port
- Specifies the Internet TCP/IP port or local Unix
domain socket file extension on which the postmaster is listening
for connections. The port number defaults to 5432, or the value of
the PGPORT environment variable (if set).
- Use password authentication. Prompts for username and
pg_dumpall will create a file or write to stdout.
Note: pg_dumpall internally executes SELECT statements.
If you have problems running pg_dumpall, make sure you are able to
select information from the database using, for example, psql.
- Connection to database 'template1' failed. connectDB() failed:
the postmaster running and accepting connections at 'UNIX Socket'
on port 'port '? pg_dumpall could not attach
to the postmaster process on the specified host and port. If you see
this message, ensure that the postmaster is running on the proper
host and that you have specified the proper port. If your site uses
an authentication system, ensure that you have obtained the required
- Connection to database 'dbname' failed. FATAL 1:
user 'username' is not in 'pg_shadow' You do not have a valid entry
in the relation pg_shadow and and will not be allowed to access Postgres.
Contact your Postgres administrator.
- dumpSequence(table ): SELECT failed
- You do not have permission
to read the database. Contact your Postgres site administrator.
pg_dumpall is a utility for dumping out all Postgres databases into
one file. It also dumps the pg_shadow table, which is global to all
databases. pg_dumpall includes in this file the proper commands to
automatically create each dumped database before loading.
pg_dumpall takes all pg_dump options, but -f, -t and dbname
should be omitted.
Refer to pg_dump for more information on this capability.
To dump all databases:
$ pg_dumpall > db.out
Tip: You can use most pg_dump options for pg_dumpall.
To reload this database:
$ psql -e template1 < db.out
Tip: You can use most psql options when reloading.
Up: Reference Manual