8.🔄 PostgreSQL Online Backup: WAL Archiving & Point-In-Time Recovery (PITR)

PostgreSQL supports robust online backups using WAL archiving and Point-In-Time Recovery (PITR). This feature is essential for high-availability systems and disaster recovery scenarios.

---

🧠 What Is WAL Archiving & PITR?

• WAL Archiving: Continuously saves Write-Ahead Logs (WALs) that contain all changes made to the database.
• PITR (Point-In-Time Recovery): Allows restoring a database to a specific moment using a base backup and replaying WAL logs up to that time.

---

⚙️ Step-by-Step: Configure WAL Archiving

1. ✅ Check Current Archive Mode

SHOW archive_mode;

Expected output (if not enabled):

 archive_mode

--------------

 off

---

2. 🛑 Stop the PostgreSQL Cluster

sudo systemctl stop postgresql-15.service

---

3. ✍️ Edit postgresql.conf

sudo vim /var/lib/pgsql/15/data/postgresql.conf

Update or add the following parameters:

wal_level = replica

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/version/archive/%f'

---

4. 🔁 Start PostgreSQL

sudo systemctl start postgresql-15.service

---

5. 🧪 Force WAL Switch (Optional Test)

SELECT pg_switch_wal();

This command creates a new WAL file immediately, useful for confirming archiving is working.

---

6. 📁 Check Archive Directory

[root@pgbouncer backups]# ls

000000010000000000000010

WAL files will be named in this format. Their presence confirms archiving is active.

---

📊 Monitoring Archive Health with pg_stat_archiver

PostgreSQL provides a system view to track archiving activity and health:

🔍 Basic Archive Status Check

SELECT * FROM pg_stat_archiver;

Returns:

• archived_count
• last_archived_wal
• last_archived_time
• last_failed_wal
• last_failed_time
• stats_reset

---

📈 Monitor Archiver Status and WAL Throughput

This query is excellent for automation and monitoring systems like Prometheus, Grafana, or Zabbix.

SELECT *,current_setting('archive_mode')::boolean AND (last_failed_wal IS NULL OR last_failed_wal <= last_archived_wal)  AS is_archived, CAST(archived_count AS NUMERIC) EXTRACT(EPOCH FROM age(NOW(), stats_reset)  AS current_archived_wal_per_second  FROM pg_stat_archiver;

---

🧠 Explanation of the Query

Expression	Description
archive_mode	Checks if archiving is enabled.
last_failed_wal IS NULL OR last_failed_wal <= last_archived_wal	Confirms archiving is functioning (no errors).
is_archived	Boolean flag showing if archiver is healthy.
archived_count / seconds_since_reset	Avg WALs archived per second.

---

✅ Output Columns (Key Metrics)

• archived_count: Total WAL files archived.
• last_archived_wal: Last successfully archived WAL file.
• last_archived_time: Timestamp of last successful archive.
• last_failed_wal: Last failed WAL (if any).
• last_failed_time: Timestamp of last failure.
• stats_reset: When these stats were last reset.
• is_archived: Custom boolean field for archive health.
• current_archived_wal_per_second: WAL archive rate.

---

🔁 Optional: Reset Archiver Stats

SELECT pg_stat_reset_shared('archiver');

⚠️ Use with caution: This will clear historical stats. Ideal before performance testing or after fixing issues.

---

✅ Summary

• Enable WAL archiving and configure archive_command for online backups.
• Use pg_stat_archiver to monitor archive health.
• PostgreSQL 17 continues to improve on incremental backup and restore tooling.
• Combine with tools like pg_basebackup for full PITR-capable backup plans.