Back up a database, then prove you can get it back

BASIC (Steps 1-5) / GOING FURTHER (Steps 6-7 scripts)

Tested for real: I ran every command in this guide top-to-bottom on a fresh Ubuntu 24.04 virtual machine (4 CPUs, 8 GB RAM) on 13 August 2026. Versions at test time: PostgreSQL 16.14 (Ubuntu package 16.14-0ubuntu0.24.04.1).

Found a problem? Tell me via the contact page.

I have written elsewhere about why a backup you have never restored is a wish, not a backup. That piece is the why. This guide is the how, on a practice database you can destroy without fear.

What you'll end up with

A PostgreSQL database, a real backup of it, and a restore you have watched succeed. You will also watch a restore fail while looking half-successful — the exact trap the essay warns about. At the end, two small scripts do the backup and the proof for you.

The finished state, from my test run:

[2026-08-13T15:53:48+00:00] verifying /var/backups/postgres/notesapp_2026-08-13_155347.dump
/var/backups/postgres/notesapp_2026-08-13_155347.dump: OK
OK   notes: live=500 restored=500
OK   users: live=3 restored=3
[2026-08-13T15:53:48+00:00] VERIFY PASSED: backup restores cleanly and counts match

Who this is for

BASIC for Steps 1–5: one path, copy-paste-safe, every output shown. Steps 6–7 (the scripts) are the GOING FURTHER tail — do them once the first five make sense.

Time and cost

The commands themselves took under a minute of machine time on my VM; the install was the longest at 26 seconds. Budget 45 minutes to read, type and understand. Cost: free; PostgreSQL needs about 175 MB of disk.

Words you'll meet

• Database — a program that stores structured data and answers questions about it. Here, PostgreSQL. (basics)
• Dump — a backup file: everything needed to rebuild the database, written into one file. (basics)
• Restore — reading a dump back in to rebuild the data somewhere. (basics)
• Checksum — a fingerprint of a file. If the file changes or corrupts, the fingerprint stops matching. (basics)
• Exit code — the number a command reports when it finishes. 0 means success; anything else means trouble, even when the output looks busy and productive. (basics)
• Scratch database — a throwaway database you restore into for testing, then delete. (basics)

Placeholders

Some guides on this site use CAPS-WITH-DASHES placeholders like YOUR-USERNAME-HERE, which you swap for your own values. This guide has none — every command runs exactly as written.

Before you start

• A computer running Ubuntu 24.04 with a user that can use sudo.

• If the machine is brand new, do First hour with a new Ubuntu server first.

• Nothing else. We install the database ourselves and practise on made-up data.

One convention to know: on Ubuntu, PostgreSQL trusts the system user called postgres. That is why nearly every command below starts with sudo -u postgres — "run this as the postgres user".

The steps

Step 1 — Install PostgreSQL

Refresh the package lists first:

sudo apt-get update

Now install PostgreSQL from Ubuntu's own archive. On 24.04 this brings in version 16:

sudo apt-get install -y postgresql

A passing run includes:

The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm17t64 libpq5
  libtypes-serialiser-perl postgresql postgresql-16 postgresql-client-16
  postgresql-client-common postgresql-common ssl-cert
0 upgraded, 12 newly installed, 0 to remove and 26 not upgraded.
Need to get 43.6 MB of archives.
After this operation, 175 MB of additional disk space will be used.
[...snipped...]
Creating new PostgreSQL cluster 16/main ...
[...snipped...]

Check it worked. Ask for the version:

psql --version

You should see something like:

psql (PostgreSQL) 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1)

Your version may be newer — that's fine. Then confirm the server started itself; Ubuntu's package does this for you, no manual start needed:

systemctl is-active [email protected]

The important line is:

active

Step 2 — Create a small database to practise on

We need data worth losing. This makes a pretend note-taking app: three users, five hundred notes.

Create the database, named notesapp:

sudo -u postgres createdb notesapp

Create a table of users:

sudo -u postgres psql -d notesapp -c "CREATE TABLE users (id serial PRIMARY KEY, username text NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now());"

A passing run includes:

CREATE TABLE

Create a table of notes, each belonging to a user:

sudo -u postgres psql -d notesapp -c "CREATE TABLE notes (id serial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), title text NOT NULL, body text, created_at timestamptz NOT NULL DEFAULT now());"

You should see something like:

CREATE TABLE

Add three users:

sudo -u postgres psql -d notesapp -c "INSERT INTO users (username) VALUES ('alice'), ('bob'), ('carol');"

The important line is:

INSERT 0 3

Add five hundred notes, spread across them:

sudo -u postgres psql -d notesapp -c "INSERT INTO notes (user_id, title, body) SELECT (i % 3) + 1, 'Note ' || i, 'Body text for note number ' || i FROM generate_series(1, 500) AS i;"

