Porter une base PostGIS d'un PostgreSQL 15 vers un 9.6 : le sens interdit

Introduction

#

Un besoin métier en apparence anodin peut révéler une dette technique profonde. Cet article raconte comment une simple demande — « rendre disponibles dans notre SIG web les données du réseau d’eau » — s’est transformée en un portage de base de données à contre-courant des versions.

Le décor : deux serveurs PostgreSQL/PostGIS, l’un moderne, l’autre très ancien, qu’il fallait synchroniser. Entre les deux, six versions majeures de PostgreSQL et une version majeure de PostGIS d’écart. Et surtout, un sens de migration que personne ne recommande : du récent vers l’ancien.

Table des matières

#

• Contexte
• Réflexion : quelle stratégie de synchronisation ?
• Le portage 15 → 9.6 : sept difficultés
• Industrialisation dans un script
• Difficultés d’exploitation du script
• Vérification
• Conclusion

Contexte

#

Deux bases nommées stareau (implémentant le standard national StaR-Eau pour les réseaux d’eau potable et d’assainissement) devaient rester synchronisées entre deux infrastructures distinctes.

cnigfr/StaR-Eau

GT Réseaux enterrés des eaux

QML

27

8

—r >}}

Le fossé technique entre les deux serveurs est le cœur du problème.

Le serveur A est la source de vérité : c’est là que les données sont créées et structurées, via QGIS. Le serveur B alimente le SIG web, en consultation seule. L’objectif : propager régulièrement A vers B.

Réflexion : quelle stratégie de synchronisation ?

#

Avant d’écrire la moindre ligne, il fallait choisir comment synchroniser. Le premier réflexe — une réplication bidirectionnelle où chacun écrit — est un piège.

Pourquoi pas de bidirectionnel

#

Si deux serveurs modifient indépendamment la même donnée, rien ne permet de trancher automatiquement laquelle fait foi au moment de réconcilier. PostgreSQL ne devine pas : il bloque la réplication pour éviter l’incohérence.

