Notes PostgreSQL : Installation

posted in Bases de données, Linux, Postgres on by

Préambule

A la date de sa rédaction, cet article part du principe que l’OS utilisé est une Linux DEBIAN 12 (ou une Ubuntu 24.04) car PostgreSQL est non seulement fait pour Linux (Windows est une antinomie pour ce projet) mais de part sa nature OpenSource, la communauté Postgres est très orientée vers cet OS et tous les standards sont en premier disponibles sur ces plateformes et leur utilisation est basée sur ce concept. PostgreSQL fonctionne évidement très bien sur d’autres distributions Linux (RHEL like, Alma, SuSE, etc), mais nous n’y ferons pas référence ici pour des besoin de simplification d’écriture.

L’utilisation de Windows est un cas à part et, considérant que l’utilisation du logiciel sur cet OS ne devrait se faire que pour du développement personnel et non pas pour de la production réelle, ceci pour divers raisons techniques concrètes, cet article ne l’abordera pas ici.

Installation de base

Utilisation des repository communautaires PGDG

L’installation peut se faire avec les packages disponibles par défaut sur les distributions mais nous vous recommandons d’utiliser les repo de la communauté pour bénéficier des dernières évolutions des versions désirées et d’éviter de vous retrouver dans des cas particuliers.

Le site principal de PostGreSQL est celui ci : https://www.postgresql.org

Téléchargez les repo de la communauté Postgres, installez les et mettez à jour APT tel que :

root@postgresql> apt install curl ca-certificates
root@postgresql> installl -d /usr/share/postgresql-common/pgdg
...
root@postgresql> curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
root@postgresql> sudo apt update
...
root@postgresql>

Vous pouvez ensuite aisément installer la version de Postgres de votre choix (elles sont toutes disponibles), par exemple pour la v16 ou la v17 :

root@postgresql> apt -y install postgresql-16
...
root@postgresql> apt -y install postgresql-17
...

Pour toute version, une instance nommée « main » (ou un « cluster » dans la terminologie Postgres) par défaut est installée mais non démarrée, celle-ci se compose de:

  • Fichiers de Conf : /etc/postgresql/<version>/main
  • Fichiers de Data : /var/lib/postgresql/<version>/main

Un fichier de service SystemV est disponible dans Fichier de service est disponible à /lib/systemd/system/postgresql@service, sa particularité est que celui ci est utilisé pour lancer toutes les clusters de toutes les versions de Postgres que vous aurez installé en utilisant « systemctl {start,stop,reload} postgresql@<version>-<instance> » comme par exemple :

root@postgresql> systemclt start postgresql@17-main
...
root@postgresql> systemclt stop postgresql@16-main
...

Création des Clusters Postgres

Dans cet article, nous allons utiliser pour l’exemple plusieurs clusters Postgresql, dans la version 16 et dans la version 17, pour les créer, nous utiliserons l’utilitaire pg_createcluster fourni la les common tools de Postres.

Nous positionnerons chacun des dossier Data de chaque cluster dans une partition liée à l’instance que nous nommerons « /data/pggsql/<version>/<instance>/« . Il faut modifier pour cela la directive « directory_data » présente dans « /etc/postgresql/<version>/<instance>/postgresql.conf » et la faire pointer vers le dossier de datas précédent : 

root@postgresql> mkdir -p /data/pgsql/16/cluster_01 && mkdir /data/pgsql/16/cluster_02
root@postgresql> mkdir -p /data/pgsql/17/cluster_01 && mkdir /data/pgsql/17/cluster_02
root@postgresql> chown -R postgres:postgres /data/pgsql
root@postgresql> chmod -R 700 /data/pgsql
root@postgresql> pg_createcluster 16 cluster_01 -d /data/pgsql/16/cluster_01
...
root@postgresql> pg_createcluster 16 cluster_02 -d /data/pgsql/16/cluster_02
...
root@postgresql> pg_createcluster 17 cluster_01 -d /data/pgsql/17/cluster_01
...
root@postgresql> pg_createcluster 17 cluster_02 -d /data/pgsql/17/cluster_02
...

