PostgreSQL Backup and PITR with pgBackRest

Why point‑in‑time recovery matters

Imagine a Tuesday morning when a migration script runs on the wrong database on your VPS. Restoring last night’s full backup would lose too much work. You need to recover to just before the migration—perhaps thirty minutes ago—rather than roll back nearly a day.

This guide covers PostgreSQL backup and PITR with pgBackRest: repository setup, WAL archiving, scheduled backups and a restore verified on an isolated server. Examples use a Debian/Ubuntu PostgreSQL 17 cluster named main and options checked against pgBackRest 2.60.0 documentation. Confirm your installed version and paths before adapting them.

How base backups, WAL and recovery targets fit together

PostgreSQL WAL records changes needed to reconstruct the database’s data files. Relevant WAL reaches durable storage before changed persistent data pages are written; WAL is not a record of every application event or configuration edit. A physical backup taken while PostgreSQL runs becomes consistent using its required WAL. PostgreSQL can then replay further WAL toward a selected recovery target.

PITR needs a suitable backup plus every required WAL segment through the target on the correct timeline. You cannot restore a newer backup backward to an earlier state, and an interrupted WAL chain can prevent reaching the target. pgBackRest manages backup files, archive retrieval and recovery configuration; PostgreSQL performs the replay after startup. Recovery affects the cluster as a whole, so valid transactions after the selected target are absent from the restored copy too.

Keep backups and archived WAL outside the production VM’s failure domain. A second disk alone does not establish that separation; a separate backup server or object storage can provide off‑VM protection.

Prep your VPS and choose where backups live

A second block volume can separate local database and backup storage, while an SSH repository host or object store can place backups outside the source VM. Choose the failure you need to survive: a second disk attached to one VPS does not establish independence from deletion or loss of that VPS. Test recovery without access to the original server.

Use PostgreSQL’s existing service account, allow space for the repository and growing WAL, and measure the CPU, I/O and network load of backups. Record your tolerated data loss (RPO) and recovery time target (RTO). Backup frequency, archive latency and restore speed all affect the result; a sample schedule does not guarantee either target.

Quick checklist before we begin

The local walkthrough assumes a dedicated lab VPS, a working PostgreSQL 17 main cluster, sudo access and no external tablespaces. Its data directory is /var/lib/postgresql/17/main; Debian/Ubuntu configuration is under /etc/postgresql/17/main. Synchronize the clock and record incident timestamps with an explicit UTC offset. Production use additionally needs an independent repository and a tested recovery procedure.

Install pgBackRest and create your first stanza

Start with a fresh local lab repository to establish the sequence. Before using the design in production, configure and test an off-VM destination. Changing a repository path does not move existing backups, WAL or encryption metadata.

Install on Debian/Ubuntu

Use a distribution or PostgreSQL package repository that provides pgBackRest compatible with PostgreSQL 17; the package commands assume that repository is already configured. Compare the installed version with the pgBackRest documentation. When a repository host is involved, both ends must run exactly the same pgBackRest version.

sudo apt update
sudo apt install pgbackrest
/usr/bin/pgbackrest version
sudo -u postgres psql -X -d postgres -c "SHOW data_directory;"

The examples use /usr/bin/pgbackrest and /etc/pgbackrest/pgbackrest.conf. Verify those locations for your package. A stanza represents one PostgreSQL cluster, potentially containing several databases, and connects that cluster’s backup and archive configuration.

Repository and permissions

For this fresh lab, create postgres-owned repository and log directories. Keep the configuration root-managed and readable by the postgres group. The file-creation command below is only for a new file; do not use it to overwrite an existing configuration or change ownership recursively on a populated repository.

sudo install -d -o root -g postgres -m 0750 /etc/pgbackrest
sudo install -d -o postgres -g postgres -m 0750 /var/lib/pgbackrest /var/log/pgbackrest
# Fresh lab configuration only; do not overwrite an existing file.
sudo install -o root -g postgres -m 0640 /dev/null /etc/pgbackrest/pgbackrest.conf
sudoedit /etc/pgbackrest/pgbackrest.conf

Write the core configuration

Save the following INI contents using the editor opened above. Replace REPLACE_WITH_RANDOM_SECRET with a strong random secret generated in a password manager before creating the stanza. Keep an independent recovery copy of the secret outside the VPS; avoid placing the real value in shell history.

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_RANDOM_SECRET
compress-type=zst
start-fast=y
log-path=/var/log/pgbackrest
log-level-console=info
log-level-file=info

