Utiliser la réplication logique pour une montée de version majeure PostgreSQL / PostGIS

Cédric Duprez

19/11/2020

Qui suis-je ?

IGN : Institut national de l’information géographique et forestière

référent bases de données techniques
administrateur des données et DBA de l’inventaire forestier national

membre du PGGTIE
contributeur PostgreSQL : relecture de la documentation française

Montée de version majeure

Différentes méthodes

méthode "classique" par sauvegarde / restauration :

simple et robuste
pas de connexion possible durant la migration
durée d'indisponibilité proportionnelle à la volumétrie

pg_upgrade :

copie physique des fichiers de données
méthode un peu plus complexe (librairies PostGIS)
pas de connexion possible durant la migration (mais moins longtemps)

Réplication Slony / pg_logical :

temps d’interruption fortement diminué
nécessite installation et configuration d’outils externes
besoin de compétences supplémentaires

Réplication Logique

solution native
disponible depuis la v10
temps d’interruption fortement diminué
des étapes à respecter
quelques particularités liées à PostGIS

Préparation de l’ancienne instance

Vérifications préalables

lister les bases avec leur volumétrie : \l+ (psql)
vérifier l’espace disque disponible (surtout si la nouvelle instance est sur le même serveur)
pour chaque base, liste des extensions installées : \dx (psql)
pour chaque base, vérifier que toutes les tables ont une clé primaire :

    SELECT tbl.table_schema, tbl.table_name
    FROM information_schema.tables tbl
    WHERE table_type = 'BASE TABLE'
    AND table_schema NOT IN ('pg_catalog', 'information_schema')
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.key_column_usage kcu
        WHERE kcu.table_name = tbl.table_name
        AND kcu.table_schema = tbl.table_schema
    )

Génération de clés primaires automatiques

WITH cles_abs AS (
    SELECT tbl.table_schema, tbl.table_name
    FROM information_schema.tables tbl
    WHERE table_type = 'BASE TABLE'
    AND table_schema NOT IN ('pg_catalog', 'information_schema')
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.key_column_usage kcu
        WHERE kcu.table_name = tbl.table_name
        AND kcu.table_schema = tbl.table_schema
    )
)
SELECT 'ALTER TABLE' || table_schema || '.' || table_name 
    || ' ADD COLUMN id INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY;'
FROM cles_abs
ORDER BY table_schema, table_name;

même principe pour préparer les requêtes supprimant ces clés en fin de migration (si voulu)

Préparation de la réplication logique et sauvegarde

wal_level = logical
redémarrage de l’instance
faire une sauvegarde complète, y compris les fichiers de configuration

pg_dumpall | gzip -c > fichier_sauvegarde.sql.gz

Installation de la nouvelle instance

Installation et paramétrage

depuis les dépôts communautaires
installer les packages nécessaires aux extensions (PostGIS notamment)
mise à jour de quelques paramètres de configuration :

port d’écoute
shared_buffers (attention si la nouvelle instance est sur le même serveur que l’ancienne)
effective_cache_size
max_worker_processes = max_logical_replication_workers + 1

redémarrer la nouvelle instance

Recopie de la structure et des rôles

fin des changements de structure (ordres DDL)
sauvegarde de la structure (sans les données) de l’ancienne instance
sauvegarde des rôles de connexion de l’ancienne instance
restauration des deux sur la nouvelle instance
rediriger les erreurs et les analyser
pour deux instances sur la même machine, sous Linux :

pg_dumpall -p 5432 -s | psql -p 5433 2> erreurs.txt

Cas particulier du passage à PostGIS 3

les tables avec colonnes raster font partie des erreurs de l’étape précédente
fonctions et types raster dans une nouvelle extension postgis_raster
se connecter à la nouvelle instance et installer l’extension postgis_raster dans chaque base où elle e