Migrate a PostgreSQL database between two VPS with pg_dump

Move an entire PostgreSQL database (schema and data) from a source VPS to a target VPS through your local machine: dump with pg_dump, restore in a single transaction, verify row counts, then cut over and roll back safely.

Updated: Sep 21, 202610 min read

PostgreSQL VPS Backup SSH

On this page

What you need
Quick reference
Migration model
1. Prepare the tools
2. Declare the connections, passwords kept separate
3. Test both connections
4. Dump the source database (read-only)
5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)
6. Restore into the target database
7. Verify: exact row counts on both sides
8. Cutover: point the backend at the new database
Automation script (one shot)
Troubleshooting
Rollback
Related guides

This guide moves an entire PostgreSQL database (schema and data) from a source VPS to a target VPS, relayed through your local machine (Windows + Git Bash) with pg_dump and psql. Use it when splitting the database onto a dedicated production VPS, switching providers, or merging/splitting infrastructure.

The source is only ever READ - nothing is written to it.
The restore runs in a single transaction -> any error rolls back cleanly, so the target database is never left half-migrated.
Idempotent: run it as many times as you like and you end up with a clean state that matches the dump exactly.

Best for small to medium databases on the same PostgreSQL major version (e.g. 16 -> 16). If pg_dump is newer than the target server (e.g. a 17 client restoring into a 16 server), also do step 5. Media on object storage (S3/B2) lives outside the database and is not affected.

What you need

Fill in your real values for these placeholders:

Placeholder	Meaning
`<source-host>` `<source-port>`	Host + port of the source Postgres (default 5432)
`<source-user>` `<source-db>` `<source-password>`	Source account + database name
`<target-host>` `<target-port>`	Host + port of the target Postgres (over an SSH tunnel this is `localhost:<tunnel-port>`)
`<target-user>` `<target-db>` `<target-password>`	Target account + database name (create the empty database beforehand)
`<pg-version>`	PostgreSQL client version on Windows (e.g. `17`)

Run every command in Git Bash, NOT PowerShell - export is bash syntax.

The target database is usually reachable only from inside its VPS, so open an SSH tunnel to it first (details in Connect to PostgreSQL on a VPS through an SSH tunnel). Each tunnel runs in its own terminal and stays open for the whole migration; replace <username>, <new-server-ip>, <old-server-ip> with the SSH user and IP of each VPS:

# Tunnel to the target VPS: local port 5433 -> Postgres (5432) on the target VPS
ssh -L 5433:localhost:5432 <username>@<new-server-ip> -i ~/.ssh/id_ed25519 -N

# If the source database is also only reachable from inside its VPS: open a second tunnel on another port
ssh -L 5434:localhost:5432 <username>@<old-server-ip> -i ~/.ssh/id_ed25519 -N

With the example above, <target-host>:<target-port> is localhost:5433 (and the source is localhost:5434 if you opened the second tunnel).

Quick reference

# 0. Add the PostgreSQL client to PATH (set <pg-version> correctly, e.g. 17)
export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"

# 1. Declare the connections - keep passwords SEPARATE, never inside the URL (avoids broken URL parsing)
export SOURCE_DATABASE_URL='postgresql://<source-user>@<source-host>:<source-port>/<source-db>?sslmode=require'
export SOURCE_DB_PASSWORD='<source-password>'
export TARGET_DATABASE_URL='postgresql://<target-user>@<target-host>:<target-port>/<target-db>?sslmode=require'
export TARGET_DB_PASSWORD='<target-password>'

# 2. Dump (plain SQL) -> strip transaction_timeout (if pg_dump is newer than the target server) -> restore
STAMP=$(date +%Y%m%d-%H%M%S); F="dump_$STAMP.sql"
PGPASSWORD="$SOURCE_DB_PASSWORD" pg_dump "$SOURCE_DATABASE_URL" \
  --format=plain --no-owner --no-privileges --clean --if-exists --file="$F"
grep -v '^SET transaction_timeout' "$F" > "$F.tmp" && mv "$F.tmp" "$F"
PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" \
  --set=ON_ERROR_STOP=on --single-transaction --file="$F"

Migration model

+-------------+   pg_dump    +---------------+  psql restore  +-------------+
|  Source VPS | ---(read)--> | Local machine | -------------> |  Target VPS |
|  Postgres   |              |  (.sql file)  |                |  Postgres   |
+-------------+              +---------------+                +-------------+