[main]
pg1-path=/var/lib/postgresql/17/main

The paths must match the actual cluster and prepared repository. zst is pgBackRest’s value for Zstandard compression; zstd is not the option value. start-fast=y requests a fast checkpoint at backup start and can increase I/O pressure. Compression savings and runtime depend on the data and available CPU.

Create the stanza

Create the stanza after reviewing the repository and encryption settings. It initializes repository metadata for this cluster; it does not create a full backup or prove that archiving is configured.

sudo -u postgres /usr/bin/pgbackrest --stanza=main stanza-create

If stanza creation fails, inspect the command’s error and verify the configuration, repository access and encryption secret. Avoid retrying with a different secret against the same initialized repository: its encryption settings cannot simply be changed afterward.

Turn on WAL archiving in PostgreSQL

PostgreSQL invokes the archive command for completed WAL segments. pgBackRest stores them alongside the repository’s backup history. Until a segment is completed and successfully transferred, its latest transactions may remain only on the source server.

Edit postgresql.conf

Edit /etc/postgresql/17/main/postgresql.conf or an included configuration file. Keep fsync and full_page_writes enabled. The example’s hot_standby=on also permits read-only inspection when the later recovery stops at its target.

# /etc/postgresql/17/main/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = '/usr/bin/pgbackrest --stanza=main archive-push %p'
archive_timeout = '60s'
hot_standby = on

Changing archive_mode or wal_level requires a restart; a reload does not apply those start-only settings. Restart this named lab cluster and inspect the effective values. A production restart needs a planned interruption of connections.

sudo pg_ctlcluster 17 main restart
sudo -u postgres psql -X -d postgres -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name IN ('wal_level','archive_mode','archive_command','archive_timeout','hot_standby');"

Now run an active configuration check. On a primary, pgBackRest check creates a restore point, requests a WAL switch and waits for the required archive. This has database activity and is different from reading repository inventory.

sudo -u postgres /usr/bin/pgbackrest --stanza=main check

If the check fails, inspect PostgreSQL and pgBackRest logs, the stanza, the absolute command path, repository permissions and credentials. After changing only archive_command, a reload can apply it; the effective value can be inspected directly:

sudo -u postgres psql -X -d postgres -c "SHOW archive_command;"

Why WAL archiving matters now, not later

In a hypothetical setup with only a midnight backup and no archived WAL, a destructive command at noon could leave twelve hours of work unrecoverable. Configure and validate WAL archiving before you need PITR; enabling archive_mode alone does not establish a working recovery process.

Create the first backup and schedule later backups

A full backup establishes a recoverable starting point while PostgreSQL continues running. pgBackRest coordinates the backup with PostgreSQL and, with its default archive check, waits for the WAL required for backup consistency. A successful backup is useful evidence, but only a restore exercise verifies your end-to-end recovery procedure.

Full backup

sudo -u postgres /usr/bin/pgbackrest --stanza=main --type=full backup

Wait for successful completion, then record the backup label and completion time. Runtime depends on file volume, I/O, CPU and repository throughput:

sudo -u postgres /usr/bin/pgbackrest --stanza=main info

info should list the completed backup. It reports the repository’s inventory and archived WAL range; it is not proof that new WAL is currently arriving or that every requested recovery time is reachable. Keep the label for the named-target restore later in this guide.

Incremental and differential backups

A normal differential backup copies files changed since the last full backup; an incremental copies files changed since the previous backup. Do not equate these modes with copying only changed pages. Dependencies on earlier backups must remain available. To create each type explicitly:

# Differential backup
sudo -u postgres /usr/bin/pgbackrest --stanza=main --type=diff backup

# Incremental backup
sudo -u postgres /usr/bin/pgbackrest --stanza=main --type=incr backup

The example keeps four full backups and sets a differential retention count of fourteen. These are counts, not weeks or days; full backups are included in the differential count. Expiring a full backup also expires its dependent differential and incremental backups. The recovery window still depends on successful jobs and the necessary WAL remaining available.

Schedule it

