Geospatial Portal

Taking Consistent PostGIS Backups with pgBackRest

This guide sets up continuous write-ahead archiving and incremental backups for a PostGIS database, with verification that the result is restorable. It belongs to Backup & Disaster Recovery for Spatial Platforms, within the Infrastructure Orchestration & Configuration Management framework.

Prerequisites

  • PostgreSQL 14 or newer with PostGIS, and superuser access for the initial configuration.
  • pgBackRest 2.45 or newer installed on the database host and on the repository host.
  • Object storage in a different failure domain from the database, with credentials that the database’s own role cannot use to delete objects.
  • Space for a scratch restore — a full copy of the database, used by the verification below.

Why a volume snapshot is not the answer

A running database writes continuously, and a snapshot taken without coordination captures files in an inconsistent state. Sometimes recovery replays the write-ahead log and produces a working database; sometimes it produces one that starts and reports corruption later, under load, in a way that looks like hardware failure. Spatial workloads make this worse rather than better: large geometry writes and index builds produce long periods of heavy, multi-file activity in which the odds of catching a bad moment are highest.

Three backup approaches, by guarantee and granularity Volume snapshot, logical dump, and base backup with continuous archiving, compared by consistency, restore points available, and cost. APPROACH CONSISTENCY RESTORE POINTS volume snapshot uncoordinated none — files caught mid-write the snapshot moment, if it works at all logical dump pg_dump consistent snapshot of the data only when the dump ran; slow to restore base backup + WAL pgBackRest consistent by construction any moment inside the retention window incremental daily cost, parallel restore

Step-by-step implementation

1. Configure the repository 

# /etc/pgbackrest/pgbackrest.conf  (on the database host)
[global]
repo1-type=s3
repo1-path=/geoportal
repo1-s3-bucket=agency-pg-backups
repo1-s3-endpoint=s3.eu-west-2.amazonaws.com
repo1-s3-region=eu-west-2

# Encrypt at rest with a passphrase stored OUTSIDE this environment.
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=${PGBACKREST_CIPHER_PASS}

# Retention: enough restore points to cover the interval between a mistake
# happening and somebody noticing it — for spatial data, weeks not days.
repo1-retention-full=4
repo1-retention-full-type=count
repo1-retention-diff=7

# Spatial databases are large; parallelism is what keeps the window sane.
process-max=8
compress-type=zst
compress-level=3

# Verify checksums on every backup, not only on demand.
repo1-bundle=y
start-fast=y
archive-async=y
spool-path=/var/spool/pgbackrest

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

2. Turn on continuous archiving 

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=geoportal archive-push %p'
archive_timeout = 60        # bound the recovery point even when writes are idle
wal_level = replica
max_wal_senders = 4

archive_timeout is easy to overlook and matters for a portal with bursty editing. Without it, a quiet afternoon means no segment is completed and therefore none is archived, so the effective recovery point drifts backwards while the graphs show a healthy system.

3. Create the stanza and take the first backup 

sudo -u postgres pgbackrest --stanza=geoportal stanza-create
sudo -u postgres pgbackrest --stanza=geoportal check
#   expect: "check command end: completed successfully"

# Full backup first; subsequent scheduled runs are incremental.
sudo -u postgres pgbackrest --stanza=geoportal --type=full backup

4. Schedule the cadence 

# /etc/cron.d/pgbackrest — weekly full, daily differential, incrementals hourly.
# Times are deliberately off the seeding window so the two do not compete for IO.
15 02 * * 0  postgres pgbackrest --stanza=geoportal --type=full backup
15 02 * * 1-6 postgres pgbackrest --stanza=geoportal --type=diff backup
15 */4 * * * postgres pgbackrest --stanza=geoportal --type=incr backup
What a restore has to read, and why the cadence matters A timeline of a full backup, a differential, several incrementals and archived segments, with the subset a point-in-time restore must apply. One week of backups, and what a Thursday restore reads full · Sun diff · Wed incr incr archived WAL segments, continuous A restore to Thursday 14:05 applies the Sunday full, then the Wednesday differential, then both incrementals, then WAL up to 14:05 — and stops. The trade-off in the cadence Less frequent full backups cost less storage and make restores longer, because more increments must be read and applied. Choose the interval from the recovery time objective, then measure an actual restore rather than estimating it.

5. Protect the repository from the platform it protects 

# The database's own credentials must not be able to delete backups. Use a
# separate identity with write-and-read but no delete, and enforce retention
# with an object-lock policy on the bucket instead.
aws s3api put-object-lock-configuration --bucket agency-pg-backups \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled","Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":35}}}'

Verification, which is the part that makes it a backup 

# 1. The stanza is healthy and archiving is flowing
sudo -u postgres pgbackrest --stanza=geoportal check
sudo -u postgres pgbackrest --stanza=geoportal info | head -20
#   expect: a recent backup, and "wal archive min/max" advancing

