pg_restore in Docker: Restoring a Django Postgres Backup

Most Django deploy guides end their backup section the same way — including my own Lightsail guide: a nightly pg_dump into a gzip file, a cron line, and the sentence "test that you can restore." Then they stop. The restore is left as an exercise, and the first time most people try it is the day they actually need it.

So I did the exercise. I built a small Django-shaped schema (a django_migrations table, two models with a foreign key, bigserial primary keys) on PostgreSQL 16, dumped it, and restored it every wrong way I could think of. This post is what came out of that: the errors you'll hit, which ones are harmless, and one that isn't an error at all — which is the dangerous one.

First, know which kind of backup you have

pg_dump has two formats you'll meet in practice, and they're restored with different tools:

Mixing them up is the most common first error. The backup script from the Lightsail guide produces a plain SQL file (db-2026-09-25.sql.gz). Feed that to pg_restore and you get:

pg_restore: error: input file appears to be a text format dump. Please use psql.

If you don't know what a file is, look at its first bytes. A custom archive starts with the signature PGDMP; a plain dump starts with an SQL comment:

head -c 5 backup.dump          # PGDMP  → pg_restore
gunzip -c db.sql.gz | head -3  # -- PostgreSQL database dump  → psql

The restore that fails and reports success

This is the one to take seriously. I restored a plain dump with psql into a database where the tables already existed — the realistic mistake of "restore over the running database":

gunzip -c db.sql.gz | psql -U app -d appdb
echo $?

The output contained 13 errors — relation "blog_post" already exists, duplicate key value violates unique constraint "blog_post_pkey", multiple primary keys for table "blog_post" are not allowed, and so on. The exit code was 0.

That's documented behavior: psql returns 0 "if it finished normally", and a failed SQL statement inside a script doesn't count as abnormal — only its own fatal errors (out of memory, file not found) or a lost connection do. If this command sits in a cron job or a CI step, it reports success while restoring nothing.

Two flags fix it:

gunzip -c db.sql.gz | psql -U app -d appdb \
  -v ON_ERROR_STOP=1 --single-transaction

• ON_ERROR_STOP=1 makes psql stop at the first error and exit with status 3 (that's what I got).
• --single-transaction wraps the whole script in one transaction, so stopping halfway rolls everything back. Without it you can be left with half the tables created.

pg_restore behaves the same way by default. Its documentation says it plainly: the default is "to continue and to display a count of errors at the end of the restoration." The difference is that pg_restore at least exits with status 1 when errors were ignored, and prints a line like pg_restore: warning: errors ignored on restore: 6.

pg_restore has the same two guards: --exit-on-error and --single-transaction (the latter implies the former). They are not equivalent, and I measured the difference. Restoring an archive that fails on the first ALTER TABLE:

• with --exit-on-error alone, the target database was left with 1 of 3 tables;
• with --single-transaction, it was left with 0 — nothing applied.

A half-restored database is worse than an empty one, because Django will happily start on top of it. Use --single-transaction.

Moving to a new server: "role does not exist"

The second most likely failure shows up when you restore on a new machine. By default a dump records who owns each table:

ALTER TABLE public.blog_post OWNER TO app;

If the new server's database user has a different name — say the old .env had POSTGRES_USER=app and the new one has django — every one of those lines fails:

pg_restore: error: could not execute query: ERROR:  role "app" does not exist
Command was: ALTER TABLE public.blog_author OWNER TO app;
...
pg_restore: warning: errors ignored on restore: 6

The data is restored anyway, so this looks harmless. It isn't always. The tables now belong to whoever ran the restore. When I restored as the postgres superuser and then connected as django, Django's user couldn't read its own tables:

ERROR:  permission denied for table blog_post

With the official postgres Docker image this is less likely to bite, because POSTGRES_USER is created as a superuser and people usually restore as that same user. But if your app connects with a separate, non-superuser role, you'll hit it.

The fix is to drop ownership from the equation and restore as the user the app connects with:

pg_restore -U django -d appdb --no-owner --single-transaction backup.dump

With --no-owner, every object is owned by the connecting user. I got exit code 0, all three tables owned by django, and 50 rows back. pg_dump accepts the same --no-owner flag if you'd rather bake it into the backup itself — useful for plain SQL dumps, where you can't choose at restore time.

Sequences survive the round trip, by the way. A dump includes a SEQUENCE SET entry for each table, and the first insert after my restore got id 51, right after the 50 restored rows. No manual setval needed.

Restoring over an existing database

There are two ways to replace a database's contents, and each has a trap.

Option 1: pg_restore --clean. This drops each object before recreating it. But on an empty or partially empty database, the drops themselves fail:

pg_restore: error: could not execute query: ERROR:  relation "public.blog_post" does not exist
Command was: ALTER TABLE ONLY public.blog_post DROP CONSTRAINT blog_post_author_id_fkey;
...
pg_restore: warning: errors ignored on restore: 13

Exit code 1 — and paired with --single-transaction, those "errors" would abort a perfectly good restore. Add --if-exists: pg_restore --clean --if-exists exited 0 both on an empty database and on one that already held the data, and the row count stayed at 50 (no duplicates).

Option 2: drop and recreate the database. Cleaner, since nothing from the old state can survive. But if Django is still running, it fails:

ERROR:  database "appdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Stop the app container first (docker compose stop backend). PostgreSQL 13 and later also accepts DROP DATABASE appdb WITH (FORCE), which terminates the open connections for you — it worked in my test, but use it knowing that it cuts off whatever those sessions were doing.

Doing all of this inside Docker Compose

If Postgres runs in a Compose service (named postgres below, as in the Lightsail guide), the restore runs inside that container. Three details matter.

Use -T. docker compose exec allocates a TTY by default. For piping a file in, disable it:

docker compose exec -T postgres \
  sh -c 'pg_restore -U "$POSTGRES_USER" -d "$POSTGRES_DB" --no-owner --single-transaction' \
  < backup.dump

pg_restore reads from standard input when no file name is given, so the dump never has to be copied into the container.

Watch which shell expands the variables. Written as pg_restore -U "$POSTGRES_USER" without the sh -c '...' wrapper, the variable is expanded by your shell on the host, before Docker sees the command. In an interactive session that might work by accident. In cron — where your .env isn't loaded — it's empty, and a script with set -u stops with POSTGRES_USER: unbound variable. Single quotes around sh -c hand the variable to the container, where the Postgres image already has it set.

Parallel restore needs a real file. On a large database pg_restore -j 4 speeds things up, but not from a pipe:

pg_restore: error: parallel restore from standard input is not supported

Copy the file in first with docker compose cp backup.dump postgres:/tmp/backup.dump and pass that path. Note that -j can't be combined with --single-transaction either — you trade atomicity for speed, so only reach for it when restore time actually matters.

Version mismatches: the new \restrict line

Open a plain dump made with a recent pg_dump and you'll see a line near the top that wasn't there a year ago:

\restrict <random key generated per dump>

This came in with the August 2025 minor releases (16.10, 17.6, 15.14, 14.19, 13.22) as the fix for CVE-2025-8714. The release notes explain it: a compromised source server could otherwise emit text that psql interprets as meta-commands, giving shell access on the machine doing the restore. \restrict switches meta-commands off for the rest of the file.

The practical consequence: a psql older than those versions doesn't have this command, and psql answers any meta-command it doesn't know with invalid command. I couldn't test an old client here, so I won't guess how far such a restore gets — the point is that there's no reason to find out. Along with the general rule from the pg_dump docs — a dump is not guaranteed to load into an older major version, "not even if the dump was taken from a server of that version" — this gives a simple habit: restore with the psql/pg_restore inside the target Postgres container, not whatever client happens to be installed on the host. The Compose commands above already do that.

After the restore

Two checks before you point traffic at it:

docker compose exec -T postgres sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "ANALYZE"'
docker compose run --rm backend python manage.py migrate --check

• The pg_dump docs recommend running ANALYZE after a restore so the query planner has fresh statistics.
• migrate --check exits non-zero if there are unapplied migrations. The restored django_migrations table reflects the day the backup was taken; if you've deployed migrations since then, this tells you before a request hits a missing column.

A restore test that runs itself

"Test your backups" only happens if it's automatic. Switch the backup to the custom format (it's compressed by default, so the gzip step goes away):

