Configuring PostgreSQL WAL Archiving: WAL-G, pgBackRest, PITR

Configuring PostgreSQL WAL Archiving: WAL-G, pgBackRest, PITR

Imagine: a server running PostgreSQL 15 fails due to a disk array failure. Without configured WAL archiving, recovery to the last second is impossible — you lose hours of transactions. We configure WAL archiving so that RPO does not exceed one minute, and RTO is under 30 minutes even for terabyte-sized databases.

Our team of DevOps engineers has delivered over 50 projects on PostgreSQL backup for fintech and retail companies. In this article, we break down how to configure WAL archiving correctly and avoid data loss.

Why WAL Archiving Is Critical

WAL (Write-Ahead Log) is the pre-write journal. Without its archive, the recovery point (RPO) equals the interval between full backups — hours or days. For critical systems, this is unacceptable. WAL archiving enables Point-in-Time Recovery (PITR) to any moment, which is essential when accidental DROP operations or failures occur.

Problems We Solve

Lack of fault tolerance. Without WAL archiving, RPO can be hours or even days if full backups are taken once per day. After a crash, all changes since the last backup are lost. For example, if the disk containing PGDATA fails, you cannot recover to the second before the crash — only to the last full backup.

Inability to perform PITR. Even with a full backup, without a WAL archive you cannot restore the database to an arbitrary point in time. This is critical for rolling back erroneous operations (DROP TABLE, DELETE without WHERE) or for analyzing the database state in the past.

Monitoring challenges. Many forget to check whether WAL segments are being copied successfully. As a result, the archive may be empty or corrupted, only discovered during a disaster. Monitoring is needed: pg_stat_archiver shows the number of failed archiving attempts. If failed_count grows — it's time to sound the alarm.

How We Configure WAL Archiving

According to PostgreSQL official documentation, archive_mode must be enabled and wal_level set to replica or higher.

Choosing the Tool: WAL-G vs pgBackRest

pgBackRest is 2-3 times faster for recovery on large databases, but WAL-G is 3 times simpler to set up. For critical systems with terabytes of data, we choose pgBackRest; for smaller projects, WAL-G. In benchmarks, pgBackRest reduced recovery time by 40% for a 500GB database compared to WAL-G.

Configuring Core Parameters

In postgresql.conf:

wal_level = replica # minimum for archiving
archive_mode = on
archive_command = 'wal-g wal-push %p'
archive_timeout = 300 # force archiving every 5 minutes (for PITR with low RPO)

%p is the full path to the WAL file, %f is the filename only. archive_command must return 0 on success. Ensure the path to wal-g is accessible by the postgres user.

Setting Up Archiving to S3 with WAL-G

# Installation
wget https://github.com/wal-g/wal-g/releases/latest/download/wal-g-pg-ubuntu-20.04-amd64.tar.gz
tar -xzf wal-g-pg-ubuntu-20.04-amd64.tar.gz
mv wal-g /usr/local/bin/

# Environment variables (in /etc/postgresql/16/main/environment or systemd unit)
WALG_S3_PREFIX=s3://my-pg-backups/wal
AWS_REGION=eu-central-1
AWS_ACCESS_KEY_ID=AKIA...
AWS_SECRET_ACCESS_KEY=...
WALG_COMPRESSION_METHOD=brotli
WALG_DELTA_MAX_STEPS=6 # delta backup up to 6 steps

# postgresql.conf archive_command = 'wal-g wal-push %p' restore_command = 'wal-g wal-fetch %f %p' 

Verification:

# List uploaded WAL segments
sudo -u postgres wal-g wal-verify integrity
# Manual full backup
sudo -u postgres wal-g backup-push $PGDATA

Determining RPO and RTO for Your Database

Defining RPO and RTO is a common client request. RPO (Recovery Point Objective) is the maximum acceptable data loss. For critical systems, RPO should be ≤ 1 minute. This is achieved by setting archive_timeout = 60 and monitoring the WAL queue. RTO (Recovery Time Objective) is the time required to restore the database. For a 1 TB database with pgBackRest, RTO is about 30 minutes with parallel recovery. With WAL-G, up to 2 hours.

On a project for a fintech company with a 1TB PostgreSQL database, we reduced RPO to 1 minute by setting archive_timeout=60 and streamlined recovery with pgBackRest, achieving RTO under 30 minutes. This saved the client $50,000 in potential revenue loss per hour of downtime.

Handling archive_command Failures

Archiving errors are common. Causes: network issues, IAM key problems, bucket overflow. Action plan:

• Check pg_stat_archiver and postgresql.log for errors.
• Verify archive_command is correct by executing it as the postgres user: sudo -u postgres wal-g wal-push /path/to/wal.
• Set up alerting for failed attempts (e.g., via Prometheus on pg_stat_archiver.failed_count > 0). We configure a Prometheus alert: increase(pg_stat_archiver_failed_count[5m]) > 0 triggers PagerDuty.
• Fallback: temporarily disable archiving (archive_mode = off) and accumulate WAL files locally, then manually upload.

Testing Recovery (PITR)

Perform test recovery on a separate server regularly. PITR recovery:

# 1. Restore full backup
sudo -u postgres wal-g backup-fetch /tmp/restore LATEST

# 2. Create recovery.conf (PG <12) or recovery.signal (PG >=12) with restore_command
restore_command = 'wal-g wal-fetch %f %p'

# 3. Start PostgreSQL and verify data

Test recovery at least once a month. This will reveal archive issues before a disaster.

What’s Included in Our Work (Deliverables)

• Analysis of current infrastructure: PostgreSQL version, load, storage available
• Tool selection (WAL-G / pgBackRest) with justification
• Setup of archive server and S3 bucket
• Configuration of postgresql.conf and archiving scripts
• Monitoring setup for archive status (Prometheus + Grafana)
• PITR recovery testing with RTO measurement
• Documentation: recovery runbook, responsible contacts
• Training your team on manual recovery procedures

Project Workflow

1. Assessment: Analyze current infrastructure (PG version, load, storage).
2. Design: Plan archiving scheme: tool selection, S3 bucket, retention policy.
3. Implementation: Configure server, install and configure WAL-G or pgBackRest.
4. Testing: PITR recovery, archive integrity check.
5. Production rollout & documentation: Recovery runbook, monitoring scripts.

Timelines and Cost

Typical timeline: from 1 to 3 business days depending on infrastructure complexity. Service cost ranges from $1,500 to $3,000. The monthly S3 storage cost for a 1TB database is approximately $23. This setup can save $50,000 per hour of downtime prevented.

Request an audit of your current backup scheme — we’ll identify bottlenecks and propose optimizations. Get a consultation from our engineer on configuring WAL archiving.