PostgreSQLの「cannot execute CREATE TABLE in a read-only transaction」エラーは、現在多くのチームのマイグレーションを失敗させており、スキーマが中途半端に適用されたり、マイグレーション履歴テーブルがロックされたりする原因となっています。このエラーは通常、書き込み操作がプライマリではなくレプリカに対して行われたときに発生し、Flyway、Liquibase、Django ORMなどのスキーマ変更に依存するツールを停止させてしまいます。

なぜこのエラーが発生するのか

PostgreSQLは、トランザクションがread-only（読み取り専用）としてマークされている場合、すべての書き込み、スキーマ変更、およびシーケンスの更新を無効にします。マイグレーションがその状態に陥る最も一般的な原因は以下の通りです：

  • コネクションプーラー（例：PgBouncer）が、誤ってマイグレーションの接続をリードレプリカに送信している。
  • ロールのdefault_transaction_read_onlyパラメータが、デフォルトでonに設定されている。
  • クラウドのエンドポイントが、リーダー用とライター用のURLを個別に公開している。リーダー用URLを使用すると（AWS RDSやAuroraでよくあるケース）、書き込みはレプリカにルーティングされます。

これらの条件のいずれかが当てはまると、マイグレーションプロセスはテーブルの作成、カラムの追加、シーケンスの更新を行おうとして壁に突き当たり、データベースが中途半端にマイグレーションされた状態になってしまいます。

トランザクション内での即時的な修正

すでにエラーが発生してしまった場合、セッションの他の部分に影響を与えることなく、現在のトランザクションに対してのみread-onlyフラグを上書きできます。これは、コネクションプーラーが他の作業のために同じセッションを再利用する場合に非常に重要です。

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

SET LOCALは、その設定をトランザクションの期間中のみ変更します。単なるSETを使用すると、セッション全体に設定が持続してしまい、読み取り専用のデフォルトを正当に必要とする他の操作を壊してしまう可能性があります。

予防策

1. プライマリノードに接続しているか確認する

マイグレーションを実行する前に、簡単なチェックを追加します：

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

結果が REPLICA の場合は、マイグレーションを中止してください。pg_is_in_recovery()関数は、スタンバイサーバー上でtrueを返すため、読み取り専用のコピーに対して書き込みを試みていないことを保証できます。

2. 専用のマイグレーション用ロールを使用する

デフォルトのトランザクションモードが書き込み可能（write-enabled）であり、必要な権限のみを付与されたロールを作成します：

  • 対象データベースに対する CREATE 権限。
  • マイグレーションツールがセッションを開けるようにするための CONNECT 権限。

一般的なユーザー向けに設定されることがある default_transaction_read_only = on 属性を、このロールに付与しないようにしてください。

3. インフラストラクチャをライターエンドポイントに向ける

IaCスクリプト（Terraform、CloudFormationなど）において、マイグレーションランナーがリーダーエンドポイントではなく、クラスターの**ライター（writer）**エンドポイントを使用するように設定します。ライターエンドポイントはプライマリノードに解決されますが、リーダーエンドポイントは書き込みを拒否するレプリカに解決されます。

4. CI/CDゲートを追加する

パイプライン（GitHub Actions、GitLab CIなど）に、pg_is_in_recovery()クエリを実行するシェルステップを挿入します。もし結果がtrueを返した場合は、デプロイを早期に停止するために、非ゼロのステータスでジョブを終了させます。

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

5. マイグレーションツールのリトライ設定を調整する

Flywayのようなツールは、多くの場合、接続を自動的にリトライします。Flyway 9以降では、flyway.connectRetries=0を設定してください。これにより、ツールが繰り返しレプリカに接続してレプリケーション遅延を増加させたり、リソースを浪費したりするのを防ぐことができます。

次に注意すべき点

  • レプリケーション遅延メトリクス: 遅延が増大している場合、マイグレーションが誤ってレプリカを対象とし、書き込み試行がキューに溜まっている可能性があります。
  • コネクションプーラーのルーティングルール: プーラーの設定が、マイグレーションのトラフィックを明示的にプライマリホストにルーティングしていることを確認してください。
  • アップグレード後のロールのデフォルト値: データベースのアップグレードにより、ロールのパラメータがリセットされることがあります。メジャーバージョンの変更後は、default_transaction_read_onlyを再監査してください。

結論として、read-onlyトランザクションエラーはPostgreSQLのバグであることは稀です。それは、トラフィックが誤ったノードに送信されているか、ロールが誤設定されていることの兆候です。ノードの役割を確認し、専用のマイグレーション用アカウントを使用し、CI/CDパイプラインを強化することで、マイグレーションをスムーズに実行し、後続の開発を停滞させるような中途半端なスキーマ適用を回避できます。