Lỗi “cannot execute CREATE TABLE in a read-only transaction” của PostgreSQL hiện đang gây trở ngại cho quá trình migration của nhiều đội ngũ, khiến schema bị áp dụng dở dang và các bảng lịch sử migration bị khóa. Lỗi này thường xuất hiện khi một thao tác ghi được gửi đến một bản sao (replica) thay vì node chính (primary), và nó làm đình trệ bất kỳ công cụ nào dựa trên việc thay đổi schema—như Flyway, Liquibase, Django ORM và các công cụ tương tự.

Tại sao lỗi này xuất hiện

PostgreSQL vô hiệu hóa tất cả các thao tác ghi, thay đổi schema và cập nhật sequence khi một transaction được đánh dấu là chỉ đọc (read-only). Những cách phổ biến nhất khiến một migration rơi vào trạng thái đó là:

  • Connection poolers (ví dụ: PgBouncer) vô tình gửi kết nối migration đến một read replica.
  • Roles có tham số default_transaction_read_only được đặt mặc định là on.
  • Cloud endpoints cung cấp các URL reader và writer riêng biệt; việc sử dụng URL reader (như thường thấy với AWS RDS hoặc Aurora) sẽ điều hướng các thao tác ghi đến một replica.

Khi bất kỳ điều kiện nào ở trên xảy ra, quá trình migration có thể tạo bảng, thêm cột hoặc cập nhật sequence nhưng sau đó lại bị chặn lại, khiến cơ sở dữ liệu rơi vào trạng thái migration dở dang.

Cách khắc phục ngay lập tức bên trong transaction

Nếu bạn đã gặp lỗi này, bạn có thể ghi đè cờ read-only cho transaction hiện tại mà không ảnh hưởng đến phần còn lại của session—điều này rất quan trọng khi một connection pool tái sử dụng cùng một session cho các công việc khác.

BEGIN;
SET LOCAL default_transaction_read_only = off;
SET TRANSACTION READ WRITE;
CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
COMMIT;

SET LOCAL chỉ thay đổi thiết lập trong suốt thời gian diễn ra transaction. Việc sử dụng SET thông thường sẽ duy trì thay đổi cho toàn bộ session, điều này có thể làm hỏng các thao tác khác vốn cần chế độ read-only mặc định một cách hợp lệ.

Các biện pháp phòng ngừa

1. Xác minh bạn đang ở trên node chính (primary node)

Thêm một bước kiểm tra nhanh trước khi chạy bất kỳ migration nào:

SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA' ELSE 'PRIMARY' END;

Nếu kết quả là REPLICA, hãy hủy bỏ migration. Hàm pg_is_in_recovery() trả về true trên một standby server, đảm bảo rằng bạn không cố gắng ghi vào một bản sao chỉ đọc.

2. Sử dụng các migration roles chuyên dụng

Tạo một role có chế độ transaction mặc định là cho phép ghi (write-enabled), và chỉ cấp cho nó những quyền cần thiết:

  • CREATE trên database mục tiêu.
  • CONNECT để cho phép công cụ migration mở một session.

Tránh cấp cho role này thuộc tính default_transaction_read_only = on vốn đôi khi được thiết lập cho các người dùng thông thường.

3. Trỏ hạ tầng vào writer endpoint

Trong các script IaC (Terraform, CloudFormation, v.v.), hãy cấu hình các migration runner sử dụng writer endpoint của cluster, thay vì reader endpoint. Writer endpoint sẽ trỏ đến node chính, trong khi reader endpoint sẽ trỏ đến một replica vốn sẽ từ chối các thao tác ghi.

4. Thêm một CI/CD gate

Chèn một bước shell vào pipeline của bạn (GitHub Actions, GitLab CI, v.v.) để chạy truy vấn pg_is_in_recovery(). Nếu nó trả về true, hãy thoát job với trạng thái khác 0 để dừng việc triển khai sớm.

if psql $DATABASE_URL -c "SELECT pg_is_in_recovery()" | grep -q t; then
  echo "Connected to replica – aborting migration"
  exit 1
fi

5. Điều chỉnh việc thử lại (retries) của công cụ migration

Các công cụ như Flyway thường tự động thử lại kết nối. Đối với Flyway 9+, hãy đặt flyway.connectRetries=0. Điều này ngăn công cụ liên tục gửi yêu cầu đến một replica, việc này nếu xảy ra có thể làm tăng replication lag và lãng phí tài nguyên.

Những điều cần lưu ý tiếp theo

  • Replication lag metrics: Độ trễ (lag) ngày càng tăng có thể cho thấy một migration đã vô tình nhắm vào một replica, khiến các nỗ lực ghi bị đưa vào hàng đợi.
  • Connection-pooler routing rules: Đảm bảo rằng các cấu hình pooler điều hướng lưu lượng migration một cách rõ ràng đến host chính.
  • Role defaults after upgrades: Việc nâng cấp database đôi khi đặt lại các tham số của role; hãy kiểm tra lại default_transaction_read_only sau các thay đổi phiên bản lớn.

Tóm lại: lỗi read-only transaction hiếm khi là lỗi của PostgreSQL; nó là triệu chứng của việc lưu lượng được gửi đến sai node hoặc một role bị cấu hình sai. Bằng cách kiểm tra vai trò của node, sử dụng các tài khoản migration chuyên dụng và thắt chặt pipeline CI/CD, bạn có thể giữ cho quá trình migration diễn ra suôn sẻ và tránh tình trạng schema bị áp dụng dở dang gây ảnh hưởng đến quá trình phát triển phía sau.