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.