Postgres WAL Archiving: A Complete Walkthrough

Every good database administrator knows the importance of having a rigorous system of backups to protect operational data. The size of the data stored in a database as well as the speed at which the data can change necessitates specialized backup procedures rather than something simple, such as copying files off a filesystem.

One of these specialized procedures that is supported by Postgres is called WAL archiving. The Postgres WAL (Write-Ahead Log) is the location in the Postgres cluster where all changes to the cluster's data files are recorded before they're written to the heap. When recovering from a crash, the WAL contains enough data for Postgres to restore its state to the last committed transaction. WAL Archiving is simply the process of backing up these WAL files.

In this article, we are going to go through setting up WAL archiving onto S3 from beginning to end in a development environment.

Reasons for WAL Archiving

There are three common methods available in Postgres to back up data: SQL dumps, filesystem level backups, and WAL archiving. It's also possible to make backups using pg_basebackup without setting up continuous archiving.

SQL dumps are fairly easy to set up but they involve duplicating data over and over again. Data usage grows linearly with the frequency of the backup, even if the database is not changing very much between backups. They also take a very long time to create and restore. If you're currently using SQL dump backups, be sure to test their recovery time and decide whether the time that recovery takes is acceptable to you. It's possible for recovery to take hours, days, or even weeks if your database is large enough.

Filesystem level backups, like SQL dumps, take up a lot of space, but have an additional disadvantage that getting them right can be tricky. An instantaneous snapshot needs to be taken of the entire filesystem. Tools like cp and rsync copy files sequentially and will produce an inconsistent backup from which it is impossible to recover data.

WAL archiving has two advantages over both these previous methods.

First, backup sizes do not grow linearly with the frequency of the backup (although each WAL file is exactly 16MB, they can be compressed down to only a few KB if they're mostly empty). Instead, backup size grows with the write activity of the database. This allows backups to be taken much more frequently, with the Postgres documentation suggesting frequencies of once a minute.

Second, WAL archiving provides the capability for Point-in-Time recovery. Since the WAL is a continuous log of all the changes that happen to the database, it is possible to stop replaying the WAL at an arbitrary point during recovery and restore the database to any point in history that is spanned by the available WAL files.

WAL-G is a piece of software that helps to integrate the internal Postgres WAL archiving features with cloud storage providers such as Amazon S3.

Disadvantages of WAL Archiving

Recovering from WAL archives will be slower than from a filesystem backup. The WAL archives have to be fetched and replayed since the last base backup, whereas a filesystem backup will be ready to go as soon as it replays from the most recent checkpoint (by default, checkpoints are taken at least every 5 minutes, but may happen more frequently on high traffic databases).

Data loss is still possible as well, since WAL files are only closed and archived at finite intervals. If you archive the WAL every 5 minutes, you can lose 5 minutes of data (potentially more if the server goes down before the previous WAL archive is uploaded).

Configuring S3

We'll be archiving all of our WALs to an S3 bucket, so the first step is to create that bucket. I'm calling my bucket wal-archive-bucket and I'm putting it in the US East 2 (Ohio) region.

Next, create a user account with which to access that bucket. In the IAM control panel select "programmatic access", as this user should not need console access. Remember to download the access keys!

We'll also want to create a custom policy for this user to grant them the necessary permissions. Here's what I used:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowObjectReadWrite",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject"
            ],
            "Resource": "arn:aws:s3:::wal-archive-bucket/*"
        },
        {
            "Sid": "AllowBucketList",
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": "arn:aws:s3:::wal-archive-bucket"
        }
    ]
}

Configuring Postgres

WAL-G requires some features that were introduced in Postgres 9.6, so make sure your version of Postgres is recent enough.

For this example we'll be creating a new Postgres cluster to work with, since it's not possible to set up WAL archiving per database. All databases in a cluster write to the same WAL.

The first step is to find where pg_ctl resides on your system. For me, it's /usr/lib/postgresql/9.6/pg_ctl. I'm going to create a new cluster called new-cluster in my Documents directory.

/usr/lib/postgresql/9.6/bin/pg_ctl init -D ~/Documents/new-cluster

The next step is to open new-cluster/postgresql.conf and make a few important modifications:

port: I have Postgres running on my system already, so for this second cluster, I'm going to set it to a nonstandard port. I'm using port 6000.
wal_level: By default, Postgres logs a minimal amount of information to the WAL. For WAL archiving, this needs to be set to at least replica (Postgres 10 writes WALs at replica level by default).
archive_mode: Set to on to enable archive mode.
archive_timeout: This variable tells Postgres to rotate WAL files at least this frequently. Generally this should be set to at least 60 seconds, as each WAL file generated will be exactly 16MB (though this can be compressed significantly). If you want to ship logs at a frequency greater than 1 minute, look into running pg_receivexlog on another server.

