Skip to content

How it works

A backup and a drill are two halves of one loop. The backup runs where the database is, with only the public key. The drill runs somewhere else, with the private key, and proves the backup comes back.

How a backup and a drill workBackup, on the server: Postgres, then pg_dump after counting rows, then age encrypts with the public key, then the encrypted dump and manifest are uploaded to the bucket with a sha256. Drill, on your laptop: download and check the sha256, decrypt with the private key, pg_restore into a throwaway Postgres, compare every table's row count with the manifest, and record PASS or FAIL.BACKUP · on the server, nightlyDRILL · on your laptop or in CI, monthlyPostgresyour live databaseDATABASE_URLpg_dump -Fcafter counting every table's rowscounts go intothe manifestage: encryptstreamed, nothing plain on diskpublic key only(AGE_RECIPIENTS)R2 or S3 bucket<prefix>/<stamp>.dump.age+ .manifest.agesha256 in metadataDownload, check sha256a mismatch stopsthe drillage: decryptprivate key(AGE_IDENTITY)pg_restoreinto a throwaway Postgresrandom password,removed afterwardsCompare row countsevery table, against the manifestPASS or FAILrecorded in .drills/
  1. Count every table’s rows. One query over pg_stat_user_tables builds an exact count(*) for each table, so the manifest has real numbers, not estimates.
  2. Dump. pg_dump -Fc --no-owner --no-acl writes Postgres’s custom format: compressed, and readable by pg_restore table by table.
  3. Encrypt in the same pipe. pg_dump’s output goes straight into age, encrypted to every public key in AGE_RECIPIENTS. The only file written is the encrypted one, in a private temporary folder (mode 0700). The plain dump never touches disk. Both exit codes are checked, so a half-finished dump never looks like a good one.
  4. Upload. The file goes to <prefix>/<stamp>.dump.age, with three values in the object’s metadata:
    • sha256, the checksum of the encrypted file
    • the server’s Postgres version
    • the tool’s version
  5. Write the manifest. <prefix>/<stamp>.manifest.age holds the row counts, the checksum, the size and the versions as JSON, also encrypted to AGE_RECIPIENTS. Table names can be sensitive too.
  6. Tidy up. With TIERS, server-side copies go to the other tiers. Then come pruning (if on) and the heartbeat ping.

The stamp is the UTC time the backup started, as 2026-11-18T020000Z, so names sort in time order.

  1. Pick a backup: the newest, or the one you name.
  2. Download it and check the sha256. A mismatch means the file is corrupt or altered, and the drill stops there.
  3. Decrypt it with the private key, and check that what comes out is a pg_dump archive (it starts with PGDMP).
  4. Restore it into a scratch database with pg_restore --clean --if-exists --exit-on-error.
  5. Count every table’s rows again, and compare them with the manifest. These all count as problems:
    • a missing table
    • an extra table
    • a table off by more than DRILL_TOLERANCE
  6. Report PASS or FAIL, record it in <prefix>/.drills/, and ping the heartbeat.

verify does steps 1–3 and lists the tables in the dump, with no database at all. It’s a quick check that a backup opens. Only a drill proves it restores.

Server (backups) Laptop or CI (drills, restores) Bucket
Public key (age1…) yes yes no
Private key (AGE-SECRET-KEY-1…) never yes no
Database password yes only for restores no
Plain dump never only with decrypt never

Connection details reach pg_dump, pg_restore and psql as PGHOST, PGPASSWORD and so on, in their environment. They’re never passed as arguments, so passwords don’t show up in ps. Inherited PG* variables are cleared first, so a stray PGHOST can’t redirect a backup. Logged URLs show the password as ***.

pgb follows the same rule. It passes rewritten URLs and the drill’s random password to Docker by name (-e VAR), never as -e VAR=value.

pgbackup-r2 is an open-source tool from Iotabyt. MIT licensed.