PostgreSQL Physical Backup and PITR on Ubuntu: A Complete pg_basebackup Script

A physical backup answers the question a logical dump cannot: restore the whole cluster to 16:42, one minute before the deployment that corrupted the data. That capability does not come from pg_basebackup alone — it comes from a base backup plus an unbroken chain of archived WAL. This guide sets up both on Ubuntu, ships a tested backup script, and walks through an actual point-in-time recovery.

What physical backup actually is

Where pg_dump asks the server for the logical contents of a database, pg_basebackup copies the cluster's files — the whole data directory, every database at once, exactly as they sit on disk. This has consequences worth internalizing before you build anything on it:

• It restores the entire cluster, never a single table. You cannot recover one dropped table from a base backup without restoring the whole thing somewhere else first.
• It only restores to the same PostgreSQL major version, on a compatible architecture. It is not a migration tool.
• Restoring is fast, because it is a file copy. There are no rows to re-insert and no indexes to rebuild — the dominant cost of a large logical restore.
• Combined with archived WAL, it supports point-in-time recovery, which no dump can provide.

For the complementary strategy — per-table recovery, cross-version portability — see PostgreSQL logical backup on Ubuntu with pg_dump. Running both is normal and correct.

The mental model: a base plus a chain

The base backup is a consistent starting point. WAL (write-ahead log) is the ordered record of every change the server made after it. Recovery replays that record forward, stopping wherever you tell it to.

Two implications follow directly, and both are where real deployments fail:

Step 1: Enable WAL archiving on the server

Without this, a base backup can only restore to the instant it finished. Configure the archive before taking the backup you intend to rely on. On Ubuntu packages, edit /etc/postgresql/16/main/postgresql.conf:

wal_level = replica              # minimum for physical backup and replication
archive_mode = on                # requires a restart to change
archive_timeout = 300            # force a segment switch every 5 minutes

# Must return non-zero on failure, and must never overwrite an existing file.
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

max_wal_senders = 10             # pg_basebackup needs at least one

sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/wal_archive
sudo systemctl restart postgresql@16-main.service

# Confirm it took effect, and that archiving is actually succeeding
sudo -u postgres psql -XAtqc "SHOW archive_mode"
sudo -u postgres psql -X -c "SELECT * FROM pg_stat_archiver;"

Read pg_stat_archiver carefully: archived_count should climb and failed_count should stay at zero. A rising failed_count means WAL is piling up in pg_wal and will eventually fill the filesystem and stop the database.

The cp example above is the documentation's illustration and is fine for a first setup, but note its limits: it does not fsync, and a local directory dies with the server. For production, archive to another host or object storage — rsync over SSH, aws s3 cp, or a purpose-built tool such as pgBackRest or Barman, which handle compression, parallelism, and retention of the base/WAL pair together.

Step 2: The base backup script

The script mirrors the guards from the logical backup — lock, staging directory, atomic promotion, verification, retention only after success — plus one specific to physical backups.

That addition is the replication slot. pg_basebackup --wal-method=stream opens a second connection to receive the WAL generated while the copy runs. On a busy server, a long copy can outrun WAL retention and the server may recycle a segment the backup still needs, which fails the backup near the end. A slot tells the server to hold that WAL. The flip side is the failure mode this creates: a slot left behind by a crashed run makes the server retain WAL forever, until pg_wal fills the disk and the primary stops. The script therefore drops the slot on every exit path.

Save as postgresql-basebackup-ubuntu.sh, or download the ready-to-run file:

#!/usr/bin/env bash
#
# Physical backup for PostgreSQL on Ubuntu using pg_basebackup.
#
# A base backup alone restores to the moment the backup ended. For
# point-in-time recovery you also need continuous WAL archiving.
#
# Usage:
#   sudo -u postgres ./postgresql-basebackup-ubuntu.sh
#   sudo -u postgres BACKUP_ROOT=/backup/base RETENTION_DAYS=14 ./postgresql-basebackup-ubuntu.sh
#
set -Eeuo pipefail

BACKUP_ROOT="${BACKUP_ROOT:-/var/backups/postgresql-base}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
SLOT_NAME="${SLOT_NAME:-pg_basebackup_script}"
COMPRESS_LEVEL="${COMPRESS_LEVEL:-6}"
MAX_RATE="${MAX_RATE:-}"
LOCK_FILE="${LOCK_FILE:-/var/lock/postgresql-basebackup.lock}"