Gestion des filesystem

Pour des raisons d’isolation des I/O et des problématiques de gestion des remplissages des partitions fondamentales sous Postresql, nous vous invitons à créer un FileSystem différent pour chaque Cluster. Nous optons pour notre article de créer les partitions suivantes :

Chaque volume et partition aura la caractéristique d’être un LVM (pour le redimensionnement dynamique à chaud des partitions et les snapshots) et avec un filesystem de type XFS (principalement pour sa gestion des tailles de fichiers et sa gestion de la déduplication).

Nous choisissons pour notre part d’utiliser la structure LVM/XFS suivante :

root@postgresql> df -hPT
Filesystem                                  Type      Size  Used Avail Use% Mounted on
tmpfs                                       tmpfs     392M  1,3M  391M   1% /run
...
/dev/mapper/VG_PGSQL_17_main-LV_PGSQL_17_main                 xfs       300G  6,0G  294G   2% /data/pgsql/17/main
/dev/mapper/VG_PGSQL_17_cluster_01-LV_PGSQL_17_cluster_01     xfs       300G  6,0G  294G   2% /data/pgsql/17/cluster_01
/dev/mapper/VG_PGSQL_17_cluster_02-LV_PGSQL_17_cluster_02     xfs       300G  6,0G  294G   2% /data/pgsql/17/cluster_02
/dev/mapper/VG_PGSQL_16_main-LV_PGSQL_16_main                 xfs       300G  6,0G  291G   2% /data/pgsql/16/main
/dev/mapper/VG_PGSQL_16_cluster_01-LV_PGSQL_16_cluster_01     xfs       300G  6,0G  291G   2% /data/pgsql/16/cluster_01
/dev/mapper/VG_PGSQL_16_cluster_02-LV_PGSQL_16_cluster_02     xfs       300G  6,0G  291G   2% /data/pgsql/16/cluster_02
...

Etat des clusters

Chaque cluster écoute sur un port TCP/IP particulier (que vous pouvez entre autre modifier dans le fichier de conf « postgresql.conf » de l’instance), les données (DATA) sont situées dans un répertoire particulier, etc. Toutes ces informations sont disponibles en utilisant la commande « pb_lsclusters » tel que :

root@postgresql> pg_lsclusters 
Ver Cluster      Port Status Owner    Data directory              Log file
16  main         5430 down   postgres /data/pgsql/16/main         /var/log/postgresql/postgresql-17-main.log
16  cluster_01   5431 down   postgres /data/pgsql/16/cluster_01   /var/log/postgresql/postgresql-16-cluster_01.log
16  cluster_02   5432 down   postgres /data/pgsql/16/cluster_02   /var/log/postgresql/postgresql-16-cluster_02.log
17  main         5433 down   postgres /data/pgsql/17/main         /var/log/postgresql/postgresql-17-main.log
17  cluster_01   5434 down   postgres /data/pgsql/17/cluster_01   /var/log/postgresql/postgresql-17-cluster_01.log
17  cluster_02   5435 down   postgres /data/pgsql/17/cluster_02   /var/log/postgresql/postgresql-17-cluster_02.log

Par exemple, nous voyons ici que aucun de nos clusters n’est démarré, que le cluster « cluster_01 » de la version 17 utilise le port TCP/IP 5434 pour que les clients se connectent, que ses données sont situées dans le dossier « /data/pgsql/17/cluster_01« , etc.

Initialisation et choix de base

Les choix de base sont assez simple, ils consistent en:

  • Démarrage automatique des Clusters Postgres
  • Sécurisation des accès distants sur les bases de données
  • Mise en place des archives des journaux de transactions (WAL / Write Ahead Logs)
  • Mise en place d’une politique de sauvegarde

Démarrage automatique des clusters

Il suffit d’utiliser comme indiqué plus haut le script SystemV pour cela tel que:

