Postgres Backups

The default Postgres backup everyone starts with is a pg_dump on a
cron job at 4am. It is better than nothing, and it has one fatal
property nobody thinks about until the wrong afternoon: your maximum
data loss is the time since the last dump. If prod dies at 3pm, you
restore last night's 4am dump and the eleven hours in between are simply
gone. For a payment system, an order book, anything transactional, that
is not a backup strategy, it is a promise to lose a workday of data.

The fix is already running inside your database. Postgres continuously
writes every single change to a write-ahead log, the WAL, before it
touches the actual data files. It exists for crash safety, but it is
also a complete, ordered record of everything that happened. Archive
that log continuously, on top of one periodic base backup, and you can
replay it to reconstruct the database at any moment you choose. This is
Point-in-Time Recovery, PITR, and it changes what "restore" means.

First, the Two Numbers: RPO and RTO

Every backup decision is downstream of two numbers, and if you have not
written them down, you have not designed a backup strategy, you have a
habit. Define them before choosing any tool.

RPO, Recovery Point Objective: how much data you can afford to lose,
measured in time. "At most 15 minutes." "Zero." It is the distance
between the last recoverable state and the moment of failure. A nightly
dump gives you an RPO of up to 24 hours. Continuous WAL archiving gives
you an RPO of seconds.

RTO, Recovery Time Objective: how fast you must be back, measured in
time. "Within one hour." It is how long the restore itself takes. This
is where large pg_dump setups quietly fail: restoring a big logical
dump can take hours, blowing an RTO no matter how good the RPO looked on
paper.

   RPO  <---- how much data you lose ----|
                                         |
   ...last good state........ FAILURE ...|... service down ...> RECOVERED
                                         |                      |
                                         |-- RTO: how long -----|
                                             until you are back

The two are independent, and both matter. Confusing them, or leaving
them undefined, is the most expensive mistake a DBA makes. Write them
down first; every technical choice below is just a way of hitting the
numbers you committed to.

What PITR Actually Gives You

Two ingredients: a base backup (a full physical copy of the cluster)
and a continuous stream of archived WAL segments since that backup. To
recover, you restore the base and then replay the WAL forward to a
target time you specify. Because the WAL holds every change in order,
that target can be any second, not just when a backup happened.

   Sunday 02:00        continuous WAL stream ------------------->
   [ BASE BACKUP ]  w  w  w  w  w  w  w  w  w  w  w  w  w  w  w
        |           every change archived, in order, all week
        |
        |  RESTORE = replay base forward to any second you pick:
        |
        v
   base ==replay WAL==> [ Tue 14:29:29 ]   <- stop here, one second
                                              before the bad command

The difference in RPO is stark. Nightly dump: up to 24 hours, you lose
everything since 4am. Continuous WAL archiving: seconds, you lose almost
nothing. Set archive_timeout = 60 and a segment is archived at least
once a minute regardless of traffic, so your worst case is measured in a
minute, not a day.

The Move That Matters: Recover to Just Before the Mistake

Here is the property that connects to every "someone deleted prod" story.
When a bad DROP TABLE or DELETE runs at 14:29:30, PITR lets you
recover the database to 14:29:29, the state one second before the
damage. Postgres replay is greedy by default and will roll all the way
forward to the latest change it can find, which includes the deletion,
so you tell it to stop at your target time instead. You get the database
back exactly as it was the instant before the mistake, with everything
up to that second intact. A nightly dump cannot do this at all; the best
it offers is "last night," losing the whole day's work along with the
mistake.

Do Not Hand-Roll It: Use pgBackRest

You can configure archive_command and pg_basebackup by hand, and it
is worth doing once to understand the mechanism. For anything real, use
pgBackRest. It manages the base backups, the WAL archive, full and
incremental backups, compression, encryption, S3-compatible storage, and
the restore-to-a-timestamp workflow, and it verifies the archive rather
than trusting it. A typical setup is a weekly full backup plus daily
incrementals, with WAL streaming continuously in between. Physical
backups like this also restore far faster than a large pg_dump, which
is how you protect the RTO as well as the RPO.

The Rules That Keep It Honest

Three things, or the whole thing is theatre.

Verify the archive is actually working. WAL archiving fails silently:
the database runs fine while the archive quietly stops, and you find out
when you try to restore. Check pg_stat_archiver regularly and alert if
the last archived segment is stale. A gap in the WAL breaks recovery at
exactly that gap.

Test the restore, on a schedule. A backup you have never restored is a
hope, not a backup. Actually perform a recovery to a timestamp on a
throwaway host, monthly, and time it. That measured number is your real
RTO, and it is the only one you can trust when it counts. If restoring
300 GB takes 55 minutes and your RTO is an hour, you have almost no
margin, and you want to know that now, not during the outage.

Push it off the box. WAL and base backups sitting on the same server as
the database die with the database. Archive to separate, ideally
immutable, off-host storage, so the same fire, or the same wrong command,
cannot take the database and its backups together.

The Takeaway

Decide your RPO and RTO first, in writing, because they choose the tool.
A nightly dump caps your data loss at a full day and can blow your
recovery time on a large restore. Postgres already records every change
in the WAL; archive it continuously and your RPO drops to seconds and you
can rewind to the instant before someone broke something. Run pgBackRest,
archive the WAL off-host, verify pg_stat_archiver, and test a
timestamped restore every month so your RTO is a measured fact, not a
guess.