I Broke My Database on a Tuesday (and Learned pgBackRest PITR the Calm Way)

A quiet panic, a cup of coffee, and why PITR matters

I still remember the Tuesday morning that taught me more about backups than a dozen whitepapers ever could. One of my clients had a tidy little VPS, the kind you feel good about because it just hums along quietly doing its job. The app was happy. The users were happy. And then a migration script ran on the wrong database. You know that cold, sinking feeling behind your ribs? That one. We had a full backup from the night before, but that would roll us back nearly a day—too much lost work. What we needed was to wind the clock back just thirty minutes. Not a day. Not an hour. Thirty minutes.

That’s when point‑in‑time recovery (PITR) went from “something we should set up” to “I will never ship a database without this again.” If you’ve ever wished you could rewind a few minutes of bad changes, you’re in the right place. In this guide we’ll walk through a calm, step‑by‑step setup of PostgreSQL backups and PITR on a VPS using pgBackRest, plus the mental model to make it stick. We’ll set up WAL archiving, run a real backup, practice a restore, and leave you with a plan you can trust when Tuesday goes sideways.

WAL, PITR, and a simple mental model

Here’s the thing: backups that only capture a snapshot are like a single photograph of a busy street. They’re helpful, but they don’t show the motion. PostgreSQL’s write‑ahead log (WAL) is the movie reel of your database—the frame‑by‑frame list of every change. PITR works by restoring a base backup (the snapshot) and then replaying WAL until you reach the exact moment you want, whether that’s a timestamp, a transaction mark, or a recovery target name.

In practice, there are three moving parts you care about. First, a base backup captured at a point in time. Second, a continuous stream of WAL files safely archived somewhere other than your active data directory. Third, a restore tool that can reassemble those pieces with confidence. That’s where pgBackRest shines. It handles base backups, WAL archiving, retention rules, and restores with an elegance that saves you from a thousand tiny gotchas.

Think of it like this: the base backup is your save point. WAL files are your time machine. pgBackRest is the friendly operator who knows which levers to pull and in what order so you land exactly where you intend. And on a VPS, keeping those pieces organized and off the same disk as production is the difference between “We’re fine” and “We lost everything when the VM died.”

Prep your VPS and choose where backups live

Before we type a single command, let’s set the stage. The biggest early decision is where your backups and WAL archives will live. I’ve tried three patterns that each work well depending on your constraints: a second block storage volume mounted on the same VPS, a separate backup server reachable over SSH, or object storage like S3. If you’re just getting started, a second volume is a great starting point for fast restores and simple permissions. If you already have an offsite server or object storage account, skip straight to that for true off‑VM resilience.

In my experience, the happy path looks like this: give PostgreSQL its own user (it already has one), make sure you have a clean directory for pgBackRest’s repository, and confirm that your VPS has enough bandwidth and CPU to compress backups without choking your app. And remember, the whole point is to keep your data safe when your primary disk or VM is unhappy. If the backups live on the same disk as your database, you have a single point of failure. It’s better than nothing for practice, but don’t stop there.

Quick checklist before we begin

Make sure you can log in as a user with sudo privileges. Confirm PostgreSQL is installed and running (version 12+ is perfectly fine). Ensure your server clock is sane and synced; recovery targets by timestamp are a lot less fun when your clock drifts. Finally, decide on a directory for your backup repository—for example, /var/lib/pgbackrest on a secondary volume.

Install pgBackRest and create your first stanza

I like starting locally because it lets you feel the whole flow before you introduce remote targets. You can pivot to SSH or S3 in a snap once the basics click.

Install on Debian/Ubuntu

If you’re on Debian or Ubuntu, installing pgBackRest is straightforward. Use the repository provided by the maintainers or your distro’s packages. The official docs stay current and are worth bookmarking. When in doubt, the pgBackRest documentation is gold.

sudo apt update
sudo apt install pgbackrest

On most systems, pgBackRest installs into /usr/bin/pgbackrest with configuration in /etc/pgbackrest. We’ll create a configuration that defines two things: your PostgreSQL “stanza” (think of it as a named database cluster) and the repository where backups and WAL will land.

Repository and permissions

Create the repository directory and hand ownership to the postgres user:

sudo mkdir -p /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest

Write the core configuration

We’ll use a simple local repository configuration to start. You can safely extend this later for SSH or S3 without rethinking everything.

sudo -u postgres mkdir -p /etc/pgbackrest/conf.d
sudo -u postgres tee /etc/pgbackrest/pgbackrest.conf >/dev/null <<'CONF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
start-fast=y
compress-type=zstd
log-level-console=info
log-level-file=info

