De PostgreSQL-foutmelding “cannot execute CREATE TABLE in a read-only transaction” zorgt er momenteel voor dat migraties bij veel teams vastlopen, waardoor schema's half worden toegepast en migratiegeschiedenis-tabellen vergrendeld blijven. De fout treedt meestal op wanneer een schrijfactie op een replica terechtkomt in plaats van op de primaire node, en het blokkeert elke tool die afhankelijk is van schemawijzigingen—Flyway, Liquibase, Django ORM en vergelijkbare tools.

Waarom de fout optreedt

PostgreSQL schakelt alle schrijfacties, schema-wijzigingen en sequence-updates uit wanneer een transactie als read-only is gemarkeerd. De meest voorkomende manieren waarop een migratie in die staat terechtkomt, zijn:

  • Connection poolers (bijv. PgBouncer) die per ongeluk de migratieverbinding naar een read replica sturen.
  • Roles die de parameter default_transaction_read_only standaard op on hebben staan.
  • Cloud-endpoints die aparte reader- en writer-URL's aanbieden; het gebruik van de reader-URL (zoals gebruikelijk bij AWS RDS of Aurora) stuurt schrijfacties naar een replica.

Wanneer een van deze omstandigheden van toepassing is, kan het migratieproces tabellen aanmaken, kolommen toevoegen of sequences bijwerken, om vervolgens tegen een muur aan te lopen, waardoor de database in een gedeeltelijk gemigreerde staat achterblijft.

Directe oplossing binnen de transactie

Als je de foutmelding al hebt ontvangen, kun je de read-only-vlag voor de huidige transactie overschrijven zonder de rest van de sessie te beïnvloeden—cruciaal wanneer een connection pool dezelfde sessie hergebruikt voor ander werk.

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

SET LOCAL wijzigt de instelling alleen voor de duur van de transactie. Het gebruik van een gewone SET zou de wijziging voor de gehele sessie laten voortbestaan, wat andere operaties kan verstoren die terecht een read-only standaardinstelling nodig hebben.

Preventieve maatregelen

1. Controleer of je op de primaire node zit

Voeg een snelle controle toe voordat een migratie wordt uitgevoerd:

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

Als het resultaat REPLICA is, breek de migratie dan af. De functie pg_is_in_recovery() geeft true terug op een standby-server, wat garandeert dat je niet probeert te schrijven naar een read-only kopie.

2. Gebruik specifieke migratie-roles

Maak een role aan waarvan de standaard transactiemodus schrijfbaar is, en verleen alleen de privileges die nodig zijn:

  • CREATE op de doel-database.
  • CONNECT om de migratietool een sessie te laten openen.

Vermijd het toekennen van het attribuut default_transaction_read_only = on aan deze role, wat soms wordt ingesteld voor algemene gebruikers.

3. Wijs infrastructuur naar het writer-endpoint

Configureer in IaC-scripts (Terraform, CloudFormation, etc.) de migration runners om het writer-endpoint van de cluster te gebruiken, niet het reader-endpoint. Het writer-endpoint verwijst naar de primaire node, terwijl het reader-endpoint verwijst naar een replica die schrijfacties zal weigeren.

4. Voeg een CI/CD-gate toe

Voeg een shell-stap toe aan je pipeline (GitHub Actions, GitLab CI, etc.) die de pg_is_in_recovery() query uitvoert. Als deze true teruggeeft, beëindig de job dan met een status die niet nul is om de deployment vroegtijdig te stoppen.

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

5. Optimaliseer retries van migratietools

Tools zoals Flyway proberen verbindingen vaak automatisch opnieuw. Stel voor Flyway 9+ flyway.connectRetries=0 in. Dit voorkomt dat de tool herhaaldelijk een replica raakt, wat anders de replication lag kan verhogen en resources kan verspillen.

Waar je vervolgens op moet letten

  • Replication lag-metrieken: Een toenemende lag kan erop wijzen dat een migratie per ongeluk een replica heeft geraakt, waardoor schrijfacties in een wachtrij worden geplaatst.
  • Routingregels van de connection pooler: Zorg ervoor dat pooler-configuraties het migratieverkeer expliciet naar de primaire host leiden.
  • Standaardinstellingen van roles na upgrades: Database-upgrades reset soms role-parameters; controleer default_transaction_read_only opnieuw na belangrijke versie-wijzigingen.

De kern van het verhaal: een read-only transactiefout is zelden een bug in PostgreSQL; het is een symptoom van verkeer dat naar de verkeerde node wordt gestuurd of een verkeerd geconfigureerde role. Door de rol van de node te controleren, specifieke migratie-accounts te gebruiken en je CI/CD-pipeline te verstevigen, kun je migraties soepel laten verlopen en half-toegepaste schema's voorkomen die de verdere ontwikkeling verlammen.