Gérer les extensions PostgreSQL : installation, gestion des versions, mise à jour et sauvegarde

Ce que c'est

La documentation décrit une extension comme un fichier de script contenant les commandes SQL qui créent ses objets, plus un fichier de contrôle avec des propriétés telles que la version par défaut, la relocalisabilité et le fait qu'elle soit trusted ou non, avec en option une bibliothèque partagée. CREATE EXTENSION name [SCHEMA s] [VERSION v] [CASCADE] la charge dans la base de données courante ; pg_available_extensions et pg_available_extension_versions montrent ce que l'installation du serveur propose. ALTER EXTENSION name UPDATE [TO v] exécute les scripts de mise à jour de l'auteur depuis la version installée jusqu'à la cible. DROP EXTENSION retire tous les objets membres en une seule fois, et pg_dump n'écrit que la commande CREATE EXTENSION, pas les objets membres.

Pourquoi c'est important

Trois versions coexistent en permanence : les fichiers installés sur le serveur (généralement un paquet du système d'exploitation), la version enregistrée dans le catalogue de la base de données, et, pour les extensions comportant du code C, la bibliothèque chargée par le serveur en cours d'exécution. Elles divergent lors des mises à jour de paquets, lors d'un pg_upgrade, et lorsqu'une sauvegarde est restaurée sur un serveur dont les fichiers sont plus anciens ou absents. Une sauvegarde dépend donc silencieusement de la présence des fichiers d'extension au moment de la restauration.

Comment l'appliquer

• Installer les fichiers d'extension par la même voie que le serveur (paquets du système d'exploitation, ou liste d'autorisation du service géré), et consigner les extensions et versions requises dans l'historique des migrations afin qu'un environnement neuf les recrée.
• Utiliser CREATE EXTENSION IF NOT EXISTS ... SCHEMA extensions dans les migrations lorsque l'extension permet de choisir un schéma (une extension qui nomme un schéma dans son fichier de contrôle ne peut pas être outrepassée), afin que les objets membres ne masquent pas les objets applicatifs et que les search paths restent propres.
• L'installation nécessite normalement les droits superutilisateur ; une extension marquée trusted peut être installée par quiconque dispose du droit CREATE sur la base de données. La documentation avertit qu'un script écrit avec négligence peut être exploité via des objets chevaux de Troie dans son schéma d'installation ; installer donc dans des schémas où les utilisateurs non fiables ne détiennent pas de privilège CREATE.
• Après une mise à jour de paquets, comparer installed_version avec default_version dans pg_available_extensions et exécuter ALTER EXTENSION ... UPDATE lors d'une fenêtre de maintenance ; après pg_upgrade, exécuter le script de mise à jour qu'il génère.
• Les modules qui doivent figurer dans shared_preload_libraries (pg_stat_statements, auto_explain) ne prennent effet qu'après un redémarrage du serveur ; le planifier.
• Ne jamais modifier les objets membres à la main avec CREATE OR REPLACE FUNCTION ; la documentation indique que de tels changements ne sont pas sauvegardés par pg_dump.

Pièges

Les noms d'extension sont uniques par base de données, pas par schéma. CASCADE installe les dépendances à leurs versions par défaut. Les extensions qui définissent des types de données utilisés dans des tables (postgis, hstore, vector) doivent être présentes avant que les tables puissent être restaurées, sinon la restauration échoue pour ces tables, pas seulement pour les fonctions. Les services gérés publient des listes d'autorisation ; une extension hors liste bloque une migration vers ce service.