[main]
pg1-path=/var/lib/postgresql/15/main
CONF

Adjust pg1-path to your actual data directory. On Debian-based systems it’s usually something like /var/lib/postgresql/14/main or /var/lib/postgresql/15/main. On other distros you might see /var/lib/pgsql/15/data.

Create the stanza

A stanza links pgBackRest to your Postgres data directory and prepares the repository metadata. It’s a one‑time setup per cluster.

sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create

If this succeeds, you’ll see a reassuring “stanza create complete.” If it fails with permission complaints, double‑check directory ownerships and that you’re running as the postgres user.

Turn on WAL archiving in PostgreSQL (the lever most people miss)

If backups are your snapshot, WAL archiving is the river of changes that lets you rewind time. PostgreSQL needs to be told to save copies of finished WAL segments somewhere safe. pgBackRest acts as the handler for that: it grabs each completed segment and tucks it into the repository.

Edit postgresql.conf

Open your PostgreSQL configuration and set a few critical parameters. If your distro uses include files, you can drop these in a dedicated file like conf.d/10-archiving.conf.

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica
archive_timeout = 60s   # optional; forces periodic WAL segments even if quiet

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

Within a minute or two of regular activity, pgBackRest should start to receive WAL segments into your repository. You can watch logs or simply run:

sudo -u postgres pgbackrest info

If the output complains about missing stanza or archive errors, re‑check the command paths and permissions. The number one culprit I see is a typo in archive_command or the pgbackrest binary not being in the PATH for the Postgres service. Using the absolute path in the command—/usr/bin/pgbackrest—can remove any doubt:

archive_command = '/usr/bin/pgbackrest --stanza=main archive-push %p'

Why WAL archiving matters now, not later

I once helped a team that ran nightly full backups but never enabled archiving. They thought, reasonably, that a nightly snapshot was fine. Then a destructive command fired at noon. They could only restore to midnight. Six gut‑wrenching hours gone. PITR is the difference between “we can get it back” and “we can get the right version back.” Turning on archive_mode today is the single highest‑leverage step you can take.

Run your first backup and validate it like you mean it

With archiving on, a base backup will anchor your PITR timeline. pgBackRest handles all the hard parts—quiescing, checksums, checks, WAL coordination—without you juggling pg_start_backup and friends.

Full backup

sudo -u postgres pgbackrest --stanza=main --type=full --log-level-console=info backup

Depending on database size, CPU, and compression, the first backup can take a while. You’ll see steps for manifest building, file copy, and WAL finalize. When it finishes, you can check status:

sudo -u postgres pgbackrest info

You should see one full backup listed, plus a growing set of archived WAL segments. This is the moment to breathe. You’ve got a snapshot and a stream.

Incremental and differential backups

You don’t need to run full backups every time. Let the first full establish a baseline, then schedule incrementals throughout the day and a differential or full once a day or week, depending on churn and window size. For example:

# differential backup
sudo -u postgres pgbackrest --stanza=main --type=diff backup

# incremental backup
sudo -u postgres pgbackrest --stanza=main --type=incr backup

That retention configuration we set earlier—two fulls, seven diffs—will prune old backups while keeping a safe path through your timeline. You can tune those numbers once you see how big your backups get and how fast your data changes.

Schedule it

I know, cron isn’t glamorous, but it gets the job done. A simple pattern is full on Sunday night, differentials on weekdays at night, incrementals every hour during business hours. On a small VPS this spreads the load and keeps your recovery points tight. Here’s a starter crontab for the postgres user:

crontab -u postgres -e

# Full on Sunday 02:00
0 2 * * 0 pgbackrest --stanza=main --type=full backup >> /var/log/pgbackrest-cron.log 2>&1

# Diff Mon-Sat 02:00
0 2 * * 1-6 pgbackrest --stanza=main --type=diff backup >> /var/log/pgbackrest-cron.log 2>&1

# Incremental at :15 each hour 08:00-20:00
15 8-20 * * * pgbackrest --stanza=main --type=incr backup >> /var/log/pgbackrest-cron.log 2>&1

Adjust frequency to your comfort with load and your target RPO. If you’re curious about broader recovery planning and realistic test loops, I walked through a calm approach in How I write a no‑drama DR plan with runbooks you’ll actually use.

Off‑VM options: SSH and S3 without the drama

Once the local flow is working, it’s time to push backups off the VPS. I like two clean paths: an SSH‑reachable backup host or an object store.