# 2. The newest restore point is inside the recovery point objective
psql -Atc "SELECT now() - pg_last_committed_xact();"       # write activity
sudo -u postgres pgbackrest --stanza=geoportal info --output=json \
  | jq -r '.[0].archive[-1].max'
#   expect: a segment archived within the last few minutes

# 3. Stored blocks verify against their checksums
sudo -u postgres pgbackrest --stanza=geoportal verify
#   expect: "verify command end: completed successfully"

# 4. A real point-in-time restore into a scratch instance
sudo -u postgres pgbackrest --stanza=geoportal \
  --type=time --target="2026-08-09 14:05:00+00" \
  --pg1-path=/var/lib/postgresql/scratch restore
sudo -u postgres pg_ctl -D /var/lib/postgresql/scratch start

# 5. And the restored data is spatially intact, not merely present
psql -h /tmp -p 5433 -d geoportal -Atc "
  SELECT count(*), round(ST_XMin(ST_Extent(geom))::numeric,3),
                   round(ST_YMax(ST_Extent(geom))::numeric,3)
  FROM parcels;"
#   expect: counts and extents matching production at that timestamp

Check 5 is the one that distinguishes a restore from a successful command. A database that starts is not evidence that the spatial data survived — compare a row count and an extent against what production held at the target time, and record the numbers so the next drill has a baseline.

Where the backup window competes with everything else

A spatial database’s backup is IO-heavy and long, and it lands on infrastructure that is also serving tiles, running seeding jobs, and executing overnight ingestion. Scheduling all of those at 02:00 because that is “the quiet time” produces a night in which each one runs slowly and the backup window creeps past the morning.

Staggering the overnight workloads Two schedules — everything at once, and a staggered order — showing how the backup window changes. All at 02:00 — each one slower, and the backup overruns database backup tile seeding harvest and index maintenance into working hours Staggered — the backup goes first, because the others can be retried database backup tile seeding harvest, index Give the backup the first slot: a delayed seed is a slower map, while a delayed backup is a wider recovery window.

Two further habits keep the window honest. Record the backup duration as a metric rather than reading it from a log, so a slow creep over months is visible before it becomes an overrun. And when a backup does overrun into working hours, treat that as a capacity signal rather than a scheduling nuisance — it usually means the database has grown past the point where a nightly full is the right shape, and the answer is a longer interval between full backups with more differentials, not an earlier start time.

Troubleshooting matrix

Symptom Likely cause Fix
Backups succeed but the recovery point is hours old No archive_timeout, so quiet periods archive nothing Set it to a minute or two; the cost is a few small segments
archive_command failures fill the data directory Archiving is blocked and WAL cannot be recycled Alert on archive failures immediately — this ends as a full disk
Restore is far slower than the objective allows Single-process restore, or too many increments since the last full Raise process-max; shorten the interval between full backups
Repository grows without bound Retention set by count while backup size grew Review retention against actual sizes; use object-lock for the guarantee
A compromised host could delete the backups The database’s own credentials have delete rights on the bucket Separate identities; enforce retention with object lock
Restore starts but PostGIS functions are missing Extension versions differ between source and restore host Pin the PostGIS version with the image, and check it in the drill
Verification passes but nobody has ever restored verify reads blocks; it does not prove a usable database Schedule the scratch restore, and assert on data, not on exit codes

FAQ

How long should a point-in-time window be?

Long enough to cover the interval between a mistake and its discovery. For transactional systems that is often a day; for spatial data it is routinely weeks, because a wrong attribute in a rarely-viewed layer can go unnoticed for a month. Pair a fine-grained recent window with weekly and monthly copies retained further back.

Does this replace logical dumps entirely?

Almost. A logical dump remains useful for moving a single database between major versions and for extracting one table without a full restore, so many teams keep an occasional dump alongside. What it should not be is the primary mechanism, because its restore point is only ever “when it ran”.

How much does continuous archiving cost in write performance?

Very little, when archiving is asynchronous and the spool has room. The failure mode to watch is not slowness but blockage: if archiving stalls, the database retains write-ahead segments and the data directory fills. Alert on archive failure with the same urgency as on disk usage, because they are the same incident a few hours apart.

Should backups run on a replica instead of the primary?

Yes where a replica exists — it removes the IO from the primary, which for a spatial workload with large sequential reads is worth having. Confirm the replica is not lagging when the backup starts, or the restore point is older than the timestamp on the backup suggests.

What should be alerted on, and at what urgency?

Three things, in descending order. An archive-push failure is the most urgent: write-ahead segments accumulate in the data directory, and the incident ends as a full disk and a stopped database a few hours later. A stale newest-restore-point is next, because the recovery window is widening while everything looks healthy. A failed verification pass is third — important, but it reports on stored data that is not currently changing. Alerting on “the backup job exited non-zero” alone misses all three, because a job can exit cleanly having archived nothing.

Up one level: Backup & Disaster Recovery for Spatial Platforms.