WAL-G is an open-source continuous archiving tool used to easily set up and recover from physical backups in Postgres. It mainly handles the storage and retrieval of physical backups and WAL archives to and from a chosen cloud storage provider. In this week's edition of TIL Postgres, we will walk you through on how to effortlessly set up WAL-G for your database as well as guide you on what to do if and when disaster strikes.
For this tutorial, we will be using two instances running Postgres databases on Ubuntu 18.04. One instance will act as your main database, the other is your recovery database. Do note that, if you’re using another operating system some file paths may vary.
Make sure the below packages are installed in your instances. Alternatively, you can spin up the latest version of Supabase Postgres which would already have everything configured and installed, along with other goodies. It is readily available in either the AWS or Digital Ocean marketplaces and only takes a few minutes to get running.
A quick installation guide can be found here.
$ sudo apt-get install -y daemontools
$ wget https://github.com/wal-g/wal-g/releases/download/v0.2.15/wal-g.linux-amd64.tar.gz $ tar -zxvf wal-g.linux-amd64.tar.gz $ mv wal-g /usr/local/bin/
When storing backups, WAL-G has numerous cloud storage provider options for us to choose from. For this tutorial, we will be using AWS. Have the following prepared:
- AWS Access & Secret keys.
- An S3 bucket.
/etc/wal-g.d/env is created and contains files that stores environment variables. It would later be used in WAL-G commands via envdir.
$ umask u=rwx,g=rx,o= $ mkdir -p /etc/wal-g.d/env $ echo 'secret-key-content' > /etc/wal-g.d/env/AWS_SECRET_ACCESS_KEY $ echo 'access-key' > /etc/wal-g.d/env/AWS_ACCESS_KEY_ID $ echo 's3://backup-bucket/project-directory' > /etc/wal-g.d/env/WALG_S3_PREFIX $ echo 'db password' > /etc/wal-g.d/env/PGPASSWORD $ chown -R root:postgres /etc/wal-g.d
Here, we enable WAL archiving and instruct Postgres to store the archives in the specified S3 bucket via WAL-G.
$ echo "archive_mode = yes" >> /etc/postgresql/12/main/postgresql.conf $ echo "archive_command = 'envdir /etc/wal-g.d/env /usr/local/bin/wal-g wal-push %p'" >> /etc/postgresql/12/main/postgresql.conf $ echo "archive_timeout = 60" >> /etc/postgresql/12/main/postgresql.conf
The database is restarted to let the changes in the configuration to take effect.
$ sudo /etc/init.d/postgresql restart
$ sudo -su postgres envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main
At this point, if you were to check the S3 path that you provided, the following two newly created and populated directories would be observed:
From then on, subsequent physical backups would be found in the directory
basebackups_005 and any WAL archives would be sent to the directory
A CRON job can then be set to schedule physical backups to be performed everyday:
$ echo "0 0 * * * postgres /usr/bin/envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main" > /etc/cron.d/pg_backup
Here, the instance has been instructed to back up the database at the start of each day at midnight. By physically backing up your instance regularly, overall recovery time could be faster. Restoring from a physical backup from yesterday would lead to fewer WAL archive files to be replayed as compared to restoring from one from a month ago.
Something goes wrong with the database or instance. We will now use what available physical backups we have in the S3 bucket to recover and restore all of our data on to a new instance.
The configuration should be the same as the original instance. For recovery and restoration, we would not need the variable
$ umask u=rwx,g=rx,o= $ mkdir -p /etc/wal-g.d/env $ echo 'secret-key-content' > /etc/wal-g.d/env/AWS_SECRET_ACCESS_KEY $ echo 'access-key' > /etc/wal-g.d/env/AWS_ACCESS_KEY_ID $ echo 's3://backup-bucket/project-directory' > /etc/wal-g.d/env/WALG_S3_PREFIX $ chown -R root:postgres /etc/wal-g.d
$ sudo /etc/init.d/postgresql stop
$ sudo -su postgres
Through restore_command, we instruct Postgres to pull all WAL archives from our S3 bucket to use during recovery.
$ echo "restore_command = '/usr/bin/envdir /etc/wal-g.d/env /usr/local/bin/wal-g wal-fetch \"%f\" \"%p\" >> /tmp/wal.log 2>&1'" >> /etc/postgresql/12/main/postgresql.conf
If we want to restore the database only up to a certain point in time (eg. right before the disaster), we can do so by setting both recovery_target_time and recovery_target_action. Do note that the timezone would need to match that of the original instance. This is usually at the UTC (+00) timezone.
$ echo "recovery_target_time = '2020-07-27 01:23:00.000000+00'" >> /etc/postgresql/12/main/postgresql.conf $ echo "recovery_target_action = 'promote'" >> /etc/postgresql/12/main/postgresql.conf
The current data directory is deleted and is replaced with the latest version of the physical backup from the S3 bucket.
$ rm -rf /var/lib/postgresql/12/main $ envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-fetch /var/lib/postgresql/12/main LATEST
This file instructs Postgres that the database should undergo recovery mode upon start.
$ touch /var/lib/postgresql/12/main/recovery.signal
$ exit $ sudo /etc/init.d/postgresql start
Once Postgres finishes starting up and completes recovery mode, all data or data up to the specified point in time would have been successfully restored on to the new instance. Disaster averted.
TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉
Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.