WAL-E + Postgres 9.x (single server + DB) Setup and Recovery

wale_postgres_recovery.md

A quick "how to" on what you need to do to both setup AND recover a single-server PostgreSQL database using WAL-E

• WAL-E: https://github.com/wal-e/wal-e
• Assuming Ubuntu 12.04 LTS ("Precise")
• We'll be using S3. Make sure you have an IAM in a group with GetObject, ListBucket and PutObject on the bucket you want to use (and that it's not public).

Setup:

1. These packages:

$ sudo apt-get install daemontools libevent-dev python-all-dev lzop pv
$ sudo easy_install pip
$ sudo pip install wal-e

Notes: daemontools provides us with the envdir program, which lets us (safely) store sensitive keys as environmental variables. We'll use it to store our S3 credentials.

2. An S3 bucket (i.e. /myservice/pg-backups)
3. An S3 user that can (at a minimum) GetObject, ListBucket and PutObject. I'd suggest turning on versioning on the bucket and adding GetObjectVersion to that list too. That'll make sure server compromise won't allow someone to overwrite the backups you made[3]
4. Set up your access keys:

$ mkdir -p /etc/wal-e.d/env
$ echo "YOUR_AWS_ACCESS_KEY" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
$ echo "YOUR_AWS_SECRET" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
$ echo 's3://myservice/pg-backups' >  /etc/wal-e.d/env/WALE_S3_PREFIX
$ chown -R root:postgres /etc/wal-e.d

5. Uncomment and modify these lines in your postgresql.conf file under /etc/postgresql/9.3/main/

wal_level = archive
archive_mode = on
archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
archive_timeout = 60

Note: Make sure to use the full path to wal-e so that PostgreSQL can find it.

Now we're ready to make our first backup. You can also set the PGDATA environmental variable to the location of your PostgreSQL data dir (/var/lib/postgresql/9.3/main on Ubuntu/Debian) either as a shell variable or using envdir like we did for the AWS keys.

Pushing Backups:

1. Make sure you're the postgres user (the default user PostgreSQL runs as) with sudo su - postgres
2. Create our first full backup, which is the reference point for our WAL logs with the following:
   envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.3/main
3. We can check the list of full backups with envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

You can also delete older backups with wal-e delete [--confirm] before <base_XXXX> where <base_XXX> is from backup-list. Make sure to double check the dates on the backups.

Fetching a Backup:

We'll assume worst-case here: you have a blank machine with everything as it was except your data. Postgres has been re-installed as before and you're now wanting to recover your database.

Note: Make sure to read the WAL-E docs on user-created tablespaces and how that interacts with fetches first.

1. Make sure PostgreSQL isn't running and change to the postgres user.
2. Delete the default data directory as the restore process will re-create it.

$ rm -r /var/lib/postgresql/9.3/main # (under Ubuntu/Debian)

3. Fetch the latest backup. You can replace LATEST with the name of a specific backup as identified from backup-list:

$ envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/9.3/main LATEST

4. Ensure the permissions are correct:

$ chown -R postgres:postgres /var/lib/postgresql/9.3/main
$ chmod 0700 /var/lib/postgresql/9.3/main

5. Create a recovery.conf within /var/lib/postgresql/9.3/main/ with the following contents:

restore_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'

6. Start your PostgreSQL server. It might take a little while (it will replay the the WAL files on top of your specified base backup). The recovery.conf file will be renamed to recovery.done once the process is complete.
7. Get back to work (read: check your data is there!)

References:

• [1] https://gist.github.com/ruckus/2293434
• [2] https://coderwall.com/p/cwe2_a
• [3] http://blog.opbeat.com/2013/01/07/postgresql-backup-to-s3-part-one/

You may want to change the comment syntax in that shell script code block:

$ rm -r /var/lib/postgresql/9.3/main // (under Ubuntu/Debian)

to

$ rm -r /var/lib/postgresql/9.3/main # (under Ubuntu/Debian)

(unless you want people to rm -r / :)

@vvpete Thanks, and done. Technically it should not be possible since you need to pass --no-preserve-root on most modern distros, but no doubt there's machines out there running WAL-E with something older/more arcane.

Unless I did something wrong, I have found you also need to apt-get libxml2-dev and libxslt1-dev (with precise64).

@james4k - I've not needed to install those on my system(s), but it may be that something else earlier in my deployment is covering that off.

I'll check the requirements for the packages and update the article if so!

PS: It's a shame that Gist's don't notify the author of comments!

In case you have problems pushing to S3 in Frankfurt: wal-e/wal-e#152

Workaround while a fix is provided: you can use S3 in Ireland in the meantime (not having that issue)

I have created an S3 bucket and pushed a backup to the bucket successfully.

-bash-4.2$ envdir /etc/wal-e.d/env wal-e backup-list
wal_e.main INFO MSG: starting WAL-E
DETAIL: The subcommand is "backup-list".
STRUCTURED: time=2018-12-07T19:41:30.441387-00 pid=10841
name last_modified expanded_size_bytes wal_segment_backup_start wal_segment_offset_backup_start wal_segment_backup_stop wal_segment_offset_backup_stop
base_000000010000000000000002_00000032 2018-12-05T21:35:58.000Z 000000010000000000000002 00000032
base_000000010000000000000004_00000032 2018-12-05T21:40:14.000Z 000000010000000000000004 00000032
base_000000010000000300000022_00000032 2018-12-07T18:52:13.000Z 000000010000000300000022 00000032
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ envdir /etc/wal-e.d/env wal-e backup-fetch /var/lib/pgsql/backups/ LATEST
wal_e.main INFO MSG: starting WAL-E
DETAIL: The subcommand is "backup-fetch".
STRUCTURED: time=2018-12-07T19:42:08.827661-00 pid=10855
wal_e.worker.s3.s3_worker INFO MSG: beginning partition download
DETAIL: The partition being downloaded is part_00000000.tar.lzo.
HINT: The absolute S3 key is test/basebackups_005/base_000000010000000300000022_00000032/tar_partitions/part_00000000.tar.lzo.
STRUCTURED: time=2018-12-07T19:42:09.869089-00 pid=10855
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$
-bash-4.2$ pwd
/var/lib/pgsql/backups
-bash-4.2$

As you can see there is no proper output or no backup downloaded from S3. I am not sure where else to look.
Postgres version: 9.2.24
Wal-e version: 0.9.0

Any help will be greatly appreciated.