Kesalahan PostgreSQL “cannot execute CREATE TABLE in a read-only transaction” kini menghambat migrasi bagi banyak tim, menyebabkan skema terpasang setengah jalan dan tabel riwayat migrasi terkunci. Kegagalan ini biasanya muncul ketika operasi tulis mendarat di replika, bukan di primary, dan menghentikan alat apa pun yang bergantung pada perubahan skema—Flyway, Liquibase, Django ORM, dan sejenisnya.

Mengapa kesalahan ini muncul

PostgreSQL menonaktifkan semua operasi tulis, perubahan skema, dan pembaruan urutan (sequence) ketika sebuah transaksi ditandai sebagai read-only. Cara paling umum migrasi berakhir dalam kondisi tersebut adalah:

  • Connection pooler (misalnya, PgBouncer) yang secara tidak sengaja mengirim koneksi migrasi ke read replica.
  • Role yang memiliki parameter default_transaction_read_only diatur ke on secara default.
  • Endpoint cloud yang menyediakan URL reader dan writer secara terpisah; menggunakan URL reader (seperti yang umum pada AWS RDS atau Aurora) akan mengarahkan operasi tulis ke replika.

Ketika salah satu kondisi ini terpenuhi, proses migrasi mungkin berhasil membuat tabel, menambah kolom, atau memperbarui urutan, namun kemudian terhenti di tengah jalan, meninggalkan database dalam kondisi migrasi yang tidak lengkap.

Perbaikan instan di dalam transaksi

Jika Anda sudah mengalami kesalahan ini, Anda dapat menimpa (override) flag read-only untuk transaksi saat ini tanpa memengaruhi sisa sesi lainnya—hal ini sangat krusial ketika connection pool menggunakan kembali sesi yang sama untuk pekerjaan lain.

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

SET LOCAL mengubah pengaturan hanya untuk durasi transaksi tersebut. Menggunakan SET biasa akan membuat perubahan tersebut bertahan selama seluruh sesi, yang dapat merusak operasi lain yang memang membutuhkan default read-only.

Langkah-langkah pencegahan

1. Verifikasi bahwa Anda berada di node primary

Tambahkan pemeriksaan cepat sebelum menjalankan migrasi apa pun:

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

Jika hasilnya adalah REPLICA, batalkan migrasi. Fungsi pg_is_in_recovery() mengembalikan nilai true pada server standby, yang menjamin Anda tidak mencoba menulis ke salinan read-only.

2. Gunakan role migrasi khusus

Buatlah sebuah role yang mode transaksi default-nya diaktifkan untuk penulisan (write-enabled), dan berikan hanya hak akses (privileges) yang dibutuhkannya:

  • CREATE pada database target.
  • CONNECT untuk memungkinkan alat migrasi membuka sesi.

Hindari memberikan atribut default_transaction_read_only = on pada role ini, yang terkadang diatur untuk pengguna umum.

3. Arahkan infrastruktur ke endpoint writer

Dalam skrip IaC (Terraform, CloudFormation, dll.), konfigurasikan migration runner untuk menggunakan endpoint writer klaster, bukan endpoint reader. Endpoint writer akan mengarah ke node primary, sedangkan endpoint reader akan mengarah ke replika yang akan menolak operasi tulis.

4. Tambahkan gate CI/CD

Masukkan langkah shell dalam pipeline Anda (GitHub Actions, GitLab CI, dll.) yang menjalankan query pg_is_in_recovery(). Jika hasilnya true, hentikan job dengan status non-zero untuk menghentikan deployment lebih awal.

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

5. Sesuaikan retry pada alat migrasi

Alat seperti Flyway sering kali mencoba kembali (retry) koneksi secara otomatis. Untuk Flyway 9+, atur flyway.connectRetries=0. Ini mencegah alat tersebut berulang kali mencoba menghubungi replika, yang jika dibiarkan dapat meningkatkan replication lag dan membuang-buang sumber daya.

Hal yang perlu diperhatikan selanjutnya

  • Metrik replication lag: Lag yang terus meningkat mungkin mengindikasikan bahwa migrasi secara tidak sengaja menargetkan replika, menyebabkan upaya penulisan masuk ke dalam antrean.
  • Aturan routing connection-pooler: Pastikan konfigurasi pooler secara eksplisit mengarahkan lalu lintas migrasi ke host primary.
  • Default role setelah upgrade: Upgrade database terkadang mereset parameter role; lakukan audit ulang pada default_transaction_read_only setelah perubahan versi utama.

Intinya: kesalahan transaksi read-only jarang sekali merupakan bug PostgreSQL; itu adalah gejala dari lalu lintas yang dikirim ke node yang salah atau role yang salah konfigurasi. Dengan memeriksa peran node, menggunakan akun migrasi khusus, dan memperkuat pipeline CI/CD Anda, Anda dapat menjaga migrasi tetap berjalan lancar dan menghindari skema yang terpasang setengah jalan yang dapat melumpuhkan pengembangan di tahap selanjutnya.