The following postgres crontab schedules a full backup on Sunday at 02:00, differentials Monday–Saturday at 02:00, and incrementals every day at 15 minutes past each hour from 08:00 through 20:00. It uses the server’s schedule timezone. The earlier setup created /var/log/pgbackrest for postgres, so the redirection has a writable destination. Open the user’s crontab, then paste the three schedule lines:

sudo crontab -u postgres -e

# Full: Sunday 02:00
0 2 * * 0 /usr/bin/pgbackrest --stanza=main --type=full backup >> /var/log/pgbackrest/cron.log 2>&1
# Differential: Monday-Saturday 02:00
0 2 * * 1-6 /usr/bin/pgbackrest --stanza=main --type=diff backup >> /var/log/pgbackrest/cron.log 2>&1
# Incremental: daily, 08:15 through 20:15
15 8-20 * * * /usr/bin/pgbackrest --stanza=main --type=incr backup >> /var/log/pgbackrest/cron.log 2>&1

Arrange log rotation and alerts for failed or overdue backups. Long jobs can overlap later runs; a lock failure must be noticed and the missed backup rescheduled. WAL archiving runs separately, and archive_timeout=60s is not a guaranteed one-minute RPO: switching depends on database activity, and transfer can be delayed or fail. Relate the schedule to measured recovery needs using a DR plan with RTO, RPO and tested runbooks.

Configure an independent SSH or S3 repository

Choose one destination and test it before relying on it. The excerpts below describe a new independent repository, not an automatic migration of the local one. Preserve the old usable backup chain until the new repository has its own verified backup, WAL archive and restore.

SSH repository (repo on a backup server)

Install the identical pgBackRest version on both machines. On the backup server, create a restricted repository directory owned by the dedicated pgbackrest account and a readable pgBackRest configuration containing the matching main stanza and repository path. Provision SSH key authentication from the database’s postgres account to that user and verify the host key. Commands initiated on the repository host additionally need its pg1-host configuration and authorized access back to the database host; the excerpt below assumes commands are initiated on the database host.

On the database server, merge these global settings with the earlier [main] stanza. On the repository host, configure the same repository path, retention and encryption settings in [global] and the cluster’s pg1-path in [main], without setting repo1-host to itself. Protect the secret on both hosts. This is a two-sided configuration, not just one additional hostname:

[global]
repo1-host=backup.example.com
repo1-host-user=pgbackrest
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_RANDOM_SECRET
compress-type=zst
start-fast=y

Use the real backup hostname and provision the new repository before directing archiving there. Create the stanza, check archiving, take a full backup and perform a separate-host restore. Review access and deletion rights as well as location: credentials on a compromised source server should not be assumed to protect every remote copy.

S3 repository (object storage)

For a new S3 repository, the bucket, region and endpoint must agree. This example uses an AWS endpoint in eu-central-1 and a dedicated object prefix; S3-compatible services may need different endpoints or addressing options. Create the bucket, grant only the required access and replace all credential placeholders. Merge the global settings with the earlier [main] stanza:

[global]
repo1-type=s3
repo1-s3-bucket=REPLACE_WITH_BUCKET_NAME
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com
repo1-s3-region=eu-central-1
repo1-s3-key=REPLACE_WITH_ACCESS_KEY
repo1-s3-key-secret=REPLACE_WITH_SECRET_KEY
repo1-path=/pgbackrest-main
repo1-retention-full=4
repo1-retention-diff=14
compress-type=zst
start-fast=y

For pgBackRest client-side encryption, also add these entries to that same [global] section before creating the new stanza. Replace the placeholder securely. Repository encryption is distinct from any encryption offered by the object-store provider:

repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_RANDOM_SECRET

Keep object lifecycle rules from independently deleting backups, required WAL or timeline history that pgBackRest still retains. Retention and object-lock policies must be designed together and tested; an unexpected denial of deletion can also disrupt maintenance. An encrypted repository remains unusable if its passphrase is lost, so test recovery of credentials without the source VPS.

Practice PITR on an isolated recovery server

Use a separate recovery VM with PostgreSQL 17, the required extensions, compatible pgBackRest and access to the prepared backup repository. Copy or restore external configuration separately. Disable application connections, backup schedules and external integrations on this VM. The walkthrough assumes no external tablespaces; if your cluster has them, review mapping and WAL paths first. A different port alone is not adequate isolation.

Make a small, deliberate mistake to recover from

