Restoring wal-e PostgreSQL database backup

If you have a backup, you must be able to restore it. In the previous post, I wrote about creating scheduled backup using wal-e. Now, we will restore the wal-e backup.

NOTE: This post is intended to restore PostgreSQL base backup that has no user defined tablespace. If you defined that, you can read more in here.

Step 1: Install PostgreSQL and wal-e

I assumed that you already install PostgreSQL and wal-e. You need to install same PostgreSQL major version and all its extensions that you use to create the base backup. FYI, PostgreSQL has a different binary file format for each major version. Postgres major version is determined by the first and second group number of the version number (e.g: 8.4, 9.0, 9.5, etc.).

Before we proceed to the next step, you need to stop the PostgreSQL server and remove the data directory.

POSRTGES_VERSION=9.5
sudo service postgresql stop
sudo rm -rf /var/lib/postgresql/${POSTGRES_VERSION}/main

If you haven’t install wal-e, you can follow this on step 3.

Step 2: Download Base Backup

Now, we can download our base backup:

POSTGRES_VERSION=9.5
sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/${POSTGRES_VERSION}/main LATEST

The above command will download the latest base backup that you have. You can list your base backup version by running

sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

Run the backup-fecth and replace LATEST argument with the base backup name from backup-list.

Step 3: Configure PostgreSQL recovery mode

Edit your PostgreSQL recovery configuration file:

sudo -u postgres vim /var/lib/postgresql/${POSTGRES_VERSION}/main/recovery.conf

Add the following configuration:

# The command that will be executed while recovering
restore_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'

# You can specify this value with exact time. This will be useful if
# you have incident and you want to recover to a few moments before.
# recovery_target_time = '2016-06-02 06:18:00'

Start the PostgreSQL database again.

sudo service postgresql start

Now you will have a fully running recovery database.