The PostgreSQL dump generates a text file that contains SQL commands which regenerates the database into the previous state it was in. The command to accomplish this is:

   pg_dump  dbname >  output_file

The dbname is the name of the database that you want to perform a dump on. The output_file is the file name that will hold the data from the dump.

To perform this command requires you to have read access permission to all of the tables in the database that you want to backup. Depending on your situation, you may need the postgres superuser permission to access the database. The pg_dump command does not require you to have special permissions to access it.

You can perform the pg_dump command either at the local host, or from a remote machine that has access to the database. This is because PostgreSQL is a client application that can be run from any machine with the correct access permissions. The following is a list of optional parameters that are used in conjunction with the pg_dump command to enable remote access:

-h host

Specifies which host database server is to be contacted. If this option is not specified, it defaults to the local host or the PGHOST environment variable.

-p port

Specifies which port to use. If this option is not used in the command, it defaults to the port indicated in the PGPORT environment variable.


Switches to another user name. If this option is not used, it defaults to the current Unix user name. Once the command is executed, it automatically prompts you to enter the name and password. Another way of switching to a different user name is by setting the PGUSER environment variable. In either case, you will need to be approved by the client authentication mechanism in order to run the pg_dump program.

For instance, this uses the pg_dump command from a remote machine:

   pg_dump -h -p 5432 booktown > booktown.db

We will discuss more about using pg_dump via remote machines later in this chapter.

There are other useful parameters that can be used with the pg_dump command. These parameters are also especially helpful in administrative tasks. They are listed below:


Dumps all object identifiers (oids). When a dump is performed, the OIDs are not automatically backed up because they are separate from tables. Using this tag backs up all of the object identifiers in your database.

-a, --data-only

Dumps only the data, not the schema. The default dumps both the data and the schema.

-C, --create

Outputs commands to create the database. After the dump is performed, you will need to manually create a database to hold the restored dump. This option allows you to skip having to create a database to hold the data because it is automated.

-d, --inserts

Dumps data using INSERT command. The default will perform the dump using the COPY command. (See note at the end of this list for reasons why this is useful)

-D, --attribute-inserts

Dumps data using the INSERT command with attribute names.

-F, --format {c|t|p}

Outputs the file format as either custom, tar, or plain text. If not specified, it defaults to a plain text file.

-i, --ignore-version

Continues with dump only when the database version is not equal to (!=) the pg_dump version.

-s, --schema-only

Dumps only the schema which does not include any data. The default dumps both the schema and the data.

-t, --table=TABLE

Dumps the specified table only. To specify all tables, use the asterisk (*).

-v, --verbose

Prints a detailed report.

The advantage to using these options is that a normal dump only performs a COPY of the data. Sometimes, the dump will contain an incorrect line of code, causing the dump to become useless. On the other hand, when an INSERT is used for the dump, it performs the dump for each line at a time. If one line of code breaks, you will only need to fix that line, not the whole document.

The next example remotely connects to the database server then dump all of the information and save it to a tar file:

   pg_dump -C -D -F t -h -p 5432 booktown > booktown.tar

The dump automatically creates a database upon being restored. Use the pg_restore command to restore the dump:

   pg_restore booktown.tar -C | psql template1

The -C in the pg_restore command means to create the database upon restoration. Then we piped the results to the psql command, it in turn opened the template1 database. It needs to access an existing database in order to create the new database and hold the restored data. The new database that will be created has the same name as the dumped database.

Note Specifying a database

If you do not specify a database for it to pipe the information into, then it will automatically try to connect to a database with your user name.