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.
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
pg_dump is the -f flag, which specifies the file to
store the dumped SQL statements within.
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
Forces only ...