In the dedicated source lab cluster only, first finish a full backup and record its label. Then connect with sudo -u postgres psql -X -d postgres. With psql autocommit enabled, create and commit a test row, record the returned timestamp including its offset, and create a uniquely named restore point before dropping the table. Use the name below only if it has not previously been used in this test history:

CREATE TABLE pitr_demo(id serial PRIMARY KEY, note text);
INSERT INTO pitr_demo(note) VALUES ('before');
SELECT clock_timestamp();
SELECT pg_create_restore_point('before-batch-42');
-- Deliberate lab-only mistake, after the preceding commands have committed:
DROP TABLE pitr_demo;

Exit psql, then run sudo -u postgres /usr/bin/pgbackrest --stanza=main check on the source lab and require success before the restore test. This requests a WAL switch and checks archiving. A checkpoint alone is not the way to force a segment switch; pg_switch_wal() requests that switch, but you must still verify successful transfer. Waiting one minute is not proof that the needed WAL has arrived.

Stop Postgres and prepare the restore target

On the recovery VM, make the repository and decryption secret accessible and restore the correct external configuration under /etc/postgresql/17/main. Verify hot_standby=on, disable inappropriate network/integration settings and confirm the lab paths. Stop its existing test cluster and preserve its current directory. The following aborts if main.before-pitr already exists; it does not delete the old files:

# Run only on the isolated recovery VM, with the correct lab paths.
set -e
sudo pg_ctlcluster 17 main stop
sudo test ! -e /var/lib/postgresql/17/main.before-pitr
sudo mv -T /var/lib/postgresql/17/main /var/lib/postgresql/17/main.before-pitr
sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/17/main

Restore to a timestamp

Use the exact timestamp recorded after the committed insert; do not subtract arbitrary seconds that could exclude the wanted data. pgBackRest can select a suitable backup for a time target, but inspect the selected backup and its completion time in the restore output. If the repository contains recovery branches, select the incident’s --target-timeline explicitly as needed. The example assumes a single relevant timeline.

read -r -p 'Recorded recovery timestamp, including UTC offset: ' PITR_TARGET
: "${PITR_TARGET:?A verified recovery timestamp is required}"
sudo -u postgres /usr/bin/pgbackrest \
  --stanza=main \
  --type=time \
  --target="$PITR_TARGET" \
  --target-action=pause \
  --archive-mode=off \
  restore

Restore fills the empty data directory and writes recovery configuration, including recovery.signal. PostgreSQL replays WAL when you start it. --archive-mode=off prevents the recovered lab from archiving new WAL into the source repository; --target-action=pause with hot standby allows inspection before leaving recovery. No --delta is needed for this empty target.

sudo pg_ctlcluster 17 main start
sudo -u postgres psql -X -d postgres -c "SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();"
sudo -u postgres psql -X -d postgres -c "SELECT * FROM pitr_demo;"

Require logs showing that the intended target was reached, pg_is_in_recovery() returning true and the pause state actually reading paused. The table should contain the row with note before. Check application-specific data as well. An unreached target or missing WAL is a failed drill; keep the application disconnected and investigate the target, selected backup and timeline.

Restore to a named stop point

The earlier pg_create_restore_point call wrote the named marker into WAL before the drop. Merely supplying a target name during restore does not create that marker. For a second drill, stop PostgreSQL and prepare another empty target while preserving the previous test result; do not run this over the already restored directory. Choose the recorded backup label that completed before the marker: unlike time recovery, a name target cannot automatically identify the appropriate prior backup.

read -r -p 'Backup label completed before the named restore point: ' PITR_BACKUP
: "${PITR_BACKUP:?A verified backup label is required}"
sudo -u postgres /usr/bin/pgbackrest \
  --stanza=main \
  --set="$PITR_BACKUP" \
  --type=name \
  --target='before-batch-42' \
  --target-action=pause \
  --archive-mode=off \
  restore

Start the recovered cluster and repeat the pause and table checks. The time and name examples are alternative restorations. After confirming the intended target, an administrator can run SELECT pg_wal_replay_resume(); to end the target pause and finish recovery. Promotion creates a new timeline. This lab keeps archiving disabled; a real cutover requires a separate plan to enable validated archiving, direct clients, reconcile later external transactions and take a new backup.

What pgBackRest does for you during restore

