---
title: "A PostgreSQL Backup Strategy with pgBackRest"
description: "pg_dump alone is not a backup. A PITR-capable, compressed, verifiable backup architecture with pgBackRest."
url: https://sade.dev/en/notes/pgbackrest-postgresql-backup-strategy/
lang: en
author: "Muhammet Şafak"
published: 2026-05-08
updated: 2026-09-06
section: Note
tags: ["postgresql","backup","production","pgbackrest"]
summary: "pg_dump is an investigation tool, not a backup tool: it produces a logical dump that WAL replay cannot use, so there is no point-in-time recovery behind it. A production backup needs continuous WAL archiving, a weekly restore drill that actually opens the copy, and retention governed by policy. With two pgBackRest repositories configured, --repo is mandatory: without it only repo1 gets the backup."
---

# A PostgreSQL Backup Strategy with pgBackRest

> pg_dump is an investigation tool, not a backup tool: it produces a logical dump that WAL replay cannot use, so there is no point-in-time recovery behind it. A production backup needs continuous WAL archiving, a weekly restore drill that actually opens the copy, and retention governed by policy. With two pgBackRest repositories configured, --repo is mandatory: without it only repo1 gets the backup.

The day I stopped wrestling with `pg_dump` is the day I think I got more serious as a systems engineer. `pg_dump` is an investigation tool — not a production backup tool.

A production backup has to deliver three things:

1. **Point-in-time recovery (PITR).** Being able to say "it was deleted at 02:13 last night."
2. **Integrity verification.** Testing that the backup file actually works when you open it.
3. **Retention governed by policy.** 6-hourly/daily/weekly rotation.

`pg_dump` covers none of the three.

## pgBackRest setup

I prefer — and recommend — taking both a local and an S3 backup. Local backups are fast; S3 backups are fire-resistant.

### Off-site copy

If the repository lives on a single disk, one VPS fire wipes out everything. pgBackRest can write to an S3 or S3-compatible (R2, MinIO) bucket:

Preferably, protect the bucket with a policy that locks versions and **forbids deletion**. Critical in a ransomware scenario.

To keep costs under control over the long term, set the bucket lifecycle:

30 Days → Glacier Instant Retrieval → 120 Days → Delete

`/etc/pgbackrest.conf`:

```ini
[global]

###################################
# Local Repository
###################################
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-bundle=y
repo1-block=y
repo1-cipher-pass=...
repo1-cipher-type=aes-256-cbc

###################################
# AWS S3 Repository
###################################
repo2-type=s3
repo2-path=/main
repo2-s3-bucket=...
repo2-s3-endpoint=s3.{region_name}.amazonaws.com
repo2-s3-region={region_name}
repo2-s3-key=...
repo2-s3-key-secret=...
repo2-bundle=y
repo2-block=y
repo2-cipher-type=aes-256-cbc
repo2-cipher-pass=...

###################################
compress-type=zst
compress-level=6
process-max=4
start-fast=y
log-level-console=info
log-level-file=detail
log-path=/var/log/pgbackrest

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
```

Tighten the permissions:

```bash
sudo chown postgres:postgres /etc/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest.conf
```

Create the stanza:

```bash
sudo -u postgres pgbackrest --stanza=main stanza-create
```

On the `postgresql.conf` side, we route WAL archiving to pgBackRest:

```ini
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60
wal_level = replica
```

`archive_timeout = 60` — the last 60 seconds of transactions can be lost; enough for most applications, not for financial transactions.

## Backup plan

On the cron side:

```
# Local repository (repo1)
0 2 * * 0          pgbackrest --stanza=main --repo=1 --type=full backup
0 2 * * 1-6        pgbackrest --stanza=main --repo=1 --type=diff backup
0 0,6,12,18 * * *  pgbackrest --stanza=main --repo=1 --type=incr backup

# S3 repository (repo2)
0 4 * * 0          pgbackrest --stanza=main --repo=2 --type=full backup
0 4 * * 1-6        pgbackrest --stanza=main --repo=2 --type=diff backup
0 3,9,15,21 * * *  pgbackrest --stanza=main --repo=2 --type=incr backup
```

With two repositories configured, `--repo` is mandatory: without it pgBackRest backs up only to the highest-priority repository (repo1), so S3 would hold nothing but archived WAL — a copy that cannot be restored on its own.

Weekly full, daily diff, 6-hourly incremental on each repository. Storage consumption is surprisingly low — the `repoN-bundle`/`repoN-block` lines in the config enable block incremental backup, so pgBackRest copies only the changed blocks of a file instead of the whole file; both options are off by default.

## Integrity: if there's a backup, can it be restored?

Taking the backup is not enough. A weekly restore drill:

```bash
pgbackrest --stanza=main --delta restore --pg1-path=/tmp/pg_restore_test
```

Then I start that instance on a temporary port and run a few sanity queries. Skip this step and the backup becomes **false confidence**.

## PITR rehearsal

Rolling back to a specific point in time:

```bash
pgbackrest --stanza=main \
  --type=time \
  --target='2026-05-07 02:13:00+03' \
  restore
```

Running this command for the first time *during a real incident* is a disaster. Rehearse it beforehand — write the steps into the runbook.

## Retention

Cleaning up unused backups in the repository with the `expire` command:

```bash
pgbackrest --stanza=main expire
```

With `repo1-retention-full=4` the last 4 full backups are kept; older ones (and the diff/incr backups depending on them) are cleaned up automatically.

## Conclusion

For copying a schema or moving a small table, `pg_dump` is still the right tool. But on any system that needs PITR, the backup software has to be pgBackRest (or barman, or your database provider's native tool). The moment a backup is *untested*, saying "I have a backup" is a sophisticated way of lying.

## Frequently asked

**Is pg_dump enough as a production backup?**

No. pg_dump produces a logical dump, and a logical dump carries none of the three things a production backup owes you: point-in-time recovery, a verified restorable copy, and retention governed by policy. For copying a schema or moving a small table it is still the right tool.

**Do you have to pass --repo when two pgBackRest repositories are configured?**

Yes. Without it pgBackRest backs up only to the highest-priority repository. WAL keeps being archived to both, so the second repository ends up holding archived WAL and no base backup: a copy that cannot be restored on its own.


## Sources

- [Continuous Archiving and Point-in-Time Recovery (PITR)](https://www.postgresql.org/docs/current/continuous-archiving.html) — PostgreSQL
- [pgBackRest User Guide](https://pgbackrest.org/user-guide.html) — pgBackRest
- [pgBackRest Configuration Reference: repo, repo-bundle, repo-block](https://pgbackrest.org/configuration.html) — pgBackRest