Creating a Filesystem Backup

One simple way of backing up your PostgreSQL database is to take a filesystem backup. Perhaps you have a system that backs up all of your servers on a nightly basis and you just want to include your PostgreSQL database. Unfortunately there is one condition that must be met before you can use this method safely: The database must be shut down. Some environments allow you to shut the database down on a nightly basis and that will allow you make a filesystem backup.

Doing so is as simple as using a utility like tar to backup the data directory as in the following command:

tar -cvzf my_backup.tgz ~/data

The "z" option tells tar to compress the resulting file so it takes less space.

If you are running on hardware that allows you to take a consistent snapshot snapshot of the filesystem, you can do so with a running PostgreSQL database. Should you need to restore from the backup, PostgreSQL will think that it was shutdown improperly and  rerun the WAL files, which is not a problem.

If you implement streaming replication or log shipping, you can set the database into a backup mode that works with write-ahead logging (WAL) to ensure your database files are kept in sync. If you try to run the SELECT pg_start_backup('x'); command on a database that does not have log shipping enabled, you will get an error indicating that WAL archiving is not active.