PostgreSQL est un système de gestion de base de données nécessaire au fonctionnement d’une part importante de nos services dont Matrix, Git, Nextcloud ou encore Liens. Il est utilisé pour conserver les données des utilisateur·ices, ce qui fait de ce logiciel un composant vital et critique pour le fonctionnement de nos services.
L’adoption de PostgreSQL remonte à la création de l’association avec la version 11 du logiciel. Il est tout d’abord utilisé pour servir de base de données à l’espace membre.
Après la mise en service de l’infrastructure V2 durant le premier semestre de 2022, une instance « standby » de PostgreSQL a été installée sur Antigone à des fins de sauvegarde et de restauration rapide en cas de panne.
Nous utilisons l’image officielle de PostgreSQL library/postgres en version stable (avec un numéro de version majeure explicite) sans modification. Retrouvez le dossier dédié à PostgreSQL sur notre dépôt Core.
Le nom du volume utilisé par notre instance PostgreSQL inclut également le numéro de version majeure de PostgreSQL pour faciliter sa mise à jour.
Le conteneur de PostgreSQL est connecté à chaque réseau virtuel des services qui ont besoin de l’utiliser comme base de données.
Notre instance standby de PostgreSQL, hébergée sur Antigone, est configurée de manière similaire.
Un guide pas-à-pas est disponible pour installer et configurer une instance standby.
Les journaux de ce service sont généralement assez explicites pour indiquer clairement des erreurs. En cas d’impossibilité de joindre la base de données, il convient de vérifier si l’IP sur laquelle l’instance est censée être accessible est bien listée dans le paramètre de configuration listen_addresses.
En date de juillet 2023, notre instance PostgreSQL héberge 10 bases de données d’une taille totale de 2.3 Go (contre 2 Go en octobre 2022) et consomme en moyenne 400 Mo de RAM.
Des statistiques sur l’espace disque consommé par chaque base de données sont indiquées sur les pages de chaque service qui utilise PostgreSQL.
PostgreSQL héberge de précieuses données, ce pourquoi il semble pertinent d’avoir une instance standby sur une machine distante, en plus des sauvegardes régulières, pour pouvoir remonter une instance PostgreSQL en panne avec le moins de délai possible.
Le port PostgreSQL ne doit sous aucun prétexte être exposé à l’extérieur. Nous utilisons le paramètre listen_addresses pour limiter les adresses IP sur lesquelles notre serveur PostgreSQL écoute, et avons imposé une politique d’authentification obligatioire et systématique lors de l’accès à la base de données, même si cet accès s’effectue en local dans le conteneur.
Il peut arriver régulièrement qu’une intervention humaine soit nécessaire sur la base de données. Voilà une manière possible de procéder :
docker exec -it postgres-db bash
psql -U <PG_USER> -p <PG_PORT>
La commande \l+ liste toutes les bases de données et leur taille, \c <DATABASE_NAME> permet de se connecter à une base de données spécifique, puis \dt affiche les tables disponibles.
SELECT pg_reload_conf() permet de recharger la configuration de PostgreSQL, y compris le fichier d’autorisations pg_hba.conf.
Dans des situations exceptionnelles, l’excellent utilitaire pgAdmin peut être employé pour intervenir sur les bases de données en production. Ce puissant outil offre une interface graphique et des outils de diagnostic qui peuvent s’avérer pratiques lorsque la ligne de commande montre ses limites.
Pour interagir avec l’instance PostgreSQL en production depuis le client lourd sans exposer publiquement le port de PostgreSQL, une solution simple consiste à effectuer un port forwarding SSH. En alternative, pgAdmin existe également sur DockerHub et peut être hébergé sur le serveur en production derrière le reverse-proxy et une Basic Authentication.
Idéalement, les journaux de ce service doivent être consultés régulièrement et supervisés afin de détecter rapidement une ou un défaut de fonctionement (dans le pire des cas : corruption de la base de données), ce qui entraînereait potentiellement une panne des services et peut-être des pertes de données.
Il arrive toutefois régulièrement que PostgreSQL affiche des erreurs qui ne sont ni critiques, ni dues à un problème de corruption ou de configuration, en particulier en provenance des services Nextcloud et Matrix : ces services ont tendance à envoyer des requêtes malformées ou exécutées dans de mauvaises conditions et sont probablement dues à des bugs de leurs logiciels.
La mise à niveau de PostgreSQL vers une version mineure supérieure est relativement simple : elle consiste à télécharger la nouvelle image de PostgreSQL et à redémarrer le conteneur.
Il est préférable d’arrêter les services qui utilisent la base de données pour éviter que leurs transactions soient interrompues à cause de la panne, ce qui pourrait causer des bugs et des pertes de données, en particulier sur les logiciels qui effectuent de nombreuses requêtes par minute comme Matrix. Cette opération n’est pas absolument nécessaire (même si elle reste suggérée) pour les services qui effectuent peu de requêtes comme l’espace membre.
Une fois l’instance PostgreSQL mise à jour et redémarrée (processus très rapide, de l’ordre de quelques secondes), les connexions interrompues seront récupérées et il sera possible de redémarrer sans risque les services interrompus le temps de la maintenance.
Appliquer une mise à jour majeure de PostgreSQL n’est pas une opération triviale : elle nécessite une maintenance planifiée et une interruption de tous les services qui utilisent PostgreSQL pour une durée d’une heure. Toutefois, nous mettons systématiquement à jour notre instance PostgreSQL dès qu’une nouvelle version est disponible.
Un guide de maintenance pas-à-pas est disponible ici.
Pour éviter que l’instance standby se désynchronise trop rapidement avec l’instance primaire en cas de panne de réseau entre les deux machines, il pourrait être envisageable de mettre en place un slot de réplication sur l’instance primaire : ce slot conservera le flux WAL jusqu’à sa resynchronisation avec l’instance standby ou jusqu’à ce que sa capacité maximale soit atteiente.
Cette solution nécessite cependant d’allouer une quantité importante d’espace disque au slot de réplication (plusieurs centaines de Mo) et reste insuffisante en cas de coupupre prolongée du réseau.
Pour faciliter la sauvegarde et la restauration de la base de données en cas de panne, il peut être envisagé de tester et d’utiliser Barman.
Passer par une mise à jour majeure de PostgreSQL entraîne nécessairement une longue interruption de service simultanée pour tous les services qui utilisent l’instance PostgreSQL. Par ailleurs, cette instance héberge une dizaine de bases de données et constitue donc naturellement un point de défaillance unique.
Il pourrait être envisageable de créer une instance PostgreSQL par base de données plutôt qu’une instance pour toutes les bases de données. Cette configuration aurait plusieurs avantages :
Cela dit, elle vient également avec ses inconvénients :
services du dépôt Core et également un volume par conteneur ; c’est sans compter les instances standby qui devraient être répliquées de la même manière.Une configuration hybride pourrait toutefois être envisagée à l’avenir, les services les plus consommateurs pourraient disposer de leur propre instance PostgreSQL, tandis que les autres services pourraient être regroupés sur une instance « généraliste ».