Postgres and data

PostgreSQL WAL archiving from scratch on a single Debian VPS

2026-11-09 · 11 min read

A nightly pg_dump gives you a recovery point per night. WAL archiving gives you one per minute or better, but only if the archive actually receives files, and PostgreSQL will happily run for months with archiving "on" and nothing useful in the archive. This post builds the whole chain by hand on one Debian server: the settings, an archive_command that cannot silently damage the archive, a base backup, and the checks that show each piece is working.

Everything here applies to PostgreSQL 15, 16 and 17. Where the version matters, it says so. Paths follow the Debian and Ubuntu packaging (/etc/postgresql/15/main, /var/lib/postgresql/15/main); change the version number to match yours.

What you are building

Three pieces, and a recovery needs all three:

1. WAL segments. PostgreSQL writes every change to 16 MiB files in pg_wal/. Their names are 24 hex digits: 8 for the timeline, 8 for the log number, 8 for the segment, as in 000000010000000000000007. Archiving means copying each finished segment somewhere else.
2. A base backup. A physical copy of the data directory taken while the server runs. Alone it brings you back to the moment it finished.
3. The archive. A directory (or share, or host) holding the segments and the base backups. It must not live on the disk you are protecting.

Recovery starts from a base backup and replays archived segments in order up to a target. If one segment between them is missing, replay stops there. So the archive has two jobs: receive every segment, and never lose or alter one.

Who needs this

A SaaS with one Postgres and paying customers. The database holds orders and accounts. A bad deploy or a mistaken DELETE at 14:20 should cost you minutes, not the day since the dump. Archiving is what makes "restore to 14:19" possible at all.

An app whose migrations are run by hand. One engineer runs schema changes against production. The worst case is a migration that drops the wrong column. With archiving you can restore a copy from just before it, and extract the column back.

A long-lived internal tool with a database nobody wants to lose. Writes are few, but each one matters (approvals, audit rows). Dumps lose a day; archiving with a timeout loses minutes, and it costs a few kilobytes a day of compressed WAL.

A server heading for a risky upgrade. Before a hosting move or an OS upgrade you want a named point to return to. Archiving plus a restore point (pg_create_restore_point) gives you one, as covered in restoring Postgres to a timestamp.

Step 1: make a place for the archive

For this walkthrough the archive is a local directory. In real use it should be a different disk or a mounted volume, ideally a different machine. A local directory protects you from the wrong DELETE, not from the dead server.

sudo mkdir -p /srv/pg-archive/wal /srv/pg-archive/base
sudo chown -R postgres:postgres /srv/pg-archive
sudo chmod 700 /srv/pg-archive

If the archive is a mounted volume, make the archive command fail when the mount is missing. Otherwise an unmounted disk becomes a directory on the system disk, archiving "works", and your WAL lands on the disk you wanted to protect against.

Step 2: write an archive_command that is safe

The PostgreSQL documentation shows test ! -f /archive/%f && cp %p /archive/%f as an example. It is fine for a first test, and it has two weaknesses. A copy interrupted halfway leaves a partial file under the final name, and test ! -f followed by cp is not atomic. So we use a short script.

The contract with PostgreSQL is simple. The command receives the path of the segment (%p, relative to the data directory) and its file name (%f). It must exit 0 only when the file is safely stored. Any non-zero exit makes PostgreSQL keep the segment and retry.

#!/usr/bin/env bash
# /opt/pgarchive/archive-wal.sh  --  used as: archive_command = '/opt/pgarchive/archive-wal.sh %p %f'
set -euo pipefail
src="$1"; name="$2"; dest=/srv/pg-archive/wal

[ -d "$dest" ] || { echo "archive dir missing: $dest" >&2; exit 1; }

# Already archived? Same content is a success (retries happen), different content is a failure.
if [ -e "$dest/$name" ]; then
  cmp -s "$src" "$dest/$name" && exit 0
  echo "refusing to overwrite $name with different content" >&2
  exit 1
fi

tmp="$(mktemp "$dest/.tmp.XXXXXX")"
trap 'rm -f "$tmp"' EXIT
cp "$src" "$tmp"
sync "$tmp"
ln "$tmp" "$dest/$name"      # hard link fails if the name exists, so it never overwrites

Four details earn their keep. The temp file lives in the archive directory so the final ln stays on one filesystem. sync on the file flushes it before the name appears. ln fails if the name exists, which turns an accidental overwrite into an error. And the cmp branch makes retries idempotent: PostgreSQL may call the command twice for the same segment after a crash.

The overwrite rule matters more than it looks. After you restore into a fresh cluster, or a failed-over standby comes up, timeline and segment numbers can repeat. A plain cp replaces the old file with a different one and your archive is silently corrupted. A refusal is loud and recoverable.

