3 Ways to Backup & Restore a PostgreSQL Database

PostgreSQL is a fantastic free and open-source relational database. I would personally argue that it's the best.

Today we are going to have a look at how you can backup and restore your PostgreSQL database.

Postgres provides to 3 fundamentally different ways of backing up your database. All with their own pros, cons and distinct uses-cases.

SQL Dump
File System Backup
Continuous Archiving

1: SQL Dump

The SQL dump (pg_dump) is a command that generates a text file that contains necessary SQL instruction that will recreate the database. The dumped file can be fed back to the server to recreate the existing structure of the database, including the data itself.

It's possibly the most common way to backup your PostgreSQL database, and shares many similarities with the mysqldump method used for the extremely popular MySQL database. Backup PostgreSQL with pg_dump

pg_dump dbname > dbbackupfilename.sql

The pg_dump command can be used to perform database backups from any remote host that you have access to. It's important to understand that the command needs access to all tables that you want to backup so it should be initiated by a database user with superuser privilege.

If your are working with a large database it useful to gizip the file as you dump it. You can gizip the dump by running the following command instead.

pg_dump dbname | gzip > dbbackupfilename.gz

Restore PostgreSQL pg_dump

To restore the pg_dump file we make use of the psql command

psql dbname < dbbackupfilename

If you want to make sure that the database restoration stops incase of an error related you can set the ON_ERROR_STOP flag.

psql --set ON_ERROR_STOP=on dbname < dbbackupfilename

If you are handling a large database you might have gzipped the dump to minimize file-size and transfer time. To restore a gzipped pg_dump file you can run the following command.

gunzip -c filename.gz | psql dbname

2: File System Backup

In some cases the SQL dump is not optimal for your situation. Another useful way to backup PostgreSQL is to do a file system backup that directly copies all the file that PostgreSQL uses to store data in the database.

tar -cf dbbackupfilename.tar /usr/local/pgsql/data

It's important to understand that the file system-level backup imposes extra restrictions when compared with the pg_dump method.

file system backups require the database to be turned off in order to generate usable backups
file system backups only work for complete backup and restoration of an entire database cluster.

3: Continuous Archiving (With Point In Time Recovery)

If your data is time sensitive or of an extremely fragile nature, continuous archiving might be interesting to you. Continuous archiving allows you to provide point in time recovery for your PostgreSQL database. With point in time recovery you can restore your data from any point in time.

The point in time recovery (PITR) can be essential to organizations working in large teams. If someone in your team accidentally dropped a table, or some schema got corrupted the day before, you can simply revert back to that point in time, before the incident occurred.

There are two aspects that underpins continuous archiving. The base backup and WAL archive. The WAL archive is built up of a series of WAL files, all of which includes every diff since the base backup was created.

Compared to the other two backup sulutions, continues archiving requires initial setup. We need to created the WAL archive and create the base backup for this to work.

Setup PostgreSQL WAL Archiving & Base Backup.

We start by creating directories and set directory permissions for both the WAL archive and the base backup.

mkdir -p /var/lib/pgsql/wals/

mkdir -p /var/lib/pgsql/backups/

chown postgres:postgres -R /var/lib/pgsql/backups/

chown postgres:postgres -R /var/lib/pgsql/wals/

Then we need to tell PostgreSQL that we ar going to use a WAL Archive by adding the following to the postgresql.conf config file.

wal_level=archive

archive_mode=on

archive_command = 'test ! -f /var/lib/pgsql/wals/%f && cp %p /var/lib/pgsql/wals/%f'

Don't forget to restart your database after you have changed the config.

service postgresql restart

Now it is time to prepare the base backup

su - postgres

psql -c "SELECT pg_start_backup('label');" postgres

tar -C /var/lib/pgsql/data/ -czvf /var/lib/pgsql/backups/basebackupfilename.tar.gz .

psql -c "SELECT pg_stop_backup();" postgres

Restore PostgreSQL from WAL Archiving & Base Backup

To restore PostgreSQL from WAL archive its important that we first stop our database.

/etc/init.d/postgresql stop

Then we extract the database.

tar xvf /var/lib/pgsql/backups/basebackupfilename.tar.gz -C /var/lib/pgsql/data

When the database has been extracted we can go ahead and create a config file for the recovery. Start by navigating to the pgsql/data folder and create a recovery.conf file.

cd /var/lib/pgsql/data

cat recovery.conf

Add the restore command to your newly created recovery.conf file.

restore_command = 'cp /var/lib/pgsql/wals/%f %p'

You can now start your PostgreSQL database again

/etc/init.d/postgresql start

Now the recovery process should be initiated, when the process is completed the server will rename the recovery.conf file to recovery.done.

Common Questions

Where is my database backup located after I run the pg_dump command?

This all depends on what operating system, environment and server setup you got running.

In many Linux distributions (which is commonly used when hosting online) the database will end up in the Postgres folder.

/var/lib/pgsql/dbbackupfilename.sql

#or

/var/lib/postgres/dbbackupfilename.sql

To easily locate files on Linux you can make use of the "find" command. It allows you to locate your database backup where ever it is throughout your system.

find 'dbbackupfilename.sql'

Common Errors When Restoring Your Database

PostgreSQL Error: Constraint for relation already exists

You might get this error if you are trying to restore a database dump to another database that already exists, and that database contains similar data or structures.

To fix this you can simply drop your existing database and create a new one