skip to content

With pgBackRest backing up a 5 TB PostgreSQL cluster to an object-store repository, which options shorten backups and keep WAL archiving ahead, and what does each cost?

level: seniorimportance: should knowfreq 40%

answer

  1. parallelism is off by default
  2. small files are expensive objects
  3. changed blocks, not whole files
  4. spool path and a queue limit
  5. copy from the replica

basics

~20 s

Raise process-max, switch compress-type to zst, enable repo-bundle and then repo-block, turn on archive-async with a spool path, and back up from a standby. Each one costs CPU, compatibility with older pgBackRest, or extra configuration.

solid answer

~50 s

pgBackRest's defaults are conservative. `process-max` is 1, so raise it to run compression and transfer in parallel without starving the database host. Switch `compress-type` from `gz` to `zst`, which is much faster at a similar ratio. Enable `repo-bundle` so small files are packed into fewer objects, then `repo-block`, which requires bundling and stores only the changed parts of files in diff and incr backups. Enable it for fulls too, so their block maps exist. On the WAL side, `archive-async=y` with a local `spool-path` lets `archive-push` work ahead in parallel. `archive-push-queue-max` protects `pg_wal` from filling, but when the limit is hit pgBackRest **drops** WAL and point-in-time recovery breaks until the next backup. `backup-standby=prefer` copies most files from a replica. The costs: CPU, repositories older releases cannot read, bundles that cannot be resumed, and an extra host to configure.

code

ini · 19 lines
ini
[global]
repo1-type=s3
repo1-path=/analytics
repo1-s3-bucket=pgbackrest-analytics
repo1-s3-endpoint=s3.example.net
repo1-s3-region=eu-central
repo1-bundle=y
repo1-block=y
compress-type=zst
process-max=8
start-fast=y
archive-async=y
spool-path=/var/spool/pgbackrest

[global:archive-push]
process-max=4

[analytics]
pg1-path=/var/lib/postgresql/18/main

go deeper

for a junior

Recall the names: process-max for parallelism, compress-type, repo-bundle and repo-block for the repository, archive-async for WAL, backup-standby for load.

for a middle

Explain why repo-block depends on repo-bundle, why zst beats gz, and how asynchronous archive-push works ahead using the spool path.

for a senior

Show judgement on costs: archive-push-queue-max trades PITR for uptime, new repo formats lock out old binaries, and parallelism must not starve production.

for a principal

Decide which load belongs on which host and repository, balancing backup window, object-store request costs and the restore path the options create.

## Where the time goes Take the reserved scenario: a 5 TB SaaS analytics cluster backed up to an object-store repository. With defaults, four costs dominate: - **One process** compresses and uploads everything (`process-max` defaults to 1). - **gzip** compression (`compress-type` defaults to `gz`) is CPU-heavy. - **Thousands of small files** each become a separate object, and object stores charge latency per request. - **Whole-file copies**: a diff or incr recopies every changed file, and PostgreSQL relation files are segments of up to 1 GB, so one updated row in a large table recopies a full gigabyte. ## Backup-side options | option | default | what it does | what it costs | |---|---|---|---| | `process-max` | 1 | parallel compression and transfer | CPU and I/O on the host being copied | | `compress-type` | `gz` | `zst` is much faster at a similar ratio | needs a build with Zstandard; older pgBackRest cannot read it | | `repo-bundle` | off | packs small files into bundles (`repo-bundle-size` 20MiB, files under `repo-bundle-limit` 2MiB) | bundles cannot be resumed; manual retrieval is harder | | `repo-block` | off | stores only changed blocks in diff/incr backups | requires `repo-bundle`; 2.46 or later, 2.52.1 or later recommended | | `backup-standby` | `n` | copies most files from a replica | needs a standby configured as `pg2-host`/`pg2-path` | | `start-fast` | off | forces a checkpoint so the backup starts at once | one extra checkpoint | The user guide recommends `zst`, `repo-bundle` and `repo-block` for any new repository. Repositories that use them cannot be read by pgBackRest releases older than the feature. ## How block incremental works With `repo-block` enabled, pgBackRest splits a file into blocks and stores a **block map** with it. A later diff or incr consults that map and stores only the blocks that changed, so a big table with a few hot rows no longer costs a whole segment per backup. The block size depends on the file's size and age. Older and larger files get larger blocks, and a file that has not changed for long enough is not stored block-incrementally at all. Enabling block incremental on **fulls as well** makes the full slightly larger but gives later backups the maps they need. It also speeds up a later delta restore, because single blocks can be fetched from the repository. ## Keeping WAL archiving ahead 1. **`archive-async=y`** plus a `spool-path` (default `/var/spool/pgbackrest`) on local disk. The asynchronous `archive-push` looks ahead in `pg_wal` and pushes ready segments in parallel. The foreground command still reports success only once a segment is safely stored. 2. Set **`process-max` for `archive-push`** in a `[global:archive-push]` section so that archiving gets its own degree of parallelism. 3. **`archive-push-batch-size`** (new in 2.59, default 16GiB) caps how much WAL one asynchronous run pushes. 4. **`archive-push-queue-max`** has no default. If it is set and the queue exceeds it, pgBackRest tells PostgreSQL the WAL was archived and **drops** it. That keeps `pg_wal` from filling the volume, but the archive stream is broken and point-in-time recovery past that point is impossible until a new backup. Without the option, a failing `archive_command` makes PostgreSQL keep the WAL, and `pg_wal` grows until the repository returns. ## Backup from a standby With `backup-standby=y` (standby required) or `prefer` (fall back to the primary), pgBackRest still needs **both** hosts. It starts and stops the backup on the primary, waits for the standby to replay to the backup's start point, copies the replicated files from the standby, and copies the few remaining files from the primary. Logs and statistics from the primary therefore end up in the backup, and the read load moves off the primary. ## Traps - Setting `process-max` so high that the backup competes with production queries. - Enabling `repo-block` on diffs only, so they have no block maps to build on. - Setting `archive-push-queue-max` without alerting on it, which silently trades PITR for uptime. - Expecting `backup-standby` to work with only the standby configured. - Turning on `zst`, bundling or block incremental and then restoring with an older pgBackRest binary on a recovery host. Keep the restore tooling at the same version as the backup tooling. - Measuring only the backup window. The same options decide restore speed, so time a restore from the object store after changing them.

  • With pgBackRest, why would you not set repo-hardlink on a repository that uses block incremental?
    `repo-hardlink` is only valid when `repo-bundle` is off, and `repo-block` requires `repo-bundle` on, so the two cannot coexist on one repository. Block incremental, which also works on object storage, is the space-saving mechanism to choose.
  • When is setting pgBackRest's archive-push-queue-max the right call, and what must accompany it?
    When a full `pg_wal` volume would take down the primary and you prefer losing PITR to losing the database. Once the queue exceeds the limit, pgBackRest acknowledges and drops WAL, so the archive has a gap until the next backup. Pair the setting with an alert on the warning it logs and on archive lag, and with a plan to take a new backup at once.

Block incremental is like a copy shop that keeps a page-by-page fingerprint index from the first full copy of every binder. Next time it recopies only the pages whose fingerprints changed, not every 1,000-page binder with one edited page, which is why the first full copy must build the index too.

  • pgBackRest parallelises backups by default, so process-max can stay at 1.
  • repo-block works on its own without enabling repo-bundle.
  • Block incremental only needs enabling on diff and incr backups.
  • archive-push-queue-max just slows archiving down; no WAL is ever lost.
  • backup-standby copies everything from the replica without contacting the primary.
  • archive-async needs no local disk because it streams straight from memory.