Hướng dẫn export và restore schema PostgreSQL bằng pg_dump và psql

Cách export và restore schema PostgreSQL chỉ gồm cấu trúc bảng, index, constraint bằng pg_dump và psql — không kèm dữ liệu, kèm xử lý các lỗi thường gặp.

Khi cần dựng database test, môi trường staging hoặc chuyển cấu trúc bảng sang một server khác, ta thường chỉ cần cấu trúc mà không cần dữ liệu. PostgreSQL hỗ trợ việc này bằng cờ --schema-only của pg_dump.

Bài viết này walkthrough trọn quy trình: export schema public, tạo database đích, rồi restore bằng psql — kèm các lỗi thường gặp và cách xử lý.

---

Bối cảnh: khi nào cần export schema

• Dựng môi trường test/staging: Copy cấu trúc bảng, index, constraint, view, function từ database dev sang database mới.

• Chuẩn bị dữ liệu mẫu: Tạo schema đúng như production rồi mới import dữ liệu đã lọc.

• Di chuyển cấu trúc sang server khác: Đặt schema vào Git để review thay đổi trước khi áp dụng.

• Tái tạo môi trường lỗi: Dựng lại schema tối thiểu để reproduce lỗi.

--schema-only chỉ xuất cấu trúc: bảng, index, constraint, view, function, sequence, type. File dump sẽ không có dòng dữ liệu nào.

---

Bước 1: Export schema

Export schema public ra file SQL

pg_dump -h localhost -p 5432 -U postgres -d ten_database \
  --schema-only -n public -f schema_public.sql

Giải thích tham số

Tham số	Ý nghĩa
-h	Host kết nối
-p	Port của PostgreSQL server
-U	User kết nối
-d	Tên database nguồn cần export
--schema-only (-s)	Chỉ xuất cấu trúc, không xuất dữ liệu
-n public	Chỉ xuất schema public, bỏ trống để xuất tất cả
-f	Tên file đầu ra

Các biến thể hữu ích

Các biến thể của lệnh export

# Bỏ owner và quyền — dễ import sang server khác
pg_dump -U postgres -d ten_database --schema-only --no-owner --no-privileges -f schema.sql

# Chỉ một số bảng cụ thể
pg_dump -U postgres -d ten_database --schema-only -t bang_a -t bang_b -f schema_tables.sql

# Dạng nén (custom format) — restore bằng pg_restore
pg_dump -U postgres -d ten_database --schema-only -Fc -f schema.dump

Export schema khi PostgreSQL chạy trong Docker

docker exec -i ten_container pg_dump -U postgres -d ten_database \
  --schema-only -n public > schema_public.sql

---

Bước 2: Tạo database đích

Tạo database mới

createdb -h localhost -p 5432 -U postgres esm45f0e_qcdd_test

Database mới luôn có sẵn schema public. Khi restore, pg_dump vẫn phát ra lệnh tạo schema này nên thường gặp cảnh báo schema "public" already exists — đây là bình thường, không phải lỗi.

---

Bước 3: Restore schema

Vì dump bằng -f (không có -Fc), file là SQL thuần nên phải restore bằng psql. Dùng pg_restore chỉ đúng với file ở dạng custom (-Fc), directory (-Fd) hoặc tar (-Ft).

Restore schema vào database mới

psql -h localhost -p 5432 -U postgres -d esm45f0e_qcdd_test \
  -v ON_ERROR_STOP=1 -f schema_public.sql

• Bắt buộc: File phải nằm đúng thư mục hiện tại, hoặc chỉ định đường dẫn đầy đủ.

• -v ON_ERROR_STOP=1: Dừng ngay khi gặp lỗi đầu tiên, tránh để lại schema dở dang mà không biết chỗ nào hỏng.

Restore khi PostgreSQL chạy trong Docker

Restore schema trong container

docker exec -i ten_container psql -U postgres -d esm45f0e_qcdd_test < schema_public.sql

---

Bước 4: Kiểm tra kết quả

Liệt kê các bảng trong schema public

psql -h localhost -U postgres -d esm45f0e_qcdd_test -c "\dt public.*"

Ngoài ra có thể kiểm tra nhanh số lượng bảng, view và function:

Kiểm tra số lượng object trong database

SELECT relkind, count(*)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
GROUP BY relkind;
-- relkind: r = table, v = view, i = index, m = materialized view

---

Các lỗi thường gặp

Lỗi	Nguyên nhân	Cách xử lý
schema "public" already exists	Database mới luôn có sẵn schema public	Bỏ qua — không dùng ON_ERROR_STOP cho phần này
role "xxx" does not exist	File dump chứa câu lệnh OWNER TO xxx	Tạo role trước CREATE ROLE xxx; hoặc dump lại với --no-owner --no-privileges
Thiếu extension (uuid-ossp, pg_trgm, postgis…)	Dump với -n public không kèm lệnh tạo extension	Tạo trước: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Sai encoding hoặc mất dấu tiếng Việt	PowerShell dùng > có thể ghi file ở dạng UTF-16	Dùng -f thay vì redirect >
pg_restore: error: input file appears to be a text format dump	Dùng pg_restore cho file plain SQL	Chuyển sang dùng psql

Muốn schema "sạch" và có thể diff/review trên Git, hãy export với --no-owner --no-privileges.

---

Lưu ý khi chạy trên Windows

Gọi trực tiếp pg_dump và psql trong PowerShell

& "C:\Program Files\PostgreSQL\16\bin\pg_dump.exe" -h localhost -U postgres -d ten_database --schema-only -n public -f schema_public.sql

& "C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d esm45f0e_qcdd_test -f schema_public.sql

• Thay 16 bằng phiên bản PostgreSQL đang cài đặt.

• Nếu pg_dump không có trong PATH, dùng đường dẫn đầy đủ như trên.

• Nên thêm pg_dump vào PATH thay vì gõ đường dẫn mỗi lần.

---

Mẹo bổ sung

• Nhập mật khẩu không tương tác: dùng PGPASSWORD=matkhau trên Linux/macOS, $env:PGPASSWORD="matkhau" trong PowerShell, hoặc lưu vào file ~/.pgpass với quyền 600.

• Muốn có cả schema lẫn dữ liệu: bỏ --schema-only.

• Chỉ muốn dữ liệu: dùng --data-only.

• Luôn restore với -v ON_ERROR_STOP=1 để dễ tìm và xác định lỗi.

• Kiểm tra trước khi áp dụng lên production: dump bằng --no-owner --no-privileges, commit file SQL vào Git và review trước khi chạy.

Khi viết bài hoặc chia sẻ file dump ra public, nhớ thay tên database, user và mọi thông tin nhạy cảm — dump schema vẫn có thể lộ cấu trúc bảng và tên cột.

---

Tóm tắt

Quy trình chỉ gồm 3 bước:

1. Dump schema → pg_dump --schema-only -n public -f schema_public.sql

2. Tạo DB đích → createdb ten_database_moi

3. Restore bằng psql → psql -d ten_database_moi -v ON_ERROR_STOP=1 -f schema_public.sql

---

Tài liệu tham khảo:

• pg_dump — PostgreSQL Documentation

• psql — PostgreSQL Documentation

• Tùy chọn dòng lệnh của pg_dump — PostgreSQL Documentation