PostgreSQL Restore When Using pg_receivewal

Learn how to effectively restore your database using point-in-time recovery when you are streaming WAL files to another system using pg_receivewal.

This tutorial assumes you have already setup 1) streaming replication between a primary and a replica PostgreSQL database cluster, 2) are using replication slots and 3) are using pg_receivewal (or pg_receivexlog). If you need to set these up use the following links to setup: streaming replication, replication slots and pg_receivewal.

# PostgreSQL 10 installed on Ubuntu
# Primary DB cluster called "main" on port 5432
# Replica DB cluster called "replica" on port 5433
# Archiver setup and running as well
# Test database exists with a posts table and two posts

# review data
sudo su - postgres
psql test -c "select * from posts;"

# start pg_receivewal command (pg_receivexlog in versions < 10)
pg_receivewal \
  -D /var/lib/postgresql/pg_log_archive/archiver \
  -S archiver \
  -Z 0 \
  -h /var/run/postgresql \
  -p 5432 \
  -U rep_user \
  -w \
  -v

# check status of slots
psql -c "select * from pg_replication_slots;"

# backup database
pg_basebackup -Ft -X none -D - | gzip > /var/lib/postgresql/db_file_backup.tar.gz

# check archiver directory
ls /var/lib/postgresql/pg_log_archive/archiver

# insert data on main
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');"

# check archiver directory
ls /var/lib/postgresql/pg_log_archive/archiver

Shutdown and destroy main database cluster

# stop DB
sudo systemctl stop postgresql@10-main

# destroy main cluster's data
rm /var/lib/postgresql/pg_log_archive/main/* -r
rm /var/lib/postgresql/10/main/* -r
ls /var/lib/postgresql/10/main/

# check wal archive directories
ls /var/lib/postgresql/pg_log_archive/main
ls /var/lib/postgresql/pg_log_archive/archiver

Restore main database cluster

# restore
tar xvfz /var/lib/postgresql/db_file_backup.tar.gz -C /var/lib/postgresql/10/main/
cp /var/lib/postgresql/pg_log_archive/archiver/* /var/lib/postgresql/pg_log_archive/main/
mv \
  /var/lib/postgresql/pg_log_archive/main/[WAL_FILE].partial \
  /var/lib/postgresql/pg_log_archive/main/[WAL_FILE]

# add recovery.conf
nano /var/lib/postgresql/10/main/recovery.conf

  restore_command = 'cp /var/lib/postgresql/pg_log_archive/main/%f %p'

# start DB and verify
sudo systemctl start postgresql@10-main
tail -n 100 /var/log/postgresql/postgresql-10-main.log

# verify data is up to date
psql test -c "select * from posts;"

# Check replication slots
psql -c "select * from pg_replication_slots;"

# Add back replication slots
psql -c "select * from pg_create_physical_replication_slot('replica');"
psql -c "select * from pg_create_physical_replication_slot('archiver');"

# check archiver status and directory
ls /var/lib/postgresql/pg_log_archive/archiver

# check replica status
tail -n 100 /var/log/postgresql/postgresql-10-replica.log

# force log switch and check archiver status
psql -c "select pg_switch_wal();"
ls /var/lib/postgresql/pg_log_archive/archiver