PostgreSQL migrations often stumble when the client utilities (pg_dump, pg_restore, psql) don’t match the server version. With Docker, you can run the exact client version you need—no multi-version installs on your host, no PATH conflicts, and fewer surprises.
This guide walks you through:
- Installing Docker on common Linux distributions
- Checking PostgreSQL server versions to pick the right client
- Dumping a database without creating the database or roles
- Restoring that dump on another server
- Using drop-in scripts (
dump.sh,restore.sh) that:- auto-detect the server’s major version and select
postgres:<major> - use
~/.pgpassorPGPASSWORD, and prompt only if needed - enforce a connect timeout and default to SSL (great for RDS)
- produce custom-format dumps for fast, parallel restores
- auto-detect the server’s major version and select
Get the newest scripts here (source of truth):
https://github.com/reliablepenguin/rp_pg_utils
Install Docker
What’s happening here? We’re installing the Docker engine so we can run the official postgres containers locally. We also enable the service so Docker starts automatically after reboot.
Ubuntu / Debian
|
1 2 3 4 |
sudo apt update sudo apt install -y docker.io sudo systemctl enable --now docker |
AlmaLinux / Rocky Linux / CentOS
|
1 2 3 4 5 |
sudo dnf install -y dnf-plugins-core sudo dnf config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo sudo dnf install -y docker-ce docker-ce-cli containerd.io sudo systemctl enable --now docker |
Fedora
|
1 2 3 |
sudo dnf install -y docker sudo systemctl enable --now docker |
Verify
|
1 2 |
docker --version |
Why verify? It confirms Docker is installed and reachable from your shell. If this fails, check that your user is allowed to run Docker (you may need to add yourself to the docker group and re-login).
Why “version-smart” matters
Concept: The PostgreSQL client tools are somewhat forward/backward compatible, but the safest path is to use the same major version as the server you’re talking to.
- Use the source server’s major version for
pg_dump(e.g., PG 15 server ⇒postgres:15) so the dump is produced using exactly the features that server expects. - Use the destination server’s major version for
pg_restore(e.g., PG 16 server ⇒postgres:16) so the restore tool understands how to apply objects on that target.
How we do it: The scripts query SHOW server_version_num; (e.g., 150007) and compute the major (15). That number becomes the Docker image tag we run (postgres:15).
Manual Steps (if you don’t want scripts)
These are “raw” one-liners for folks who prefer manual control. The scripts later automate the same flow but handle version detection, timeouts, and credentials for you.
Step 1: Check server versions
What this does: Runs psql inside a container to connect to each server and print its reported version. You can also read the numeric variant for precise major/minor math.
Source:
|
1 2 3 |
docker run --rm -e PGPASSWORD="$SRC_PASS" postgres:latest \ psql -h "$SRC_HOST" -U "$SRC_USER" -d "$SRC_DB" -tAc "SHOW server_version;" |
Destination:
|
1 2 3 |
docker run --rm -e PGPASSWORD="$DST_PASS" postgres:latest \ psql -h "$DST_HOST" -U "$DST_USER" -d "$DST_DB" -tAc "SHOW server_version;" |
Why this matters: Knowing the exact versions helps you pick the right postgres:<major> tag (e.g., postgres:15), so your client tools match the server. If your server enforces SSL (e.g., RDS), add sslmode=require via a connection string (the scripts do this automatically).
Step 2: Dump the database (no CREATE DATABASE, no roles)
What this does: Runs pg_dump inside a container that matches the source server version. We use custom format so we can do a parallel, selective restore later. We explicitly do not include CREATE DATABASE or any roles—this keeps the dump portable and safe to restore into an already-created DB.
Custom format (recommended):
|
1 2 3 4 5 6 7 8 9 |
mkdir -p backups docker run --rm -e PGPASSWORD="$SRC_PASS" \ -v "$PWD/backups":/backup \ postgres:15 \ pg_dump -h "$SRC_HOST" -U "$SRC_USER" -d "$SRC_DB" \ -F c -f "/backup/${SRC_DB}_$(date +%F).dump" \ --no-owner --no-privileges --blobs |
Plain SQL (optional):
|
1 2 3 4 5 6 7 |
docker run --rm -e PGPASSWORD="$SRC_PASS" \ -v "$PWD/backups":/backup \ postgres:15 \ pg_dump -h "$SRC_HOST" -U "$SRC_USER" -d "$SRC_DB" \ -F p -f "/backup/${SRC_DB}_$(date +%F).sql" \ --no-owner --no-privileges --blobs |
Flag explanations:
-F c= custom format (compressed, supportspg_restore --jobs)--no-owner --no-privileges= strip GRANT/OWNER; you’ll re-grant as needed- No
-C= noCREATE DATABASEin the output - Not using
pg_dumpall -g= roles are excluded on purpose
Step 3: Transfer the dump file
What this does: Copies your dump to the box from which you’ll run the restore. Use whatever you like—scp, rsync, S3, etc.
|
1 2 |
scp backups/mydb_2025-09-12.dump user@dest:/tmp/ |
Tips: For very large dumps, prefer a path with low latency to the DB (e.g., same region/AZ). The custom format is already compressed; for plain SQL, consider compressing before transfer.
Step 4: Prepare the destination
What this does: Ensures the destination database exists and optionally sets schema ownership so your app user owns the default schema. If the DB already exists, you can skip creation.
Create DB:
|
1 2 3 |
docker run --rm -e PGPASSWORD="$DST_PASS" postgres:17 \ createdb -h "$DST_HOST" -U "$DST_USER" "$DST_DB" |
(Optionally) set schema owner:
|
1 2 3 4 |
docker run --rm -e PGPASSWORD="$DST_PASS" postgres:17 \ psql -h "$DST_HOST" -U "$DST_USER" -d "$DST_DB" \ -c "ALTER SCHEMA public OWNER TO $DST_USER;" |
Why do this now? Since the dump does not contain CREATE DATABASE, the target DB must exist. Ownership adjustments ensure your app user can write to the schema as expected.
Step 5: Restore
What this does: Uses pg_restore inside a container that matches the destination server version. We restore with --jobs to parallelize larger databases. We also tell pg_restore not to change owners or privileges; apply the policy you want afterwards.
Custom format restore:
|
1 2 3 4 5 6 7 |
docker run --rm -e PGPASSWORD="$DST_PASS" \ -v "$PWD/backups":/backup \ postgres:17 \ pg_restore -h "$DST_HOST" -U "$DST_USER" -d "$DST_DB" \ --no-owner --no-privileges --disable-triggers --jobs=4 \ "/backup/mydb_2025-09-12.dump" |
Plain SQL restore:
|
1 2 3 4 5 6 |
docker run --rm -e PGPASSWORD="$DST_PASS" \ -v "$PWD/backups":/backup \ postgres:17 \ psql -h "$DST_HOST" -U "$DST_USER" -d "$DST_DB" \ -f "/backup/mydb_2025-09-12.sql" |
Flags to note:
--jobs=4= parallelize restore (tune for your CPU/IO)--disable-triggers= speed things up at the risk of more load; often OK for one-off migrations--no-owner --no-privileges= leave ownership/GRANTs alone; set them explicitly after
Automated Scripts (recommended)
Always check the repo for the latest:
https://github.com/reliablepenguin/rp_pg_utils
What these do:
- Detect the server’s major version and choose
postgres:<major>automatically - Use
~/.pgpassif present (mounted into the container), orPGPASSWORDif set - If neither is available, prompt once and pass the password to the container
- Use a libpq connection string with
connect_timeout=10andsslmode=require(tweakPGSSLMODEif your target doesn’t require SSL) - Avoid pseudo-TTY quirks and print verbose progress so you can see what’s happening
dump.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
#!/usr/bin/env bash set -eo pipefail [[ "${DEBUG:-}" == "1" ]] && set -x usage() { cat <<'EOF' Usage: dump.sh --host HOST --db DB --user USER [--port PORT] [--out FILE.dump] - Dumps in custom format (-F c) - No CREATE DATABASE or roles; strips ownership/privileges - Auto-selects postgres:<major> based on SOURCE server version - Uses ~/.pgpass (0600) or $PGPASSWORD; otherwise prompts once - Forces connect_timeout=10 and sslmode=require via connection string EOF } SRC_HOST=""; SRC_DB=""; SRC_USER=""; SRC_PORT="5432"; OUTFILE="" while [[ $# -gt 0 ]]; do case "$1" in --host) SRC_HOST="$2"; shift 2 ;; --db) SRC_DB="$2"; shift 2 ;; --user) SRC_USER="$2"; shift 2 ;; --port) SRC_PORT="$2"; shift 2 ;; --out) OUTFILE="$2"; shift 2 ;; -h|--help) usage; exit 0 ;; *) echo "Unknown arg: $1"; usage; exit 1 ;; esac done [[ -z "$SRC_HOST" || -z "$SRC_DB" || -z "$SRC_USER" ]] && { usage; exit 1; } mkdir -p backups OUTFILE="${OUTFILE:-backups/${SRC_DB}_$(date +%F).dump}" DOCKER_ARGS=(--rm) # no -t to avoid TTY quirks DOCKER_ENVS=(-e "PGCONNECT_TIMEOUT=${PGCONNECT_TIMEOUT:-10}") # Prefer ~/.pgpass if [[ -f "$HOME/.pgpass" ]]; then PERM=$(stat -c "%a" "$HOME/.pgpass" 2>/dev/null || echo "600") if [[ "$PERM" != "600" && "$PERM" != "400" ]]; then echo "Warning: ~/.pgpass should be chmod 600; current=$PERM (libpq may ignore it)." fi DOCKER_ARGS+=(-v "$HOME/.pgpass:/pgpass/.pgpass:ro") DOCKER_ENVS+=(-e PGPASSFILE=/pgpass/.pgpass) fi # If no pgpass and no env var, prompt once and use PGPASSWORD if [[ ! -f "$HOME/.pgpass" && -z "${PGPASSWORD:-}" ]]; then read -s -p "Password for $SRC_USER@$SRC_HOST: " PROMPT_PASS; echo DOCKER_ENVS+=(-e "PGPASSWORD=${PROMPT_PASS}") elif [[ -n "${PGPASSWORD:-}" ]]; then DOCKER_ENVS+=(-e "PGPASSWORD=${PGPASSWORD}") fi CONNECT_TIMEOUT="${CONNECT_TIMEOUT:-10}" SSL_MODE="${PGSSLMODE:-require}" CONNSTR="host=${SRC_HOST} port=${SRC_PORT} dbname=${SRC_DB} user=${SRC_USER} connect_timeout=${CONNECT_TIMEOUT} sslmode=${SSL_MODE}" probe_version() { docker run "${DOCKER_ARGS[@]}" "${DOCKER_ENVS[@]}" postgres:latest \ psql --no-psqlrc --no-password "${CONNSTR}" \ -tA -c "SHOW server_version_num;" 2>/dev/null || true } VER_NUM="$(probe_version | tr -d '[:space:]')" if ! [[ "$VER_NUM" =~ ^[0-9]+$ ]]; then if ! printf '%s\0' "${DOCKER_ENVS[@]}" | tr '\0' '\n' | grep -q '^-e PGPASSWORD='; then read -s -p "Password for $SRC_USER@$SRC_HOST: " PROMPT_PASS2; echo DOCKER_ENVS+=(-e "PGPASSWORD=${PROMPT_PASS2}") VER_NUM="$(probe_version | tr -d '[:space:]')" fi fi if [[ "$VER_NUM" =~ ^[0-9]+$ ]]; then SRC_MAJOR=$(( VER_NUM / 10000 )) else echo "Warning: could not determine source version; using postgres:latest" SRC_MAJOR="latest" fi docker run "${DOCKER_ARGS[@]}" "${DOCKER_ENVS[@]}" \ -v "$PWD/backups":/backup \ "postgres:${SRC_MAJOR}" \ pg_dump --no-password --verbose --dbname="${CONNSTR}" \ -F c -f "/backup/$(basename "$OUTFILE")" \ --no-owner --no-privileges --blobs echo "Dump complete: $OUTFILE (client image: postgres:${SRC_MAJOR})" |
restore.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
#!/usr/bin/env bash set -eo pipefail [[ "${DEBUG:-}" == "1" ]] && set -x usage() { cat <<'EOF' Usage: restore.sh --host HOST --db DB --user USER --file FILE.dump [--port PORT] [--create-db] - Restores a custom-format dump into an existing DB (or create with --create-db) - Auto-selects postgres:<major> based on DESTINATION server version - Uses ~/.pgpass (0600) or $PGPASSWORD; otherwise prompts once - Forces connect_timeout=10 and sslmode=require via connection string - Parallel restore (--jobs=4) EOF } DST_HOST=""; DST_DB=""; DST_USER=""; DST_PORT="5432"; DUMPFILE=""; CREATE_DB=0 while [[ $# -gt 0 ]]; do case "$1" in --host) DST_HOST="$2"; shift 2 ;; --db) DST_DB="$2"; shift 2 ;; --user) DST_USER="$2"; shift 2 ;; --file) DUMPFILE="$2"; shift 2 ;; --port) DST_PORT="$2"; shift 2 ;; --create-db) CREATE_DB=1; shift 1 ;; -h|--help) usage; exit 0 ;; *) echo "Unknown arg: $1"; usage; exit 1 ;; esac done [[ -z "$DST_HOST" || -z "$DST_DB" || -z "$DST_USER" || -z "$DUMPFILE" ]] && { usage; exit 1; } [[ -f "$DUMPFILE" ]] || { echo "Dump file not found: $DUMPFILE"; exit 1; } DOCKER_ARGS=(--rm -v "$PWD":/work) DOCKER_ENVS=(-e "PGCONNECT_TIMEOUT=${PGCONNECT_TIMEOUT:-10}") if [[ -f "$HOME/.pgpass" ]]; then PERM=$(stat -c "%a" "$HOME/.pgpass" 2>/dev/null || echo "600") if [[ "$PERM" != "600" && "$PERM" != "400" ]]; then echo "Warning: ~/.pgpass should be chmod 600; current=$PERM (libpq may ignore it)." fi DOCKER_ARGS+=(-v "$HOME/.pgpass:/pgpass/.pgpass:ro") DOCKER_ENVS+=(-e PGPASSFILE=/pgpass/.pgpass) fi if [[ ! -f "$HOME/.pgpass" && -z "${PGPASSWORD:-}" ]]; then read -s -p "Password for $DST_USER@$DST_HOST: " PROMPT_PASS; echo DOCKER_ENVS+=(-e "PGPASSWORD=${PROMPT_PASS}") elif [[ -n "${PGPASSWORD:-}" ]]; then DOCKER_ENVS+=(-e "PGPASSWORD=${PGPASSWORD}") fi CONNECT_TIMEOUT="${CONNECT_TIMEOUT:-10}" SSL_MODE="${PGSSLMODE:-require}" PROBE_CONNSTR="host=${DST_HOST} port=${DST_PORT} dbname=postgres user=${DST_USER} connect_timeout=${CONNECT_TIMEOUT} sslmode=${SSL_MODE}" RESTORE_CONNSTR="host=${DST_HOST} port=${DST_PORT} dbname=${DST_DB} user=${DST_USER} connect_timeout=${CONNECT_TIMEOUT} sslmode=${SSL_MODE}" probe_version() { docker run "${DOCKER_ARGS[@]}" "${DOCKER_ENVS[@]}" postgres:latest \ psql --no-psqlrc --no-password "${PROBE_CONNSTR}" \ -tA -c "SHOW server_version_num;" 2>/dev/null || true } VER_NUM="$(probe_version | tr -d '[:space:]')" if ! [[ "$VER_NUM" =~ ^[0-9]+$ ]]; then if ! printf '%s\0' "${DOCKER_ENVS[@]}" | tr '\0' '\n' | grep -q '^-e PGPASSWORD='; then read -s -p "Password for $DST_USER@$DST_HOST: " PROMPT_PASS2; echo DOCKER_ENVS+=(-e "PGPASSWORD=${PROMPT_PASS2}") VER_NUM="$(probe_version | tr -d '[:space:]')" fi fi if [[ "$VER_NUM" =~ ^[0-9]+$ ]]; then DST_MAJOR=$(( VER_NUM / 10000 )) else echo "Warning: could not determine destination version; using postgres:latest" DST_MAJOR="latest" fi # Create DB if requested (use psql for consistent conn settings) if [[ $CREATE_DB -eq 1 ]]; then docker run "${DOCKER_ARGS[@]}" "${DOCKER_ENVS[@]}" "postgres:${DST_MAJOR}" \ psql --no-psqlrc --no-password "${PROBE_CONNSTR}" \ -v ON_ERROR_STOP=1 -tA -c "CREATE DATABASE \"${DST_DB}\";" || true fi docker run "${DOCKER_ARGS[@]}" "${DOCKER_ENVS[@]}" "postgres:${DST_MAJOR}" \ pg_restore --no-password --verbose --jobs=4 --dbname="${RESTORE_CONNSTR}" \ "/work/$(basename "$DUMPFILE")" echo "Restore complete: ${DST_DB}@${DST_HOST} (client image: postgres:${DST_MAJOR})" |
Usage examples
What this shows: Typical invocations. The scripts prompt for a password only when neither ~/.pgpass nor PGPASSWORD is available. They print which client image they used and where the dump went.
Dump
|
1 2 |
./dump.sh --host db.acme.example.com --db acme_db --user acme_db_user --out backups/acme_db_$(date +%F).dump |
Restore
|
1 2 |
./restore.sh --host db.acme.example.com --db acme_db --user acme_db_user --file backups/acme_db_2025-09-12.dump --create-db |
~/.pgpass quick reference
What/why: A local credentials file that libpq (Postgres client library) reads automatically. It prevents interactive prompts and keeps passwords out of your shell history.
- Location:
~/.pgpass(permissions 0600) - Format:
host:port:database:username:password
Example (AWS RDS-style endpoint):
|
1 2 |
acme-db.cluster-xyz123.ap-southeast-2.rds.amazonaws.com:5432:acme_db:acme_db_user:SuperSecret! |
|
1 2 |
chmod 600 ~/.pgpass |
Troubleshooting (what’s going on and how to fix)
“It hangs on connect.”
We set connect_timeout=10 in the connection string, so hard hangs usually mean network or SSL mismatches. Verify security groups/NACL, DNS, and SSL policy.
Raw test:
|
1 2 3 4 5 6 7 8 9 |
docker run --rm \ -e PGPASSFILE=/pgpass/.pgpass \ -e PGCONNECT_TIMEOUT=10 \ -v "$HOME/.pgpass:/pgpass/.pgpass:ro" \ postgres:latest \ psql --no-psqlrc --no-password \ "host=db.acme.example.com port=5432 dbname=acme_db user=acme_db_user connect_timeout=10 sslmode=require" \ -c "\conninfo" |
“Auth fails despite .pgpass.”
Libpq ignores .pgpass unless it’s 0600. The line must match exactly (host, port, db, user). For RDS, ensure you’re using the correct endpoint (cluster vs instance endpoint can differ).
“Restore is slow.”
Increase --jobs and ensure you’re on a machine close to the DB (same region/AZ). Watch for constraints/locks—--disable-triggers can help but increases load.
“Ownership/GRANTs missing after restore.”
By design we use --no-owner --no-privileges. Apply your intended grants/roles explicitly post-restore—that’s safer and more repeatable.
Keep current: GitHub repo
We’ll keep improving these utilities over time. For the latest versions and any fixes, get them from the repo:
