DEV Community

Cover image for GitLab Had 5 Backup Methods and None Worked Reliably. Restore-Test Yours Nightly
Gabriel Anhaia
Gabriel Anhaia

Posted on Edited on AI-assisted

GitLab Had 5 Backup Methods and None Worked Reliably. Restore-Test Yours Nightly

#postgres #devops #backup #sideprojects

On January 31, 2017, a GitLab.com engineer fighting a replication problem removed a PostgreSQL data directory on the wrong server. The command was meant for the secondary. It ran on the primary. By the time it was stopped, according to GitLab's own incident write-up, "of around 300 GB only about 4.5 GB is left."

Then the team went to the backups. The same write-up lists what they found. The pg_dump job was running a 9.2 client against a 9.6 server, "failing silently," and "no SQL dumps were made as a result." The regular backups were "producing files only a few bytes in size." The backups to S3: "the bucket is empty." Azure disk snapshots were on for the NFS server, not the database servers. The cron job did send failure emails, but the postmortem explains that DMARC was not enabled for them, so the receiving side rejected every one. GitLab summed it up in one line: "out of five backup/replication techniques deployed none are working reliably or set up in the first place."

GitLab.com was down for about 18 hours. Roughly six hours of database changes were lost. The postmortem puts that at "at least 5000 projects, 5000 comments, and roughly 700 users." The data came back from a staging copy an engineer had taken six hours earlier for load testing. The postmortem's root cause: "nobody was responsible for testing this procedure." Item 10 on its fix list reads "Automated testing of recovering PostgreSQL database backups."

Your side project has the same problem with fewer people. Here is a script that owns the restore test for you.

A backup you never restored is a guess

Every failure in that list would have shown up the first time anyone tried a restore. A dump of a few bytes cannot restore anything. An empty bucket has nothing to download. So the job below does not stop at "the dump command exited 0." Every night it:

  1. Dumps the database with pg_dump -Fc from the official postgres:18 Docker image, so the client is at least as new as the server (set PG_IMAGE to match if you run something newer).
  2. Uploads the dump to S3-compatible storage (Cloudflare R2, Backblaze B2, anything the AWS CLI can talk to).
  3. Asks the bucket for its newest object, checks it is tonight's, and downloads it. The test restores the copy in the bucket, not the file still sitting on disk.
  4. Restores it into a throwaway postgres:18 container and compares the table list and row counts against production.
  5. Pings a dead-man's-switch monitor, but only if steps 1 to 4 all passed.

Step 1 closes GitLab's first hole. The pg_dump docs say it "cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try." The live write-up called GitLab's failure silent, and the postmortem clarifies that pg_dump did error out: the error went into the emails that were being rejected. Running the client from an image at least as new as your server removes the mismatch. If you upgrade the server past 18 and forget to bump PG_IMAGE, the run fails and step 5 never pings, so you find out the next morning.

The script

First, a small SQL file that prints one line per table: schema.table rowcount. It uses query_to_xml to run an exact count(*) per table in a single query, so you never have to list your tables by hand.

-- counts.sql: one line per table, "schema.table rows"
SELECT format('%I.%I', table_schema, table_name),
       (xpath('/row/n/text()', query_to_xml(
          format('SELECT count(*) AS n FROM %I.%I',
                 table_schema, table_name),
          false, true, '')))[1]::text::bigint
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1;
Enter fullscreen mode Exit fullscreen mode

Then the job itself, about 50 lines of bash:

#!/usr/bin/env bash
# pg-backup-verify: dump, upload, restore, count, ping.
set -euo pipefail
: "${DATABASE_URL:?}" "${BUCKET:?}" "${S3_ENDPOINT:?}" "${HC_URL:?}"
IMG="${PG_IMAGE:-postgres:18}"
SQL="${COUNTS_SQL:-/etc/pg-backup-verify/counts.sql}"
MIN_RATIO="${MIN_RATIO:-0.95}"
KEY="pg/$(date -u +%Y%m%dT%H%M%SZ).dump"
WORK="$(mktemp -d)"
NAME="restore-test-$$"
trap 'docker rm -f "$NAME" >/dev/null 2>&1; rm -rf "$WORK"' EXIT
s3() { aws s3 "$@" --endpoint-url "$S3_ENDPOINT"; }
pg() { docker run --rm --network host -v "$WORK:/w" "$IMG" "$@"; }

