Ever
accidentally deleted a database table, or had the disk with your live database
crash? If so, you know all too well that sinking feeling you get when you
realize that weeks of hard work just vanished into nothingness.

Well,
it doesn’t always have to be that way. If your data
is stored in a PostgreSQL table, you can prevent mishaps by regularly
backing up your databases. PostgreSQL comes with built-in tools to perform such
back ups and, in the event of a system crash or accident,
the tools allow you to “roll back” and restore the system to its
original state from a previously-saved snapshot.

Backing up data

PostgreSQL
comes with a built-in backup tool called pg_dump. The tool works by reading the
selected database and reproducing its contents as a series of SQL commands,
which serve as a snapshot and can be used to recreate the data at a later date.
A client-server connection is used to perform backups.

Note:
Before proceeding, ensure that you have the credentials needed to log in to the
server and read the database or table you wish to back up. You can do this by
using the PostgreSQL command-line client, psql, to attempt a server login. Pass the host name (-h),
user name (-u) and password (-p), and database name to the client, and check whether
you are granted access.

Using
pg_dump is simplicity
itself—just run the program at the command prompt with the name of the database
to be exported, as below (alter the path to your PostgreSQL installation as
needed):

$ /usr/local/pgsql/bin/pg_dump -D -h localhost -U pgsql test > test.bak

This
will create the file test.bak and populate it with the SQL commands needed to regenerate
the database. Look inside the file, and you will see something like this next
example:

—
— Name: pets; Type: TABLE; Schema: public; Owner: root; Tablespace:
—

CREATE TABLE pets (
    species character varying,
    name character varying
);

ALTER TABLE public.pets OWNER TO root;

—
— Data for Name: pets; Type: TABLE DATA; Schema: public; Owner: root
—

INSERT INTO pets (species, name) VALUES (‘dog’, ‘Sparky’);
INSERT INTO pets (species, name) VALUES (‘cat’, ‘Tabitha’);

If
you would prefer to back up all the
databases on the system (rather than just one), you can use the pg_dumpall command instead of pg_dump. This command
backs up all the databases PostgreSQL knows about (including its own system
databases) to a single file. Here’s an example of how to use it:

$ /usr/local/pgsql/bin/pg_dumpall -D -h localhost -U pgsql > all.bak

To
ensure that your backups are always fresh, you should schedule a recurring
backup, by adding the pg_dump or pg_dumpall command to
your cron table. Here
are two example cron entries. The
first one backs up the test database every night at 3 AM,
and the second backs up all the databases once a week, on Fridays at 9 PM:

0 3 * * *   /usr/local/pgsql/bin/pg_dump -D -h localhost -U pgsql test > /disk2/test.bak0 21 * * 5 /usr/local/pgsql/bin/pg_dumpall -D -h localhost -U pgsql > /disk2/all.bak

Restoring data

Restoring
data from a backup is even simpler than creating the backup—all you need do is
recreate the databases by executing the SQL commands in the backup file.

If
you backed up a single database with pg_dump, the backup will contain CREATE
TABLE
statements to reproduce the original tables. However, it is necessary for you
to first generate an empty database to store these tables. This is easily done
with a call to the createdb tool, also part of the PostgreSQL suite:

$ /usr/local/pgsql/bin/createdb restored

You
can now restore the database by executing the SQL commands in the backup file:

$ /usr/local/pgsql/bin/psql -h localhost -U pgsql -d restored < test.bak

If
you backed up all the databases with pg_dumpall, there is no
requirement to first create databases, because the backup file will contain the
necessary calls to CREATE DATABASE. In this case, simply pipe the backup
file through the psql command-line client without specifying a target database:

$ /usr/local/pgsql/bin/psql -h localhost -U pgsql < all.bak

Once
the data recovery is complete, you should be able to log in to the server and
see your restored data.