# Continuous PostgreSQL Backups using WAL-G

[Supabase](https://yomu.fyi/company/supabase) · Angelico de los Reyes · Aug 2, 2020

**Type:** Tutorial

## 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.

**Tags:** [AWS](https://yomu.fyi/topic/aws), [Open Source](https://yomu.fyi/topic/open-source), [Postgres](https://yomu.fyi/topic/postgres), [Reliability](https://yomu.fyi/topic/reliability)

[Read original post](https://supabase.com/blog/continuous-postgresql-backup-walg)