pg_dump  --  Exports a database to a script file.


pg_dump [ dbname ]
pg_dump [ -h host ] [ -p port ]
    [ -t table ]
    [ -a ] [ -c ] [ -d ] [ -D ] [ -i ] [ -n ] [ -N ]
    [ -o ] [ -s ] [ -u ] [ -v ] [ -x ]
    [ dbname ]



Use this parameter to provide the name of the database pgdump should extract.


Pass this parameter to suppress the dumping of schema information, and only dump data.


Pass this parameter to drop the database schema before creating it.


Pass this parameter to dump data using INSERT commands, instead of using COPY.


Using this parameter will slow down the restoration process considerably.


Pass this parameter to dump data using INSERT commands and include column names.


Using this parameter will slow down the restoration process considerably.


Pass this parameter to ignore any version conflicts between pg_dump and the PostgreSQL database server. The pg_dump program works with system catalogs and other version- specific information, therefor most versions of it are only compatible with their corresponding PostgreSQL releases. Using this option may cause pg_dump to fail.


Pass this parameter to suppress the display of double quotes around identifiers during the dump. This option may cause difficulties if there are identifiers within the database that have been named the same as reserved words.


Pass this parameter to include double quote characters around identifier names. This is pg_dump's default behavior.


Pass this parameter to instruct pg_dump to dump object identifiers for every table within the database.


Pass this parameter to instruct pg_dump to only dump the schema information. This option will not dump data.

-t table

Pass this parameter with the name of an existing table to instruct pg_dump to only dump data for that table.


Pass this parameter to request that pg_dump prompt for username and password authentication.


Pass this parameter to view verbose information during the dump of a database.


Use this parameter to stop the dumping of ACLs and table ownership information.

-h host

Use this parameter to specify the hostname of the server that postmaster is running on. By default, pg_dump will look on the local machine and attempt to connect through domain sockets, rather than a TCP/IP connection.

-p port

If postmaster is not listening on the default socket file (or TCP/IP port), you may use this parameter to specify the socket file (or port) it is listening on. By default, it should be running on port 5432. If you have the PGPORT set, it will listen on whatever port is assigned to that variable.


Use this parameter to instruct pg_dump to prompt for a database username and password before allowing the user to perform a dump.


If successful, the pg_dump program will either dump the data to a file, or write to standard output. In the event that it is unsuccessful, you may receive one of a few standard errors.

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

This error message is displayed if pg_dump was not able to connect to the postmaster process on either the localhost or the hostname and port you specified. Make sure postmaster is running on the host you are trying to connect to; and if it is running, make sure that you have given the correct port number to connect to.

Connection to database 'dbname' failed. FATAL 1: SetUserId: user 'username' is not in 'pg_shadow'

The username you specified is not entered in the pg_shadow system relation (i.e., your username is not recognized by this database). Make sure you are using the correct username; if you are, you may have to try a different username or recreate your account within the database.

dumpSequence(table): SELECT failed

This error message is displayed if your database account does not have the correct permissions to view table information (if this is the case, you will also get an error message if you try to SELECT from the database).


The pg_dump program executes SELECT statements to retrieve data from the database; if you do not have permissions to SELECT data, you will encounter errors using pg_dump.


Use pg_dump to export a database into script format (either to standard output or a file). This includes table data and/or schema information, depending on what you specify when originally executing the program.

Once outputted, the format can be read by PostgreSQL and used to reconstruct the database with the old data (even on other machines and different architectures). After using pg_dump, make sure to check the output file for any errors that may have been encountered.


The pg_dump program has certain limitations that are primarily caused by the amount of difficulty involved in extracting some meta-information from system catalogs.


Use the following example to dump a database of the same name as the user:

% pg_dump > backupdb

The next example demonstrates how to reload the dumped database.

% psql -e database < backupdb