Back up and restore a PostgreSQL database

Getting started (Steps 1-5) / Next steps (Steps 6-7 scripts)

Tested setup: 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.

This guide practises database recovery using a disposable database. For background, see How this site’s backups are checked.

What you'll end up with

Create a PostgreSQL practice database, back it up and restore it separately. Then examine a partially failed restore and add scripts to automate the checks.

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

Steps 1–5 cover the manual process. Steps 6–7 turn it into scripts once you have checked the individual commands.

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 Set up 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

Restore into a separate database so you can check the backup without overwriting the original.

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 — Check for a partially failed restore

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.

---

Next steps — the scripts

Everything up to here was the Getting started 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 — the same restore-it-or-it-isn't-real discipline, applied to files and whole folders.
• The why, in full: How this site’s backups are checked.
• 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.