Docker Compose Postgres: How to Restore a Dump

You have a PostgreSQL dump and a Docker Compose stack, and you need the data back, often under pressure. This guide shows how to restore a Postgres dump in Docker Compose step by step, for both custom-format (.dump) and plain SQL (.sql, .sql.gz) files, and how to fix the errors that usually appear halfway through.

Why restores fail when they matter most

Most teams test the backup command and never the restore. When an incident happens, they find that the file is in a format psql cannot read, that roles are missing, that the target database already has tables, or that the dump came from a newer PostgreSQL version. Each of these problems costs hours while the application is down and customers are waiting.

Knowing the exact restore commands for your stack, and rehearsing them, turns a crisis into a routine procedure.

Step 1: identify the dump format

file app-2026-10-25.dump
head -c 5 app-2026-10-25.dump    # "PGDMP" means custom format

Step 2: stop the application, keep the database running

Stop every service that writes to the database, so nothing changes during the restore:

docker compose stop app worker
docker compose up -d db

Before overwriting anything, take a safety dump of the current state, even if it is damaged. You may need it if the restore goes wrong:

docker compose exec -T db pg_dump -U app -d app -Fc > before-restore-$(date +%F_%H%M).dump

Step 3: recreate an empty database

Restoring into a database that already contains tables produces "already exists" errors and duplicate rows. The cleanest path is to drop and recreate it. Connect to the postgres maintenance database to do so:

docker compose exec -T db dropdb -U app --if-exists --force app
docker compose exec -T db createdb -U app -O app app

--force (PostgreSQL 13 and later) closes remaining connections. If you saved roles separately with pg_dumpall --globals-only, restore them first:

docker compose exec -T db psql -U app -d postgres < globals-2026-10-25.sql

Errors saying a role already exists are harmless at this step.

Step 4a: restore a custom-format dump

docker compose exec -T db pg_restore -U app -d app --no-owner --exit-on-error < app-2026-10-25.dump

• -T lets the dump flow through standard input without a terminal.
• --no-owner assigns objects to the user running the restore, which avoids "role does not exist" when restoring into a stack with different role names.
• --exit-on-error stops at the first error, instead of finishing with a half-restored database that looks fine.

For large dumps, parallel restore is much faster, but it needs a file the container can read, not standard input:

docker compose cp app-2026-10-25.dump db:/tmp/app.dump
docker compose exec db pg_restore -U app -d app --no-owner -j 4 /tmp/app.dump
docker compose exec db rm /tmp/app.dump

Step 4b: restore a plain SQL dump

# .sql
docker compose exec -T db psql -U app -d app -v ON_ERROR_STOP=1 < app-2026-10-25.sql

# .sql.gz
gunzip -c app-2026-10-25.sql.gz | docker compose exec -T db psql -U app -d app -v ON_ERROR_STOP=1

ON_ERROR_STOP=1 makes psql stop at the first error. Without it, a restore with hundreds of errors can still end with exit code 0.

Alternative: restore on first start with docker-entrypoint-initdb.d

The official postgres image runs files from /docker-entrypoint-initdb.d when the data directory is empty. It accepts .sql, compressed .sql.gz and shell scripts (.sh), but not custom-format dumps directly. This is useful for test environments:

services:
  db:
    image: postgres:17
    volumes:
      - pgdata_test:/var/lib/postgresql/data
      - ./restore/app.sql.gz:/docker-entrypoint-initdb.d/10-app.sql.gz:ro

It runs only once, on an empty volume. If the volume already has data, the files are ignored silently, which surprises many people.

Step 5: check the result and restart

docker compose exec -T db psql -U app -d app -c "\dt"
docker compose exec -T db psql -U app -d app -c "SELECT count(*) FROM users;"
docker compose exec -T db psql -U app -d app -c "ANALYZE;"
docker compose start app worker

Compare row counts of key tables with what you expect, run ANALYZE so the planner has fresh statistics, then restart the application and test a login and a typical transaction.

Common errors and how to fix them

Make the next restore boring

Write these commands into a runbook next to your docker-compose.yml, and restore the latest dump into a test stack every month. Measure the time: that is your real recovery time. If your backups are not yet automated, start with Docker Compose Postgres backup, and read how to back up Docker volumes safely for the rest of the stack.

Backnix is being built to back up databases running in Docker consistently and keep them encrypted offsite, ready to restore. See the Docker backup page for the current status and the plans and prices.

References

• PostgreSQL documentation: pg_restore
• PostgreSQL documentation: SQL Dump
• Docker Hub: official postgres image (initialization scripts)

Topics: Docker Composepg_restorePostgreSQLRestore