L'erreur PostgreSQL « cannot execute CREATE TABLE in a read-only transaction » bloque désormais les migrations de nombreuses équipes, laissant les schémas partiellement appliqués et les tables d'historique de migration verrouillées. L'échec survient généralement lorsqu'une opération d'écriture atterrit sur un réplica au lieu du nœud primaire, et il paralyse tout outil reposant sur des changements de schéma — Flyway, Liquibase, Django ORM, et outils similaires.

Pourquoi l'erreur s'affiche

PostgreSQL désactive toutes les écritures, les altérations de schéma et les mises à jour de séquences lorsqu'une transaction est marquée comme étant en lecture seule (read-only). Les raisons les plus courantes pour lesquelles une migration se retrouve dans cet état sont :

  • Les gestionnaires de pool de connexions (ex. : PgBouncer) qui envoient par inadvertance la connexion de migration vers un réplica de lecture.
  • Les rôles ayant le paramètre default_transaction_read_only réglé sur on par défaut.
  • Les points de terminaison (endpoints) cloud qui exposent des URL distinctes pour la lecture et l'écriture ; l'utilisation de l'URL de lecture (comme c'est souvent le cas avec AWS RDS ou Aurora) redirige les écritures vers un réplica.

Lorsque l'une de ces conditions s'applique, le processus de migration peut créer des tables, ajouter des colonnes ou mettre à jour des séquences pour finalement se heurter à un mur, laissant la base de données dans un état de migration partielle.

Correctif immédiat à l'intérieur de la transaction

Si vous rencontrez déjà l'erreur, vous pouvez outrepasser le flag de lecture seule pour la transaction en cours sans affecter le reste de la session — ce qui est crucial lorsqu'un pool de connexions réutilise la même session pour d'autres tâches.

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

SET LOCAL modifie le paramètre uniquement pour la durée de la transaction. L'utilisation d'un simple SET rendrait le changement persistant pour toute la session, ce qui pourrait interrompre d'autres opérations nécessitant légitimement un mode lecture seule par défaut.

Mesures préventives

1. Vérifiez que vous êtes sur le nœud primaire

Ajoutez une vérification rapide avant l'exécution de toute migration :

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

Si le résultat est REPLICA, interrompez la migration. La fonction pg_is_in_recovery() renvoie true sur un serveur de secours (standby), garantissant que vous ne tentez pas d'écrire sur une copie en lecture seule.

2. Utilisez des rôles de migration dédiés

Créez un rôle dont le mode de transaction par défaut permet l'écriture, et ne lui accordez que les privilèges nécessaires :

  • CREATE sur la base de données cible.
  • CONNECT pour permettre à l'outil de migration d'ouvrir une session.

Évitez d'attribuer à ce rôle l'attribut default_transaction_read_only = on qui est parfois configuré pour les utilisateurs polyvalents.

3. Dirigez l'infrastructure vers le point de terminaison d'écriture

Dans les scripts IaC (Terraform, CloudFormation, etc.), configurez les exécuteurs de migration pour utiliser le point de terminaison writer du cluster, et non le point de terminaison reader. Le point de terminaison writer pointe vers le nœud primaire, tandis que le point de terminaison reader pointe vers un réplica qui rejettera les écritures.

4. Ajoutez une étape de contrôle CI/CD

Insérez une étape shell dans votre pipeline (GitHub Actions, GitLab CI, etc.) qui exécute la requête pg_is_in_recovery(). Si elle renvoie true, quittez le job avec un statut non nul pour arrêter le déploiement prématurément.

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

5. Ajustez les tentatives de reconnexion des outils de migration

Les outils comme Flyway tentent souvent de se reconnecter automatiquement. Pour Flyway 9+, définissez flyway.connectRetries=0. Cela empêche l'outil de solliciter de manière répétée un réplica, ce qui pourrait autrement augmenter le retard de réplication (replication lag) et gaspiller des ressources.

Points de vigilance pour la suite

  • Métriques de retard de réplication (replication lag) : Un retard croissant peut indiquer qu'une migration a ciblé par inadvertance un réplica, provoquant la mise en file d'attente des tentatives d'écriture.
  • Règles de routage du gestionnaire de pool de connexions : Assurez-vous que les configurations du pooler dirigent explicitement le trafic de migration vers l'hôte primaire.
  • Valeurs par défaut des rôles après mise à jour : Les mises à jour de bases de données réinitialisent parfois les paramètres des rôles ; ré-auditez default_transaction_read_only après des changements de version majeure.

En résumé : une erreur de transaction en lecture seule est rarement un bug de PostgreSQL ; c'est le symptôme d'un trafic envoyé vers le mauvais nœud ou d'un rôle mal configuré. En vérifiant le rôle du nœud, en utilisant des comptes de migration dédiés et en renforçant votre pipeline CI/CD, vous pouvez assurer le bon déroulement des migrations et éviter les schémas partiellement appliqués qui paralysent le développement en aval.