The dump travels between the two VPS through your local machine: pg_dump pulls the data from the source into a .sql file, then psql pushes that file to the target.

1. Prepare the tools

You need pg_dump + psql. On Windows they usually already exist in C:\Program Files\PostgreSQL\<pg-version>\bin. Add them to PATH for the current Git Bash session:

export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"
pg_dump --version && psql --version

Use a pg_dump version >= the target server. Same major version (16 -> 16) is the safest.

2. Declare the connections, passwords kept separate

If the password contains @ % $ / ? and you put it straight into the URL, parsing breaks (an @ in the password collides with the @ before the host; %xx is read as percent-encoding). The reliable approach: the URL holds no password, and the password goes into a *_DB_PASSWORD variable (libpq reads it raw, no parsing).

export SOURCE_DATABASE_URL='postgresql://<source-user>@<source-host>:<source-port>/<source-db>?sslmode=require'
export SOURCE_DB_PASSWORD='<source-password>'
export TARGET_DATABASE_URL='postgresql://<target-user>@<target-host>:<target-port>/<target-db>?sslmode=require'
export TARGET_DB_PASSWORD='<target-password>'

The URL is just user@host, with NO :password part. Use single quotes '...' so the shell does not interpret special characters. Drop ?sslmode=require if the database does not have SSL enabled.

3. Test both connections

echo "== SOURCE ==" && PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc "SELECT current_database();"
echo "== TARGET ==" && PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -tAc "SELECT current_database();"

If both print their database name, the connections are fine. Connection refused on the target -> check the SSH tunnel.

4. Dump the source database (read-only)

STAMP=$(date +%Y%m%d-%H%M%S)
F="dump_$STAMP.sql"
PGPASSWORD="$SOURCE_DB_PASSWORD" pg_dump "$SOURCE_DATABASE_URL" \
  --format=plain \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  --file="$F"
ls -lh "$F"

Flag	Meaning
`--format=plain`	Output plain SQL (easy to read/audit; restored with `psql`)
`--no-owner`	Skip owner assignments (users usually differ between the two VPS)
`--no-privileges`	Skip GRANT/REVOKE (avoids missing-role errors)
`--clean --if-exists`	Prepend `DROP ... IF EXISTS` -> safe to re-run (idempotent)

5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)

pg_dump 17+ writes a SET transaction_timeout = 0; line at the top of the dump - servers older than 17 do not know this parameter and the restore fails. It only means "no timeout limit", so removing it is safe:

grep -v '^SET transaction_timeout' "$F" > "$F.tmp" && mv "$F.tmp" "$F"

Skip this step if pg_dump is the same version as the target server.

6. Restore into the target database

PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" \
  --set=ON_ERROR_STOP=on \
  --single-transaction \
  --file="$F"

--single-transaction: wraps everything in one transaction -> any error rolls it all back, so the database is never left half-restored.
--set=ON_ERROR_STOP=on: stop at the first error.

7. Verify: exact row counts on both sides

This block fetches the table list (with schema) on its own, then compares the real row count (count(*)) of every table on both sides:

TABLES=$(PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc \
  "SELECT schemaname||'.'||relname FROM pg_stat_user_tables ORDER BY 1;")

printf "%-40s %12s %12s\n" "table" "source" "target"
for t in $TABLES; do
  s=$(PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc "SELECT count(*) FROM $t;")
  d=$(PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -tAc "SELECT count(*) FROM $t;")
  flag=""; [ "$s" != "$d" ] && flag="  <-- MISMATCH"
  printf "%-40s %12s %12s%s\n" "$t" "$s" "$d" "$flag"
done

It passes when every row has source = target and no line is flagged <-- MISMATCH.

Building the table list as schema.table also catches tables outside the public schema - for example projects using Drizzle ORM keep __drizzle_migrations in the drizzle schema.

8. Cutover: point the backend at the new database

Edit DATABASE_URL in the backend's env file:

DATABASE_URL=postgresql://<target-user>:<target-password-encoded>@<target-host>:<target-port>/<target-db>?sslmode=require

Here the backend parses the URL string, so the password MUST be percent-encoded if it has special characters - unlike step 2, which passes it raw through PGPASSWORD.

Character	Write in the URL
`%`	`%25` (encode this one first)
`@`	`%40`
`/`	`%2F`
`?`	`%3F`
`#`	`%23`
`:`	`%3A`

