PostgreSQL High Availability: Setting Up Streaming Replication, Failover, and Point-in-Time Recovery with WAL

[Primary]  ──── WAL Stream ────►  [Standby (Hot Standby)]
    │
    └── WAL Archive ──► [S3 / NFS]  ◄── used for PITR recovery

# /etc/postgresql/16/main/postgresql.conf

# Replication
wal_level = replica              # replica or logical
max_wal_senders = 5              # max Standby connections
wal_keep_size = 1GB              # WAL retention size
hot_standby = on

# WAL archiving (for PITR)
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-bucket/archive/%f'
archive_timeout = 60             # force an archive at least every 60 seconds

# Performance tuning
wal_compression = zstd
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# Monitoring
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

# /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE    USER        ADDRESS         METHOD
host    replication replicator  10.0.1.0/24     scram-sha-256

# Create a replication-only user
sudo -u postgres psql -c "
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong-password';
"

sudo systemctl reload postgresql

# On the Standby: initialize the data directory from a Primary base backup
sudo -u postgres pg_basebackup \
  -h 10.0.1.10 \            # Primary IP
  -U replicator \
  -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R              # -R: auto-generate recovery settings

# -Fp: plain format
# -Xs: include WAL streaming
# -P: show progress
# -R: auto-generate standby.signal + postgresql.auto.conf

# postgresql.auto.conf (auto-generated by pg_basebackup -R)
primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator password=strong-password application_name=standby1'

# Start the Standby
sudo systemctl start postgresql

# Check replication status (on the Primary)
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

-- On the Primary: WAL lag for each Standby
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

-- On the Standby: inbound WAL receiver status
SELECT
  status,
  receive_start_lsn,
  received_tli,
  pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replay_lag
FROM pg_stat_wal_receiver;

# Method 1: pg_ctl promote (immediate promotion)
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# Method 2: trigger file (recommended — easier to automate)
sudo -u postgres touch /tmp/failover.trigger
# Set in postgresql.conf: promote_trigger_file = '/tmp/failover.trigger'

# Confirm promotion
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# Result: f (false) → now acting as Primary

# On the old Primary (after it has been recovered)
sudo systemctl stop postgresql

sudo -u postgres pg_rewind \
  --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=10.0.1.20 user=replicator dbname=postgres"

# Create standby.signal
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

# Point primary_conninfo at the new Primary (10.0.1.20)
sudo -u postgres psql -c "
ALTER SYSTEM SET primary_conninfo = 'host=10.0.1.20 port=5432 user=replicator password=strong-password';
"

sudo systemctl start postgresql

# On the recovery server: download the latest base backup
aws s3 cp s3://my-wal-bucket/basebackup/latest.tar.gz /tmp/
sudo -u postgres tar -xzf /tmp/latest.tar.gz -C /var/lib/postgresql/16/main

# Role of recovery.conf: put these settings in postgresql.conf (PG12+)

# postgresql.conf (recovery server)

restore_command = 'aws s3 cp s3://my-wal-bucket/archive/%f %p'

# Target time (just before an accidental DROP TABLE)
recovery_target_time = '2026-05-19 14:30:00 KST'
recovery_target_action = 'promote'   # auto-promote when recovery finishes

# Create standby.signal (start in recovery mode)
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

sudo systemctl start postgresql

# Follow recovery progress in the logs
sudo journalctl -u postgresql -f | grep -E "recovery|redo|consistent"

# Confirm recovery is complete
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

#!/bin/bash
# /usr/local/bin/pg-basebackup.sh

S3_BUCKET="s3://my-wal-bucket/basebackup"
DATE=$(date +%Y%m%d-%H%M)
BACKUP_DIR="/tmp/pg-backup-$DATE"

# Create a base backup
sudo -u postgres pg_basebackup \
  -D "$BACKUP_DIR" \
  -Ft -z \        # tar + gzip
  -Xs \           # include WAL streaming
  -P

# Upload to S3
aws s3 cp "$BACKUP_DIR/base.tar.gz" "$S3_BUCKET/$DATE/base.tar.gz"
aws s3 cp "$BACKUP_DIR/pg_wal.tar.gz" "$S3_BUCKET/$DATE/pg_wal.tar.gz"

# Clean up local files
rm -rf "$BACKUP_DIR"

# Delete S3 backups older than 30 days
aws s3 ls "$S3_BUCKET/" | awk '{print $4}' | while read dir; do
  dir_date=$(echo "$dir" | cut -d'-' -f1)
  if [ "$(date -d "$dir_date" +%s 2>/dev/null)" -lt "$(date -d '30 days ago' +%s)" ]; then
    aws s3 rm "$S3_BUCKET/$dir" --recursive
  fi
done

echo "Backup completed: $DATE"

# Run daily at 01:00
echo "0 1 * * * root /usr/local/bin/pg-basebackup.sh >> /var/log/pg-backup.log 2>&1" > /etc/cron.d/pg-backup