Install it:

sudo install -d -o root -g root -m 755 /opt/pgarchive
sudo install -o root -g root -m 755 archive-wal.sh /opt/pgarchive/archive-wal.sh

The postgres user must be able to execute it and write the archive, and nobody else should be able to edit it.

Step 3: the settings

On Debian, drop a file into conf.d, which postgresql.conf includes by default. That keeps your changes out of the packaged file.

# /etc/postgresql/15/main/conf.d/archiving.conf
wal_level = replica
archive_mode = on
archive_command = '/opt/pgarchive/archive-wal.sh %p %f'
archive_timeout = 60

What each does:

A note for PostgreSQL 15 and later: there is also an archive_library setting that loads an archive module instead of running a shell command. Its default is empty, which means archive_command is used. Set one or the other, never both, and for a shell script like this one leave archive_library alone.

Restart, because archive_mode is changing:

sudo pg_ctlcluster 15 main restart
sudo -u postgres psql -Atc "show archive_mode" -c "show archive_command"

Both settings should come back as you wrote them. If archive_mode still says off, your setting is being overridden somewhere later in the include order, or in postgresql.auto.conf. This query tells you where each value came from:

select name, setting, sourcefile, pending_restart
from pg_settings
where name in ('wal_level','archive_mode','archive_command','archive_timeout');

Step 4: prove that a segment arrives

Do not trust the config. Force a switch and look.

sudo -u postgres psql -c "select pg_switch_wal()"
sleep 5
ls -l /srv/pg-archive/wal
sudo -u postgres psql -x -c "select archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time from pg_stat_archiver"

You want a new file in wal/, archived_count above zero, and failed_count at zero. A caution: pg_switch_wal() does nothing when no WAL has been written since the last switch, so on a brand-new idle server run any write first (create table t(); drop table t; works).

If failed_count climbs, read last_failed_wal and the PostgreSQL log (/var/log/postgresql/postgresql-15-main.log). The script prints its complaint to stderr and PostgreSQL logs it next to a line saying the archive command failed. Typical causes are a permission on the archive directory, a missing mount, or a script without the execute bit.

Step 5: take a base backup

sudo -u postgres pg_basebackup \
  -D /srv/pg-archive/base/base-$(date -u +%Y%m%dT%H%M%SZ) \
  -Fp -X stream -c fast --manifest-checksums=SHA256 -P

What the flags do:

• -D is the target directory. It must be empty or not exist.
• -Fp writes a plain copy of the data directory, ready to start from. It is also the format pg_verifybackup can check in PostgreSQL 15 to 17.
• -X stream opens a second connection and streams the WAL generated during the backup, so the backup is self-consistent. It is the default since PostgreSQL 10, and we spell it out.
• -c fast asks for an immediate checkpoint so the backup starts now instead of after up to checkpoint_timeout.
• --manifest-checksums=SHA256 writes a manifest with a SHA-256 for every file, which pg_verifybackup uses.

The user running it needs the REPLICATION attribute or to be a superuser, and pg_hba.conf must allow a replication connection. Debian's default includes a local replication all peer line, so running as postgres over the socket works with no changes. Over TCP you need your own replication line.

Verify the copy right away:

sudo -u postgres pg_verifybackup /srv/pg-archive/base/base-20261109T021000Z

Use your real directory name. It exits 0 and prints backup successfully verified when every file matches the manifest.

Two practical notes. First, a base backup is the size of the data directory (index bloat included), so keep an eye on the staging and archive disks. Second, the Debian config files in /etc/postgresql are not in the backup. Back them up with your normal file backup; they are small.

What lands where

After a few minutes you have something like this:

/srv/pg-archive/
  wal/
    000000010000000000000005
    000000010000000000000006
    000000010000000000000006.00000028.backup
    000000010000000000000007
  base/
    base-20261109T021000Z/
      PG_VERSION  base/  global/  pg_wal/  backup_label  backup_manifest  ...

The odd file is the backup history file. When pg_basebackup finishes, PostgreSQL archives a small .backup file named after the first segment the backup needs, with the start and stop locations inside. The segment named in it is the oldest one you must keep for that backup. Anything older is only needed by older backups.

Inside the base backup, backup_label records the start location and the checkpoint. It is what tells PostgreSQL at startup that this is a backup being restored and where replay begins.

Timeline history files (00000002.history) will appear in wal/ after your first point-in-time recovery. They are tiny, and you should never prune them.

Step 6: check that the chain has no gaps

