⚙️ Fixing a PostgreSQL Restore Version Mismatch Between Local and Docker

on

Today I encountered an interesting PostgreSQL compatibility issue while trying to restore a backup into a Docker container.

I had created my .dump file using pg_dump 18.0 on macOS (installed via Homebrew), but my Docker container was running PostgreSQL 15.14 (Alpine). When I tried to restore the dump with: 

cat backup.dump | docker exec -i goudengids-postgres \
psql 'postgresql://user:password@localhost:5432/dbname'

PostgreSQL responded with:

“The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.”

After switching to pg_restore, I still hit version-related errors — because a dump made with pg_dump 18 cannot be restored using an older pg_restore (v15). PostgreSQL tools are mostly backward compatible, but not forward compatible.

✅ The Solution

  1. Keep the container version (Postgres 15 or 16) – no need to upgrade the server.
  2. Install the newer client (pg_restore 18) on your local machine or Ubuntu host:
    sudo apt install postgresql-client-18
  3. Run the restore with the newer client:
    PGPASSWORD='mypassword' pg_restore \
  --clean --if-exists --no-owner --no-privileges -j 4 \
  -h 127.0.0.1 -U myuser -d mydb \
  /path/to/backup.dump
  4. The restore completed successfully — PostgreSQL 15 accepted the data once restored using the correct client version.

💡 Takeaway

  • Always match or exceed the client version (pg_restore) to the pg_dump version used for backup.
  • Server version differences are usually fine — the key factor is the restore tool.
  • Using Docker with the right pg_restore version is a clean, portable way to handle cross-environment restores.

Lesson learned: always check your PostgreSQL client versions before restoring dumps across environments.

Comments

Post a Comment