Finally, we're ready to start the new cluster. Call postgres with the directory of your cluster and tell it to put the socket and lock file in /tmp, since otherwise it might try to put it in a directory you don't have write permissions to:

/usr/lib/postgresql/9.6/bin/postgres -D ~/Documents/new-cluster/ -k /tmp

Now we can populate the database with data. You'll need to create a database with createdb before you can connect to it via psql. Since we're running Postgres on a unconventional port and with a non-conventional socket location, we need to specify some additional parameters. For both createdb and psql use -h localhost -p 6000 to connect over the network (rather than the socket that it won't find, because we've moved it). I'm using Python and a Django app on this database, but you can put whatever data you want here. You could use pg_dump and pg_restore to populate the database with data from any other cluster you have.

Configuring WAL-G

It's time to get the WAL-G binary. Grab it from GitHub and put it somewhere in your PATH.

Next, tell WAL-G how to authenticate with your IAM user's credentials. Create a file at ~/.aws/credentials that looks something like this:

[default]
aws_access_key_id = <access key>
aws_secret_access_key = <secret_key>

WAL-G also needs to know how to connect to the database. We can do that with Postgres environment parameters (the whole list of possible env parameters can be found here).

For the backup scripts, I'll use the following parameters:

export PGHOST=localhost
export PGPORT=6000
export AWS_REGION=us-east-2
export WALE_S3_PREFIX=s3://wal-archive-bucket

We'll use these for our two scripts: the basebackup script and the archive command.

make_basebackup.sh:

#!/bin/bash

export PGHOST=localhost
export PGPORT=6000
export AWS_REGION=us-east-2
export WALE_S3_PREFIX=s3://walshipping-bucket-test

wal-g backup-push $1

archive_command.sh:

#!/bin/bash

export PGHOST=localhost
export PGPORT=6000
export AWS_REGION=us-east-2
export WALE_S3_PREFIX=s3://walshipping-bucket-test

wal-g wal-push $1

The next step is to change archive_command in postgresql.conf to point to archive_command.sh %p.

Make your first base backup

It's time to start backing up! If you run make_basebackup.sh ~/Documents/new-cluster/, you should see WAL-G uploading the various files in the cluster to S3. After the backup is complete, Postgres will push the relevant WAL files to S3 as well, using the archive_command we set up earlier.

IMPORTANT: pg_basebackup does not take a consistent snapshot of the database. You won't be able to restore with only the base backup, you need the WAL from when the backup started to when the backup ended in order to be able to restore the database to a consistent state.

Check the S3 bucket for your backup files. You should see two directories in your bucket: basebackups_ and wal_ ending with a string of numbers.

Also, if you're running Postgres like I am, you should see its debug output. Each time Postgres finishes writing a WAL file, it will call the archive command to upload it to S3. The output of the archive command looks something like this:

WAL PATH: wal_005/000000010000000100000005.lz4
BUCKET: walshipping-bucket-test
SERVER:

Test Restoration

Remember that untested backups are not real backups! In production, you want to be testing your backups regularly. We're not in production right now though, so we'll only test the backup we just made.

wal-g backup-fetch will get your base backup from S3 and create a new cluster directory in the location you tell it. You can also pass the path of a specific base backup or LATEST for the latest backup.

wal-g backup-fetch /new/path/to/restored-cluster LATEST

Inside the restored-cluster directory, create a recovery.conf file. This file will tell Postgres where to get the WAL from and how far to play it back. We'll use another script for the restore_command:

#!/bin/bash

export PGHOST=localhost
export PGPORT=6000
export AWS_REGION=us-east-2
export WALE_S3_PREFIX=s3://walshipping-bucket-test

wal-g wal-fetch $1 $2

We'll configure our restore command like so:

restore_command = '/path/to/restore_command.sh %f %p'

It is also possible to configure recovery_target_time or recovery_target_xid to tell the cluster exactly which point to recover to if you do not want to recover to the end of the available WALs.

Once this new cluster starts, it will use the restore_command to fetch all the necessary WAL files from S3. Depending on how many there are, this could take a while.

Once the server has finished recovery it will print a message to that effect and begin allowing connections. Connect to the database and verify that all the data that you expect to be there is there.

Congratulations! You've backed up Postgres! Hopefully this guide provided enough information for you to adapt it to your production environment. For help with your Postgres database design project, contact us. We're always eager to help the community of developers.

Return to Articles & Guides