pohzipohzi/walg-pitr.md

## walg-pitr.md

      
    Raw
  

              walg-pitr.md
            
          
    WAL-G PITR

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.
Setup

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.
Restore backup

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