SSH repository (repo on a backup server)

On the backup server, install pgBackRest and create a repository path owned by a dedicated system user (often also named postgres or pgbackrest). Set up SSH keys from your database server to the backup server, locked down to the pgBackRest command if you want to be extra careful.

On the database server, your config gains a repo1-host line:

[global]
repo1-host=backup.example.com
repo1-host-user=pgbackrest
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zstd
start-fast=y

Now when you run backups or archive WAL, pgBackRest will store them remotely. It’s the same commands, just a safer destination.

S3 repository (object storage)

If you prefer object storage, pgBackRest supports S3‑compatible endpoints. Add credentials and bucket information to your config:

[global]
repo1-type=s3
repo1-s3-bucket=my-pgbackups
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=eu-central-1
repo1-s3-key=AKIA...
repo1-s3-key-secret=...redacted...
repo1-path=/
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zstd
start-fast=y

With S3, two best practices have saved me headaches. First, enable server‑side or client‑side encryption. pgBackRest can encrypt before upload with AES‑256:

repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<your-strong-passphrase>

Second, keep lifecycle policies in the bucket aligned with pgBackRest retention. Let pgBackRest prune; let the bucket retain what pgBackRest expects. If the bucket deletes objects behind pgBackRest’s back, you can end up with gaps.

Practice the thing that saves you: a real PITR restore

I make a big deal about practice restores because that’s where the confidence comes from. It doesn’t have to be fancy. Spin up a throwaway VM, or temporarily restore to a different directory and port on the same VPS. What matters is going through the motions until it feels ordinary.

Make a small, deliberate mistake to recover from

Create a test table, insert a few rows, and then drop it. That gives you something to find in the past. Note the current timestamp so you have a clean recovery target.

-- inside psql
CREATE TABLE pitr_demo(id serial primary key, note text);
INSERT INTO pitr_demo(note) VALUES ('before');
SELECT now();  -- copy this timestamp
-- simulate a mistake
DROP TABLE pitr_demo;

After the drop, wait a minute so the WAL segment containing the change can be archived. You can force WAL rotation by running a quick checkpoint if you’re impatient.

Stop Postgres and prepare the restore target

If you’re restoring in place (be careful on production), stop PostgreSQL and move the current data directory aside. In a practice environment, I often restore to a new directory and bind it to a temporary port to keep things safe.

sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.broken-$(date +%s)
sudo -u postgres mkdir -p /var/lib/postgresql/15/main

Restore to a timestamp

Now the fun part. Tell pgBackRest to restore to the moment just before the mistake. Use the timestamp you saved, adjusting a few seconds earlier to be safe.

sudo -u postgres pgbackrest 
  --stanza=main 
  --type=time 
  --target='2025-11-11 10:54:30+00' 
  --target-action=promote 
  --log-level-console=info 
  restore

pgBackRest will drop the base backup into place, configure restore_command under the hood, and replay WAL until it reaches your target. When it’s done, start PostgreSQL and check your data:

sudo systemctl start postgresql
psql -c 'SELECT * FROM pitr_demo;'

If you see your rows again, that’s the good stuff. If you don’t, make sure your target time is right and that your server’s timezone didn’t trick you. Timezones are sneaky. I like using explicit offsets in timestamps to keep my head straight.

Restore to a named stop point

Another pattern I love is using named recovery targets. You can create a named anchor by setting a recovery target name before your risky operation and then restoring to that exact name later. In Postgres 12+, you can set this via pgbackrest restore options or by adding a recovery target to the command. For example:

sudo -u postgres pgbackrest 
  --stanza=main 
  --type=name 
  --target='before-batch-42' 
  --target-action=promote 
  restore

Whether you prefer timestamps or names, the flow is the same: base backup, then WAL, then promotion to normal operation at the precise point you want.

What pgBackRest does for you during restore

One of the reasons I recommend pgBackRest is that it writes the messy bits for you—creating recovery.signal, setting restore_command to fetch WAL via archive-get, and cleaning up when it’s safe to promote. You focus on the “when” and “where,” pgBackRest handles the “how.” If you ever want to read the under‑the‑hood details, the PostgreSQL docs on continuous archiving and PITR are a friendly deep dive.

Retention, pruning, and watching for trouble

Backups are like houseplants: a little bit of regular care goes a long way. With pgBackRest you’re mostly telling it what to keep and then checking in occasionally to be sure the river is flowing.

Set retention you can afford

