Continuous PostgreSQL Backups using WAL-G

Summary

Continuous PostgreSQL backups require coordinating physical base snapshots with ongoing write-ahead log archiving to support point-in-time recovery. The open-source WAL-G utility simplifies this workflow by managing backup pushes and fetches to cloud storage services such as Amazon S3. In a typical setup on Ubuntu with PostgreSQL 12, envdir supplies AWS credentials and storage prefixes to WAL-G commands embedded directly in the PostgreSQL archive configuration. Regular base backups can be automated via cron jobs, which minimizes the volume of WAL archives that must be replayed during a restore. When recovering to a new instance, administrators fetch the latest base backup, define a restore command with targeted recovery timestamps in the configuration, and initiate replay using a recovery signal file.

Takeaways

1. 1

   Configuring archive_mode and archive_command in postgresql.conf enables WAL-G to push write-ahead log files to cloud storage automatically based on archive_timeout.

2. 2

   Scheduling frequent physical backups reduces recovery time by limiting the number of WAL archive files that must be replayed during a restore.

3. 3

   Performing point-in-time recovery involves specifying recovery_target_time and recovery_target_action alongside a recovery.signal file in the Postgres data directory.