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):

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:

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:

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:

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:

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

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:

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