Mastodon

Postgres backups on Storm Buckets

Your database is the thing you can least afford to lose and the thing least
likely to be in your file backup. This page takes a whole PostgreSQL database,
writes it to one file while the database keeps running, and puts that file in a
Storm Bucket on a schedule. Then it restores it, because a backup you have never
restored is a guess.

This is how Storm backs up the database behind this website. Every command below
was run against it, with placeholders where your bucket and credentials go.

What you need first

  • A running PostgreSQL server you can reach with psql.

  • At least one bucket. See Getting Started.

  • An access key for that bucket. See
    Managing Access Keys.

  • rclone configured against your bucket. The endpoint, the storm region, and
    the force_path_style = true line all matter: see
    Connecting over S3. Get rclone ls working
    before you automate anything.

  • A pg_dump at least as new as your server. An older client refuses to dump
    a newer server, and it refuses late, which is a bad thing to discover from
    cron. Check both:

pg_dump --version
psql -U youruser -d yourdb -tAc "show server_version;"

Take the dump

Use the custom format, not plain SQL:

pg_dump -U youruser -d yourdb --format=custom --compress=9 -f yourdb.dump

--format=custom buys three things a .sql file does not have. It is already
compressed, so there is no separate gzip step. It carries a table of contents,
so you can inspect it without restoring it. And it can be restored selectively
and in parallel.

If Postgres runs in Docker, dump inside the container and copy the file out:

docker compose exec -T db \
  pg_dump -U youruser -d yourdb --format=custom --compress=9 -f /tmp/yourdb.dump
docker compose cp db:/tmp/yourdb.dump ./yourdb.dump

Verify the archive before you trust it

The dump describes itself. Read its table of contents without touching any
database:

pg_restore --list yourdb.dump | head

You get the creation timestamp, the source database name, and one line per
object. If that command works, the file is a real archive and not a truncated
upload.

pg_restore cannot read a pipe. It seeks around the archive, so
pg_dump ... | rclone rcat produces a file you cannot list, cannot restore
selectively, and cannot verify without a full restore attempt. Streaming
straight to the bucket saves disk and costs you every cheap check on this
page. Write the file, verify it, then upload it.

Send it to the bucket

rclone copy yourdb.dump stormdevelopments:your-bucket/postgres/

Name dumps by date so they sort and so you can see gaps:

STAMP=$(date -u +%Y-%m-%dT%H%M%SZ)
rclone copyto yourdb.dump "stormdevelopments:your-bucket/postgres/yourdb-$STAMP.dump"

Put it on a schedule

A script that stops on the first failure, so a broken dump is never uploaded and
never silently replaces a good one:

#!/usr/bin/env bash
set -euo pipefail

DB=yourdb
USER=youruser
REMOTE=stormdevelopments:your-bucket/postgres
STAMP=$(date -u +%Y-%m-%dT%H%M%SZ)
OUT=/var/backups/postgres/$DB-$STAMP.dump

mkdir -p "$(dirname "$OUT")"

pg_dump -U "$USER" -d "$DB" --format=custom --compress=9 -f "$OUT"
pg_restore --list "$OUT" > /dev/null      # refuse to upload an unreadable archive
rclone copyto "$OUT" "$REMOTE/$(basename "$OUT")"
rm -f "$OUT"

# Keep 30 days in the bucket.
rclone delete "$REMOTE" --min-age 30d

The set -euo pipefail and the pg_restore --list line are the whole point.
Without them a database that fails to dump still uploads a zero-byte file, and
the file that is there is not the file you think is there.

Run it nightly from cron:

15 3 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Storage is flat per TB and egress throttles instead of billing, so a nightly
dump and the occasional restore do not produce a surprise line on your invoice.

Restore it

Pull the dump back:

rclone copy stormdevelopments:your-bucket/postgres/yourdb-2026-08-16T031500Z.dump .

Restore into an empty database, never over the live one. pg_restore does
not clear the target first, so restoring onto a populated database gives you
conflicts on every object and a half-merged result:

createdb -U youruser restore_check
pg_restore -U youruser -d restore_check --no-owner --jobs=4 yourdb-2026-08-16T031500Z.dump

--no-owner drops the ownership commands, so the restore works when the target
role is not the role that made the dump. --jobs=4 restores tables in parallel
and only works with the custom format.

Prove the restore

Exit code zero means pg_restore had no errors. It does not mean your data
arrived. Compare the two databases:

# Table count
psql -U youruser -d yourdb        -tAc "select count(*) from information_schema.tables where table_schema='public';"
psql -U youruser -d restore_check -tAc "select count(*) from information_schema.tables where table_schema='public';"

# Row count on a table you would notice losing
psql -U youruser -d yourdb        -tAc "select count(*) from your_busiest_table;"
psql -U youruser -d restore_check -tAc "select count(*) from your_busiest_table;"

# Extensions, the usual reason a restore fails on a different host
psql -U youruser -d restore_check -tAc "select extname, extversion from pg_extension;"

Both sides matching is the pass. Any mismatch means the dump, the upload, or the
restore lost something, and you want to know which before you need this file.

Extensions are worth their own check because the dump records that a database
uses an extension, not the extension's code. Restoring onto a host that lacks
pgvector, postgis, or whatever you depend on fails at that line and leaves
you with a partial database. Install the extension on the target first.

Drop the scratch database when you are done:

dropdb -U youruser restore_check

Do this on a schedule, not when you need it. A restore drill is the only thing
that distinguishes a backup from a file.

What this does not cover yet

A dump is a snapshot of the moment it started. Everything written between that
moment and the failure is gone, so a nightly schedule means a worst case of
nearly a full day of lost writes.

Continuous archiving closes that gap. The database ships its write-ahead log
into a bucket as it goes, and you restore to any minute you name instead of to
last night. pgBackRest and WAL-G both archive to S3 endpoints, and Storm Buckets
is a standard S3 endpoint, so the pieces already fit.

Those guides are coming, one per tool. They land when each one has been run end
to end against a real cluster, the same way every command on this page was,
rather than assembled from documentation and published hopefully. It is also the
architecture behind Storm DB, so this is a road we are walking anyway.

Until they land, the posture is the one on the rest of this site: Storm holds
your off-site copy, you keep your primary, and you find out which is which by
running the restore before you need it.

Next steps