You should see something like:

INSERT 0 500

Check it worked. Count everything:

sudo -u postgres psql -d notesapp -c "SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM notes) AS notes;"

A passing run includes:

 users | notes 
-------+-------
     3 |   500
(1 row)

The line that matters is 3 | 500. Remember those two numbers; the whole guide is about getting them back.

Step 3 — Make the backup

First, a folder for backups that the postgres user can write to. This one command creates it with the right owner:

sudo install -d -o postgres -g postgres /var/backups/postgres

Now the backup itself. pg_dump writes the whole database into one file. -Fc picks the "custom" format — compressed, and restorable table-by-table later:

sudo -u postgres pg_dump -Fc -d notesapp -f /var/backups/postgres/notesapp.dump

It prints nothing on success. Take the file's fingerprint now, so future-you can prove the file never rotted or got truncated:

sudo -u postgres sha256sum /var/backups/postgres/notesapp.dump

You should see something like:

3cc998574fa841c9426c2c3510e659365bd8e1b8d226fa8564b547062be81cbd  /var/backups/postgres/notesapp.dump

Your fingerprint will differ — it depends on your exact data. That's fine.

Check it worked. Ask the dump file to describe itself:

sudo -u postgres pg_restore --list /var/backups/postgres/notesapp.dump | head -15

Your output will vary, but look for:

;
; Archive created at 2026-08-13 15:52:53 UTC
;     dbname: notesapp
;     TOC Entries: 20
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
[...snipped...]

The line that matters is Format: CUSTOM — a real, readable archive, not an empty or broken file. On my VM this dump was 9.2 KB and took 0.19 seconds; toy data, real mechanics.

Step 4 — Restore it, and prove the copy is faithful

Here is the heart of the essay: a backup is only real once you have restored it. We restore into a separate database, so the original is never touched.

Create the scratch target:

sudo -u postgres createdb notesapp_restore

Restore the dump into it. -d names the target database — without -d, pg_restore prints SQL to your screen instead of restoring, which surprises people:

sudo -u postgres pg_restore -d notesapp_restore /var/backups/postgres/notesapp.dump

Silence again means success. Now count the copy:

sudo -u postgres psql -d notesapp_restore -c "SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM notes) AS notes;"

The important line is:

 users | notes 
-------+-------
     3 |   500
(1 row)

3 | 500 — the same numbers as the original.

Check it worked more deeply. Counting rows is good; checking the relationships survived is better. This counts notes per user in the restored copy:

sudo -u postgres psql -d notesapp_restore -c "SELECT u.username, count(n.id) FROM users u JOIN notes n ON n.user_id=u.id GROUP BY u.username ORDER BY u.username;"

You should see something like:

 username | count 
----------+-------
 alice    |   166
 bob      |   167
 carol    |   167
(3 rows)

Those per-user numbers matched my original exactly. That is a restore you can trust — and now you have done one.

Step 5 — Watch a restore lie to you

This step is the reason this guide exists. Run the same restore again, into the same database — which is no longer empty. This is exactly what a tired person does at 3 a.m. into a half-broken production database.

sudo -u postgres pg_restore -d notesapp_restore /var/backups/postgres/notesapp.dump

Your output will vary, but look for:

pg_restore: error: could not execute query: ERROR:  relation "notes" already exists
[...snipped...]
pg_restore: error: COPY failed for table "notes": ERROR:  duplicate key value violates unique constraint "notes_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY notes, line 1
pg_restore: error: COPY failed for table "users": ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1) already exists.
[...snipped...]
pg_restore: warning: errors ignored on restore: 10

The line that matters is the last one: errors ignored on restore: 10. Read it the way the database means it, not the way hope reads it.

Three things are true here, and I verified each one:

• It did not duplicate your data. The COPY failed ... duplicate key errors mean the data load aborted. Count the rows and they are still 3 | 500.
• It did not refresh your data either. You think you restored. The database still holds exactly its pre-restore contents. Nothing you dumped was loaded.
• It carried on past every error. pg_restore continues by default and only admits trouble at the end, in that one warning line and in its exit code. The exit code was 1, not 0. A script, or monitoring, that never checks the exit code will call this a success. So will a human who scrolls past the noise.

Check it worked (that is: check the failure behaved as described). Count the rows again:

sudo -u postgres psql -d notesapp_restore -c "SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM notes) AS notes;"

You should see something like:

 users | notes 
-------+-------
     3 |   500
(1 row)

Unchanged. Now the fix. --clean drops each object before recreating it; --if-exists keeps the drops quiet when an object is missing:

