ข้อผิดพลาด “cannot execute CREATE TABLE in a read-only transaction” ของ PostgreSQL กำลังสร้างปัญหาให้กับกระบวนการ migration ของหลายทีม ทำให้ schema ถูกนำไปใช้เพียงบางส่วนและตารางประวัติการ migration ถูกล็อก ความล้มเหลวมักเกิดขึ้นเมื่อคำสั่งเขียน (write operation) ถูกส่งไปยัง replica แทนที่จะเป็น primary ซึ่งจะทำให้เครื่องมือที่ต้องพึ่งพาการเปลี่ยนแปลง schema เช่น Flyway, Liquibase, Django ORM และเครื่องมืออื่น ๆ หยุดชะงัก

ทำไมข้อผิดพลาดนี้จึงปรากฏขึ้น

PostgreSQL จะปิดการเขียน (writes), การเปลี่ยนแปลง schema และการอัปเดต sequence ทั้งหมดเมื่อ transaction ถูกกำหนดให้เป็นแบบ read-only สาเหตุที่พบบ่อยที่สุดที่ทำให้การ migration ตกอยู่ในสถานะดังกล่าวคือ:

  • Connection poolers (เช่น PgBouncer) ที่ส่งการเชื่อมต่อสำหรับการ migration ไปยัง read replica โดยไม่ตั้งใจ
  • Roles ที่มีการตั้งค่าพารามิเตอร์ default_transaction_read_only เป็น on โดยค่าเริ่มต้น
  • Cloud endpoints ที่แยก URL สำหรับ reader และ writer ออกจากกัน; การใช้ reader URL (ซึ่งเป็นเรื่องปกติใน AWS RDS หรือ Aurora) จะเป็นการส่งคำสั่งเขียนไปยัง replica

เมื่อเกิดเงื่อนไขใดเงื่อนไขหนึ่งข้างต้น กระบวนการ migration อาจสร้างตาราง, เพิ่มคอลัมน์ หรืออัปเดต sequence ได้เพียงบางส่วนก่อนจะติดปัญหา ทำให้ฐานข้อมูลอยู่ในสถานะที่ migration ไม่สมบูรณ์

วิธีแก้ไขทันทีภายใน transaction

หากคุณพบข้อผิดพลาดนี้แล้ว คุณสามารถ override flag read-only สำหรับ transaction ปัจจุบันได้โดยไม่ส่งผลกระทบต่อส่วนที่เหลือของ session ซึ่งเป็นเรื่องสำคัญมากเมื่อ connection pool นำ session เดิมกลับมาใช้ใหม่สำหรับงานอื่น

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

SET LOCAL จะเปลี่ยนการตั้งค่าเฉพาะในช่วงเวลาของ transaction เท่านั้น การใช้ SET แบบธรรมดาจะทำให้การเปลี่ยนแปลงนั้นคงอยู่ตลอดทั้ง session ซึ่งอาจส่งผลเสียต่อการทำงานอื่น ๆ ที่จำเป็นต้องใช้ค่าเริ่มต้นแบบ read-only

มาตรการป้องกัน

1. ตรวจสอบว่าคุณกำลังใช้งานบน primary node

เพิ่มการตรวจสอบอย่างรวดเร็วก่อนเริ่มการ migration:

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

หากผลลัพธ์คือ REPLICA ให้ยกเลิกการ migration ฟังก์ชัน pg_is_in_recovery() จะคืนค่าเป็น true บน standby server ซึ่งช่วยรับประกันว่าคุณไม่ได้กำลังพยายามเขียนข้อมูลลงในสำเนาแบบ read-only

2. ใช้ migration roles โดยเฉพาะ

สร้าง role ที่มีโหมด transaction เริ่มต้นเป็นแบบ write-enabled และมอบสิทธิ์เฉพาะที่จำเป็นเท่านั้น:

  • CREATE บนฐานข้อมูลเป้าหมาย
  • CONNECT เพื่ออนุญาตให้เครื่องมือ migration เปิด session ได้

หลีกเลี่ยงการกำหนด attribute default_transaction_read_only = on ให้กับ role นี้ ซึ่งบางครั้งอาจมีการตั้งค่าไว้สำหรับผู้ใช้งานทั่วไป

3. ชี้โครงสร้างพื้นฐานไปยัง writer endpoint

ในสคริปต์ IaC (Terraform, CloudFormation และอื่น ๆ) ให้กำหนดค่า migration runners ให้ใช้ writer endpoint ของ cluster แทนที่จะเป็น reader endpoint โดย writer endpoint จะชี้ไปยัง primary node ในขณะที่ reader endpoint จะชี้ไปยัง replica ซึ่งจะปฏิเสธการเขียนข้อมูล

4. เพิ่ม CI/CD gate

เพิ่มขั้นตอน shell ใน pipeline ของคุณ (GitHub Actions, GitLab CI และอื่น ๆ) เพื่อรัน query pg_is_in_recovery() หากคืนค่าเป็น true ให้จบการทำงาน (exit) ด้วยสถานะที่ไม่ใช่ศูนย์ (non-zero status) เพื่อหยุดการ deployment ตั้งแต่เนิ่น ๆ

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

5. ปรับแต่งการ retry ของเครื่องมือ migration

เครื่องมืออย่าง Flyway มักจะพยายามเชื่อมต่อใหม่โดยอัตโนมัติ สำหรับ Flyway 9 ขึ้นไป ให้ตั้งค่า flyway.connectRetries=0 วิธีนี้จะช่วยป้องกันไม่ให้เครื่องมือพยายามเชื่อมต่อกับ replica ซ้ำ ๆ ซึ่งอาจทำให้ replication lag เพิ่มขึ้นและสิ้นเปลืองทรัพยากร

สิ่งที่ควรเฝ้าระวังต่อไป

  • Replication lag metrics: ค่า lag ที่เพิ่มขึ้นอาจบ่งชี้ว่าการ migration พุ่งเป้าไปที่ replica โดยไม่ตั้งใจ ทำให้ความพยายามในการเขียนข้อมูลถูกนำไปเข้าคิวรอ
  • Connection-pooler routing rules: ตรวจสอบให้แน่ใจว่าการกำหนดค่า pooler มีการกำหนดเส้นทาง (route) traffic ของการ migration ไปยัง primary host อย่างชัดเจน
  • Role defaults after upgrades: การอัปเกรดฐานข้อมูลบางครั้งอาจรีเซ็ตพารามิเตอร์ของ role ดังนั้นควรตรวจสอบ default_transaction_read_only อีกครั้งหลังการเปลี่ยนเวอร์ชันหลัก

สรุปสั้น ๆ: ข้อผิดพลาด read-only transaction มักไม่ใช่บั๊กของ PostgreSQL แต่เป็นอาการที่เกิดจากการส่ง traffic ไปยัง node ที่ผิด หรือการกำหนดค่า role ที่ไม่ถูกต้อง การตรวจสอบบทบาทของ node, การใช้บัญชีสำหรับการ migration โดยเฉพาะ และการเสริมความแข็งแกร่งให้กับ CI/CD pipeline จะช่วยให้การ migration ดำเนินไปอย่างราบรื่น และหลีกเลี่ยงปัญหา schema ที่ถูกนำไปใช้เพียงบางส่วนซึ่งจะส่งผลกระทบต่อการพัฒนาในขั้นตอนถัดไป