We started with two full backups and seven differentials. That’s sane for many small teams, but every dataset is different. If your database changes slowly, longer retention is cheap. If it churns fast or stores large binaries, consider more frequent differentials and tighter WAL retention. The goal is that you can restore to a safe point from the last few days without needing to chain a mountain of WAL segments.

Prune and info

pgBackRest prunes on backup runs by default, but it’s handy to know you can ask for a cleanup directly:

sudo -u postgres pgbackrest --stanza=main expire
sudo -u postgres pgbackrest info

The first removes backups beyond your retention policy; the second shows what remains. If you ever feel uneasy, take a moment to scan the info output. Seeing a fresh full, recent diffs, and a healthy archive flow is like checking the locks before bed.

Monitoring and alerts

I keep an eye on a few signals: successful backup exit codes in cron logs, the size and age of WAL archives, and free space in the repository. A classic red flag is WAL piling up in the database server’s pg_wal directory because the archive_command is failing. When archiving breaks, Postgres will keep WAL locally for a while, and then—if the disk fills—everything stops. It’s noisy in logs when this happens, so even a simple tail in your monitoring can catch it early.

Vacuum and bloat matter too

Sometimes folks blame backups for slow databases when the real culprit is table bloat and lazy autovacuum settings. If your backups are dragging or your WAL volume is spiking, peek at vacuuming. I shared a calm, real‑world approach in The guide to tuning PostgreSQL autovacuum and shrinking bloat without drama. Healthy vacuum keeps your WAL stream reasonable and your backups lean.

Common gotchas and the friendly fixes

Over the years, I’ve bumped into a handful of predictable snags. Knowing them makes you look like a wizard when someone else bumps into the same wall.

“archive_command failed” on repeat

This usually boils down to one of three things: the pgbackrest binary isn’t in the PATH of the Postgres service, the stanza name is wrong, or permissions on the repository are too strict or too loose. Use the full path in archive_command, verify the stanza name exactly, and ensure postgres owns the repo directory with 750 permissions.

Backups are huge even after compression

Two ideas help here. First, switch to zstd if you haven’t already; it’s a great balance of speed and size on a VPS. Second, check for large unchanging blobs in the database and consider moving them to object storage with references in Postgres. That reduces churn and the size of every incremental backup.

Restore works, but the target time is off

Timezones strike again. Use explicit offsets in your target time and confirm the server’s timezone. When in doubt, a named recovery target avoids ambiguity. Also, back up your postgresql.conf and related includes with your IaC or config management so you can reproduce the exact environment on restore.

Testing restores without downtime

One trick I love is restoring to a temporary directory and starting Postgres on a different port just for verification. For example, restore to /var/lib/postgresql/15/restore, set port = 55432 in a temporary config file, and start it with a custom service. You can then point a one‑off psql at it, run a few checks, and shut it down quietly. It’s like a dress rehearsal that doesn’t disturb the main stage.

Security and encryption

If your backups ever leave the machine—and they should—encrypt them. pgBackRest’s client‑side encryption with repo1-cipher-type=aes-256-cbc and a strong passphrase is simple and effective. Store the passphrase in a password manager or a vault service and make sure your runbook includes how to retrieve it during a restore. I’ve seen teams nail everything technically and then stumble because only one person knew the passphrase. Share safely.

Document the path, not just the tools

The best backup setups are boring on game day because someone wrote down exactly what to run and where to look if it fails. If you want a friendly template and a way to pressure‑test your steps, I laid out a practical approach in this guide to writing a no‑drama DR plan. It pairs beautifully with pgBackRest.

Wrap‑up: make Tuesday boring again

If you’ve made it this far, you’ve got the bones of a resilient backup and recovery flow on your VPS. You understand the mental model—base backup plus WAL equals a time machine. You’ve installed pgBackRest, turned on WAL archiving, run a full backup, and practiced a restore to a precise moment. That practice run is the difference between hoping and knowing. It’s the warm blanket you want when someone whispers “I think I dropped the wrong table.”

My final advice is simple. Keep your repository off the main disk if you can. Schedule backups like you schedule brushing your teeth—habit beats heroics. Encrypt if it ever leaves the box. Watch the logs for archive hiccups. And once a month, do a quick restore to a throwaway path just to keep the muscle memory fresh. That small cadence will save you hours when it matters.

Most of all, give yourself permission to make this boring. The best backups are the ones you forget about until you need them, and when you do, they just work. Hope this was helpful! If there’s a step that feels fuzzy or you want a sanity check on your setup, send me a note. See you in the next post, and may your WAL stream flow steady and your restores be gloriously uneventful.