This gist summarises a way to simulate point-in-time recovery (PITR) using WAL-G. Most of the material is adapted from Creston's tutorial.
First we initialize a database cluster
pg_ctl init -D cluster
Next we edit the cluster configuration to enable WAL archiving
# ./cluster/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'wal-g wal-push %p'
Start the server
pg_ctl -D cluster -l cluster/logfile start
Insert some fake data
createdb $USER
psql -c "create database test;"
psql test -c "
create table posts (
id integer,
title character varying(100),
content text,
published_at timestamp without time zone,
type character varying(100)
);
insert into posts (id, title, content, published_at, type) values
(100, 'Intro to SQL', 'Epic SQL Content', '2018-01-01', 'SQL'),
(101, 'Intro to PostgreSQL', 'PostgreSQL is awesome!', now(), 'PostgreSQL');
"
Archive the logs manually
psql -c "select pg_switch_wal();"
Backup the database
wal-g backup-push cluster
Insert more data into the current database
psql test -c "insert into posts (id, title, content, type) values
(102, 'Intro to SQL Where Clause', 'Easy as pie!', 'SQL'),
(103, 'Intro to SQL Order Clause', 'What comes first?', 'SQL');"
Archive the logs manually again
psql -c "select pg_switch_wal();"
Finally we stop the server
pg_ctl -D cluster stop
Right now the posts
table in our latest backup does not contain the last 2 rows. The goal is to restore the database to it's latest state from the older backup using the latest WAL files.
Fetch the latest backup
wal-g backup-fetch cluster-bak LATEST
If we try to start the server now it will fail
pg_ctl -D cluster-bak start
# invalid checkpoint record
Add a file recovery.conf
in the cluster-bak
directory
# ./cluster-bak/recovery.conf
restore_command = 'wal-g wal-fetch %f %p'
standby_mode = on
recovery_target_time = '2222-11-11 00:00:00'
Start the server
pg_ctl -D cluster-bak start
# entering standby mode
# restored log file "000000010000000000000002" from archive
# redo starts at 0/2000028
# consistent recovery state reached at 0/20000F8
# Archive '000000010000000000000004' does not exist.
A database with such a recovery.conf
set will poll WAL-G storage for WAL indefinitely. We can exit recovery by running pg_ctl promote
pg_ctl -D cluster-bak promote
# server promoted
Verify that the restored database contains our last two rows
psql test -c "select * from posts;"
# contains 102 and 103