Skip to content

PostgreSQL PITR

PITR — Point In Time Recovery — backs up a PostgreSQL cluster continuously, so you can restore it to any chosen moment.

Different granularity from pg_dump

PITR backs up a whole cluster, continuously. Database Backups (pg_dump) back up individual databases, at a point in time. They solve different problems; most setups want both.

Muppy uses WAL-G (github.com/wal-g/wal-g).

PITR works by shipping a cluster's WAL (Write Ahead Log) to an S3-compatible store. S3 is the only backend Muppy supports today, so you need a working S3 Bucket in Muppy before you start.

Because WAL ships every minute, the volumes are large. Muppy compresses WAL by default, and even so an active database can produce hundreds of gigabytes a day.

Info

PITR is independent of replication. A single standalone cluster can use it.

PITR is an incremental backup

Every minute WAL-G saves the changes of the last minute into a WAL Segment.

Restoring needs a starting point: a full backup, called a base backup or Cluster Backup (WAL-G). A restore first puts back that cluster backup, then replays every WAL Segment on top of it.

Restore time is proportional to the number of WAL Segments to replay. That makes cluster backup frequency the key PITR parameter:

Cluster backup frequency Consequence
Weekly Up to a week of WAL to replay — around 10 000 segments. Long restores.
Daily Far fewer segments, so much faster restores — but you run a full backup every day, and must budget its impact on your applications.

Warning

Pick the frequency from your acceptable restore time, not from your storage budget.

Rollout

  1. Configure WAL-G to ship WAL Segments every minute
  2. Schedule and launch Cluster Backups
  3. List and refresh the Cluster Backups
  4. Purge old backup files
  5. Monitor PITR
  6. Inspect what WAL-G stores in S3
  7. Restore