Je t’explique simplement comment configurer PostgreSQL pour un environnement de Production

 

Je t’explique simplement comment configurer PostgreSQL pour un environnement de Production

Donatien Mbadi, Architecte de Bases de Données

PostgreSQL est probablement le système de bases de données open source le plus avancé. Depuis 1989, il a bénéficié de nombreuses améliorations.

PostgreSQL est un serveur de bases de données ayant une architecture orientée sur un modèle multiprocessus, plutôt que multithreads. Ainsi il utilise un processus distinct pour chaque connexion client. PostgreSQL est utilisé par les grandes organisations du monde tels que des startups, des entreprises du Fortune 500 parmi lesquels Apple, Spotify, Netflix, Fujisu, RedHat, Cisco, Istagram. PostgreSQL est également soutenu par Amazon et Microsoft qui proposent respectivement Amazon RDS et Azure Database for PostgreSQL.

Dans un article précédent, je t’ai expliqué simplement les éléments qui composent l’architecture physique et logique de PostgreSQL.

Dans cet article, nous allons discuter de l’installation et la configuration d’un serveur de bases de données PostgreSQL pour un environnement de production. Nous allons utiliser un environnement Oracle Linux 10, mais c’est valable également si tu utilises Windows ou une autre variété d’Unix ou de Linux.

Nous supposons que Linux est déjà installé par les Administrateurs Systèmes car c’est généralement le cas en production.

Configuration du serveur (dédié)

Nous allons utiliser la configuration minimale suivante :

-     4 processeurs

-     16Go de RAM

-     Stockage SSD de 100Go

-     Réseaux

Installation de PostgreSQL-18 sous Linux 10

PostgreSQL est disponible au téléchargement sous forme de packages ou d'installateurs prêts à l'emploi pour différentes plateformes, ainsi que sous forme d'archive de code source si vous souhaitez le compiler vous-même. Il vous suffit de sélectionner votre système d'exploitation. On suppose ici que vous êtes connecté sur Internet.

Étape 1 : Ajouter le référentiel PostgreSQL-18

Par défaut, la dernière version de PostgreSQL n’est pas incluse dans le référentiel Oracle Linux; alors vous devez l’ajouter en exécutant la commande:

dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 

…

 

Étape 2 : Installation

Mettre à jour le référentiel avec la commande :

dnf update -y

…

 

Pour installer PostgreSQL-18, exécuter la commande :

dnf install postgresql18 postgresql18-server --nogpgcheck

 

…

Initialiser PostgreSQL en exécutant la commande :

/usr/pgsql-18/bin/postgresql-18-setup initdb

Démarrer l’instance PostgreSQL et l’activer à démarrer automatiquement au démarrage du serveur :

systemctl start postgresql-18

systemctl enable postgresql-18

 

Vérification de base

-     Vérifier la version de PostgreSQL :

psql --version

 

 -     Vérifier le statut :

systemctl status postgresql-18

-    Visualiser le dictionnaire de données

ls -la /var/lib/pgsql/18/data

-    Répertoire des logs

ls -la /var/lib/pgsql/18/data/log

Configuration de l’authentification

PostgreSQL utilise 2 fichiers de configuration pour les authentifications : pg_hba.conf et postgresql.conf.

Le fichier pg_hba.conf (PostgreSQL Host Based Authentication Configuration File) contrôle qui se connecte à la base de données PostgreSQL et comment il est authentifié. En d’autres termes, lorsqu’un utilisateur initie une connexion, PostgreSQL vérifie les règles contenues dans le fichier pg_hba.conf. Ces règles sont :

-     Contrôle de l'authentification des clients

-     Définition des adresses IP autorisées

-     Spécification des méthodes d'authentification

-     Amélioration de la sécurité de la base de données

Exemple :

La ligne en bleu signifie que PostgreSQL autorise les connexions en local sur le serveur, a toutes les bases de données (all) et a tous les utilisateurs (all) sans fournir de mot de passe (peer). Cette configuration n’est pas conseillée en production.

Nous allons remplacer peer par scram-sha-256 dans le fichier pg_hba.conf :

Puis on redémarre PostgreSQL pour la prise en compte du changement :

Testons de nouveau la connexion :

