Practical PostgreSQL

Backing Up and Restoring Data

The concept of backup and restoration of data is a vital one to any database administrator. No system is immune from hard drive crashes, careless users, or any number of potential catastrophes that can endanger data stored within PostgreSQL.

This section covers two general methods for backing up your data. The first approach uses the pg_dump application distributed with PostgreSQL to create a set of SQL instructions with which a database can be fully restored. The second method consists of backing up the filesystem itself.

Using pg_dump

The pg_dump (which is short for “PostgreSQL dump”) application is run from a command line, and creates a list of SQL commands. These commands, executed in the order provided, re-create the database from scratch.

Here is the syntax for pg_dump:

pg_dump [ options ] dbname

In this syntax, dbname is the name of the database that you want to “dump” SQL instructions for. The available options are similar in format to those of the other database management utilities included with PostgreSQL, such as createdb. The most common flag specified in the options to pg_dump is the -f flag, which specifies the file to store the dumped SQL statements within.

Note

If the -f flag is not specified to pg_dump, the dumped SQL will be written to stdout rather than stored in a file.

The complete list of pg_dump options follow:

-a, - -data-only