docker compose exec -T postgres \
  sh -c 'pg_dump -U "$POSTGRES_USER" -Fc "$POSTGRES_DB"' \
  > "$HOME/backups/db-$(date +%F).dump"

Then, after the backup, restore the newest file into a scratch database and check that it contains something:

#!/bin/bash
# ~/restore-test.sh
set -euo pipefail
 
COMPOSE="docker compose -f $HOME/app/docker-compose.yml"
LATEST=$(ls -1t "$HOME"/backups/db-*.dump | head -n 1)
 
$COMPOSE exec -T postgres sh -c \
  'dropdb -U "$POSTGRES_USER" --if-exists restore_test && createdb -U "$POSTGRES_USER" restore_test'
 
$COMPOSE exec -T postgres sh -c \
  'pg_restore -U "$POSTGRES_USER" -d restore_test --no-owner --single-transaction' \
  < "$LATEST"
 
ROWS=$($COMPOSE exec -T postgres sh -c \
  'psql -U "$POSTGRES_USER" -d restore_test -tAc "SELECT count(*) FROM django_migrations"')
 
[ "$ROWS" -gt 0 ] || { echo "restore test FAILED: django_migrations is empty"; exit 1; }
echo "restore test OK: $LATEST ($ROWS migrations)"

I ran the same logic against my test cluster (minus the Docker wrapper): it passed on a valid dump, passed again on a re-run, and failed with exit code 1 on a corrupt file (input file does not appear to be a valid archive). Wire the failure into whatever already alerts you — a cron MAILTO, a health-check ping — because a restore test nobody reads is as useful as no test.

Two things this doesn't cover. It checks the database only; uploaded media files need their own backup. And the pgdata volume in a Docker Compose setup outlives its container but not the server, so the dump files have to be copied off the machine too.

Summary

• Plain dumps go through psql, custom archives through pg_restore. head -c 5 tells you which you have.
• psql exited 0 after 13 errors in my test. Always use -v ON_ERROR_STOP=1 --single-transaction.
• For pg_restore, prefer --single-transaction over --exit-on-error: the latter left a third of the schema behind.
• On a new server, --no-owner plus restoring as the app's own user avoids both role does not exist and permission denied.
• --clean needs --if-exists; dropping the database needs the app stopped (or WITH (FORCE)).
• In Compose: exec -T, single-quoted sh -c so the container expands the variables, and the container's own client, not the host's.
• Automate the restore test. Until it has run successfully, your backup is a file you hope works.