La ligne en rouge que PostgreSQL autorise les connexions depuis toutes adresses de la plage 192.168.123.0/32 a toutes les bases de données et a tous les utilisateurs en utilisant l’authentification scram-sha-256.

Les différentes méthodes d’authentification de PostgreSQL sont les suivantes :

-     peer : utilisé pour les connexions locales sur le serveur. Il utilise l’authentification par le système d’exploitation et non recommandé en production

-     md5 : Mot de passe basé sur l’authentification avec un hashage MD5

-     scram-sha-256 : méthode moderne d’authentification qui protège contre l'interception de mots de passe et le piratage de mots de passe côté serveur, recommandée en production

-     trust : Pas d’authentification (Ne jamais l’utiliser en production)

-     reject : rejette toutes les connexions

 

Voici un exemple de contenu du fichier pg_hba.conf en production :

Le fichier postgresql.conf est le fichier principal de configuration du serveur postgreSQL. Pendant que pg_hba.conf gère l’authentification.

 Ci-dessous un tableau comparatif des fichiers pg_hba.conf et postgresql.conf :

Fonctionnalités	pg_hba.conf	postgresql.conf
But	Contrôle de Authentification	Configuration du serveur
Sécurité	Défini qui peut se connecter	Contrôle les paramètres du serveur
Orientation	Access utilisateur et restrictions des Ips	Réglage des performances
Configuration	Authentification basée sur l’hôte de la base de données	Mémoire, connexions, journalisation
Usage par le DBA	Gestion de la sécurité	Optimisation de la base de données

 

 

 

Configuration d’un mot de passe pour le super utilisateur postgres

Se connecter a postgreSQL puis exécuter la commande ALTER USER…

Optimisation des performances

Mémoire

Le fichier postgresql.conf contrôle les paramètres du serveur, ses performances, l’utilisation de la mémoire etc. ce fichier est utilisé par les administrateurs de bases de données pour optimiser les performances. Les fonctions principales de postgresql.conf :

-     Contrôle des paramètres du serveur

-     Gestion de la configuration de la mémoire

-     Configuration des paramètres de journalisation

-     Définition des limites de connexion

-     Optimisation des performances de la base de données

Voici un exemple de contenu du fichier postgres.conf en production :

On peut donc retenir cette configuration recommandée pour la mémoire :

• shared_buffers: 25% de votre RAM
• effective_cache_size: 75% de votre RAM
• maintenance_work_mem: 5% de votre RAM
• work_mem: RAM / (max_connections * 2-3)

Plans d’exécution des requêtes

Pour optimiser les plans d’exécutions des requêtes SQL, quelques paramètres doivent être pris en compte dans le fichier postgresql.conf, parmi lesquels :

# Plan execution

random_page_cost = 1.1               # Pour le stockage SSD

effective_io_concurrency = 200       # Pour le stockage SSD

default_statistics_target = 100      #Niveau de détail des statistiques

#Pour les disques traditionnels:

random_page_cost = 4.0

effective_io_concurrency = 2

Occupation de disque (Vacuum et autovacuum)

La commande VACUUM est essentielle pour la maintenance et les performances des bases de données PostgreSQL. Elle analyse les tables à la recherche d'enregistrements obsolètes, les marque comme réutilisables ou les supprime. Un nettoyage régulier optimise l'utilisation du disque et améliore les performances des requêtes. Sans elle, la taille des tables peut croître indéfiniment, entraînant une dégradation des performances.

PostgreSQL a une option pour exécuter cette tâche automatiquement. On l’appelle alors AUTOVACUUM. On la configure dans le fichier postgresql.conf. Ci-dessous un exemple en production

Configuration des stratégies de sauvegarde

Les bonnes pratiques recommandent trois copies de vos données au moins dans deux types de médias parmi lesquels, une copie en dehors de votre site. Dans PostgreSQL, vous avez des sauvegardes logiques et des sauvegardes physiques. Comprendre la différence entre ces deux sauvegardes est crucial pour sélectionner la bonne approche.

Les sauvegardes logiques exportent les objets de la base de données sous forme de requêtes SQL, afin de rendre les sauvegardes portables à travers les versions. Elles sont recommandées pour des petites et moyennes bases de données. On utilise la commande pg_dump pour effectuer les sauvegardes logiques.

