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.

Context

Database administrators need mechanisms to continuously archive PostgreSQL data and restore a database to a specific historical point in time when unexpected outages or disasters occur.

Approach / What changed

The configuration utilizes WAL-G and envdir on Ubuntu to continuously stream PostgreSQL write-ahead logs to AWS S3 and schedule recurring physical base backups. To recover, a secondary instance fetches the latest base backup from S3, defines WAL retrieval commands and recovery targets in postgresql.conf, and initializes recovery via a recovery.signal file.

Takeaways

• 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.
• Scheduling frequent physical backups reduces recovery time by limiting the number of WAL archive files that must be replayed during a restore.
• Performing point-in-time recovery involves specifying recovery_target_time and recovery_target_action alongside a recovery.signal file in the Postgres data directory.

Related reading

• Supabase · Nov 18, 2020

  Postgres Views

  Postgres views serve as query shortcuts that execute underlying SQL statements upon retrieval without generating new tables or persisting duplicate data. By encapsulating complex multi-table joins, standard views provide query consistency across applications, simplify repetitive calls, improve logical schema organization, and enhance security by restricting sensitive columns. In contrast, materialized views physically store query results on disk, dramatically reducing read latency for heavy queries spanning millions of rows. Because materialized views introduce the trade-off of stale data, administrators must periodically run the refresh command based on workload tolerances for use cases like analytics and internal dashboards. Materialized views should not substitute query optimization, as underlying query efficiency remains essential.

  Paul Copplestone

• Supabase · Nov 28, 2021

  New in PostgreSQL 14: What every developer should know