log() {
  echo "[$(date --iso-8601=seconds)] $*"
}

fail() {
  echo "ERROR: $*" >&2
  exit 1
}

[[ "$RETENTION_DAYS" =~ ^[0-9]+$ ]] || fail "RETENTION_DAYS must be an integer."
[[ "$COMPRESS_LEVEL" =~ ^[1-9]$ ]] || fail "COMPRESS_LEVEL must be 1-9."
[[ "$SLOT_NAME" =~ ^[a-z0-9_]+$ ]] || fail "SLOT_NAME must match ^[a-z0-9_]+$."

for command_name in psql pg_basebackup flock; do
  command -v "$command_name" >/dev/null || fail "Missing command: $command_name"
done

exec 9>"$LOCK_FILE" || fail "Cannot open lock file $LOCK_FILE"
flock -n 9 || fail "Another base backup is already in progress."

SERVER_VERSION_NUM="$(psql -XAtqc 'SHOW server_version_num' postgres)" ||
  fail "Cannot connect to PostgreSQL. The role needs the REPLICATION attribute."
SERVER_VERSION="$(psql -XAtqc 'SHOW server_version' postgres)"

# pg_basebackup opens a second connection for WAL streaming, so the server needs
# at least one spare walsender slot beyond any replicas already connected.
WAL_SENDERS="$(psql -XAtqc 'SHOW max_wal_senders' postgres)"
(( WAL_SENDERS >= 1 )) || fail "max_wal_senders is 0; pg_basebackup cannot stream WAL."

ARCHIVE_MODE="$(psql -XAtqc 'SHOW archive_mode' postgres)"
if [[ "$ARCHIVE_MODE" != "on" ]]; then
  log "WARNING: archive_mode is '$ARCHIVE_MODE'. This backup restores only to its"
  log "WARNING: own end point. Point-in-time recovery requires WAL archiving."
fi

TIMESTAMP="$(date +%Y%m%dT%H%M%S)"
STAGING_DIR="${BACKUP_ROOT}/.in-progress-${TIMESTAMP}"
FINAL_DIR="${BACKUP_ROOT}/${TIMESTAMP}"

install -d -m 700 "$BACKUP_ROOT"
install -d -m 700 "$STAGING_DIR"

cleanup_staging() {
  local exit_code=$?
  if (( exit_code != 0 )); then
    [[ -d "$STAGING_DIR" ]] && { log "Run failed; removing $STAGING_DIR"; rm -rf -- "$STAGING_DIR"; }
    # A slot left behind after a failure makes the server retain WAL forever,
    # which eventually fills pg_wal and stops the primary.
    psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
                   WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                                  WHERE slot_name = '${SLOT_NAME}')" postgres >/dev/null 2>&1 || true
  fi
  exit "$exit_code"
}
trap cleanup_staging EXIT

# A stale slot from a killed previous run would make --create-slot fail.
psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
               WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                              WHERE slot_name = '${SLOT_NAME}' AND active IS NOT TRUE)"   postgres >/dev/null

log "PostgreSQL $SERVER_VERSION"
log "Destination: $FINAL_DIR"
log "Starting pg_basebackup (this reads the whole cluster)"

BASEBACKUP_ARGS=(
  --pgdata="$STAGING_DIR"
  --format=tar
  --gzip
  --compress="$COMPRESS_LEVEL"
  --wal-method=stream          # ships the WAL generated during the copy
  --checkpoint=fast            # do not wait for the next scheduled checkpoint
  --create-slot
  --slot="$SLOT_NAME"
  --progress
  --verbose
  --no-password
)
[[ -n "$MAX_RATE" ]] && BASEBACKUP_ARGS+=( --max-rate="$MAX_RATE" )

pg_basebackup "${BASEBACKUP_ARGS[@]}"

# pg_basebackup drops the slot itself on success; this only covers the gap where
# the server kept it after an unclean disconnect.
psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
               WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                              WHERE slot_name = '${SLOT_NAME}' AND active IS NOT TRUE)"   postgres >/dev/null