sudo -u postgres pg_restore --clean --if-exists -d notesapp_restore /var/backups/postgres/notesapp.dump

No errors this time, and the exit code is 0. Count once more and you get 3 | 500, now genuinely from the dump. Two habits to take away. Restore into a fresh scratch database when you can. Reach for --clean --if-exists when you must restore over something.

---

GOING FURTHER — the scripts

Everything up to here was the BASIC path. The two steps below automate it; do them once Steps 1–5 make sense.

Step 6 — a backup script with checksum and retention

Doing Step 3 by hand every day will not happen; we both know it. This script dumps, fingerprints, and deletes old backups. The functional lines are exactly the script I ran in the test; I have added a comment above each line to say why it is there.

Save this as backup.sh:

#!/usr/bin/env bash
# backup.sh — pg_dump custom-format backup with checksum + retention.
# Usage: backup.sh <dbname> [backup_dir] [retention_days]
# Run as a user with database access (e.g. postgres, or via sudo -u postgres).

# Stop on any error, on unset variables, and on failures inside pipes.
set -euo pipefail

# First argument is the database name; refuse to run without it.
DB="${1:?usage: backup.sh <dbname> [backup_dir] [retention_days]}"
# Second argument is where backups go; default is /var/backups/postgres.
BACKUP_DIR="${2:-/var/backups/postgres}"
# Third argument is how many days of backups to keep; default 7.
RETENTION_DAYS="${3:-7}"

# Timestamp for the filename, e.g. 2026-08-13_155347.
STAMP="$(date +%Y-%m-%d_%H%M%S)"
# Full path of the dump we are about to write.
OUT="${BACKUP_DIR}/${DB}_${STAMP}.dump"

# Make sure the backup folder exists.
mkdir -p "$BACKUP_DIR"

# Say what we are doing, with a timestamp, so logs are readable later.
echo "[$(date -Is)] dumping ${DB} -> ${OUT}"
# The backup itself: custom format (-Fc), named database, named output file.
pg_dump -Fc -d "$DB" -f "$OUT"

# Fingerprint the dump and store the fingerprint next to it.
sha256sum "$OUT" > "${OUT}.sha256"
# Show the fingerprint in the log too.
echo "[$(date -Is)] checksum: $(cut -d' ' -f1 "${OUT}.sha256")"

# Retention: delete dumps (and their checksums) older than N days for this db.
find "$BACKUP_DIR" -maxdepth 1 -name "${DB}_*.dump" -mtime +"$RETENTION_DAYS" -print -delete
find "$BACKUP_DIR" -maxdepth 1 -name "${DB}_*.dump.sha256" -mtime +"$RETENTION_DAYS" -print -delete

# Finish by listing what backups now exist for this database.
echo "[$(date -Is)] done. current backups for ${DB}:"
ls -lh "$BACKUP_DIR"/"${DB}"_*.dump

Install it where the system finds commands:

sudo install -m 755 backup.sh /usr/local/bin/

Check it worked. Run it for real:

sudo -u postgres backup.sh notesapp

Your output will vary, but look for:

[2026-08-13T15:53:47+00:00] dumping notesapp -> /var/backups/postgres/notesapp_2026-08-13_155347.dump
[2026-08-13T15:53:47+00:00] checksum: af23a7974205ffcf36f4330df7d73007d00a03fa484c1cec15e7cbe804cd66ac
[2026-08-13T15:53:47+00:00] done. current backups for notesapp:
-rw-rw-r-- 1 postgres postgres 9.2K Aug 13 15:53 /var/backups/postgres/notesapp_2026-08-13_155347.dump

Your timestamp and checksum will differ — that's fine. The whole run took 0.27 seconds on my toy database.

Step 7 — a script that proves the backup restores

This is the essay made executable. It takes the newest dump, checks the fingerprint, restores into a scratch database, and compares every table's row count against the live database. Then it cleans up after itself. Again: functional lines exactly as tested, comments added.

Save this as verify-restore.sh:

#!/usr/bin/env bash
# verify-restore.sh — prove a pg_dump backup actually restores.
# Restores the newest dump for <dbname> into a scratch database and
# compares per-table row counts against the live database.
# Usage: verify-restore.sh <dbname> [backup_dir]

# Same safety net as before: stop on errors and unset variables.
set -euo pipefail

# Database name is required; backup folder defaults as in backup.sh.
DB="${1:?usage: verify-restore.sh <dbname> [backup_dir]}"
BACKUP_DIR="${2:-/var/backups/postgres}"
# Scratch database name includes this script's process ID, so runs never collide.
SCRATCH="${DB}_verify_$$"

