L'errore di PostgreSQL “cannot execute CREATE TABLE in a read-only transaction” sta bloccando le migrazioni di molti team, lasciando gli schemi applicati solo parzialmente e le tabelle della cronologia delle migrazioni bloccate. Il fallimento si verifica solitamente quando un'operazione di scrittura finisce su una replica invece che sulla primaria, e blocca qualsiasi strumento che faccia affidamento sulle modifiche allo schema, come Flyway, Liquibase, Django ORM e simili.

Perché compare l'errore

PostgreSQL disabilita tutte le scritture, le alterazioni dello schema e gli aggiornamenti delle sequenze quando una transazione è contrassegnata come di sola lettura (read-only). I modi più comuni in cui una migrazione finisce in questo stato sono:

  • Connection pooler (ad es. PgBouncer) che inviano involontariamente la connessione di migrazione a una replica di sola lettura.
  • Ruoli che hanno il parametro default_transaction_read_only impostato su on di default.
  • Endpoint cloud che espongono URL separati per la lettura (reader) e la scrittura (writer); l'uso dell'URL reader (come avviene comunemente con AWS RDS o Aurora) indirizza le scritture verso una replica.

Quando si verifica una di queste condizioni, il processo di migrazione può creare tabelle, aggiungere colonne o aggiornare sequenze solo per poi scontrarsi con un muro, lasciando il database in uno stato di migrazione parziale.

Soluzione immediata all'interno della transazione

Se hai già riscontrato l'errore, puoi sovrascrivere il flag di sola lettura per la transazione corrente senza influenzare il resto della sessione — un aspetto cruciale quando un connection pool riutilizza la stessa sessione per altri compiti.

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

SET LOCAL modifica l'impostazione solo per la durata della transazione. L'uso del semplice SET renderebbe la modifica persistente per l'intera sessione, il che potrebbe interrompere altre operazioni che necessitano legittimamente di un default di sola lettura.

Misure preventive

1. Verifica di essere sul nodo primario

Aggiungi un controllo rapido prima dell'esecuzione di qualsiasi migrazione:

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

Se il risultato è REPLICA, interrompi la migrazione. La funzione pg_is_in_recovery() restituisce true su un server standby, garantendo che non si stia tentando di scrivere su una copia di sola lettura.

2. Utilizza ruoli di migrazione dedicati

Crea un ruolo il cui modo di transazione predefinito sia abilitato alla scrittura, e concedigli solo i privilegi necessari:

  • CREATE sul database di destinazione.
  • CONNECT per consentire allo strumento di migrazione di aprire una sessione.

Evita di assegnare a questo ruolo l'attributo default_transaction_read_only = on che viene talvolta impostato per gli utenti generici.

3. Punta l'infrastruttura all'endpoint writer

Negli script IaC (Terraform, CloudFormation, ecc.), configura gli esecutori di migrazione per utilizzare l'endpoint writer del cluster, non l'endpoint reader. L'endpoint writer punta al nodo primario, mentre l'endpoint reader punta a una replica che rifiuterà le scritture.

4. Aggiungi un gate nella CI/CD

Inserisci uno step shell nella tua pipeline (GitHub Actions, GitLab CI, ecc.) che esegua la query pg_is_in_recovery(). Se restituisce true, esci dal job con uno stato diverso da zero per interrompere precocemente il 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. Ottimizza i tentativi di riprova degli strumenti di migrazione

Strumenti come Flyway spesso riprovano le connessioni automaticamente. Per Flyway 9+ imposta flyway.connectRetries=0. Questo evita che lo strumento colpisca ripetutamente una replica, il che altrimenti potrebbe aumentare il ritardo di replica (replication lag) e sprecare risorse.

Cosa monitorare in seguito

  • Metriche del ritardo di replica (replication lag): un ritardo crescente può indicare che una migrazione ha puntato involontariamente a una replica, causando l'accodamento dei tentativi di scrittura.
  • Regole di routing del connection pooler: assicurati che le configurazioni del pooler indirizzino esplicitamente il traffico di migrazione verso l'host primario.
  • Default dei ruoli dopo gli aggiornamenti: gli aggiornamenti del database a volte resettano i parametri dei ruoli; effettua nuovamente un audit di default_transaction_read_only dopo i cambiamenti di versione principali.

In sintesi: un errore di transazione in sola lettura è raramente un bug di PostgreSQL; è un sintomo di traffico inviato al nodo sbagliato o di un ruolo configurato in modo errato. Controllando il ruolo del nodo, utilizzando account di migrazione dedicati e rinforzando la pipeline CI/CD, potrai mantenere le migrazioni fluide ed evitare schemi applicati solo parzialmente che compromettono lo sviluppo a valle.