The characters $ ! - _ can stay as they are. Example: the password ab%cd@ef is written in the URL as ab%25cd%40ef.

Restart the backend and check its health/ready endpoint to confirm it is connected to the new database.

Automation script (one shot)

Save it as migrate-db.sh next to where you want the dumps stored. It reads the 4 environment variables from step 2 (password-less URLs + *_DB_PASSWORD).

#!/usr/bin/env bash
# Migrate a PostgreSQL DB between two servers via pg_dump (plain SQL) -> psql.
# Source is READ-ONLY. Restore runs in a single transaction.
#
#   export SOURCE_DATABASE_URL='postgresql://<user>@<host>:<port>/<db>?sslmode=require'
#   export SOURCE_DB_PASSWORD='...'
#   export TARGET_DATABASE_URL='postgresql://<user>@<host>:<port>/<db>?sslmode=require'
#   export TARGET_DB_PASSWORD='...'
#   ./migrate-db.sh            # asks before touching the target
#   CONFIRM=1 ./migrate-db.sh  # non-interactive
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DUMP_DIR="${DUMP_DIR:-$SCRIPT_DIR/dumps}"
TS="$(date +%Y%m%d-%H%M%S)"
DUMP_FILE="$DUMP_DIR/dump_$TS.sql"

die() { echo "ERROR: $*" >&2; exit 1; }
redact() { printf '%s' "$1" | sed -E 's#(://[^:/@]+:)[^@]*@#\1***@#'; }

command -v pg_dump >/dev/null 2>&1 || die "pg_dump not found in PATH"
command -v psql    >/dev/null 2>&1 || die "psql not found in PATH"
: "${SOURCE_DATABASE_URL:?Set SOURCE_DATABASE_URL}"
: "${TARGET_DATABASE_URL:?Set TARGET_DATABASE_URL}"
mkdir -p "$DUMP_DIR"

echo "==> Source (read-only): $(redact "$SOURCE_DATABASE_URL")"
echo "==> Target (overwrite): $(redact "$TARGET_DATABASE_URL")"
echo "==> Dump file:          $DUMP_FILE"

if [ "${CONFIRM:-0}" != "1" ]; then
  echo "This DUMPS the source (read-only) and OVERWRITES matching tables on the TARGET."
  printf "Type 'yes' to continue: "; read -r ans
  [ "$ans" = "yes" ] || die "Aborted by user"
fi

echo "==> [1/3] Dumping source ..."
if [ -n "${SOURCE_DB_PASSWORD:-}" ]; then export PGPASSWORD="$SOURCE_DB_PASSWORD"; fi
pg_dump "$SOURCE_DATABASE_URL" --format=plain --no-owner --no-privileges --clean --if-exists --file="$DUMP_FILE"
unset PGPASSWORD
# pg_dump 17+ emits `SET transaction_timeout` that servers < 17 reject; safe to drop.
if grep -q '^SET transaction_timeout' "$DUMP_FILE"; then
  grep -v '^SET transaction_timeout' "$DUMP_FILE" > "$DUMP_FILE.tmp" && mv "$DUMP_FILE.tmp" "$DUMP_FILE"
fi

echo "==> [2/3] Restoring into target ..."
if [ -n "${TARGET_DB_PASSWORD:-}" ]; then export PGPASSWORD="$TARGET_DB_PASSWORD"; fi
psql "$TARGET_DATABASE_URL" --set=ON_ERROR_STOP=on --single-transaction --quiet --file="$DUMP_FILE"

echo "==> [3/3] Row counts on target:"
psql "$TARGET_DATABASE_URL" --quiet --command='DO $$ DECLARE r record; BEGIN FOR r IN SELECT schemaname, relname FROM pg_stat_user_tables LOOP EXECUTE format($q$ANALYZE %I.%I$q$, r.schemaname, r.relname); END LOOP; END $$;'
psql "$TARGET_DATABASE_URL" --quiet --command="SELECT schemaname||'.'||relname AS table, n_live_tup AS rows FROM pg_stat_user_tables ORDER BY 1;"
unset PGPASSWORD

echo "Done. Dump kept at: $DUMP_FILE"

The dump contains real data (possibly password hashes and emails). Never commit dump files to git - add dumps/ and *.sql to .gitignore.

If you keep the .sh script in a repo on Windows, add *.sh text eol=lf to .gitattributes so CRLF line endings do not break it when it runs on Linux.

Troubleshooting