pgBackRest restores files and configures WAL retrieval with archive-get; PostgreSQL handles recovery and the requested target action. Recovery configuration does not guarantee that the required backup and archive actually cover the target. The PostgreSQL continuous archiving and PITR documentation explains consistency, timeline history and tablespace limitations.

Retention, pruning, and watching for trouble

Treat retained backups as dependency chains with a required WAL window. Repository size alone does not describe how far back you can recover or how quickly the recovery will finish.

Set retention you can afford

The sample counts of four full backups and fourteen differentials must be evaluated against actual completion times, backup dependencies and required recovery dates. More frequent backups can reduce replay work, but aggressive WAL expiration can remove PITR coverage. Keep default archive retention aligned with retained full backups unless a separately tested policy requires otherwise.

Prune and info

pgBackRest normally runs expiration after backups. Before changing retention or running a manual cleanup, preview what the proposed policy would remove:

sudo -u postgres /usr/bin/pgbackrest --stanza=main --dry-run expire
sudo -u postgres /usr/bin/pgbackrest --stanza=main info

The dry run does not remove data. Only after checking the required recovery window and dependencies should you run sudo -u postgres /usr/bin/pgbackrest --stanza=main expire without --dry-run. This is destructive to expired backup chains; info afterward is inventory, not a replacement for a restore test.

Monitoring and alerts

Alert on failed jobs, time since the last successful backup, repository capacity and source pg_wal growth. Inspect pg_stat_archiver fields such as last_archived_time, last_failed_time and interval changes in failed_count, alongside logs. Quiet activity can explain an old archive timestamp; persistent archive errors can retain WAL until the source disk fills and PostgreSQL stops. Historical success cannot guarantee current health.

Vacuum and bloat matter too

Database maintenance affects file size and I/O, but backup growth has several causes. The PostgreSQL autovacuum and bloat guide helps separate dead tuples, reusable space and physical file growth. VACUUM does not always shrink files, and maintenance can itself generate WAL; measure before attributing archive or backup size to one setting.

Diagnose common backup and recovery failures

Investigate the actual error and the affected stage: repository access, archive transfer, backup creation or PostgreSQL recovery. Changing several unrelated settings at once makes it harder to determine which failure was resolved.

“archive_command failed” on repeat

Check the configured binary path, stanza spelling, postgres access to the configuration, decryption secret and repository destination. Local and remote repositories have different ownership requirements; do not recursively change a remote or populated repository to postgres as a generic repair. Use command output and service logs to distinguish a permission failure from network, capacity or authentication errors.

Backups are huge even after compression

Compression cannot promise a fixed saving. Confirm that your build supports zst, then compare compression ratio, CPU cost, file churn and full-backup size. Unchanged large files may already be referenced by differential or incremental backups. Moving large objects out of the database is a separate application consistency decision and requires their own backup/recovery design; it does not automatically reduce every incremental backup.

Restore works, but the target time is off

Confirm the recorded UTC offset, backup completion time and correct recovery timeline. A named target avoids timestamp selection only if its marker was actually written and retained. Also preserve postgresql.conf, pg_hba.conf, includes, extension requirements and pgBackRest configuration separately where they are outside the data directory. WAL replay does not reconstruct configuration edits.

Testing restores without downtime

A separate VM gives a clearer boundary for rehearsals. If a same-host restore is unavoidable, it needs distinct data/configuration paths, ports, sockets and mapped tablespaces, disabled outbound integrations and prevention of archive writes into the source repository. WAL tablespace operations can reference original absolute paths. These conditions must be addressed before startup; changing only the port is insufficient.

Security and encryption

Configure client-side repository encryption before initializing the stanza, keep its configuration restricted and store an independent recovery copy of the passphrase. To change encryption for an existing repository, plan and test a separate repository migration; changing the cipher or passphrase in place is not a conversion procedure. Prove that another authorized operator can recover both the data and the secret when the source machine is unavailable.

Document the path, not just the tools

Record the cluster and package versions, paths, repository, selected backup label, target, timeline, start/end times and application validation results in the runbook. Keep actual secrets in their controlled store. Include the criteria for abandoning a failed restore and preparing a new empty target.

What a completed recovery test should demonstrate

A useful test shows that an independent repository and available credentials can restore the cluster to the intended target, with application checks passing inside the required recovery time. Schedule repeated drills based on change rate and risk; a monthly interval can be a starting policy, with another drill after configuration or retention changes.