A recovery needs every segment from the base backup's first one to the target. Missing one in the middle is the worst kind of silent failure, because the archive looks healthy until the day you need it. A rough gap check for a single timeline:

cd /srv/pg-archive/wal
start=000000010000000000000005       # the segment named in the base backup's .backup file
prev=-1
for f in $(ls | grep -E '^[0-9A-F]{24}$' | sort | awk -v s="$start" '$0 >= s'); do
  n=$((16#${f:16:8}))                # the last 8 hex digits: segment number within the log
  if [ "$prev" -ge 0 ] && [ "$n" -ne $((prev + 1)) ]; then echo "GAP before $f"; fi
  prev=$n
done

It assumes a single log number and timeline (segment numbers wrap from ...FF to the next log number on 16 MiB segments, so very long-lived archives need to handle that). The kit's verify-archive.sh follows timeline history and handles the wrap. If you write your own, schedule it daily, and test it by deleting a copy of one segment in a scratch directory.

The same applies to freshness: last_archived_time in pg_stat_archiver should never be older than your archive_timeout plus a margin, on a server that has activity. Put that into a check that alerts; see monitoring backups so silence is not success.

Step 7: the first restore test

An archive you have never restored from is a hope. Restore into a scratch directory on this machine, as the walkthrough in restoring Postgres to a timestamp shows, before you declare the job finished. Then repeat it on a different machine from time to time; a destroy-your-server recovery drill describes how.

What happens when the archive fails

This is the part that surprises people. If archive_command fails, PostgreSQL does not drop the segment. It keeps the file in pg_wal/, marks it with a .ready file in pg_wal/archive_status/, and retries. It never recycles a segment that was not archived. That is the safe behavior, and it means a broken archive slowly fills the data disk.

Rule of thumb: a failing archive never loses WAL by itself, it fills the data disk instead. Alert before that happens.

We reproduced it by making the archive directory read-only: failed_count rose, .ready files piled up, and the oldest unarchived segment was still in pg_wal/ after a CHECKPOINT. When the directory became writable, the backlog shipped and the count settled. Nothing was lost, but only because the problem was fixed before the disk filled.

So an unattended archive needs two guards. Alert on failed_count rising, and alert on pg_wal/ growing. The first protects the archive, the second protects the database. Journald, logrotate and the disk-full problem covers the general disk-watching side.

Choosing how to ship: the options

For one server and one database, a short script you can read is a reasonable choice. Once you have several servers, large databases, or want incremental backups and encryption, move to pgBackRest or Barman. The concepts in this post carry over directly.

Mistakes we see

Putting the archive on the data disk. It works until the day it matters. Another disk or another host is the whole point.

A cp that overwrites. Timeline resets and restores can reuse names. Make the command refuse a different file under an existing name.

Setting archive_command and never restarting. archive_mode needs a restart. show archive_mode is the quick check; pending_restart in pg_settings is the explanation.

Exiting 0 on failure. A script that ends with || true, or a pipeline whose failure is swallowed, tells PostgreSQL the segment is safe. It then recycles it. Use set -euo pipefail and finish with the command whose exit status you mean.

Archiving without a base backup. Segments alone replay nothing. Take the first base backup the same day you enable archiving, and schedule the next ones.

Never testing a restore. Everything above can look perfect and still fail on a missing segment or a wrong permission. Restore into a scratch directory this week.

FAQ

Do I need to restart PostgreSQL to enable WAL archiving?

Yes the first time. archive_mode and wal_level only change at server start. archive_command and archive_timeout can be changed with a reload afterwards, so tuning the command later does not need downtime.

Where does the %p in archive_command point?

It is the path of the segment file relative to the data directory, such as pg_wal/000000010000000000000007, and the command runs with the data directory as its working directory. %f is just the file name. Always use both as given; do not hard-code paths into pg_wal.

What happens if archive_command fails?

PostgreSQL keeps the segment, marks it ready for archiving and retries, logging each failure and counting it in pg_stat_archiver.failed_count. WAL accumulates in pg_wal/ until archiving works again, and the data disk can fill if you do not notice. Monitor the failure count and the size of pg_wal.

Can I archive WAL without a base backup?

You can, but it is useless for recovery. WAL records describe changes relative to a starting state, and the base backup is that state. Take one right after enabling archiving, then regularly.

How often should I take a base backup?

Often enough that replaying WAL since the last one fits your recovery time budget. Nightly is common for small databases. More frequent backups mean less WAL to replay. They also let you delete older WAL sooner.

If you would rather start from tested scripts than assemble them, the Postgres PITR Kit packages this setup, with gzip, an SFTP option, retention and a monitoring script, and checks that the first segment reached the archive before it says it worked.

postgreswalbackuppitr