Symptom	Cause	Fix
`export: command not found`	You are typing in PowerShell	Open Git Bash (the script is bash). Or in PowerShell use `$env:VAR='...'`
Connection fails even with the right password	The password contains `@ % $ /` and sits inside the URL -> parsing breaks	Keep the URL password-less + use `PGPASSWORD` (step 2)
`unrecognized configuration parameter "transaction_timeout"`	Dumped with pg_dump 17+, restoring into a server < 17	Strip that line (step 5)
`permission denied to create extension "..."`	The target user is not allowed to create extensions	Have a superuser create it on the target database first: `CREATE EXTENSION IF NOT EXISTS <extension-name>;` (e.g. `citext`, `unaccent`, `uuid-ossp`), then restore again
`WARNING: permission denied to analyze "pg_..."`	A global `ANALYZE;` touches system catalogs and the user is not a superuser	Harmless - the app's tables still get analyzed. Or analyze user tables only (the script already does)
`relation "..." does not exist` during verify	The table lives in a schema other than `public` (e.g. `drizzle`)	Use the full `schema.table` name (the verify block in step 7 already handles this)
`SSL connection required`	The database enforces SSL	Append `?sslmode=require` to the connection string
`Connection refused` on the target	No SSH tunnel to the target VPS yet	Open the tunnel first (see What you need)

Rollback

The target database only contains data from this migration, so rolling back is very safe: re-run the script (idempotent), or wipe the public schema on the target and restore again from step 6:

PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

DROP SCHEMA ... CASCADE deletes everything. Only ever run it against TARGET_DATABASE_URL, and double-check that variable before pressing Enter.

The source is never touched, so if you have already cut over (step 8) and need to go back, just point the backend's DATABASE_URL at the source database and restart. Keep in mind that data written to the new database after the cutover will not exist on the source.

Clone a PostgreSQL database from a VPS to local Windows - pull a copy onto your dev machine (custom format, changing the local port)
Connect to PostgreSQL on a VPS through an SSH tunnel - open a tunnel to the database on a VPS
Set up PostgreSQL on a VPS securely - bring up PostgreSQL on the target VPS before migrating

Migrate a PostgreSQL database between two VPS with pg_dump

Updated: Sep 21, 202610 min read

PostgreSQL VPS Backup SSH

On this page

What you need
Quick reference
Migration model
1. Prepare the tools
2. Declare the connections, passwords kept separate
3. Test both connections
4. Dump the source database (read-only)
5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)
6. Restore into the target database
7. Verify: exact row counts on both sides
8. Cutover: point the backend at the new database
Automation script (one shot)
Troubleshooting
Rollback
Related guides

The source is only ever READ - nothing is written to it.
The restore runs in a single transaction -> any error rolls back cleanly, so the target database is never left half-migrated.
Idempotent: run it as many times as you like and you end up with a clean state that matches the dump exactly.

What you need

Fill in your real values for these placeholders:

Placeholder	Meaning
`<source-host>` `<source-port>`	Host + port of the source Postgres (default 5432)
`<source-user>` `<source-db>` `<source-password>`	Source account + database name
`<target-host>` `<target-port>`	Host + port of the target Postgres (over an SSH tunnel this is `localhost:<tunnel-port>`)
`<target-user>` `<target-db>` `<target-password>`	Target account + database name (create the empty database beforehand)
`<pg-version>`	PostgreSQL client version on Windows (e.g. `17`)

Run every command in Git Bash, NOT PowerShell - export is bash syntax.

# Tunnel to the target VPS: local port 5433 -> Postgres (5432) on the target VPS
ssh -L 5433:localhost:5432 <username>@<new-server-ip> -i ~/.ssh/id_ed25519 -N

# If the source database is also only reachable from inside its VPS: open a second tunnel on another port
ssh -L 5434:localhost:5432 <username>@<old-server-ip> -i ~/.ssh/id_ed25519 -N

With the example above, <target-host>:<target-port> is localhost:5433 (and the source is localhost:5434 if you opened the second tunnel).

Quick reference

# 0. Add the PostgreSQL client to PATH (set <pg-version> correctly, e.g. 17)
export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"

# 1. Declare the connections - keep passwords SEPARATE, never inside the URL (avoids broken URL parsing)
export SOURCE_DATABASE_URL='postgresql://<source-user>@<source-host>:<source-port>/<source-db>?sslmode=require'
export SOURCE_DB_PASSWORD='<source-password>'
export TARGET_DATABASE_URL='postgresql://<target-user>@<target-host>:<target-port>/<target-db>?sslmode=require'
export TARGET_DB_PASSWORD='<target-password>'