# Find the newest dump for this database, or stop with a clear error.
LATEST="$(ls -1t "$BACKUP_DIR"/"${DB}"_*.dump 2>/dev/null | head -1)"
[ -n "$LATEST" ] || { echo "ERROR: no dump found for ${DB} in ${BACKUP_DIR}"; exit 1; }

# Log which file we are testing.
echo "[$(date -Is)] verifying ${LATEST}"

# 1. Checksum must match what we wrote at backup time.
sha256sum -c "${LATEST}.sha256"

# 2. Restore into a scratch database.
createdb "$SCRATCH"
# Whatever happens next — success or failure — drop the scratch DB on exit.
trap 'dropdb --if-exists "$SCRATCH"' EXIT
# Restore into the scratch DB; it is empty, so no --clean gymnastics needed.
pg_restore -d "$SCRATCH" "$LATEST"

# 3. Compare per-table row counts, live vs restored.
FAIL=0
# List every table in the live database's public schema.
TABLES="$(psql -d "$DB" -tAc \
  "SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY tablename")"
for tbl in $TABLES; do
  # Count rows in the live table...
  LIVE="$(psql -d "$DB" -tAc "SELECT count(*) FROM \"${tbl}\"")"
  # ...and in the restored copy.
  REST="$(psql -d "$SCRATCH" -tAc "SELECT count(*) FROM \"${tbl}\"")"
  if [ "$LIVE" = "$REST" ]; then
    echo "OK   ${tbl}: live=${LIVE} restored=${REST}"
  else
    echo "FAIL ${tbl}: live=${LIVE} restored=${REST}"
    FAIL=1
  fi
done

# Report the verdict, and exit 0 only if every table matched.
if [ "$FAIL" -eq 0 ]; then
  echo "[$(date -Is)] VERIFY PASSED: backup restores cleanly and counts match"
else
  echo "[$(date -Is)] VERIFY FAILED: counts differ (see above)"
fi
exit "$FAIL"

Install it:

sudo install -m 755 verify-restore.sh /usr/local/bin/

Check it worked. Run the proof:

sudo -u postgres verify-restore.sh notesapp

A passing run includes:

[2026-08-13T15:53:48+00:00] verifying /var/backups/postgres/notesapp_2026-08-13_155347.dump
/var/backups/postgres/notesapp_2026-08-13_155347.dump: OK
OK   notes: live=500 restored=500
OK   users: live=3 restored=3
[2026-08-13T15:53:48+00:00] VERIFY PASSED: backup restores cleanly and counts match

The line that matters is VERIFY PASSED — and it took 0.93 seconds. Confirm the scratch database really was dropped:

sudo -u postgres psql -l | grep -c verify

The important line is:

0

Zero scratch databases left behind. Run verify-restore.sh on a schedule and the essay's wish becomes a fact you re-prove daily.

Something went wrong?

• You see errors ignored on restore: N at the end of a restore → you restored into a database that already had those tables; your old data is still there and the dump was not loaded → restore into a fresh scratch database, or re-run with --clean --if-exists (Step 5).
• Your restore "worked" but a script reports exit code 1 → same situation as above; pg_restore continues past errors and only confesses at the end → always check the exit code, not the scrollback. (pg_restore -e would instead stop at the first error.)
• You see Peer authentication failed or permission denied from psql/pg_dump → you ran it as your own user; a stock Ubuntu install trusts the postgres system user → put sudo -u postgres in front (and make sure the backup folder is owned by postgres, as in Step 3).

Undo all of this

Tested with one exception: the final PostgreSQL package removal follows the standard path but was not part of the recorded run.

Drop the scratch copy:

sudo -u postgres dropdb notesapp_restore

Drop the practice database:

sudo -u postgres dropdb notesapp

Remove the backups and checksums:

sudo rm -r /var/backups/postgres

Remove the scripts, if you installed them:

sudo rm /usr/local/bin/backup.sh /usr/local/bin/verify-restore.sh

Remove PostgreSQL itself and its data. This is the untested package removal flagged above; my recorded test proved the database drops (Step 7's cleanup check), not this step:

sudo apt-get remove --purge -y postgresql postgresql-16 postgresql-client-16 postgresql-common postgresql-client-common

sudo apt-get autoremove -y

sudo rm -r /var/lib/postgresql /etc/postgresql

Where to go next

• Backups that actually restore: restic from zero (coming) — the same restore-it-or-it-isn't-real discipline, applied to files and whole folders.
• The why, in full: A backup you have never restored is a wish, not a backup.
• Official docs: postgresql.org/docs/16/backup.html (PostgreSQL backup and restore).

---

Last tested: 13 August 2026 on Ubuntu 24.04.4. Versions: PostgreSQL 16.14 (16.14-0ubuntu0.24.04.1), pg_dump custom format 1.15-0.