Migrate database PostgreSQL giữa hai VPS bằng pg_dump và psql
Di chuyển toàn bộ một database PostgreSQL (schema lẫn data) từ VPS nguồn sang VPS đích qua máy local: dump bằng pg_dump, restore trong một transaction, verify số dòng, cutover và rollback an toàn.
Mục lục
- Cần chuẩn bị
- Tóm tắt nhanh
- Mô hình di chuyển
- 1. Chuẩn bị tool
- 2. Khai báo connection, mật khẩu để riêng
- 3. Test kết nối hai đầu
- 4. Dump database nguồn (chỉ đọc)
- 5. Strip SET transaction_timeout (chỉ khi pg_dump mới hơn server đích)
- 6. Restore vào database đích
- 7. Verify: đếm số dòng chính xác hai đầu
- 8. Cutover: trỏ backend sang database mới
- Script tự động hóa (chạy một phát)
- Xử lý sự cố
- Rollback
- Liên quan
Bài này di chuyển toàn bộ một database PostgreSQL (cả schema lẫn data) từ VPS nguồn
sang VPS đích, trung chuyển qua máy local (Windows + Git Bash) bằng pg_dump và
psql. Dùng khi tách database sang VPS production riêng, đổi nhà cung cấp, hoặc
gộp/tách hạ tầng.
- Nguồn chỉ bị ĐỌC - không ghi gì lên nguồn.
- Restore chạy trong một transaction -> lỗi là rollback sạch, không để database đích nửa vời.
- Idempotent: chạy lại bao nhiêu lần cũng ra trạng thái sạch, đúng bằng bản dump.
Phù hợp database nhỏ tới vừa, cùng major version PostgreSQL (ví dụ 16 -> 16). Nếu
pg_dump mới hơn server đích (ví dụ client 17 dump rồi restore vào server 16) thì làm
thêm bước 5. Media trên object storage (S3/B2) nằm ngoài database nên không bị ảnh hưởng.
Cần chuẩn bị
Điền giá trị thật của bạn vào các placeholder sau:
| Placeholder | Ý nghĩa |
|---|---|
<source-host> <source-port> | Host + port Postgres nguồn (mặc định 5432) |
<source-user> <source-db> <source-password> | Tài khoản + tên database nguồn |
<target-host> <target-port> | Host + port Postgres đích (nếu nối qua SSH tunnel thì là localhost:<port-tunnel>) |
<target-user> <target-db> <target-password> | Tài khoản + tên database đích (nên tạo sẵn database rỗng trước) |
<pg-version> | Phiên bản PostgreSQL client trên Windows (ví dụ 17) |
Chạy mọi lệnh bằng Git Bash, KHÔNG phải PowerShell - export là cú pháp bash.
Database đích thường chỉ cho truy cập nội bộ VPS, nên phải mở SSH tunnel tới nó
trước (chi tiết xem bài
Kết nối PostgreSQL trên VPS qua SSH Tunnel từ máy local).
Mỗi tunnel chạy trong một terminal riêng và giữ mở suốt quá trình migrate; thay
<username>, <new-server-ip>, <old-server-ip> bằng user SSH và IP của từng VPS:
# Tunnel tới VPS đích: port 5433 local -> Postgres (5432) của VPS đích
ssh -L 5433:localhost:5432 <username>@<new-server-ip> -i ~/.ssh/id_ed25519 -N
# Nếu database nguồn cũng chỉ truy cập được từ nội bộ VPS: mở thêm tunnel ở port khác
ssh -L 5434:localhost:5432 <username>@<old-server-ip> -i ~/.ssh/id_ed25519 -NVới ví dụ trên, <target-host>:<target-port> là localhost:5433 (và nguồn là
localhost:5434 nếu bạn mở tunnel thứ hai).
Tóm tắt nhanh
# 0. Thêm PostgreSQL client vào PATH (đổi <pg-version> cho đúng, ví dụ 17)
export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"
# 1. Khai báo kết nối - mật khẩu để RIÊNG, KHÔNG nhét vào URL (tránh vỡ 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 (nếu pg_dump mới hơn server đích) -> 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"Mô hình di chuyển
+-------------+ pg_dump +--------------+ psql restore +-------------+
| VPS nguồn | ---(đọc)---> | Máy local | -------------> | VPS đích |
| Postgres | | (file .sql) | | Postgres |
+-------------+ +--------------+ +-------------+File dump được chuyển giữa hai VPS thông qua máy local: pg_dump kéo dữ liệu từ nguồn
về thành một file .sql, sau đó psql đẩy file đó lên đích.
1. Chuẩn bị tool
Cần pg_dump + psql. Trên Windows thường đã có sẵn trong
C:\Program Files\PostgreSQL\<pg-version>\bin. Thêm vào PATH cho phiên Git Bash hiện tại:
export PATH="/c/Program Files/PostgreSQL/<pg-version>/bin:$PATH"
pg_dump --version && psql --versionNên dùng pg_dump phiên bản >= server đích. Cùng major version (16 -> 16) là an
toàn nhất.
2. Khai báo connection, mật khẩu để riêng
Nếu mật khẩu có ký tự @ % $ / ? mà nhét thẳng vào URL thì hỏng parsing (dấu @ trong
mật khẩu đụng dấu @ ngăn host; %xx bị hiểu là percent-encoding). Cách chắc chắn: URL
không chứa mật khẩu, để mật khẩu vào biến *_DB_PASSWORD (libpq đọc raw, không parse).
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>'URL chỉ còn user@host, KHÔNG có phần :mật-khẩu. Dùng nháy đơn '...' để ký tự đặc
biệt không bị shell hiểu nhầm. Bỏ ?sslmode=require nếu database không bật SSL.
3. Test kết nối hai đầu
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();"Cả hai in ra tên database tương ứng là kết nối OK. Lỗi Connection refused ở đích ->
kiểm tra SSH tunnel.
4. Dump database nguồn (chỉ đọc)
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 | Ý nghĩa |
|---|---|
--format=plain | Xuất plain SQL (dễ đọc/audit; restore bằng psql) |
--no-owner | Bỏ lệnh gán owner (user trên hai VPS thường khác nhau) |
--no-privileges | Bỏ GRANT/REVOKE (tránh lỗi role không tồn tại) |
--clean --if-exists | Thêm DROP ... IF EXISTS ở đầu -> chạy lại được (idempotent) |
5. Strip SET transaction_timeout (chỉ khi pg_dump mới hơn server đích)
pg_dump 17+ chèn dòng SET transaction_timeout = 0; vào đầu dump - tham số này
server < 17 không hiểu và sẽ làm restore lỗi. Nó chỉ có nghĩa là "không giới hạn
timeout" nên bỏ đi an toàn:
grep -v '^SET transaction_timeout' "$F" > "$F.tmp" && mv "$F.tmp" "$F"Nếu pg_dump cùng version với server đích thì bỏ qua bước này.
6. Restore vào database đích
PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" \
--set=ON_ERROR_STOP=on \
--single-transaction \
--file="$F"--single-transaction: bọc toàn bộ trong một transaction -> lỗi là rollback hết, không để database nửa vời.--set=ON_ERROR_STOP=on: dừng ngay khi gặp lỗi đầu tiên.
7. Verify: đếm số dòng chính xác hai đầu
Block này tự lấy danh sách bảng (kèm schema) rồi so số dòng thật (count(*)) của từng
bảng ở cả hai đầu:
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=" <-- LỆCH"
printf "%-40s %12s %12s%s\n" "$t" "$s" "$d" "$flag"
doneĐạt yêu cầu khi mọi dòng có source = target và không có dòng nào bị đánh dấu <-- LỆCH.
Việc tự lấy danh sách bảng dạng schema.table giúp bắt được cả bảng nằm ngoài schema
public - ví dụ dự án dùng Drizzle ORM để bảng __drizzle_migrations trong schema
drizzle.
8. Cutover: trỏ backend sang database mới
Sửa DATABASE_URL trong file env của backend:
DATABASE_URL=postgresql://<target-user>:<target-password-đã-encode>@<target-host>:<target-port>/<target-db>?sslmode=requireỞ đây backend parse chuỗi URL, nên mật khẩu PHẢI được percent-encode nếu có ký tự
đặc biệt - khác với bước 2 dùng PGPASSWORD raw.
| Ký tự | Viết trong URL |
|---|---|
% | %25 (encode trước tiên) |
@ | %40 |
/ | %2F |
? | %3F |
# | %23 |
: | %3A |
Các ký tự $ ! - _ để nguyên được. Ví dụ mật khẩu ab%cd@ef -> viết trong URL là
ab%25cd%40ef.
Khởi động lại backend và kiểm tra health/ready endpoint để chắc nó kết nối đúng database mới.
Script tự động hóa (chạy một phát)
Lưu thành migrate-db.sh, đặt cạnh nơi muốn chứa dump. Script đọc 4 biến môi trường ở
bước 2 (URL không mật khẩu + *_DB_PASSWORD).
#!/usr/bin/env bash
# Migrate một database PostgreSQL giữa hai server bằng pg_dump (plain SQL) -> psql.
# Nguồn CHỈ ĐỌC. Restore chạy trong một transaction duy nhất.
#
# 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 # hỏi xác nhận trước khi đụng vào đích
# CONFIRM=1 ./migrate-db.sh # chạy không cần tương tác
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+ chèn `SET transaction_timeout` mà server < 17 từ chối; bỏ đi an toàn.
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"Dump chứa data thật (có thể gồm password hash, email). Đừng commit file dump lên git -
thêm dumps/ và *.sql vào .gitignore.
Nếu để script .sh trong repo trên Windows, thêm *.sh text eol=lf vào .gitattributes
để script không bị CRLF làm hỏng khi chạy trên Linux.
Xử lý sự cố
| Triệu chứng | Nguyên nhân | Cách xử lý |
|---|---|---|
export: command not found | Đang gõ trong PowerShell | Mở Git Bash (script là bash). Hoặc trong PowerShell dùng $env:VAR='...' |
| Kết nối hỏng dù đúng mật khẩu | Mật khẩu có @ % $ / nhét trong URL -> vỡ parsing | Để URL không mật khẩu + dùng PGPASSWORD (bước 2) |
unrecognized configuration parameter "transaction_timeout" | Dump bằng pg_dump 17+ rồi restore vào server < 17 | Strip dòng đó (bước 5) |
permission denied to create extension "..." | User đích không đủ quyền tạo extension | Nhờ superuser tạo trước trên database đích: CREATE EXTENSION IF NOT EXISTS <tên-extension>; (ví dụ citext, unaccent, uuid-ossp) rồi restore lại |
WARNING: permission denied to analyze "pg_..." | ANALYZE; toàn cục đụng system catalog mà user không phải superuser | Vô hại - bảng của app vẫn được analyze. Hoặc chỉ analyze bảng user (script đã làm vậy) |
relation "..." does not exist khi verify | Bảng nằm ở schema khác public (ví dụ drizzle) | Dùng tên đầy đủ schema.table (block verify ở bước 7 đã tự xử lý) |
SSL connection required | Database bắt buộc SSL | Thêm ?sslmode=require vào cuối connection string |
Connection refused ở đích | Chưa mở SSH tunnel tới VPS đích | Mở tunnel trước (xem phần Cần chuẩn bị) |
Rollback
Database đích chỉ chứa dữ liệu của lần migrate này nên rollback rất an toàn: chạy lại
script (idempotent), hoặc xóa sạch schema public trên đích rồi restore lại từ bước 6:
PGPASSWORD="$TARGET_DB_PASSWORD" psql "$TARGET_DATABASE_URL" -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"DROP SCHEMA ... CASCADE xóa sạch dữ liệu. Chỉ chạy với TARGET_DATABASE_URL và kiểm
tra kỹ biến này trước khi Enter.
Nguồn không bao giờ bị đụng tới, nên nếu đã cutover (bước 8) mà cần quay lại, chỉ việc
trỏ DATABASE_URL của backend về database nguồn rồi khởi động lại. Lưu ý dữ liệu ghi
vào database mới sau thời điểm cutover sẽ không có ở nguồn.
Liên quan
- Clone database PostgreSQL từ VPS về máy local Windows - lấy bản sao về máy dev (custom format, đổi port local)
- Kết nối PostgreSQL trên VPS qua SSH Tunnel từ máy local - mở tunnel tới database trên VPS
- Cài đặt PostgreSQL trên VPS an toàn - dựng PostgreSQL trên VPS đích trước khi migrate