graph TB
    A["Serveur A : valeur X"]
    B["Serveur B : valeur Y"]
    A --> C{"Réconciliation :
qui fait foi ?"}
    B --> C
    C -->|"pas de résolution
de conflit native"| STOP["Réplication bloquée"]
    style A fill:#bbdefb,stroke:#1565c0,stroke-width:2px,color:#000
    style B fill:#ffe0b2,stroke:#e65100,stroke-width:2px,color:#000
    style C fill:#fff9c4,stroke:#f9a825,stroke-width:2px,color:#000
    style STOP fill:#ffcdd2,stroke:#c62828,stroke-width:2px,color:#000

La règle d’or qui en découle : une seule source de vérité par table. Jamais la même table écrite des deux côtés.

Pourquoi pas de réplication native

#

Restait la réplication logique native de PostgreSQL. Mais elle n’existe qu’à partir de la version 10. Le serveur B, en 9.6, ne peut pas être abonné. La réplication physique, elle, exige des versions majeures identiques. Les deux sont éliminées d’office.

Le flux retenu est donc simple et unidirectionnel : on dumpe A, on adapte, on restaure sur B.

graph LR
    A[("Serveur A
PG 15 / PostGIS 3.5")] -->|"pg_dump"| D["Dump SQL"]
    D -->|"adaptation 15 → 9.6"| D2["Dump compatible 9.6"]
    D2 -->|"psql"| B[("Serveur B
PG 9.6 / PostGIS 2.5")]
    style A fill:#bbdefb,stroke:#1565c0,stroke-width:2px,color:#000
    style B fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px,color:#000
    style D fill:#eeeeee,stroke:#616161,stroke-width:2px,color:#000
    style D2 fill:#fff9c4,stroke:#f9a825,stroke-width:2px,color:#000

Le portage 15 → 9.6 : sept difficultés

#

C’est ici que le « sens interdit » se paie. Chaque tentative de rejouer le dump de structure sur une base test 9.6 a buté sur une construction apparue dans une version PostgreSQL postérieure. Les voici dans l’ordre, avec pour chacune le symptôme, la cause et la correction.

1. Un paramètre de configuration inconnu

#

Ce paramètre est apparu en PostgreSQL 12. Le dump de la 15 l’écrit, la 9.6 ne le connaît pas. C’est purement cosmétique : on supprime la ligne.

sed -i "/^SET default_table_access_method/d" stareau_schema.sql

2. Le search_path vidé casse gen_random_uuid()

#

Piège subtil. pg_dump insère set_config('search_path', '', false) pour forcer les noms pleinement qualifiés. Résultat : la fonction gen_random_uuid(), native en PostgreSQL 13+ mais fournie par l’extension pgcrypto (dans public) en 9.6, n’est plus trouvée puisque public a quitté le chemin de recherche.

# On conserve public dans le search_path
sed -i "s/set_config('search_path', ''/set_config('search_path', 'public'/" stareau_schema.sql

3. Le type des séquences

#

La clause AS integer pour typer une séquence date de PostgreSQL 10. La 9.6 crée ses séquences en bigint implicite, ce qui reste compatible avec des colonnes id integer.

sed -i '/^    AS integer$/d' stareau_schema.sql
sed -i '/^    AS bigint$/d'  stareau_schema.sql

4. Les colonnes générées

#

Les colonnes générées (GENERATED ALWAYS AS (...) STORED) sont apparues en PostgreSQL 12. Ici, quatre colonnes d’une table de points d’eau incendie calculaient des coordonnées (Lambert 93 et WGS84) depuis la géométrie.

La correction se fait en deux temps. D’abord, transformer ces colonnes en colonnes simples (leurs valeurs existantes viendront du dump de données) :

sed -i 's/    x_l93 numeric GENERATED ALWAYS AS.*STORED,/    x_l93 numeric,/' stareau_schema.sql
# … idem pour y_l93, lon, lat

Ensuite, recréer le calcul automatique via un trigger, qui est l’équivalent fonctionnel en 9.6 :

CREATE OR REPLACE FUNCTION defense_incendie.pei_calc_coords()
RETURNS trigger AS $$
BEGIN
  IF NEW.geom IS NOT NULL THEN
    NEW.x_l93 := ST_X(ST_Centroid(NEW.geom));
    NEW.y_l93 := ST_Y(ST_Centroid(NEW.geom));
    NEW.lon   := ST_X(ST_Transform(ST_Centroid(NEW.geom), 4326));
    NEW.lat   := ST_Y(ST_Transform(ST_Centroid(NEW.geom), 4326));
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_pei_calc_coords
  BEFORE INSERT OR UPDATE OF geom ON defense_incendie.pei
  FOR EACH ROW
  EXECUTE PROCEDURE defense_incendie.pei_calc_coords();

5. EXECUTE FUNCTION dans les triggers

#

Le mot-clé EXECUTE FUNCTION date de PostgreSQL 11. La 9.6 n’accepte que l’ancienne forme EXECUTE PROCEDURE, au comportement identique. Un détail qui vaut aussi pour le trigger écrit à l’étape précédente.

sed -i 's/EXECUTE FUNCTION/EXECUTE PROCEDURE/g' stareau_schema.sql

6. Les extensions manquantes

#

Le schéma requiert pgcrypto (pour gen_random_uuid en 9.6) et hstore. Une comparaison des extensions installées de part et d’autre révèle un paradoxe instructif : pgcrypto est absente du serveur A… parce qu’elle y est native (PostgreSQL 15). Elle n’avait jamais eu besoin d’être installée. Sur la cible 9.6, il faut la créer explicitement.

psql -d stareau_test -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
psql -d stareau_test -c "CREATE EXTENSION IF NOT EXISTS hstore;"

7. Les domaines exclus du dump

#

Deux domaines métier StaR-Eau — c_insee (un text avec contrainte de format sur le code INSEE) et c_annee — vivent dans le schéma public. Or le dump exclut public pour éviter d’emporter l’appareillage PostGIS 3.5 incompatible avec la 2.5. L’exclusion, trop large, laisse aussi ces domaines de côté.

La parade : les extraire à part et les charger avant la structure.

CREATE DOMAIN public.c_annee AS integer;
CREATE DOMAIN public.c_insee AS text
  CHECK (VALUE ~ '^([013-9]\d|2[AB1-9])\d{3}$');

Industrialisation dans un script

#

Une fois le portage validé à la main, il fallait le rendre reproductible. Le script pg_dump → adaptation → archivage → restauration reprend les conventions d’un script de sauvegarde existant (configuration sourcée, arborescence datée, rotation par rétention).

Deux principes de conception ont guidé le script.

graph TB
    subgraph "Phase 1-2 : préparation"
        P1["Découverte auto des schémas"]
        P2["Dump structure + données"]
        P3["7 adaptations 15 → 9.6"]
    end
    subgraph "Phase 3 : archivage"
        P4["Zip horodaté + rétention"]
    end
    subgraph "Phase 4 : restauration"
        P5{"Mode ?"}
        P6["test : base recréée"]
        P7["prod : purge + dump sécurité"]
    end
    P1 --> P2 --> P3 --> P4 --> P5
    P5 -->|"--restore test"| P6
    P5 -->|"--restore prod"| P7
    style P5 fill:#fff9c4,stroke:#f9a825,stroke-width:2px,color:#000
    style P6 fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px,color:#000
    style P7 fill:#ffe0b2,stroke:#e65100,stroke-width:2px,color:#000

La découverte automatique des schémas. Plutôt que de figer une liste, le script interroge le catalogue pour dumper tout schéma métier présent. Un nouveau schéma créé plus tard est ainsi pris en compte sans toucher au code.

SCHEMAS=$(psql -d stareau -At -c \
  "SELECT nspname FROM pg_namespace
   WHERE nspname NOT IN ('public','information_schema','topology','tiger','tiger_data')
     AND nspname NOT LIKE 'pg_%';")

Trois niveaux de sécurité de restauration. Sans option, le script se contente de dumper et d’archiver, sans jamais écrire sur la cible. --restore test vise une base bac à sable recréée à neuf. --restore prod est le seul mode qui touche la production, et il déclenche d’abord un dump de sécurité automatique.

Difficultés d’exploitation du script

#

Passer du fonctionnement manuel à l’automatisation a révélé une deuxième série de pièges, plus insidieux car liés à l’environnement.

Le fichier .pgpass

#

Deux erreurs classiques ont fait redemander le mot de passe malgré un .pgpass rempli. D’abord, le champ hôte doit correspondre littéralement à ce que passe l’option -h : une IP dans le fichier ne matche pas un localhost dans la commande. Ensuite, le mode restauration se connecte aussi à la base postgres (pour les DROP/CREATE DATABASE), pas seulement à stareau. La solution tient dans un wildcard sur la base :

10.10.22.100:5432:*:dyndb:MOTDEPASSE
195.42.149.140:5432:*:dyndb:MOTDEPASSE

Le nettoyage garanti des fichiers temporaires

#

Le script laissait traîner les fichiers .sql volumineux à côté des archives. La correction robuste ne consiste pas à ajouter un rm en fin de script — qui serait sauté en cas d’erreur — mais à utiliser un trap qui s’exécute quoi qu’il arrive en sortie.

cleanup() {
    [ -n "$SCHEMA_RAW" ] && rm -f "$SCHEMA_RAW"
    [ -n "$SCHEMA_SQL" ] && rm -f "$SCHEMA_SQL"
    [ -n "$DATA_SQL" ]   && rm -f "$DATA_SQL"
}
trap cleanup EXIT

Restaurer sur une base déjà peuplée

#

Dernier obstacle, en mode production. Contrairement à la base test recréée vierge, la vraie base cible existe déjà et contient les objets. Les CREATE se heurtent à l’existant, avec un le type « c_annee » existe déjà.

Puisque le serveur A est la source de vérité et la cible en lecture seule, la stratégie retenue est de repartir de zéro : purger les schémas métier avant de recharger.

for s in $SCHEMAS; do
    psql -h "$DST_HOST" -U "$DST_USER" -d "$DB" \
         -c "DROP SCHEMA IF EXISTS \"$s\" CASCADE;"
done

Vérification

#

Le contrôle final compare le nombre de tables par schéma entre la source et la cible.

psql -h 195.42.149.140 -U dyndb -d stareau -c "
SELECT schemaname, count(*) FROM pg_tables
WHERE schemaname NOT IN ('public','information_schema') AND schemaname NOT LIKE 'pg_%'
GROUP BY schemaname ORDER BY schemaname;"

Conclusion

#

Ce que vous retenez de ce retour d’expérience :

• Le sens de migration récent → ancien n’est pas supporté : chaque construction postérieure à la version cible doit être adaptée à la main.
• Une base « vidée » de ses schémas se restaure proprement, là où une base peuplée rejette les CREATE.
• L’automatisation a ses propres pièges : .pgpass, $HOME en cron, nettoyage garanti par trap.
• Travailler en bac à sable avec ON_ERROR_STOP=1 transforme un mur d’erreurs en une liste d’étapes.