# 2. Dump (plain SQL) -> strip transaction_timeout (if pg_dump is newer than the target server) -> restore
STAMP=$(date +%Y%m%d-%H%M%S); F="dump_$STAMP.sql"
PGPASSWORD="$SOURCE_DB_PASSWORD" pg_dump "$SOURCE_DATABASE_URL" \
  --format=plain --no-owner --no-privileges --clean --if-exists --file="$F"
grep -v '^SET transaction_timeout' "$F" > "$F.tmp" && mv "$F.tmp" "$F"
PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" \
  --set=ON_ERROR_STOP=on --single-transaction --file="$F"

Migration model

+-------------+   pg_dump    +---------------+  psql restore  +-------------+
|  Source VPS | ---(read)--> | Local machine | -------------> |  Target VPS |
|  Postgres   |              |  (.sql file)  |                |  Postgres   |
+-------------+              +---------------+                +-------------+

The dump travels between the two VPS through your local machine: pg_dump pulls the data from the source into a .sql file, then psql pushes that file to the target.

1. Prepare the tools

You need pg_dump + psql. On Windows they usually already exist in C:\Program Files\PostgreSQL\<pg-version>\bin. Add them to PATH for the current Git Bash session:

export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"
pg_dump --version && psql --version

Use a pg_dump version >= the target server. Same major version (16 -> 16) is the safest.

2. Declare the connections, passwords kept separate

export SOURCE_DATABASE_URL='postgresql://<source-user>@<source-host>:<source-port>/<source-db>?sslmode=require'
export SOURCE_DB_PASSWORD='<source-password>'
export TARGET_DATABASE_URL='postgresql://<target-user>@<target-host>:<target-port>/<target-db>?sslmode=require'
export TARGET_DB_PASSWORD='<target-password>'

The URL is just user@host, with NO :password part. Use single quotes '...' so the shell does not interpret special characters. Drop ?sslmode=require if the database does not have SSL enabled.

3. Test both connections

echo "== SOURCE ==" && PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc "SELECT current_database();"
echo "== TARGET ==" && PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -tAc "SELECT current_database();"

If both print their database name, the connections are fine. Connection refused on the target -> check the SSH tunnel.

4. Dump the source database (read-only)

STAMP=$(date +%Y%m%d-%H%M%S)
F="dump_$STAMP.sql"
PGPASSWORD="$SOURCE_DB_PASSWORD" pg_dump "$SOURCE_DATABASE_URL" \
  --format=plain \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  --file="$F"
ls -lh "$F"

Flag	Meaning
`--format=plain`	Output plain SQL (easy to read/audit; restored with `psql`)
`--no-owner`	Skip owner assignments (users usually differ between the two VPS)
`--no-privileges`	Skip GRANT/REVOKE (avoids missing-role errors)
`--clean --if-exists`	Prepend `DROP ... IF EXISTS` -> safe to re-run (idempotent)

5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)

grep -v '^SET transaction_timeout' "$F" > "$F.tmp" && mv "$F.tmp" "$F"

Skip this step if pg_dump is the same version as the target server.

6. Restore into the target database

PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" \
  --set=ON_ERROR_STOP=on \
  --single-transaction \
  --file="$F"

--single-transaction: wraps everything in one transaction -> any error rolls it all back, so the database is never left half-restored.
--set=ON_ERROR_STOP=on: stop at the first error.

7. Verify: exact row counts on both sides

This block fetches the table list (with schema) on its own, then compares the real row count (count(*)) of every table on both sides:

TABLES=$(PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc \
  "SELECT schemaname||'.'||relname FROM pg_stat_user_tables ORDER BY 1;")

printf "%-40s %12s %12s\n" "table" "source" "target"
for t in $TABLES; do
  s=$(PGPASSWORD="$SOURCE_DB_PASSWORD" psql "$SOURCE_DATABASE_URL" -tAc "SELECT count(*) FROM $t;")
  d=$(PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -tAc "SELECT count(*) FROM $t;")
  flag=""; [ "$s" != "$d" ] && flag="  <-- MISMATCH"
  printf "%-40s %12s %12s%s\n" "$t" "$s" "$d" "$flag"
done