# backup_manifest exists from PostgreSQL 13 onward; pg_verifybackup reads the
# tar set directly from PostgreSQL 17, so older versions only get a file check.
if [[ -f "${STAGING_DIR}/backup_manifest" ]]; then
  if command -v pg_verifybackup >/dev/null && (( SERVER_VERSION_NUM >= 170000 )); then
    log "Verifying backup manifest with pg_verifybackup"
    pg_verifybackup --format=tar "$STAGING_DIR" || fail "pg_verifybackup reported a corrupt backup."
  else
    log "backup_manifest present; verify after extraction with pg_verifybackup"
  fi
else
  log "No backup_manifest (PostgreSQL < 13); skipping manifest verification."
fi

[[ -f "${STAGING_DIR}/base.tar.gz" ]] || fail "base.tar.gz is missing from the backup."
gzip --test "${STAGING_DIR}"/*.tar.gz || fail "A compressed archive failed the gzip integrity test."

(cd "$STAGING_DIR" && sha256sum ./*.tar.gz > SHA256SUMS)

cat > "${STAGING_DIR}/MANIFEST.txt" <<MANIFEST
backup_type      physical (pg_basebackup, tar + gzip)
started_at       ${TIMESTAMP}
finished_at      $(date --iso-8601=seconds)
server_version   ${SERVER_VERSION}
archive_mode     ${ARCHIVE_MODE}
host             $(hostname -f 2>/dev/null || hostname)
restore          stop postgres, empty PGDATA, extract base.tar.gz into it,
                 extract pg_wal.tar.gz into PGDATA/pg_wal, then start the server
pitr             add restore_command and recovery_target_time to postgresql.conf
                 and create the file PGDATA/recovery.signal before starting
MANIFEST

chmod -R go-rwx "$STAGING_DIR"
mv -- "$STAGING_DIR" "$FINAL_DIR"
trap - EXIT

if (( RETENTION_DAYS > 0 )); then
  log "Applying retention: removing sets older than ${RETENTION_DAYS} days"
  find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d     -name '20*T*' -mtime "+${RETENTION_DAYS}"     -print -exec rm -rf -- {} +
fi

log "Base backup completed: $FINAL_DIR"
log "Total size: $(du -sh "$FINAL_DIR" | cut -f1)"
log "Reminder: WAL older than the oldest retained base backup can be recycled;"
log "keep archived WAL for at least as long as the base backups it covers."

Step 3: Run and schedule it

sudo install -m 750 -o postgres -g postgres   postgresql-basebackup-ubuntu.sh /usr/local/bin/

sudo -u postgres /usr/local/bin/postgresql-basebackup-ubuntu.sh

The result is a small, self-describing set:

/var/backups/postgresql-base/20260804T020001/
├── base.tar.gz        # the data directory
├── pg_wal.tar.gz      # WAL generated during the copy
├── backup_manifest    # per-file checksums (PostgreSQL 13+)
├── MANIFEST.txt       # versions, archive_mode, restore instructions
└── SHA256SUMS

Base backups are usually weekly, with WAL archiving providing continuous coverage between them, because a full cluster copy is expensive:

sudo crontab -u postgres -e

# Weekly base backup, Sunday at 01:00. Throttled so it does not saturate
# storage bandwidth during the copy.
MAILTO=dba@example.com
0 1 * * 0 MAX_RATE=100M RETENTION_DAYS=30 /usr/local/bin/postgresql-basebackup-ubuntu.sh   >> /var/log/postgresql/basebackup.log 2>&1

Step 4: Point-in-time recovery, step by step

This is the procedure the whole setup exists for. The scenario: at 16:43 a migration deleted rows it should not have, and you need the cluster as it was at 16:42.

1. Stop PostgreSQL and set the old data directory aside.

sudo systemctl stop postgresql@16-main.service
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken
sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/16/main

2. Extract the base backup. The two archives go to different places: base.tar.gz into PGDATA, and pg_wal.tar.gz into PGDATA/pg_wal.

cd /var/backups/postgresql-base/20260804T020001
sha256sum --check SHA256SUMS

sudo -u postgres tar -xzf base.tar.gz -C /var/lib/postgresql/16/main
sudo -u postgres tar -xzf pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal

3. Tell recovery where to find WAL and where to stop. Since PostgreSQL 12 these are ordinary settings in postgresql.conf; the old recovery.conf file no longer exists.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-08-05 16:42:00-03'
recovery_target_action = 'pause'

recovery_target_action = 'pause' is the important one. The server reaches the target and waits instead of promoting itself, so you can connect read-only and confirm the data is what you expected before committing to that point. The other targets available are recovery_target_lsn, recovery_target_xid, and recovery_target_name (set beforehand with pg_create_restore_point()).

4. Create the recovery signal file and start. Its presence is what puts the server into archive recovery.

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql@16-main.service

# Watch recovery replay the WAL chain
sudo tail -f /var/log/postgresql/postgresql-16-main.log

5. Verify before promoting. While paused, the server accepts read-only connections:

sudo -u postgres psql -X -c "SELECT pg_is_in_recovery();"          -- expect t
sudo -u postgres psql -X -c "SELECT pg_last_wal_replay_lsn();"

# The actual check: is the data as it was before the incident?
sudo -u postgres psql -X -d app -c "SELECT count(*) FROM orders;"

If the target was wrong, stop the server, adjust recovery_target_time, remove the data directory, and extract the base backup again. Recovery cannot rewind past a point it already replayed — you start over from the base.

6. Promote, when the data is confirmed. This ends recovery and makes the cluster writable. It is a one-way door.

sudo -u postgres psql -X -c "SELECT pg_wal_replay_resume();"
sudo -u postgres pg_ctlcluster 16 main promote

sudo -u postgres psql -X -c "SELECT pg_is_in_recovery();"          -- expect f

After promotion, the cluster starts a new timeline. This is PostgreSQL keeping the abandoned history distinct from the new one, so a later recovery does not confuse the two. Take a fresh base backup immediately — your old base plus its WAL belongs to the previous timeline, and treating it as current is a mistake you discover only during the next incident.

Verifying a physical backup

backup_manifest (PostgreSQL 13+) records a checksum for every file, and pg_verifybackup validates the set against it:

# Verify an extracted backup
pg_verifybackup /var/lib/postgresql/16/restore_test

# PostgreSQL 17+ can verify a tar-format set without extracting it
pg_verifybackup --format=tar /var/backups/postgresql-base/20260804T020001

That proves the files are intact. It does not prove the cluster starts, that the WAL chain is complete, or that recovery reaches your target — only an actual restore does. Schedule a real recovery drill quarterly: restore the latest base to a scratch host, replay to an arbitrary recent timestamp, start the cluster, run application queries, and write down how long the whole thing took. That figure is your true RTO.

What breaks PITR in practice

• A silently failing archive_command. The single most common cause of an unrecoverable cluster. If the command returns zero when it did not archive the file, PostgreSQL marks the segment done and recycles it. Ensure it fails loudly, and alert on pg_stat_archiver.failed_count and on the archive's own file count.
• pg_wal filling the disk. When archiving stalls or an abandoned replication slot holds WAL, segments accumulate until the filesystem is full and the server stops. Monitor pg_wal size and pg_replication_slots for inactive slots.
• Archived WAL deleted before the base backup it serves. Retention must be reasoned about as a pair. Never let WAL retention be shorter than base backup retention.
• An archive on the same disk as the database. It survives an accidental DELETE, not the hardware or host failure that physical backups exist for. Ship it off the machine.
• Time zone ambiguity in recovery_target_time. Always write an explicit offset ('2026-08-05 16:42:00-03'). During a DST transition an unqualified local timestamp can match two different instants.
• Extracting pg_wal.tar.gz into the wrong place. It belongs in PGDATA/pg_wal, not in PGDATA. Getting this wrong produces a cluster that will not start, usually at the worst possible moment.

When to move beyond scripts

The script here is deliberately readable and dependency-free, which is the right starting point and enough for many single-server deployments. Past a certain scale, purpose-built tools earn their complexity: pgBackRest and Barman add parallel compressed backups, incremental and differential backups, retention that understands the base/WAL relationship, encryption, direct-to-S3 archiving, and — most valuably — built-in verification. If your database is large enough that a full weekly base backup is painful, evaluate them rather than growing this script into a worse version of one.

Make the archive observable

Physical backup failures are quiet by nature: archiving stops, WAL accumulates, and nothing looks wrong until either the disk fills or a recovery fails. PG Monitoring watches WAL generation rate, pg_wal growth, checkpoint behavior, and replication slot state alongside the workload driving them, so a broken archive surfaces as an alert on the day it breaks — not on the day you need it.

References: the PostgreSQL manual documents continuous archiving and point-in-time recovery, the pg_basebackup reference, archive recovery settings, and pg_verifybackup.