root@postresqgl> systemctl enable postgresql@16-cluster_0
root@postresqgl> systemctl enable postgresql@16-cluster_1
root@postresqgl> systemctl enable postgresql@17-cluster_0
root@postresqgl> systemctl enable postgresql@17-cluster_1

Sécurisation des accès distants

Même s’il nous faudra évidement créer des users dans les DB de nos clusters Postgres pour que les serveurs puissent se connecter ey faire des requêtes, il existe, pour chaque cluster, la possibilité de limiter les accès en fonction de divers critères tels que l’adresse IP source, le role du user, le username, etc, et éventuellement refuser un accès avant même l’accès à la base de donées qui pourrait, elle aussi, refuser cet accès. Ces définitions se font dans le fichier « /etc/postgresql/<version>/<instance>/pg_hba.conf » pour chaque cluster.

Le niveau de cryptage des mots de passe exigé doit être : scram-sha-256 (ne plus utiliser md5)

Supposons que notre serveur d’application ait pour adresse IP 192.168.1.1 et qu’il veuillez accéder à une DB DB1 avec l’utilisateur USER1, il faudra ajouter la ligne suivante à la fin de votre fichier:

host       DB1       USER1       192.168.1.1/32       scram-sha-256

La gestion du mot de passe revenant évidement au final à la DB de Postgresql au cas où cette gestion de la sécurité est passée avec succès.

Mis en place des archives

La mise en place des archives des journaux de transactions (WAL sous Postgresql) est fondamentale, elle va vous permettre en effet de récupérer vos bases de données en cas de crash ou d’erreur de manipulation.

La mise en place du système d’archivage est une opération qui peut s’avérer complexe en fonction de la politique de sauvegarde que vous allez mettre en place, mais attention, cette notion nécessite un redémarrage de votre Cluster. Pour éviter cela, nous allons mettre en place la notion d’archivage en place dans chaque cluster mais désactivant proprement dit les ordres coté OS permettant cet archivage qui est quant à lui dynamique et ne nécessite pas le redémarrage de l’instance.

Exemple pour le cluster « cluster_01 » du moteur Postgresql version 17 :

root@postgresql> systemctl start postgresql@17-cluster_01
root@postgresql> su - postgres
postgres@postgresql:~$ psql -p 5434
psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2))
Saisissez « help » pour l'aide.

postgres=# alter system set wal_level to replica;
ALTER SYSTEM
postgres=# alter system set archive_mode to on;
ALTER SYSTEM
postgres=# alter system set archive_command to '/bin/true';
ALTER SYSTEM
postgres=# exit
postgres@postgresql:~$ logout
root@postgresql> systemctl restart postgresql@17-cluster_01
root@postgresql>

Brèves explications :

  • WAL_LEVEL : Niveau de log des journaux de transaction. Les positionner à REPLICA est le minimum pour gérer les archives de WAL, ils permettent de prendre en compte d’autres fonctionnalités que nous verrons dans d’autres posts.
  • ARCHIVE_COMMANDE : Quelle commande OS doit se déclancher à chaque fois que le moteur de Postgresql va estimer qu’il faut archive un WAL. Cette méthode va retourner « true », indiquant au système que tout va bbien à chaque demande d’archivage sans rien faire pour autant. Ceci permet de mettre en place l’archivage des logs pour ne pas avoir à redémarer le cluster plus tard et sans être obligé de s’en occuper immédiatement.
  • ARCHIVE_MODE : Mets en place le mode d’archivage des WAL. Sera pris en compte après un redémarrage du cluster.

Notez que le port TCP/IP 5434 est utilisé pour se connecter à l’instance comme nous l’a indiqué la commande « pg_lsclusters » utilisée plus haut.

Mis en place d’une politique de sauvegarde

Quelques liens

Publications similaires :

  1. Cluster HA DRBD
  2. Oracle STANDBY Database
  3. TDPO : Sauvegarde Rman Oracle avec TSM (Tivoli Storage Manager)
  4. CHROOT et SFTP

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *