Get Rewarded! We will reward you with up to €50 credit on your account for every tutorial that you write and we publish!

Back up PostgreSQL to Object Storage and test the restore

profile picture
Author
Super Lede
Published
2026-11-16
Time to read
16 minutes reading time

About the author- Works on database backup and restore tooling.

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_dump writes 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-backups in Falkenstein (fsn1)
  • Database: appdb on the same server, at 127.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:

  1. Select Object Storage in the left menu and click Create Bucket.
  2. Pick a location, enter a name such as holu-pg-backups and set the visibility to Private. Bucket names are unique across all of Hetzner, so you may have to try a few.
  3. Go to Security » S3 Credentials and click Generate credentials.
  4. 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-17

Then install rclone and Docker from Ubuntu's repositories:

sudo apt install -y rclone docker.io

Check the versions:

pg_dump --version
rclone version
sudo docker version

The 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-backup

Create /etc/pg-backup/pg-backup.env:

sudo nano /etc/pg-backup/pg-backup.env

Replace 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.conf

If 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 = private

If 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=custom writes a compressed dump that pg_restore can read selectively and in parallel. Plain SQL dumps work too, but you lose both.
  • pg_restore --list reads the table of contents of the dump without restoring anything. Every TABLE DATA entry 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 to row-counts.tsv.
  • sha256sum records a checksum, so a damaged or incomplete download is caught before the restore even starts.
  • rclone copy uploads the three files into a folder named after the time of the backup, for example appdb/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.sh

You 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.tsv

If 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, postgres is not a superuser. Create the backup role from step 3.1 while connected as supabase_admin.
  • By default, application tables live in the public schema and users in the auth schema, while most other schemas belong to Supabase's own services. For that layout, set PG_DUMP_OPTIONS in /etc/pg-backup/pg-backup.env as below. If your app also uses custom schemas, add a --schema option 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_restore stops 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-privileges skips 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_restore runs inside the container with the container's own version, so the server doesn't need a matching pg_restore for 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 trap removes 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.sh
Testing backup 2026-11-16T091530Z
Restore test passed: 2026-11-16T091530Z, 6 tables, restored and checked in 4s

It'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.sh

Replace 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 match

If 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
exit

Step 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.sh
sudo nano /etc/systemd/system/pg-backup.timer
[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=15min
Persistent=true

[Install]
WantedBy=timers.target

The 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.sh
sudo 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.target

Enable 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 20

A 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 --list on a dump to see everything in it, and use -L to 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.
License: MIT
Want to contribute?

Get Rewarded: Get up to €50 in credit! Be a part of the community and contribute. Do it for the money. Do it for the bragging rights. And do it to teach others!

Report Issue

Get new tutorials via RSS / Atom

Try Hetzner Cloud

Get €20/$20 free credit!

Valid until: 31 December 2026 Valid for: 3 months and only for new customers
Get started
Want to contribute?

Get Rewarded: Get up to €50 credit on your account for every tutorial you write and we publish!

Find out more
Report Issue

Get new tutorials via RSS / Atom