Introduction
A dump can finish without errors, land in the Bucket and still be no good for a restore. The file might be cut off, or the schema might need an extension the new server doesn't have. Restoring it into a separate PostgreSQL instance on a schedule shows you that while you still have a working database.
In this tutorial, you set up two jobs on an Ubuntu server:
- A nightly backup.
pg_dumpwrites the database to a file, and rclone uploads it to a private Bucket in Hetzner Object Storage, together with a checksum and the row count of every table. - A weekly restore test. A second script downloads the newest backup, checks the checksum, restores it into a throwaway PostgreSQL container, compares the row counts and removes the container again. The job fails, and shows up as failed in
systemctl, on a checksum mismatch, a restore error, a missing table, or a table that had rows but comes back empty. Other row-count differences are logged without failing the job.
The database can run on the same server or anywhere the server can reach. A self-hosted Supabase database works as well, with two small changes described in step 4.
Prerequisites
- A server with Ubuntu 24.04 or 26.04, for example a Hetzner Cloud server, and a user with sudo rights
- A PostgreSQL database (version 14 or newer) that the server can connect to
- Access to the Hetzner Console to create a Bucket and S3 credentials
- Enough free memory and disk on the server to restore the database once. If the database is large, run the restore test on a separate server (see step 6).
Example terminology
- Username:
holu - Bucket:
holu-pg-backupsin Falkenstein (fsn1) - Database:
appdbon the same server, at127.0.0.1:5432
Replace these with your own values.
Step 1 - Create a Bucket and S3 credentials
In the Hetzner Console, open your project and:
- Select Object Storage in the left menu and click Create Bucket.
- Pick a location, enter a name such as
holu-pg-backupsand set the visibility to Private. Bucket names are unique across all of Hetzner, so you may have to try a few. - Go to Security » S3 Credentials and click Generate credentials.
- Copy the access key and the secret key. The secret key is shown only once.
Note the location of the Bucket. The S3 endpoint contains it, for example fsn1.your-objectstorage.com for Falkenstein or nbg1.your-objectstorage.com for Nuremberg.
The S3 credentials are valid for the whole project, so anyone holding them can also delete your backups. Keep them on the backup server only.
Step 2 - Install the tools
You need three things on the server: the PostgreSQL client tools (pg_dump, pg_restore and psql), rclone, and Docker for the restore test.
pg_dump has to be the same major version as the database server, or newer. If PostgreSQL itself runs on this server, the right client is already installed. Otherwise, the PostgreSQL project's own package repository has every version. Add it and install the client for your version, here PostgreSQL 17:
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt install -y postgresql-client-17Then install rclone and Docker from Ubuntu's repositories:
sudo apt install -y rclone docker.ioCheck the versions:
pg_dump --version
rclone version
sudo docker versionThe rclone package in Ubuntu 24.04 and 26.04 is version 1.60, which is new enough for everything in this tutorial.
Step 3 - Prepare the database and the configuration
Step 3.1 - Create a backup user
Don't run backups as the postgres superuser. Create a separate backup role with pg_read_all_data and BYPASSRLS instead. Connect to the database as a superuser, for example with sudo -u postgres psql appdb if PostgreSQL runs on this server, and run:
CREATE ROLE backup WITH LOGIN BYPASSRLS PASSWORD 'choose-a-long-password';
GRANT pg_read_all_data TO backup;pg_read_all_data (PostgreSQL 14 and newer) gives read access to every table and sequence. BYPASSRLS is needed because pg_dump stops with an error on tables with row-level security that the role can't bypass.
Step 3.2 - Store the settings
Everything the two scripts need goes into one directory that only root can read:
sudo mkdir -p /etc/pg-backup
sudo chmod 700 /etc/pg-backupCreate /etc/pg-backup/pg-backup.env:
sudo nano /etc/pg-backup/pg-backup.envReplace the example values with your actual information.
# Source database. The password is in here, so keep this file at mode 600.
# DATABASE_URL="postgresql://backup:<password_from_step_3.1>@127.0.0.1:5432/<database_name>"
DATABASE_URL="postgresql://backup:choose-a-long-password@127.0.0.1:5432/appdb"
# Extra pg_dump options. Leave empty to dump the whole database.
PG_DUMP_OPTIONS=""
# rclone remote, Bucket and folder for this database.
# BACKUP_TARGET="hetzner-<bucket_location>:<bucket_name>/<database_name>"
BACKUP_TARGET="hetzner-fsn1:holu-pg-backups/appdb"
# Docker image for the restore test. Use the same major version as pg_dump.
RESTORE_IMAGE="postgres:17"
# Keep the rclone configuration next to this file.
export RCLONE_CONFIG=/etc/pg-backup/rclone.confIf the password contains characters such as @, / or #, percent-encode them in the URL (@ becomes %40).
Create /etc/pg-backup/rclone.conf with the credentials from step 1. The remote name hetzner-fsn1 is the part before the colon in BACKUP_TARGET:
sudo nano /etc/pg-backup/rclone.conf[hetzner-fsn1]
type = s3
provider = Other
access_key_id = <your_access_key>
secret_access_key = <your_secret_key>
endpoint = fsn1.your-objectstorage.com
region = fsn1
acl = privateIf the Bucket is in another location, change endpoint and region to match. Then lock both files down and check that rclone can see the Bucket:
sudo sh -c 'chmod 600 /etc/pg-backup/*'
sudo rclone --config /etc/pg-backup/rclone.conf lsd hetzner-fsn1:The output should list holu-pg-backups.
Step 4 - Write the backup script
Create /usr/local/bin/pg-backup.sh:
sudo nano /usr/local/bin/pg-backup.sh#!/usr/bin/env bash
# Dump one PostgreSQL database and upload it to Hetzner Object Storage.
set -euo pipefail
source /etc/pg-backup/pg-backup.env
stamp="$(date -u +%Y-%m-%dT%H%M%SZ)"
workdir="$(mktemp -d)"
trap 'rm -rf "$workdir"' EXIT
cd "$workdir"
# 1. The dump itself. Custom format is compressed and lets pg_restore
# pick what to restore and in which order.
# shellcheck disable=SC2086
pg_dump --dbname="$DATABASE_URL" --format=custom --file=db.dump $PG_DUMP_OPTIONS
# 2. Row count of every table in the dump, so the restore test has
# numbers to compare against.
pg_restore --list db.dump \
| awk '$4 == "TABLE" && $5 == "DATA" { print $6 "\t" $7 }' \
| while IFS=$'\t' read -r schema table; do
count="$(psql "$DATABASE_URL" --no-psqlrc --tuples-only --no-align \
--command="SELECT count(*) FROM \"$schema\".\"$table\"")"
printf '%s\t%s\t%s\n' "$schema" "$table" "$count"
done > row-counts.tsv
# 3. A checksum, so a damaged download is caught before anything else.
sha256sum db.dump > db.dump.sha256
# 4. Upload everything into a folder named after the time of the backup.
rclone copy "$workdir" "$BACKUP_TARGET/$stamp"
echo "Backup $stamp uploaded ($(du -h db.dump | cut -f1))"What it does, in order:
pg_dump --format=customwrites a compressed dump thatpg_restorecan read selectively and in parallel. Plain SQL dumps work too, but you lose both.pg_restore --listreads the table of contents of the dump without restoring anything. EveryTABLE DATAentry is a table whose rows are in the file. The script counts the rows of exactly those tables in the live database and writes them torow-counts.tsv.sha256sumrecords a checksum, so a damaged or incomplete download is caught before the restore even starts.rclone copyuploads the three files into a folder named after the time of the backup, for exampleappdb/2026-11-16T023112Z/.
The rows are counted right after the dump, not inside the same snapshot. On a quiet database the numbers match the dump exactly. On a busy database they can differ, because rows may change between the dump snapshot and the count queries. The restore test reports those differences but doesn't treat them as an error. Counting also reads every table once more, which can take a while on very large tables.
Make the script executable and run it once:
sudo chmod 755 /usr/local/bin/pg-backup.sh
sudo /usr/local/bin/pg-backup.shYou should see something like:
Backup 2026-11-16T091530Z uploaded (2.4M)List what ended up in the Bucket:
# sudo rclone --config /etc/pg-backup/rclone.conf ls hetzner-<bucket_location>:<bucket_name>
sudo rclone --config /etc/pg-backup/rclone.conf ls hetzner-fsn1:holu-pg-backups 2491408 appdb/2026-11-16T091530Z/db.dump
74 appdb/2026-11-16T091530Z/db.dump.sha256
152 appdb/2026-11-16T091530Z/row-counts.tsvIf the database belongs to a self-hosted Supabase
A Supabase database is a PostgreSQL database, so the script works unchanged. A few things differ:
- In Supabase,
postgresis not a superuser. Create the backup role from step 3.1 while connected assupabase_admin. - By default, application tables live in the
publicschema and users in theauthschema, while most other schemas belong to Supabase's own services. For that layout, setPG_DUMP_OPTIONSin/etc/pg-backup/pg-backup.envas below. If your app also uses custom schemas, add a--schemaoption for each of them.
PG_DUMP_OPTIONS="--schema=public --schema=auth"Policies and grants in a Supabase database refer to the roles anon, authenticated and service_role, which a plain PostgreSQL container doesn't have. The restore test in the next step runs /etc/pg-backup/pre-restore.sql before restoring, if the file exists. Create it with:
-- A dump made with --schema options creates the public schema itself,
-- so remove the empty one the new database starts with.
DROP SCHEMA public;
-- Roles that Supabase grants and policies refer to.
CREATE ROLE anon NOLOGIN;
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE service_role NOLOGIN;Keep in mind that the database is only one part of a self-hosted Supabase. Storage files (and the storage schema that keeps track of them), the .env file with your JWT secret and the Vault encryption key are separate and need their own backup.
Step 5 - Write the restore test
The second script does what you would do by hand after a disaster, on a container you throw away afterwards.
Create /usr/local/bin/pg-restore-test.sh:
sudo nano /usr/local/bin/pg-restore-test.sh#!/usr/bin/env bash
# Restore the newest backup into a throwaway container and check it.
set -euo pipefail
source /etc/pg-backup/pg-backup.env
container="pg-restore-test"
workdir="$(mktemp -d)"
cleanup() {
docker rm --force --volumes "$container" >/dev/null 2>&1 || true
rm -rf "$workdir"
}
trap cleanup EXIT
# Folder names are UTC timestamps, so the last one in sort order is the newest.
latest="$(rclone lsf --dirs-only "$BACKUP_TARGET" | sort | tail -n 1)"
latest="${latest%/}"
if [ -z "$latest" ]; then
echo "No backups found in $BACKUP_TARGET" >&2
exit 1
fi
echo "Testing backup $latest"
rclone copy "$BACKUP_TARGET/$latest" "$workdir"
(cd "$workdir" && sha256sum --check --quiet db.dump.sha256)
if [ ! -s "$workdir/row-counts.tsv" ]; then
echo "The dump in $latest contains no table data" >&2
exit 1
fi
started="$(date +%s)"
# Left over from a run that was killed? Remove it first.
docker rm --force --volumes "$container" >/dev/null 2>&1 || true
docker run --detach --name "$container" \
--env POSTGRES_PASSWORD=restore-test \
"$RESTORE_IMAGE" >/dev/null
# Ask over TCP: during first start-up the image runs a temporary server
# that only listens on a socket, and we want the real one.
until docker exec "$container" pg_isready --host=127.0.0.1 --username=postgres >/dev/null 2>&1; do
sleep 1
done
docker exec "$container" createdb --username=postgres restore_test
docker cp "$workdir/db.dump" "$container:/tmp/db.dump"
if [ -f /etc/pg-backup/pre-restore.sql ]; then
docker exec --interactive "$container" \
psql --username=postgres --dbname=restore_test --set=ON_ERROR_STOP=1 --quiet \
< /etc/pg-backup/pre-restore.sql
fi
docker exec "$container" pg_restore --username=postgres --dbname=restore_test \
--no-owner --no-privileges --exit-on-error /tmp/db.dump
failed=0
while IFS=$'\t' read -r schema table expected; do
if ! actual="$(docker exec "$container" psql --username=postgres --dbname=restore_test \
--no-psqlrc --tuples-only --no-align \
--command="SELECT count(*) FROM \"$schema\".\"$table\"" 2>/dev/null)"; then
echo "MISSING $schema.$table"
failed=1
elif [ "$expected" -gt 0 ] && [ "$actual" -eq 0 ]; then
echo "EMPTY $schema.$table (had $expected rows)"
failed=1
elif [ "$actual" -ne "$expected" ]; then
echo "CHANGED $schema.$table: $actual rows, source had $expected when counted"
fi
done < "$workdir/row-counts.tsv"
seconds="$(( $(date +%s) - started ))"
if [ "$failed" -ne 0 ]; then
echo "Restore test FAILED for $latest" >&2
exit 1
fi
echo "Restore test passed: $latest, $(wc -l < "$workdir/row-counts.tsv") tables, restored and checked in ${seconds}s"A few details:
- With
--exit-on-error,pg_restorestops at the first error. Without it, the restore carries on and the first error can get buried under the ones that follow from it. Either way, a restore with errors exits with a non-zero status and fails the job. --no-owner --no-privilegesskips ownership and grants, because the roles of your production server don't exist in the container. This is a test of your data and schema, not of your permissions.pg_restoreruns inside the container with the container's own version, so the server doesn't need a matchingpg_restorefor every database you test.- A table that had rows in the source but comes back empty, or is missing, fails the test. Any other row-count difference is reported without failing the test, regardless of its size. Look at those lines in the journal from time to time (see the note in step 4).
- The
trapremoves the container and the downloaded files even when the script fails halfway.
Run it:
sudo chmod 755 /usr/local/bin/pg-restore-test.sh
sudo /usr/local/bin/pg-restore-test.shTesting backup 2026-11-16T091530Z
Restore test passed: 2026-11-16T091530Z, 6 tables, restored and checked in 4sIt's worth breaking a backup on purpose once, to see that the test notices. Copy a backup to a new folder, cut the dump short and upload it again:
sudo -i
source /etc/pg-backup/pg-backup.env
rclone copy "$BACKUP_TARGET/2026-11-16T091530Z" /tmp/broken
head -c 1000000 /tmp/broken/db.dump > /tmp/broken/short && mv /tmp/broken/short /tmp/broken/db.dump
rclone copy /tmp/broken "$BACKUP_TARGET/zz-broken-test"
/usr/local/bin/pg-restore-test.shReplace 2026-11-16T091530Z with the name of your backup. Letters sort after digits, so the test takes zz-broken-test for the newest backup and stops at the checksum:
Testing backup zz-broken-test
db.dump: FAILED
sha256sum: WARNING: 1 computed checksum did NOT matchIf you also replace db.dump.sha256 with a checksum of the cut-off file (cd /tmp/broken && sha256sum db.dump > db.dump.sha256, then upload again), the checksum passes and pg_restore fails instead with could not read from input file: end of file. Either way the script exits with code 1. Delete the fake backup and leave the root shell when you're done:
rclone purge "$BACKUP_TARGET/zz-broken-test"
rm -rf /tmp/broken
exitStep 6 - Run both jobs on a schedule
systemd timers are a good fit here: a failed run shows up as a failed unit, the output goes to the journal, and Persistent=true catches up on a run that was missed while the server was off.
Create the backup service and timer:
sudo nano /etc/systemd/system/pg-backup.service[Unit]
Description=Back up PostgreSQL to Object Storage
Wants=network-online.target
After=network-online.target
[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg-backup.shsudo nano /etc/systemd/system/pg-backup.timer[Unit]
Description=Daily PostgreSQL backup
[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=15min
Persistent=true
[Install]
WantedBy=timers.targetThe backup runs every night at 02:30, plus a random delay of up to 15 minutes. If several servers write to the same Bucket, the delay keeps them from starting at the same second.
The restore test gets its own pair, once a week on Sunday morning. It needs Docker, so the service waits for it:
sudo nano /etc/systemd/system/pg-restore-test.service[Unit]
Description=Restore the newest PostgreSQL backup and check it
Wants=network-online.target
After=network-online.target docker.service
Requires=docker.service
[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg-restore-test.shsudo nano /etc/systemd/system/pg-restore-test.timer[Unit]
Description=Weekly restore test of the PostgreSQL backup
[Timer]
OnCalendar=Sun *-*-* 04:00:00
Persistent=true
[Install]
WantedBy=timers.targetEnable both timers and check when they run next:
sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer pg-restore-test.timer
systemctl list-timers 'pg-*'To read the output of the last runs:
journalctl -u pg-backup.service -n 20
journalctl -u pg-restore-test.service -n 20A failed restore test is only useful if someone sees it. If you already have monitoring that watches systemd units, add pg-backup.service and pg-restore-test.service to it. If not, OnFailure= in the [Unit] section of a service can start another unit that sends you a mail or calls a webhook.
The restore needs roughly as much disk as the database itself and enough memory to build its indexes. For a large database, run the restore test on a second, small cloud server instead: install the same tools, copy /etc/pg-backup and /usr/local/bin/pg-restore-test.sh over, and only enable pg-restore-test.timer there. That server doesn't need access to the database at all, only to the Bucket.
Step 7 - Delete old backups (Optional)
Backups are never deleted by the scripts above, so the Bucket keeps growing. To keep 30 days, add this line at the end of /usr/local/bin/pg-backup.sh:
rclone delete --min-age 30d "$BACKUP_TARGET"It only runs after a successful upload, because the script stops at the first error. If backups start failing, the old ones stay where they are instead of running out one by one.
Conclusion
You now have a nightly pg_dump of your database in Hetzner Object Storage and a weekly job that restores the newest backup and checks it. To recover the database, download the backup folder, check the checksum and run pg_restore. The test skips ownership and grants. Restoring those on a replacement server also requires the roles they refer to, so create them first. For a self-hosted Supabase, restore the separate files and configuration mentioned in step 4 as well.
Things you can add from here:
- Run
pg_restore --liston a dump to see everything in it, and use-Lto restore only part of it. - If the dumps must be encrypted before they leave the server, put an rclone crypt remote in front of the Bucket.
- For a self-hosted Supabase, back up Storage files and the configuration as well. This guide walks through the whole stack.