3 Ways to Backup & Restore a PostgreSQL Database

Database Management Database Management
PostgreSQL PostgreSQL

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.

  1. file system backups require the database to be turned off in order to generate usable backups
  2. 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 databasebackup located after I run the pg_dump command?

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

In many linux distros (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 find a file on linux you can use the following command to located your database backup.

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

dropdb dbname

createdb dbname

And run the psql command once again.

psql dbname < dbbackupfilename.sql

Did You Enjoy the Tutorial?

Consider sending some love to Freddie the creator.

Comments (0)

You need to be logged in to comment..

Topics Mentioned in this Tutorial

Database Management
2
0
5
Data Management is a process that comprises all disciplines related to managing data as a resource. It applies to both physical and digital data, however, in this day and age most database management refers to later. Database management has become more vital to every day business and the world we live in today. With well managed data, business, industry and the public can derive valuable information that can be used in decision making and prediction. When managing data its important to have an overall knowledge of the underlaying database and its structure. The process also involves ...
PostgreSQL
4
0
7
PostgreSQL, also known as Postgres, is a relational database management system (RDBMS) that is free and open-source. The software is widely popular for storing relation data for web-base applications. PostgreSQL emphasise extensibility and technical standards compliance. Which make the database used for a wide variety of web services with many concurrent users. PostgreSQL can be deployed on a single machine or spread out on clusters among different data centers....