background

PostgreSQL WAL Archiving with pg_receivewal

Join Over 1,000 Engineers & Get New Episodes Weekly!

Learn how to archive PostgreSQL's Write Ahead Log (WAL) to a separate server using pg_receivewal. This is probably one of the most efficient ways to archive your WAL files.

This tutorial assumes you have already setup streaming replication between a primary and a replica PostgreSQL server. If you need to set that up first, run through this tutorial.

# Assumes Replication is already setup as follows:
# Primary DB cluster called "main" on port 5432
# Replica DB cluster called "replica" on port 5433

# setup directory to receive WAL files
sudo su - postgres
mkdir /var/lib/postgresql/pg_log_archive/archiver -p
ls /var/lib/postgresql/pg_log_archive/

# create replication slot for wal receiver
psql -c "select * from pg_create_physical_replication_slot('archiver');"
psql -c "select * from pg_replication_slots;"

# start pg_receivewal command
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;"

# archive the logs
psql -c "select pg_switch_wal();" # -- pg_switch_xlog();

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