# 1. Dump with a client at least as new as the server.
cp "$SQL" "$WORK/counts.sql"
pg pg_dump -Fc -f /w/db.dump "$DATABASE_URL"
pg psql -X -At -F ' ' -f /w/counts.sql "$DATABASE_URL" \
  > "$WORK/live.txt"

# 2. Upload, then fetch whatever the bucket says is newest.
s3 cp "$WORK/db.dump" "s3://$BUCKET/$KEY"
NEWEST="$(s3 ls "s3://$BUCKET/pg/" | sort | tail -n 1 \
  | awk '{print "pg/" $4}')"
[ "$NEWEST" = "$KEY" ] || { echo "newest: $NEWEST" >&2; exit 1; }
s3 cp "s3://$BUCKET/$NEWEST" "$WORK/back.dump"

# 3. Restore it into a throwaway container.
docker run -d --name "$NAME" --network none \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -v "$WORK:/w" "$IMG" >/dev/null
for _ in $(seq 60); do
  docker exec "$NAME" pg_isready -h 127.0.0.1 -q && break
  sleep 1
done
docker exec "$NAME" pg_restore -U postgres -d postgres \
  --no-owner --no-acl --exit-on-error /w/back.dump

# 4. Every live table exists, with at least MIN_RATIO of its rows.
docker exec "$NAME" psql -U postgres -X -At -F ' ' \
  -f /w/counts.sql > "$WORK/restored.txt"
awk -v r="$MIN_RATIO" '
  NR == FNR { live[$1] = $2 + 0; next }
  { got[$1] = $2 + 0 }
  END {
    for (t in live)
      if (!(t in got) || got[t] < live[t] * r) {
        print "FAIL", t, got[t] + 0, "of", live[t]; bad = 1
      }
    exit (bad ? 1 : 0)
  }' "$WORK/live.txt" "$WORK/restored.txt"

# 5. Only a run that got this far ever reaches the monitor.
curl -fsS -m 10 --retry 5 -o /dev/null "$HC_URL"
Enter fullscreen mode Exit fullscreen mode

A few choices in there are deliberate.

set -euo pipefail is the whole error-handling strategy. Any command that fails ends the script before the curl on the last line, and the trap cleans up the container and temp files either way. There is no "send an alert on failure" branch to get wrong.

The live row counts are taken right after the dump, not inside the same snapshot, so production may have gained a few rows in between. MIN_RATIO=0.95 absorbs that drift. A truncated or half-restored dump will be far below 95% on some table and fail. If production deleted rows since the dump, the restored count is higher, which passes.

POSTGRES_HOST_AUTH_METHOD=trust is something the image docs warn against, because it lets anyone connect without a password. Here the container has no network at all (--network none), holds a copy for a few minutes and is deleted by the trap. The pg_isready -h 127.0.0.1 check is aimed at TCP on purpose: the docs note that the temporary server the image starts during initialization "listens only on the Unix socket," so a socket check can report ready before the real server is up.

--exit-on-error turns the first failed SQL statement into a fatal error. The pg_restore default is to continue and print an error count at the end, which is how a half-restored database ends up looking fine. --no-owner and --no-acl skip ownership and grants, so production roles that do not exist in the throwaway container do not trip it.

Run it every night with a systemd timer

Put the secrets in an environment file only root can read. The R2 endpoint format and region = auto come from Cloudflare's AWS CLI guide.

# /etc/pg-backup-verify/env   (chmod 600)
DATABASE_URL=postgresql://backup:CHANGE_ME@localhost:5432/app
BUCKET=app-backups
S3_ENDPOINT=https://ACCOUNT_ID.r2.cloudflarestorage.com
AWS_ACCESS_KEY_ID=...
AWS_SECRET_ACCESS_KEY=...
AWS_DEFAULT_REGION=auto
HC_URL=https://hc-ping.com/YOUR-CHECK-UUID
Enter fullscreen mode Exit fullscreen mode

Use a read-only database role for backup. On Postgres 14 and later, granting the predefined role pg_read_all_data gives it SELECT on everything, though it does not bypass row-level security. If you use RLS, give the role BYPASSRLS too, or pg_dump stops with an error.

The service and the timer:

# /etc/systemd/system/pg-backup-verify.service
[Unit]
Description=Postgres dump, upload and restore test
Requires=docker.service
After=docker.service network-online.target
Wants=network-online.target

[Service]
Type=oneshot
EnvironmentFile=/etc/pg-backup-verify/env
ExecStart=/usr/local/bin/pg-backup-verify
TimeoutStartSec=2h
Enter fullscreen mode Exit fullscreen mode 
# /etc/systemd/system/pg-backup-verify.timer
[Unit]
Description=Nightly Postgres restore test