It passes when every row has source = target and no line is flagged <-- MISMATCH.

Building the table list as schema.table also catches tables outside the public schema - for example projects using Drizzle ORM keep __drizzle_migrations in the drizzle schema.

8. Cutover: point the backend at the new database

Edit DATABASE_URL in the backend's env file:

DATABASE_URL=postgresql://<target-user>:<target-password-encoded>@<target-host>:<target-port>/<target-db>?sslmode=require

Here the backend parses the URL string, so the password MUST be percent-encoded if it has special characters - unlike step 2, which passes it raw through PGPASSWORD.

Character	Write in the URL
`%`	`%25` (encode this one first)
`@`	`%40`
`/`	`%2F`
`?`	`%3F`
`#`	`%23`
`:`	`%3A`

The characters $ ! - _ can stay as they are. Example: the password ab%cd@ef is written in the URL as ab%25cd%40ef.

Restart the backend and check its health/ready endpoint to confirm it is connected to the new database.

Automation script (one shot)

Save it as migrate-db.sh next to where you want the dumps stored. It reads the 4 environment variables from step 2 (password-less URLs + *_DB_PASSWORD).

#!/usr/bin/env bash
# Migrate a PostgreSQL DB between two servers via pg_dump (plain SQL) -> psql.
# Source is READ-ONLY. Restore runs in a single transaction.
#
#   export SOURCE_DATABASE_URL='postgresql://<user>@<host>:<port>/<db>?sslmode=require'
#   export SOURCE_DB_PASSWORD='...'
#   export TARGET_DATABASE_URL='postgresql://<user>@<host>:<port>/<db>?sslmode=require'
#   export TARGET_DB_PASSWORD='...'
#   ./migrate-db.sh            # asks before touching the target
#   CONFIRM=1 ./migrate-db.sh  # non-interactive
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DUMP_DIR="${DUMP_DIR:-$SCRIPT_DIR/dumps}"
TS="$(date +%Y%m%d-%H%M%S)"
DUMP_FILE="$DUMP_DIR/dump_$TS.sql"

die() { echo "ERROR: $*" >&2; exit 1; }
redact() { printf '%s' "$1" | sed -E 's#(://[^:/@]+:)[^@]*@#\1***@#'; }

command -v pg_dump >/dev/null 2>&1 || die "pg_dump not found in PATH"
command -v psql    >/dev/null 2>&1 || die "psql not found in PATH"
: "${SOURCE_DATABASE_URL:?Set SOURCE_DATABASE_URL}"
: "${TARGET_DATABASE_URL:?Set TARGET_DATABASE_URL}"
mkdir -p "$DUMP_DIR"

echo "==> Source (read-only): $(redact "$SOURCE_DATABASE_URL")"
echo "==> Target (overwrite): $(redact "$TARGET_DATABASE_URL")"
echo "==> Dump file:          $DUMP_FILE"

if [ "${CONFIRM:-0}" != "1" ]; then
  echo "This DUMPS the source (read-only) and OVERWRITES matching tables on the TARGET."
  printf "Type 'yes' to continue: "; read -r ans
  [ "$ans" = "yes" ] || die "Aborted by user"
fi

echo "==> [1/3] Dumping source ..."
if [ -n "${SOURCE_DB_PASSWORD:-}" ]; then export PGPASSWORD="$SOURCE_DB_PASSWORD"; fi
pg_dump "$SOURCE_DATABASE_URL" --format=plain --no-owner --no-privileges --clean --if-exists --file="$DUMP_FILE"
unset PGPASSWORD
# pg_dump 17+ emits `SET transaction_timeout` that servers < 17 reject; safe to drop.
if grep -q '^SET transaction_timeout' "$DUMP_FILE"; then
  grep -v '^SET transaction_timeout' "$DUMP_FILE" > "$DUMP_FILE.tmp" && mv "$DUMP_FILE.tmp" "$DUMP_FILE"
fi

echo "==> [2/3] Restoring into target ..."
if [ -n "${TARGET_DB_PASSWORD:-}" ]; then export PGPASSWORD="$TARGET_DB_PASSWORD"; fi
psql "$TARGET_DATABASE_URL" --set=ON_ERROR_STOP=on --single-transaction --quiet --file="$DUMP_FILE"