Les sauvegardes physiques copient les fichiers de données; ce qui permet des opérations de sauvegarde et de restauration plus rapides. On utilise la commande pg_basebackup pour effectuer les sauvegardes logiques. Elles sont recommandées pour de grosses bases de données de production. Ils incluent les sauvegardes complètes et les sauvegardes incrémentales.

 

Je t'explique l’Architecture de PostgreSQL simplement

 

Je t'explique l’Architecture de PostgreSQL simplement

 Donatien Mbadi, Architecte Bases de Données

PostgreSQL est probablement le système de bases de données open source le plus avancé. Depuis 1989, il a bénéficié de nombreuses améliorations.

PostgreSQL est un serveur de bases de données ayant une architecture orientée sur un modèle multiprocessus, plutôt que multithreads. Ainsi il utilise un processus distinct pour chaque connexion client.

Dans cet article, nous allons discuter des différents composants de l’architecture de PostgreSQL et de comment ils interagissent entre eux.

L’architecture de PostgreSQL se divise en trois :

-     Une architecture de mémoire

-     Une architecture de processus

-     Une architecture de fichiers

                                                                                                                    Schéma de l’architecture de PostgreSQL

 

Dans PostgreSQL, une instance peut contenir une ou plusieurs bases de données. C’est pour cette raison nous désignons une instance de bases de données par un Cluster de bases de données.

Lorsque vous démarrez votre instance PostgreSQL, le premier processus qui démarre s’appelle le processus Postmaster. Il joue le rôle de superviseur et de module d’écoute et est responsable de l’authentification et l’autorisation de toute demande client. Poastmaster assigne un nouveau processus Postgres pour chaque nouvelle connexion et le contrôle durant toute la durée de la connexion.

 

Architecture de mémoire

Lorsqu’une instance PostgreSQL est démarrée, la mémoire partagée (Shared Memory) est allouée. Cette mémoire est réservée aux transactions et aux tâches de maintenance. Elle contient plusieurs segments pour différentes opérations. Nous décrivons dans la suite, les plus importants.

Le tampon partagé (Shared Buffers)

Le tampon partagé est la quantité de mémoire dédiée utilisée pour maintenir les données en cache avant qu’elles ne soient écrites sur disque par le processus Background Writer. Il permet de minimiser les entrées/sorties sur le disque. Il opère comme un cache primaire permettant un accès concurrent aux processus pour accéder aux données. Le paramètre Shared_buffers détermine la taille de cette zone mémoire. La valeur par défaut est 128M.

Le tampon de journalisation anticipé (Wal Buffers)

Les Wal Buffers sont une zone de mémoire partagée dédiée au stockage temporaire des enregistrements du journal de transactions, avant qu’ils ne soient écrits les Wal Segments sur le disque par le processus Wal Writer, lorsque les transactions sont validées et ceci, de manière cyclique. Ils sont essentiels pour assurer la durabilité des transactions et la récupération après une panne. Le paramètre Wal_buffers détermine la taille de cette zone.

Les autres zones de mémoire

La zone de mémoire Work Memory est utilisée pour les opérations de tri, de jointure. Elle est contrôlée par le paramètre work_mem.

La zone Mantenance work est utilisée pour les opérations de maintenance tels que la création des index etc. elle est contrôlée par le paramètre maintenance_work_mem.

Le tampon Temp est une zone utilisée lors de l’accès aux tables temporaires durant les opérations de tri. Il est spécifique à une session.

Architecture de processus

Les processus en arrière-plan sont utilisés pour maintenir la consistance entre la mémoire et les disques. Chaque processus a un rôle bien particulier.

Les processus les plus courants sont les suivants :

Le processus background writer

 Le processus background writer est un processus de serveur dédié dont la fonction est d’écrire les pages (Nouvelles données ou données modifiées) du cache partagé vers le disque

Les processus Wal writer et Archiver

Le processus Wal writer est un processus en arrière-plan responsable de la durabilité des données et la fiabilité des transactions. Il écrit toutes les transactions de journal (WAL) vers le disque.

Lorsque que la base de données est en mode Archive Log, le processus Archiver copie automatiquement les journaux de transactions (WAL) vers un emplacement sécurisé dès qu’ils sont pleins. Il est activé via le paramètre archive_mode=on dans le fichier postgresql.conf.