[Timer]
OnCalendar=*-*-* 03:15:00
RandomizedDelaySec=15m
Persistent=true

[Install]
WantedBy=timers.target
Enter fullscreen mode Exit fullscreen mode

Per the systemd.timer docs, a timer with no Unit= starts the service with the same name, and Persistent=true runs a missed job as soon as the machine comes back if it was off at 03:15. TimeoutStartSec=2h kills a hung run, and a killed run never pings. Enable it, then run it once by hand and read the log:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup-verify.timer
sudo systemctl start pg-backup-verify.service
journalctl -u pg-backup-verify.service -n 50
Enter fullscreen mode Exit fullscreen mode

One gotcha on non-AWS storage: recent AWS SDKs and the CLI changed their checksum defaults, and when the AWS SDK for Go v2 made that change, GitLab Runner hit SignatureDoesNotMatch errors from third-party S3-compatible providers. If an upload or download fails with a checksum or signature error, add AWS_REQUEST_CHECKSUM_CALCULATION=WHEN_REQUIRED and AWS_RESPONSE_CHECKSUM_VALIDATION=WHEN_REQUIRED to the env file.

Silence is the alert

GitLab had failure alerts. They were emails, and the emails never arrived. An alert that has to be sent when something breaks depends on the broken system to send it.

A dead-man's switch flips that around. Healthchecks.io gives you a URL like https://hc-ping.com/<uuid>. You tell it how often to expect a ping, and when the period plus a grace time passes with no ping, the check goes down and it notifies you. A powered-off server and a full disk produce the same thing: no ping, then an alert.

Set the check to a period of 1 day and a grace time of 2 hours, which covers the randomized start and a slow restore. The curl flags come from the Healthchecks bash guide: -f treats non-200 responses as errors, -m 10 caps the request at 10 seconds, and --retry 5 retries transient failures. If you want a faster signal, you can also ping $HC_URL/fail from an ERR trap. Keep the silence rule as the backstop either way.

What it costs at side-project scale

These numbers are illustrative, from the providers' pricing pages as of October 2026.

Say your compressed dump is 150 MB and you keep 30 nights. That is 4.5 GB. R2's free tier covers 10 GB-month of storage, 1 million Class A and 10 million Class B operations a month, and egress is free, so pulling the dump back down every night costs nothing. B2's first 10 GB are free, and egress up to 3x your average stored data is free too. Thirty uploads and thirty downloads a month do not get near either limit.

Now say the dump is 2 GB. Thirty copies is 60 GB. On R2 that is (60 - 10) × $0.015, about $0.75 a month. On B2 at $6.95 per TB, it is about $0.35. Downloading each night's copy is 60 GB a month, which stays inside B2's 3x free egress.

Healthchecks.io's Hobbyist plan is $0 and monitors 20 jobs. The restore runs on the server you already pay for. Total: $0 for most side projects, under a dollar a month for a database with real users.

To keep it at 30 nights, add a lifecycle rule. On R2 you can delete objects after a set number of days and scope the rule to a prefix, so pg/ expires and anything else in the bucket stays.

What a green check does not prove

Three limits come with the design itself.

  • It is a nightly logical dump. Lose the server at 02:59 and you lose almost a day of writes. Point-in-time recovery needs WAL archiving, which tools like pgBackRest and WAL-G handle. Keep this script alongside them: it proves the logical dump restores, and the WAL archive needs its own restore test.
  • Row counts are not correctness. A bug that nulls a column restores with perfect counts. Add one query that matters to your app, for example that the newest order is under 26 hours old.
  • Extensions must exist in the image. If production uses pgvector, point PG_IMAGE at one of the pgvector project's Postgres 18 images or the restore fails on CREATE EXTENSION, which is the correct outcome.

The restore also competes with production for CPU and disk. At side-project size that is fine at 03:15, and when it stops being fine, move the restore half to a separate cheap box.

And the script is not your runbook. A few times a year, restore onto a fresh server by hand, with the steps written down and a stopwatch running, because how long recovery takes is the number everyone will ask you for during a real outage.

If this was useful

A nightly restore test is one of the dull pieces between localhost and production that nobody owns until the day it is needed. Ship It covers the rest of that gap for a small team: hosting, deploys, auth, payments, CI/CD and monitoring, picked for people who would rather ship the product than babysit the stack. If you are setting up the side project this script protects, that is the book I wrote for it.

Ship It — the pragmatic startup tech stack

Top comments (0)