echo "==> [3/3] Row counts on target:"
psql "$TARGET_DATABASE_URL" --quiet --command='DO $$ DECLARE r record; BEGIN FOR r IN SELECT schemaname, relname FROM pg_stat_user_tables LOOP EXECUTE format($q$ANALYZE %I.%I$q$, r.schemaname, r.relname); END LOOP; END $$;'
psql "$TARGET_DATABASE_URL" --quiet --command="SELECT schemaname||'.'||relname AS table, n_live_tup AS rows FROM pg_stat_user_tables ORDER BY 1;"
unset PGPASSWORD

echo "Done. Dump kept at: $DUMP_FILE"

The dump contains real data (possibly password hashes and emails). Never commit dump files to git - add dumps/ and *.sql to .gitignore.

If you keep the .sh script in a repo on Windows, add *.sh text eol=lf to .gitattributes so CRLF line endings do not break it when it runs on Linux.

Troubleshooting

Symptom	Cause	Fix
`export: command not found`	You are typing in PowerShell	Open Git Bash (the script is bash). Or in PowerShell use `$env:VAR='...'`
Connection fails even with the right password	The password contains `@ % $ /` and sits inside the URL -> parsing breaks	Keep the URL password-less + use `PGPASSWORD` (step 2)
`unrecognized configuration parameter "transaction_timeout"`	Dumped with pg_dump 17+, restoring into a server < 17	Strip that line (step 5)
`permission denied to create extension "..."`	The target user is not allowed to create extensions	Have a superuser create it on the target database first: `CREATE EXTENSION IF NOT EXISTS <extension-name>;` (e.g. `citext`, `unaccent`, `uuid-ossp`), then restore again
`WARNING: permission denied to analyze "pg_..."`	A global `ANALYZE;` touches system catalogs and the user is not a superuser	Harmless - the app's tables still get analyzed. Or analyze user tables only (the script already does)
`relation "..." does not exist` during verify	The table lives in a schema other than `public` (e.g. `drizzle`)	Use the full `schema.table` name (the verify block in step 7 already handles this)
`SSL connection required`	The database enforces SSL	Append `?sslmode=require` to the connection string
`Connection refused` on the target	No SSH tunnel to the target VPS yet	Open the tunnel first (see What you need)

Rollback

The target database only contains data from this migration, so rolling back is very safe: re-run the script (idempotent), or wipe the public schema on the target and restore again from step 6:

PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

DROP SCHEMA ... CASCADE deletes everything. Only ever run it against TARGET_DATABASE_URL, and double-check that variable before pressing Enter.

Clone a PostgreSQL database from a VPS to local Windows - pull a copy onto your dev machine (custom format, changing the local port)
Connect to PostgreSQL on a VPS through an SSH tunnel - open a tunnel to the database on a VPS
Set up PostgreSQL on a VPS securely - bring up PostgreSQL on the target VPS before migrating

Migrate a PostgreSQL database between two VPS with pg_dump

What you need

Quick reference

Migration model

1. Prepare the tools

2. Declare the connections, passwords kept separate

3. Test both connections

4. Dump the source database (read-only)

5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)

6. Restore into the target database

7. Verify: exact row counts on both sides

8. Cutover: point the backend at the new database

Automation script (one shot)

Troubleshooting

Rollback

Connect to PostgreSQL on a VPS through an SSH tunnel

Tune VPS performance: swap, PostgreSQL and the DB pool

Fix Docker containers that cannot reach PostgreSQL on a VPS

Migrate a PostgreSQL database between two VPS with pg_dump

What you need

Quick reference

Migration model

1. Prepare the tools

2. Declare the connections, passwords kept separate

3. Test both connections

4. Dump the source database (read-only)

5. Strip SET transaction_timeout (only if pg_dump is newer than the target server)

6. Restore into the target database

7. Verify: exact row counts on both sides

8. Cutover: point the backend at the new database

Automation script (one shot)

Troubleshooting

Rollback

Connect to PostgreSQL on a VPS through an SSH tunnel

Tune VPS performance: swap, PostgreSQL and the DB pool

Fix Docker containers that cannot reach PostgreSQL on a VPS

Related articles

Connect to PostgreSQL on a VPS through an SSH tunnel

Tune VPS performance: swap, PostgreSQL and the DB pool

Fix Docker containers that cannot reach PostgreSQL on a VPS

Related articles

Connect to PostgreSQL on a VPS through an SSH tunnel

Tune VPS performance: swap, PostgreSQL and the DB pool

Fix Docker containers that cannot reach PostgreSQL on a VPS