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 :

Génération de clés primaires automatiques

  • 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 est nécessaire
  • réexporter la structure des tables avec colonne raster de l’ancienne instance
  • l’importer dans la nouvelle instance et vérifier les erreurs
  • pour deux instances sur la même machine, sous Linux :
pg_dump -Fp -p 5432 -s -t "tbl_rast” -d base_raster > table_raster.sql
psql -p 5433 -f table_raster.sql -d base_raster 2> erreurs_raster.txt

Réplication logique

Principes

Publications sur l’ancienne instance

  • dans chaque base de données
  • créer une publication pour toutes les tables de la base
  • nommer différemment chaque publication
  • exemple de commande SQL :

Cas particulier de la table spatial_ref_sys

  • table présente et préremplie à l’installation de l’extension PostGIS
  • contient toutes les références de projections spatiales
  • son contenu dépend des versions de PostGIS (ajouts)
  • si on la laisse, conflit de réplication
  • sur la nouvelle instance, pour chaque base avec l’extension PostGIS, la recopier dans une autre table puis la vider :

Souscriptions sur la nouvelle instance

  • dans chaque base de données
  • créer une souscription vers la publication correspondante
  • nommer différemment chaque souscription
  • exemple de commande SQL :

Suivi de l’avancement de la réplication

  • sur la nouvelle instance
  • interroger la vue système pg_subscription_rel
  • la colonne srsubstate donne l’état de la souscription de chaque table :
    • i = initialisation
    • d = copie des données en cours
    • s = synchronisé
    • r = prêt
  • la recopie des données est terminée quand toutes les tables sont à l’état r
  • les modifications des données de l’ancienne instance continuent d’être répliquées

Changement d’instance

Coupure de l’accès à l’ancienne instance

  • on commence par bloquer les nouveaux accès aux différentes bases
  • interroger la vue système pg_stat_activity pour lister les connexions résiduelles
  • on stoppe les connexions en cours (manuellement ou automatiquement)
  • modifier pg_hba.conf pour éviter toute reconnexion

Recopie de l’avancement des séquences

  • la réplication logique ne rejoue pas l’avancement des séquences
  • il faut donc les mettre à jour sur la nouvelle instance depuis l’ancienne
  • pour chaque base de données, recopier l’état d’avancement des différentes séquences
  • plusieurs solutions possibles : commandes shell, SQL dynamique dans psql, pg_dump, etc.
  • exemple de code bash pour l’automatiser :
psql -p 5432 -XAtqc "SELECT \$\$select setval('\$\$ || quote_ident(schemaname)||\$\$.\$\$|| quote_ident(sequencename) || \$\$', \$\$ || last_value || \$\$); \$\$ AS sql FROM pg_sequences" ma_base_1 \
| psql -p 5433 ma_base_1

Basculement sur la nouvelle instance

  • mise à jour des paramètres de configuration (port, shared_buffers, etc.)
  • recopie des fichiers pg_hba.conf et pg_ident.conf
  • modification des paramètres de connexion des applications (si nécessaire)
  • arrêt de l’ancienne instance
  • redémarrage de la nouvelle instance
  • suppression des souscriptions pour chaque base sur la nouvelle instance :

Mise à niveau de spatial_ref_sys

  • pour chaque base PostGIS, spatial_ref_sys contient les anciennes références
  • récupération des nouvelles références depuis la copie précédente
  • puis suppression de la table de copie

Conclusions

Une méthode efficace

  • la réplication logique permet de préparer la migration sans couper l’instance de production
  • elle permet de faire le basculement vers la nouvelle version quand tout y est prêt
  • le temps d’indisponibilité est très faible (< 5 min)
  • il peut être encore réduit par automatisation et pooler de connexions

Une prise en main plus délicate

  • une méthode moins simple que les autres méthodes natives
  • la complexité augmente avec le nombre de bases sur l’instance (paramétrage du nombre de souscriptions)
  • elle nécessite de se l’approprier par la pratique et la répétition
  • comme pour toute montée de version, testez, testez et testez au préalable

Merci de votre attention

Des questions ?     #pgsession13