Clone database PostgreSQL từ VPS về máy local Windows
Tạo bản sao đầy đủ (schema lẫn data) của database PostgreSQL trên VPS về máy dev Windows bằng SSH Tunnel, pg_dump và pg_restore, kèm cách đổi port PostgreSQL local để không xung đột.
Mục lục
- Tóm tắt nhanh
- 1. Tìm thư mục data thực tế của PostgreSQL
- 2. Đổi port PostgreSQL local
- 3. Tạo database trên local
- 4. Mở SSH tunnel
- 5. Dump database từ VPS
- 6. Restore vào database local
- 7. Kiểm tra kết quả
- Xử lý sự cố
- Lỗi role does not exist khi restore
- Đã sửa port = 5433 nhưng không có tác dụng
- Không tìm thấy thư mục log
Khi cần test với dữ liệu thật mà không đụng vào production, cách gọn nhất là clone
database PostgreSQL từ VPS về máy dev. Bài này làm việc đó trên Windows: mở SSH Tunnel
tới VPS, dump bằng pg_dump, rồi restore bằng pg_restore vào PostgreSQL local - lấy
cả schema lẫn data.
Thay <username> bằng user SSH trên VPS, <server-ip> bằng IP VPS, <db_user> và
<database_name> bằng user và tên database trên VPS. <local_database_name> là tên
database mới trên máy local (ví dụ myapp_local), <local_password> là mật khẩu user
postgres của PostgreSQL local. Các lệnh dùng PostgreSQL 17 - đổi 17 theo version
bạn cài.
Tóm tắt nhanh
Điều kiện: PostgreSQL local dùng port khác (ví dụ 5433) để không xung đột với SSH tunnel (5432).
# 1. Đổi port PostgreSQL local sang 5433 (trong postgresql.conf, bỏ dấu # và sửa)
# port = 5433
# Sau đó restart service:
net stop PostgreSQL-17
net start PostgreSQL-17
# 2. Tạo database local
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE DATABASE <local_database_name>;"
# 3. Mở SSH tunnel (giữ terminal này mở)
ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -N
# 4. Dump từ VPS qua tunnel (terminal khác)
& "C:\Program Files\PostgreSQL\17\bin\pg_dump.exe" -h localhost -p 5432 -U <db_user> -d <database_name> -F c -f D:\db_dump.backup
# 5. Restore vào local
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -F c D:\db_dump.backup
# 6. Kiểm tra kết quả
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -c "\dt"1. Tìm thư mục data thực tế của PostgreSQL
PostgreSQL trên Windows có thể cài ở nhiều vị trí khác nhau. Dùng Registry để tìm chính xác:
reg query "HKLM\SYSTEM\CurrentControlSet\Services\PostgreSQL-17" /v ImagePathKết quả sẽ hiện đường dẫn tương tự:
ImagePath REG_EXPAND_SZ "C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe" runservice -N "PostgreSQL-17" -D "D:\Software\PostgreSQL\17\data" -wPhần -D "..." chính là thư mục data. Ở ví dụ trên là D:\Software\PostgreSQL\17\data -
các bước sau dùng đường dẫn này, bạn thay bằng đường dẫn trên máy mình.
2. Đổi port PostgreSQL local
Cần đổi port vì SSH tunnel sẽ chiếm port 5432. Mở file config bằng Notepad (chạy as Administrator):
notepad "D:\Software\PostgreSQL\17\data\postgresql.conf"Tìm dòng port và sửa (chú ý bỏ dấu # nếu có):
# Trước (bị comment, không có tác dụng):
#port = 5432
# Sau (đã bỏ comment và đổi port):
port = 5433Lưu file rồi restart service:
# Stop rồi start lại
net stop PostgreSQL-17
net start PostgreSQL-173. Tạo database trên local
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE DATABASE <local_database_name>;"4. Mở SSH tunnel
Mở một terminal riêng và giữ nó mở trong suốt quá trình dump:
ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -NLúc này localhost:5432 trên máy bạn chính là PostgreSQL đang chạy trên VPS. Chi tiết
về tunnel (chạy background, giữ kết nối ổn định) xem bài
Kết nối PostgreSQL trên VPS qua SSH Tunnel từ máy local.
5. Dump database từ VPS
Mở terminal khác (không đóng terminal SSH):
& "C:\Program Files\PostgreSQL\17\bin\pg_dump.exe" -h localhost -p 5432 -U <db_user> -d <database_name> -F c -f D:\db_dump.backup-F c: custom format (nén, restore nhanh hơn plain SQL)-f: đường dẫn file output- Nhập mật khẩu của
<db_user>khi được hỏi
6. Restore vào database local
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -F c D:\db_dump.backup7. Kiểm tra kết quả
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -c "\dt"Lệnh này liệt kê tất cả bảng - nếu hiện ra danh sách bảng là restore thành công.
Connection string để dùng trong app:
postgresql://postgres:<local_password>@localhost:5433/<local_database_name>Xử lý sự cố
Lỗi role does not exist khi restore
pg_restore: error: could not execute query: ERROR: role "<db_user>" does not exist
Command was: ALTER SCHEMA drizzle OWNER TO <db_user>;Nguyên nhân: file backup chứa lệnh gán ownership cho role <db_user>, nhưng role này
không tồn tại trên máy local.
Lỗi này không nghiêm trọng - data và schema đã được restore thành công, ownership
chỉ đơn giản là thuộc về postgres. Bạn có thể dùng bình thường.
Nếu muốn ownership đúng như trên VPS, tạo role trước rồi restore lại:
# Tạo role trùng tên với user database trên VPS
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE ROLE <db_user> WITH LOGIN PASSWORD '<password>' SUPERUSER;"
# Restore lại (--clean để drop objects cũ trước)
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> --clean -F c D:\db_dump.backupĐã sửa port = 5433 nhưng không có tác dụng
Kiểm tra xem dòng đó có bị comment không. Dòng có dấu # ở đầu là bị vô hiệu hóa:
# Không có tác dụng (bị comment):
#port = 5433
# Có tác dụng:
port = 5433Không tìm thấy thư mục log
Nếu D:\Software\PostgreSQL\17\data\log không tồn tại, nghĩa là PostgreSQL chưa start
thành công lần nào. Thử start thủ công để xem lỗi:
& "C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe" start -D "D:\Software\PostgreSQL\17\data" -l "D:\Software\PostgreSQL\17\data\startup.log"
notepad "D:\Software\PostgreSQL\17\data\startup.log"