Les autres processus

Le processus logger est utilisé pour écrire les messages dans le fichier de log. La destination de ce fichier est définie dans le fichier des paramètres postgresql.conf.

Le processus Autovacuum maintient la santé de la base de données en libérant l’espace disque occupé par les lignes supprimées or obsolètes et en mettant les statistiques à jour.

Le processus Checkpointer permet de gérer et séparer les points de synchronisation, permettant ainsi de raccourcir le temps de récupération après un arrêt brutal.

Le processus Logger est chargé de capturer les message d’erreurs, d’avertissement et d’information envoyés par les processus serveurs et de les enregistrer dans les fichiers journaux

Le processus Logical Replication est utilisé pour les gérer les réplications logiques dans PostgreSQL

Le processus IO Workers récemment introduit dans PostgreSQL est conçu pour améliorer les performances en déléguant les opérations de lecture/écriture bloquantes sur disque à des processus d’arrière-plan dédiés, permettant ainsi au processus principal de continuer à traiter des données sans attendre que le disque réponde, simulant ainsi un comportement asynchrone.

Architecture de fichiers

Les fichiers physiques sont utilisés pour stocker des données dans les fichiers de données (Data files), des changements de données dans les fichiers de journalisation (Wal files), des changements archivés dans les fichiers de journalisation archivés (Archive logs) et des messages d’erreurs et d’avertissement dans les fichiers journaux (log files).

Les fichiers de données

Les fichiers de données servent à stocker les données brutes. Ils contiennent uniquement les données, sans aucune instruction ni information de code. Lorsqu'un utilisateur demande des données, PostgreSQL les recherche dans le tampon partagé. Si les données ne s'y trouvent pas, il les charge depuis les fichiers de données présents dans le tampon partagé, puis les traite.

Les fichiers de journalisation

Les fichiers de journalisation servent à enregistrer toutes les modifications apportées au tampon WAL avant la validation. Ils sont principalement utilisés pour garantir la durabilité et la cohérence des données lors d'une opération d'écriture sur le stockage de la base de données.

Les fichiers de journalisation archivés

Les fichiers de journalisation archivés servent à stocker le segment WAL sur le disque. Ces journaux sont utilisés en cas de panne inattendue entraînant une perte de données ; ils permettent alors de réparer ou de récupérer la base de données.

Les fichiers journaux

Les fichiers journaux stockent tous les journaux relatifs au serveur : stderr, csvlog, Syslog, messages d’erreur, d’avertissement, d’information, etc. Ils permettent à l’administrateur de la base de données de déboguer facilement tout problème.

Mécanismes de Commit et Checkpoint dans PostgreSQL

Lorsque vous exécutez des transactions dans PostgreSQL, avant que la transaction ne soit validée (Commit), tous les changements sont maintenus en mémoire dans le tampon partagé et le tampon de journalisation. Lorsque vous effectuez la validation, les changements sont écrits dans le fichier de journalisation par le processus Wal Writer, pour assurer la durabilité et les changements sont marqués comme validé dans le tampon partagé et visibles pour toutes les sessions. Le tampon partagé continue d’être synchronisé par des opérations de validations, jusqu’au moment du point de synchronisation (Checkpoint); moment auquel toutes les transactions marquées validées sont écrites dans les fichiers de données par le processus Background Writer.

Mutiples instances sur PostgreSQL

Vous pouvez créer plusieurs instances de bases de données sur PostgreSQL. Chaque instance va utiliser son propre répertoire de données …/data, son propre fichier de configuration …pg_hba.conf son propre port d’écoute …5444, 5434 ainsi que ses propres processus et mémoires.

 

Exécution d’une requête dans PostgreSQL

L’exécution d’une requête PostgreSQL suit un processus rigoureux.

Analyse : PostgreSQL vérifie la syntaxe SQL et s’assurent de l’existence des objets. Cette phase permet aussi d’appliquer les règles définies, notamment sur les vues et transforme la requête si nécessaire.

Optimisation : Le planification analyse différentes stratégies d’accès aux données (analyses séquentielles, index, jointures) et génère le plan le moins couteux.

Exécution : Le moteur exécute le plan, parcourt les tables, applique les filtres et renvoie les résultats.