# Installation de PostgreSQL > Transcription française complète du chapitre « Installation de PostgreSQL » du manuel DBA1, révision 26.09, pages PDF 107 à 194. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## 3/ Installation de PostgreSQL Contenu extrait du manuel complet, pages PDF 107 à 194. ## 3.1 INTRODUCTION - Pré-requis - Installation depuis les sources - Installation depuis les binaires - à partir des paquets - installeur Windows/macOS - Installation dans les conteneurs - Premiers réglages - Mises à jours Il existe trois façons d’installer PostgreSQL : - Avec les installeurs graphiques : - uniquement Windows et macOS ; - avantages : installation facile, idéale pour les nouveaux venus; - inconvénients : mauvaise intégration à l’écosystème des outils PostgreSQL. - Avec les paquets du système (fournis par l’OS ou la communauté PostgreSQL) : - avantages : meilleure intégration avec les autres logiciels, configuration éprouvée; - inconvénients : aucun ? - Le code source : - avantages : contrôle total; configuration très fine, ajout de patchs, intéressant pour les utilisateurs expérimentés et les testeurs, ou pour embarquer PostgreSQL au sein d’un ensemble de logiciels ; - inconvénients : nécessite un environnement de compilation, de refaire une partie du travail des mainteneurs des paquets (liens avec les nombreuses librairies annexes, configuration des utilisateurs, scripts de démarrage…); mises à jour à prévoir soi-même. En production, nous conseillons d’installer PostgreSQL avec les paquets fournis par le PGDG. ## 3.2 PRÉ-REQUIS MINIMAUX POUR UNE INSTANCE POSTGRESQL ### 3.2.1 Pré-requis minimaux - À peu près n’importe quel OS actuel - Linux (conseillé) - Unix propriétaires (dont macOS), FreeBSD - Windows - N’importe quelle machine - …selon les besoins - 64 bits conseillé - Stockage fiable - Pas d’antivirus! **Configuration :** Il n’existe pas de configuration minimale pour une installation de PostgreSQL. La configuration de base est très conservatrice (128 Mo de cache). PostgreSQL peut fonctionner sur n’importe quelle machine actuelle, sur x86, PowerPC, ARM… 32 ou 64 bits. La configuration dépend plutôt des performances et volumétries attendues. **Plate-forme :** Les plate-formes officiellement supportées 1 incluent les principaux dérivés d’Unix, en premier lieu Linux, mais aussi FreeBSD, OpenBSD, macOS, Solaris, ou illumos ; ainsi que Window. PostgreSQL est écrit pour être portable, mais il faut des contributeurs pour l’adapter à un système et son environnement, des machines pour y tester les évolutions de PostgreSQL, et des utilisateurs pour justifier l’effort. Cette liste évolue donc : par exemple HP-UX n’est plus supporté depuis PostgreSQL 16 2 , et AIX depuis PostgreSQL 17 3 . Linux 64 bits est de loin la plate-forme privilégiée par les développeurs, celle disposant du plus d’outils annexes, et est donc recommandée pour faire tourner PostgreSQL. Les versions 32 bits fonctionnent mais sont de plus en plus ignorées par les développeurs d’extensions ou les mainteneurs 4 , et ne se voient plus vraiment en production. Debian et Red Hat et leurs dérivés sont les principales distributions vues en production (à côté d’Alpine pour les images docker ou des serveurs PGaaS dans le cloud). Antivirus & IDS : 1 https://www.postgresql.org/docs/current/supported-platforms.html 2 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9db300ce6e38411144f1e36dba345a5f91bbdee4 3 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0b16bb877 4 https://www.postgresql.org/message-id/Zr3_tXWr0VQol-VH%40msg.df7cb.de Les antivirus et autres systèmes de détection d’intrusion (IDS 5 ) fonctionnent dans des couches très basses du système. L’interaction avec PostgreSQL mène régulièrement à des problèmes de performance voire de corruption. Nous déconseillons fortement d’installer un antivirus ou un logiciel de détection d’intrusions sur un serveur PostgreSQL. Si vous devez absolument en installer un, il faut impérativement exclure de son analyse tous les répertoires, fichiers et processus de PostgreSQL. En cas de souci de performance sans cause évidente, pensez à faire un test avec l’antivirus désactivé. 5 https://fr.wikipedia.org/wiki/Syst%C3%A8me_de_d%C3%A9tection_d%27intrusion ## 3.3 INSTALLATION À PARTIR DES SOURCES ### 3.3.1 Étapes d’une installation à partir des sources - Téléchargement - Vérification des prérequis - Compilation - Installation Les utilisateurs compilent rarement PostgreSQL, et nous recommandons d’utiliser les paquets précompilés du PGDG. Certaines utilisations le nécessitent toutefois. C’est aussi l’occasion de voir quelques concepts techniques importants. Nous allons aborder ici les différentes étapes à réaliser pour installer PostgreSQL à partir des sources : - trouver les fichiers sources; - préparer le serveur pour accueillir PostgreSQL; - compiler le serveur; - vérifier le résultat de la compilation; - installer les fichiers compilés. ### 3.3.2 Téléchargement - Disponible depuis https://www.postgresql.org/download/ - `postgresql-.tar.bz2` - Git : https://git.postgresql.org/git/postgresql.git Les fichiers sources et les instructions de compilation sont disponibles sur le site officiel du projet 6 (ou plus directement https://www.postgresql.org/ftp/source/ ou https://ftp.postgresql.org/pub/source). Le nom du fichier à télécharger se présente toujours sous la forme `postgresql-.tar.bz2` où `` représente la version de PostgreSQL (par exemple : https://ftp.postgresql.org/pub/source/v18.0/postgresql-18.0.tar.bz2) Vous pouvez contrôler que le fichier téléchargé n’a pas été altéré en comparant son empreinte SHA256 avec celle présente dans le dépôt FTP ( postgresql-18.0.tar.bz2.sha256 ) : **Exemple 3-2.** Exemple extrait du PDF\, page 111\. ```console $ curl https://ftp.postgresql.org/pub/source/v18.0/postgresql-18.0.tar.bz2.sha256 ``` 6 https://www.postgresql.org/download/ 0d5b903b1e5fe361bca7aa9507519933773eb34266b1357c4e7780fdee6d6078 postgresql-18.0.tar.bz2 **Exemple 3-3.** Exemple extrait du PDF\, page 112\. ```console $ sha256sum postgresql-18.0.tar.bz2 ``` 0d5b903b1e5fe361bca7aa9507519933773eb34266b1357c4e7780fdee6d6078 postgresql-18.0.tar.bz2 Lorsque la future version de PostgreSQL paraît en phase de test (versions bêta), souvent à partir de mai, les sources sont accessibles à l’adresse suivante : https://www.postgresql.org/developer/beta. Il existe bien sûr un dépôt git 7 officiel, avec un miroir sur Github 8 . ### 3.3.3 Phases de compilation/installation **Exemple 3-4.** Exemple extrait du PDF\, page 112\. ```console # au choix tar xvfj postgresql-18.0.tar.bz2 git clone --branch REL_18_0 https://git.postgresql.org/git/postgresql.git cd postgresql-18.0 ./configure --prefix=/usr/local/pgsql # beaucoup d'options ! make -j8 cd contrib/ && make -j8 # ne pas oublier les contrib make check sudo make install && cd contrib/ && sudo make install pg_config --configure ``` La compilation de PostgreSQL sous Linux/Unix suit un processus classique. Une fois l’archive ou le dépôt Git récupéré, si les librairies habituelles de développement sont présentes, la compilation est extrêmement classique. Le répertoire contrib contient des modules et extensions gérés par le projet, dont l’installation est chaudement conseillée. La compilation s’effectue de la même manière. **Exemple 3-5.** Exemple extrait du PDF\, page 112\. ```text ./configure ``` Des options courantes pour une machine de production sont : - --prefix= répertoire : répertoire d’installation personnalisé (par défaut, il s’agit de **Exemple 3-6.** Exemple extrait du PDF\, page 112\. ```console /usr/local/pgsql ) ; ``` - --with-pgport= port : port par défaut (si différent de 5432) ; - --with-openssl : support d’OpenSSL pour bénéficier de connexions chiffrées; - --enable-nls : le support de la langue utilisateur pour les messages provenant du serveur et des applications; 7 https://git.postgresql.org/gitweb/?p=postgresql.git;a=summary 8 https://github.com/postgres/postgres - --with-perl , --with-python : installe les langages PL correspondants. On voudra généralement activer ces trois dernières options… et beaucoup d’autres. **Exemple 3-7.** Exemple extrait du PDF\, page 113\. ```console make --jobs=8 all (cd contrib/ && make --jobs=8 all) make --jobs=8 check sudo make install && cd contrib/ && sudo make install ``` (Le make de GNU se nomme gmake sur certaines systèmes différents de Linux). Cette phase est la plus longue, mais ne dure que quelques minutes sur du matériel récent en parallélisant. make check lance des tests de non régression pour vérifier que PostgreSQL fonctionne correctement sur la machine cible. Pour les mises à jour, il est important de connaître les options utilisées lors de la précédente compilation. L’outil pg_config le permet. Un PostgreSQL 18.0 sur Debian 12 affichera ce qui suit (les distributions ont tendance à intégrer toutes les options) : **Exemple 3-8.** Exemple extrait du PDF\, page 113\. ```console $ /usr/lib/postgresql/18/bin/pg_config --configure '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/18/man' '--docdir=/usr/share/doc/postgresql-doc-18' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/18' '--bindir=/usr/lib/postgresql/18/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian 18.0-1.pgdg120+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-16' 'CLANG=/usr/bin/clang-16' '--with-lz4' '--with-zstd' '--with-systemd' '--with-selinux' '--enable-dtrace' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' ``` Il existe aussi une vue pg_config accessible sur un serveur démarré : TABLE pg_config ; **Tableau 3-1.** Tableau ou résultat de commande extrait du PDF\, page 114\. ```text name | setting -------------------+----------------------------------------------------------- BINDIR | /usr/lib/postgresql/18/bin DOCDIR | /usr/share/doc/postgresql-doc-18 HTMLDIR | /usr/share/doc/postgresql-doc-18 INCLUDEDIR | /usr/include/postgresql PKGINCLUDEDIR | /usr/include/postgresql INCLUDEDIR-SERVER | /usr/include/postgresql/18/server LIBDIR | /usr/lib/x86_64-linux-gnu PKGLIBDIR | /usr/lib/postgresql/18/lib LOCALEDIR | /usr/share/locale MANDIR | /usr/share/postgresql/18/man SHAREDIR | /usr/share/postgresql/18 SYSCONFDIR | /etc/postgresql-common PGXS | /usr/lib/postgresql/18/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE | '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/18/man' '--docdir=/usr/share/doc/postgresql-doc-18' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/18' '--bindir=/usr/lib/postgresql/18/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian 18.0-1.pgdg120+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-16' 'CLANG=/usr/bin/clang-16' '--with-lz4' '--with-zstd' '--with-systemd' '--with-selinux' '--enable-dtrace' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' CC | gcc CPPFLAGS | -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer CFLAGS_SL | -fPIC LDFLAGS | -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-16/lib -Wl,--as-needed LDFLAGS_EX | LDFLAGS_SL | ``` **Tableau 3-2.** Tableau ou résultat de commande extrait du PDF\, page 115\. ```text LIBS | -lpgcommon -lpgport -lselinux -lzstd -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm VERSION | PostgreSQL 18.0 (Debian 18.0-1.pgdg120+1) ``` (23 lignes) ### 3.3.4 Utilisateur dédié de PostgreSQL - Jamais root - Utilisateur dédié - propriétaire des répertoires et fichiers - peut lancer PostgreSQL - traditionnellement : postgres - Variables d’environnement (défaut) : **Exemple 3-9.** Exemple extrait du PDF\, page 115\. ```console export PATH=/usr/local/pgsql/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH export MANPATH=$MANPATH:/usr/local/pgsql/share/man export PGDATA=/usr/local/pgsql/data # Données ``` Le serveur PostgreSQL ne peut pas être exécuté par l’utilisateur root, pour des raisons de sécurité. Un utilisateur système sans droits particuliers suffit. Il sera le seul propriétaire des répertoires et fichiers gérés par le serveur PostgreSQL. Il sera aussi le compte qui permettra de lancer PostgreSQL (directement ou indirectement via les outils système comme systemd). Cet utilisateur système est habituellement appelé postgres (et les paquets d’installation utilisent ce nom), mais ce n’est absolument pas une obligation. Il est nécessaire de positionner un certain nombre de variables d’environnement dans ${HOME}/.profile ou dans /etc/profile . Avec le chemin par défaut de la compilation, elles valent : **Exemple 3-10.** Exemple extrait du PDF\, page 115\. ```console export PATH=/usr/local/pgsql/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH export MANPATH=$MANPATH:/usr/local/pgsql/share/man export PGDATA=/usr/local/pgsql/data ``` Chemins des binaires : - /usr/local/pgsql/bin est le chemin par défaut (ou le chemin indiqué à l’option --prefix lors de l’étape configure ). L’ajouter à PATH permet de rendre l’exécution de PostgreSQL possible depuis n’importe quel répertoire; - LD_LIBRARY_PATH indique au système où trouver les différentes bibliothèques nécessaires à l’exécution des programmes; - MANPATH indique le chemin de recherche des pages de manuel. Emplacement des données : PDGATA est une spécificité de PostgreSQL : cette variable indique le répertoire des fichiers de données de PostgreSQL, c’est-à-dire les données de l’utilisateur. (Plus rigoureusement : elle pointe l’emplacement du fichier postgresql.conf , généralement dans ce répertoire de données. Les paquets pour Debian/Ubuntu représentent une grosse exception, avec un postgresql.conf dans /etc , qui contient le paramètre data_directory , lequel pointe vers le vrai répertoire de données.) PostgreSQL n’impose de lui-même aucune contrainte sur le chemin. ### 3.3.5 Création du répertoire de données de l’instance **Exemple 3-11.** Exemple extrait du PDF\, page 116\. ```console $ initdb -D /usr/local/pgsql/data ``` - Une seule instance sur le répertoire! - Pas à la racine d’un point de montage - Options d’emplacement : - --data pour les fichiers de données - --waldir pour les journaux de transactions - Sommes de contrôle activées par défaut (v18+) - Autres options : - --data-checksums (<18, conseillé!) - chemin des journaux, mot de passe, encodage… La commande initdb doit être exécutée sous le compte de l’utilisateur système PostgreSQL décrit dans la section précédente (généralement postgres). (Les paquets d’installation fournissent d’autres outils, mais tous utilisent initdb .) Répertoire : initdb crée les fichiers d’une nouvelle instance avec une première base de données dans le répertoire indiqué. Si le répertoire n’existe pas, initdb tentera de le créer, s’il a les droits pour le faire. S’il existe, il doit être vide. Typiquement, les chemins par défaut sont ceux-ci : **Exemple 3-12.** Exemple extrait du PDF\, page 116\. ```console # chemin par défaut d'une version compilée sans --prefix /usr/local/pgsql/data # chemin par défaut des paquets Debian/Ubuntu (première instance v18) ``` /var/lib/postgresql/18/main **Exemple 3-13.** Exemple extrait du PDF\, page 116\. ```text # chemin par défaut des paquets RPM du PGDG (première instance v18) ``` /var/lib/pgsql/18/data Vous pouvez décider de placer le PGDATA n’importe où pourvu que l’utilisateur sous lequel tourne PostgreSQL puisse y accéder; il y a cependant quelques conseils à connaître. Attention : pour des raisons de sécurité et de fiabilité, les répertoires choisis pour les » données de votre instance ne doivent pas être à la racine d’un point de montage. Que ce soit le répertoire PGDATA, le répertoire pg_wal ou les éventuels tablespaces. Si un ou plusieurs points de montage sont dédiés à l’utilisation de PostgreSQL, positionnez toujours les données dans un sous-répertoire, voire deux niveaux en-dessous du point de montage, couramment : point de montage/version majeure/nom instance. Exemples : **Exemple 3-14.** Exemple extrait du PDF\, page 117\. ```text # Si /mnt/donnees est le point de montage /mnt/donnees/18/dbprod # Si /var/lib/postgresql est une partition /var/lib/postgresql/18/main # Si /var/lib/pgsql est une partition /var/lib/pgsql/18/data ``` À ce propos, voir : - chapitre Use of Secondary File Systems 9 ; - le détail des raisons techniques 10 . Enfin : Un répertoire de données ne doit être utilisé que par une seule instance (processus) à la fois! PostgreSQL vérifie au démarrage qu’aucune autre instance du même serveur n’utilise les fichiers indiqués, mais cette protection n’est pas absolue, notamment avec des accès depuis des systèmes différents. Faites donc bien attention à ne lancer PostgreSQL qu’une seule fois sur un répertoire de données. Si plusieurs instances cohabitent sur le serveur, elles devront avoir chacune leur répertoire. Lancement de initdb : Voici ce qu’affiche cette commande : **Exemple 3-15.** Exemple extrait du PDF\, page 117\. ```console $ initdb --data /usr/local/pgsql/data ``` The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with this locale configuration: locale provider: libc LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 LC_MESSAGES: en_US.UTF-8 LC_MONETARY: en_GB.UTF-8 LC_NUMERIC: en_GB.UTF-8 LC_TIME: en_GB.UTF-8 The default database encoding has accordingly been set to "UTF8". 9 https://www.postgresql.org/docs/current/creating-cluster.html 10 https://bugzilla.redhat.com/show_bug.cgi?id=1247477#c1 The default text search configuration will be set to "english". Data page checksums are enabled. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... Europe/Paris creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok **Exemple 3-16.** Exemple extrait du PDF\, page 118\. ```text initdb: warning: enabling "trust" authentication for local connections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. ``` Success. You can now start the database server using: **Exemple 3-17.** Exemple extrait du PDF\, page 118\. ```console pg_ctl -D /usr/local/pgsql/data -l logfile start ``` Nous pouvons ainsi suivre les actions de initdb : - détection de l’utilisateur, de l’encodage et de la locale; - prise en compte des sommes de contrôle; - création ou vérification des droits du répertoire PGDATA ( /usr/local/pgsql/data ici); - création des sous-répertoires; - création et modification des fichiers de configuration; - exécution du script bootstrap; - synchronisation sur disque ; - affichage de quelques informations supplémentaires (noter les droits par défaut très laxistes d’une version compilée). Authentification par défaut : Il est possible de changer la méthode d’authentification par défaut avec les paramètres en ligne de commande --auth , --auth-host et --auth-local . Les options --pwprompt ou --pwfile permettent d’assigner un mot de passe à l’utilisateur postgres. Sommes de contrôle : Il est conseillé d’activer les sommes de contrôle des fichiers de données avec l’option --data-checksums . À partir de la version 18, les sommes de contrôle sont activées par défaut. Ces sommes de contrôle sont vérifiées à chaque lecture d’un bloc. Leur activation est chaudement recommandée pour détecter une corruption physique le plus tôt possible. Tous les processeurs Intel/AMD pas trop anciens supportent le jeu d’instruction SSE 4.2 (voir dans /proc/cpuinfo ), il ne devrait pas y avoir d’impact notable sur les performances. Les journaux générés seront cependant plus nombreux. Il est possible de vérifier, activer ou désactiver toutes les sommes de contrôle grâce à l’outil pg_checksums . Cependant, l’opération d’activation sur une instance existante impose un arrêt et une réécriture complète des fichiers. (L’opération était même impossible avant PostgreSQL 12.) Pensez-y donc dès l’installation, y compris si vous installez par les paquets. Emplacement des journaux : L’option --waldir indique l’emplacement des journaux de transaction. Par défaut, ils sont dans le sous-répertoire pg_wal/ dans le PGDATA. Si vous désignez un autre répertoire, pg_wal sera un lien symbolique vers le répertoire désigné. Taille des segments : Les fichiers des journaux de transaction ont une taille par défaut de 16 Mo. Augmenter leur taille avec --wal-segsize n’a d’intérêt que pour les très grosses installations générant énormément de journaux pour optimiser leur archivage. ### 3.3.6 Lancement et arrêt - Avec le script de l’OS (recommandé) ou pg_ctl : **Exemple 3-18.** Exemple extrait du PDF\, page 119\. ```console systemctl [action] postgresql # systemd /etc/init.d/postgresql [action] # SysV Init service postgresql [action] # idem … $ pg_ctl --pgdata /usr/local/pgsql/data --log logfile [action] ``` **Tableau 3-3.** Tableau ou résultat de commande extrait du PDF\, page 119\. ```text --mode [smart|fast|immediate] ``` - [action] dépend du besoin : - start / stop / restart - reload pour recharger la configuration - status - promote , logrotate , kill … **(Re)démarrage et arrêt :** La méthode recommandée est d’utiliser un script de démarrage adapté à l’OS, (voir plus bas les outils les commandes systemd ou celles propres à Debian) , surtout si l’on a installé PostgreSQL par les paquets. Au besoin, des scripts d’exemple existent dans le répertoire des sources ( contrib/start-scripts/ 11 ) pour Linux, FreeBSD et macOS. Ces scripts sont à exécuter en tant qu’utilisateur root. Sinon, il est possible d’exécuter pg_ctl avec l’utilisateur créé précédemment. Les deux méthodes partagent certaines des actions présentées ci-dessus : start , stop , restart (aux sens évidents), ou reload (pour recharger la configuration sans redémarrer PostgreSQL ni couper les connexions). L’option --mode permet de préciser le mode d’arrêt parmi les trois disponibles : 11 https://github.com/postgres/postgres/tree/master/contrib/start-scripts - smart : pour vider le cache de PostgreSQL sur disque, interdire de nouvelles connexions et attendre la déconnexion des clients et d’éventuelles sauvegardes; - fast (par défaut) : pour vider le cache sur disque et déconnecter les clients sans attendre (les transactions en cours sont annulées) ; - immediate : équivalent à un arrêt brutal : tous les processus serveur sont tués et donc, au redémarrage, le serveur devra rejouer ses journaux de transactions. Rechargement de la configuration : Pour recharger la configuration après changement du paramétrage, la commande : **Exemple 3-19.** Exemple extrait du PDF\, page 120\. ```console pg_ctl reload -D /repertoire_pgdata ``` est équivalente à cet ordre SQL : **Exemple 3-20.** Exemple extrait du PDF\, page 120\. ```sql SELECT pg_reload_conf() ; ``` Il faut aussi savoir que quelques paramètres nécessitent un redémarrage de PostgreSQL et non un simple rechargement, ils sont indiqués dans les commentaires de postgresql.conf . ## 3.4 INSTALLATION À PARTIR DES PAQUETS LINUX ### 3.4.1 Paquets - Packages Debian - Packages RPM Pour une utilisation en environnement de production, il est généralement préférable d’installer les paquets binaires préparés spécialement pour la distribution utilisée. Les paquets sont préparés par des personnes différentes, suivant les recommendations officielles de la distribution. Il y a donc des différences, parfois importantes, entre les paquets. ### 3.4.2 Paquets Debian officiels - Nombreux paquets disponibles : - serveur, client, contrib, docs - extensions, outils - `apt install postgresql-` - installe les binaires - crée l’utilisateur postgres - exécute initdb - démarre le serveur Sur Debian et les versions dérivées (Ubuntu notamment), l’installation de PostgreSQL a été découpée en plusieurs paquets (ici pour la version majeure 18) : - le serveur : postgresql-18 ; - les clients : postgresql-client-18 ; - la documentation : postgresql-doc-18 . Ce paquet correspond toujours à la dernière version mineure trimestrielle (par exemple 18.1). La version majeure dans le nom des paquets ( 9.6 , 10 , 14 , 18 …) permet d’installer plusieurs versions majeures sur le même serveur physique ou virtuel. Par défaut, sans autre dépôt, une seule version majeure est disponible dans une version de distribution. Par exemple, apt install postgresql sur Debian 12 installe en fait postgresql-15 (il est en dépendance du paquet postgresql ), et sur Debian 13 c’est postgresql-17 . N’installez pas le paquet postgresql si vous utilisez les dépôts du PGDG ! Il pointe vers une version majeure différente chaque année, forçant ainsi une mise à jour majeure automatique! Précisez plutôt le paquet de la version majeure qui vous intéresse. Il existe aussi des paquets pour les outils, les extensions, etc. Certains langages de procédures stockées sont disponibles dans des paquets séparés : - PL/python dans postgresql-plpython3-18 - PL/perl dans postgresql-plperl-18 - PL/Tcl dans postgresql-pltcl-18 - etc. Pour compiler des outils liés à PostgreSQL, il est recommandé d’installer également les bibliothèques de développement qui font partie du paquet postgresql-server-dev-18 . Quand le paquet postgresql-18 est installé, plusieurs opérations sont immédiatement exécutées : - téléchargement du paquet (dans la dernière version mineure); - installation des binaires contenus dans le paquet; - création de l’utilisateur postgres (s’il n’existe pas déjà) ; - paramétrage d’une première instance nommée main ; - création du répertoire des données, lancement de l’instance. Les exécutables sont installés dans : **Exemple 3-21.** Exemple extrait du PDF\, page 122\. ```console /usr/lib/postgresql/18/bin ``` Chaque instance porte un nom, qui se retrouve dans le paramètre cluster_name , et permet d’identifier les processus dans un ps ou un top . Le nom de la première instance de chaque version majeure est par défaut main . Pour cette instance : - les données sont dans : /var/lib/postgresql/18/main - les fichiers de configuration (pas tous! certains restent dans le répertoire des données) sont dans : /etc/postgresql/18/main - les traces sont gérées par l’OS sous ce nom : /var/log/postgresql/postgresql-18-main.log - un fichier PID, la socket d’accès local, et l’espace de travail temporaire des statistiques d’activité figurent dans /var/run/postgresql . Tout ceci vise à respecter le plus possible la norme FHS 12 (Filesystem Hierarchy Standard). En cas de mise à jour d’un paquet, le serveur PostgreSQL est redémarré après mise à jour des binaires. 12 https://fr.wikipedia.org/wiki/Filesystem_Hierarchy_Standard ### 3.4.3 Paquets Debian : spécificités - Plusieurs versions majeures installables - Wrappers/scripts pour la gestion des différentes instances : - pg_lsclusters - pg_ctlcluster **Tableau 3-4.** Tableau ou résultat de commande extrait du PDF\, page 123\. ```text - ou : systemctl stop|start postgresql-15@main ``` - pg_createcluster - etc. - Respect de la FHS - Configuration dans /etc/postgresql/ Numéroter les paquets permet d’installer plusieurs versions majeures de PostgreSQL (mais chacune seulement dans sa dernière version mineure) au besoin sur le même système, si les dépôts les contiennent. Les mainteneurs des paquets Debian ont écrit des scripts pour faciliter la création, la suppression et la gestion de différentes instances sur le même serveur. Les principaux sont : - pg_lsclusters liste les instances; - `pg_createcluster ` crée une instance de la version majeure et du nom voulu; - `pg_dropcluster ` détruit l’instance désignée ; - /etc/postgresql-common/createcluster.conf permet de centraliser les paramètres par défaut des instances; - la gestion d’une instance est réalisée avec la commande pg_ctlcluster : **Tableau 3-5.** Tableau ou résultat de commande extrait du PDF\, page 123\. ```text pg_ctlcluster start|stop|reload|status|promote ``` Ce dernier script interagit avec systemd, qui peut être utilisé pour arrêter ou démarrer séparément chaque instance. Ces deux commandes sont équivalentes : sudo pg_ctlcluster 18 main start **Exemple 3-22.** Exemple extrait du PDF\, page 123\. ```console sudo systemctl start postgresql@18-main ``` ### 3.4.4 Paquets Debian communautaires : apt.postgresql.org - La communauté met des paquets Debian à disposition : - https://apt.postgresql.org - Synchrone avec le projet PostgreSQL - Ajout du dépôt dans /etc/apt/sources.list.d/pgdg.list - Utilisation chaudement conseillée La distribution Debian préfère des paquets testés et validés, y compris sur des versions assez anciennes, plutôt que d’adopter la dernière version dès qu’elle est disponible. Par exemple, Debian 12 ne contiendra jamais que PostgreSQL 15 et ses versions mineures, et Debian 13 ne contiendra que PostgreSQL 17. Pour faciliter les mises à jour, la communauté PostgreSQL met à disposition son propre dépôt de paquets Debian. Elle en assure le maintien et le support. Les paquets de la communauté ont la même provenance et le même contenu que les paquets officiels Debian, avec d’ailleurs les mêmes mainteneurs. La seule différence est que apt.postgresql.org est mis à jour plus fréquemment, en liaison directe avec la communauté PostgreSQL, et contient beaucoup plus d’outils et extensions. Le wiki 13 indique quelle est la procédure, qui peut se résumer à : sudo apt update sudo apt install -y postgresql-common **Exemple 3-23.** Exemple extrait du PDF\, page 124\. ```console sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh ``` … Setting up postgresql-common (278) ... Creating config file /etc/postgresql-common/createcluster.conf with new version Building PostgreSQL dictionaries from installed myspell/hunspell packages... Removing obsolete dictionary files: Created symlink '/etc/systemd/system/multi-user.target.wants/postgresql.service' → '/usr/lib/systemd/system/postgresql.service'. Processing triggers for man-db (2.13.0-1) ... This script will enable the PostgreSQL APT repository on apt.postgresql.org on your system. The distribution codename used will be trixie-pgdg. Press Enter to continue, or Ctrl-C to abort. Si l’on continue, ce dépôt sera ajouté au système (ici sous Debian 13) : **Exemple 3-24.** Exemple extrait du PDF\, page 124\. ```console $ cat /etc/apt/sources.list.d/pgdg.sources ``` Types: deb URIs: https://apt.postgresql.org/pub/repos/apt Suites: trixie-pgdg Components: main Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg et n’importe quelle version majeure de PostgreSQL sera installable en plus de celle disponible dans Debian, même la plupart des versions non supportées, par exemple ainsi : sudo apt install postgresql-17 sudo apt install postgresql-15 sudo apt install postgresql-10 13 https://wiki.postgresql.org/wiki/Apt ### 3.4.5 Paquets Red Hat communautaires : yum.postgresql.org - Préférer les paquets distribués par la communauté : - https://yum.postgresql.org/ - https://yum.postgresql.org/howto/ - plus complets que les Appstream - Ajout du dépôt comme paquet RPM Les versions majeures de Red Hat et de ses dérivés (Rocky Linux, AlmaLinux, Fedora, CentOS, Scientific Linux…) sont très espacées, et la version par défaut de PostgreSQL n’est parfois plus supportée. Même les versions disponibles en AppStream (avec dnf module ) sont parfois en retard ou ne contiennent que certaines versions majeures. Les dépôts de la communauté sont donc fortement conseillés. Ils contiennent aussi beaucoup plus d’utilitaires, toutes les versions majeures supportées de PostgreSQL simultanément, et collent au plus près des versions publiées par la communauté. Ce dépôt convient pour les dérivés de Red Hat comme Fedora, CentOS, Rocky Linux… ### 3.4.6 Paquets Red Hat communautaires : installation sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf -qy module disable postgresql sudo dnf install -y postgresql18-server **Exemple 3-25.** Exemple extrait du PDF\, page 125\. ```console # dont : utilisateur postgres sudo /usr/pgsql-18/bin/postgresql-18-setup initdb sudo systemctl enable postgresql-18 sudo systemctl start postgresql-18 ``` L’installation de la configuration du dépôt de la communauté est très simple. Les commandes peuvent même être générées en fonction des versions sur https://www.postgresql.org/download/linux/redhat/. L’exemple ci-dessus installe PostgreSQL 18 sur Rocky 9. ### 3.4.7 Paquets Red Hat communautaires : spécificités - Paquets séparés serveur, client, contrib - /usr/pgsql-XX/bin : binaires - Initialisation manuelle ( postgresql-18-setup initdb ) - vers : /var/lib/pgsql/XX/data - Gestion par systemd - Particularités : - plusieurs versions majeures installables - configuration : dans le répertoire de données Sous Red Hat et les versions dérivées, les dépôts communautaires ont été découpés en plusieurs paquets, disponibles pour chacune des versions majeures supportées : - le serveur : postgresqlXX-server ; - les clients : postgresqlXX ; - les modules contrib : postgresqlXX-contrib ; - la documentation : postgresqlXX-docs . où XX est la version majeure (par exemple 14 ou 18 ). Il existe aussi des paquets pour les outils, les extensions, etc. Certains langages de procédures stockées sont disponibles dans des paquets séparés : - PL/python dans postgresqlXX-plpython3 ; - PL/perl dans postgresqlXX-plperl ; - PL/Tcl dans postgresqlXX-pltcl ; - etc. Pour compiler des outils liés à PostgreSQL, il est recommandé d’installer également les bibliothèques de développement qui font partie du paquet postgresqlXX-devel . Ce nommage sous-entend qu’il est possible d’installer plusieurs versions majeures sur le même serveur physique ou virtuel. Les exécutables sont installés dans le répertoire /usr/pgsql-XX/bin , les traces dans /var/lib/pgsql/XX/data/log (utilisation du logger process de PostgreSQL), les données dans /var/lib/pgsql/XX/data . Ce dernier est le répertoire par défaut des données, mais il est possible de le surcharger. Sur un système type Red Hat sans dépôt communautaire, les noms des paquets ne comb portent pas le numéro de version et installent tous les binaires cités ici dans /usr/bin . Quand le paquet serveur est installé, plusieurs opérations sont exécutées : téléchargement du paquet, installation des binaires contenus dans le paquet, et création de l’utilisateur postgres (s’il n’existe pas déjà). Le répertoire des données n’est pas créé. Cela reste une opération à réaliser par la personne qui a installé PostgreSQL sur le serveur. Lancer le script /usr/psql-XX/bin/postgresqlXX-setup en tant que root : **Exemple 3-26.** Exemple extrait du PDF\, page 127\. ```console # Jusque version 17 incluse PGSETUP_INITDB_OPTIONS="--data-checksums --lc_messages=C" \ /usr/pgsql-17/bin/postgresql-17-setup initdb # À partir de la v18, les sommes de contrôles sont par défaut PGSETUP_INITDB_OPTIONS="--lc_messages=C" \ /usr/pgsql-17/bin/postgresql-18-setup initdb ``` Plutôt que de respecter la norme FHS (Filesystem Hierarchy Standard), les mainteneurs ont fait le choix de respecter l’emplacement des fichiers utilisé par défaut par les développeurs PostgreSQL. La configuration de l’instance ( postgresql.conf entre autres) est donc directement dans le PGDATA. Pour installer plusieurs instances, il faudra créer manuellement des services systemd différents. En cas de mise à jour d’un paquet, le serveur PostgreSQL n’est pas redémarré après mise à jour des binaires. ### 3.4.8 Paquets Red Hat communautaires : zypp.postgresql.org - https://zypp.postgresql.org - Paquets RPM adaptés à Suse Les paquets RPM ont été adaptés à la distribution Suse. Une fois les dépôts du PGDG installés, le maniement est identique (chemins, scripts, systemd…) ## 3.5 INDUSTRIALISATION AVEC PGLIFT ### 3.5.1 pglift : présentation rapide - Déploiement et infrastructure as code : - ligne de commande - collections Ansible - Couverture fonctionnelle : - Sauvegardes avec pgBackRest - Supervision avec Prometheus - Administration avec temBoard - Analyse avec PoWA - Haute disponibilité avec Patroni - Intégration système avec systemd ou rsyslog pglift 14 est un outil permettant de déployer et d’exploiter PostgreSQL à grande échelle. Le projet fournit à la fois une interface en ligne de commande pour gérer le cycle de vie des instances et une collection de modules Ansible pour piloter une infrastructure-as-code dans un contexte de production. L’élément fondamental de pglift est l’instance. Celle-ci est constituée d’une instance PostgreSQL et inclut des composants satellites, facultatifs, permettant d’exploiter PostgreSQL à grande échelle. Dans sa version 1.0, pglift supporte les composants suivants : pgBackRest 15 permet de prendre en charge les sauvegardes physiques PITR (Point In Time Recovery) de PostgreSQL. postgres_exporter 16 est un service de supervision permettant de remonter des informations à l’outil de surveillance Prometheus 17 . temBoard 18 est une console web de supervision et d’administration dédiée aux instances PostgreSQL. PoWA 19 est une console web permettant d’analyser l’activité des instances PostgreSQL en direct. Patroni 20 est un outil permettant de construire un agrégat d’instances PostgreSQL résilient offrant un service de haute disponibilité. Tous les composants satellites supportés par pglift sont des logiciels libres. Le projet pglift est lui aussi nativement open source, sous licence GPLv3. Son développement se passe en public sur https://gitlab.com/dalibo/pglift/ 14 https://pglift.readthedocs.io/ 15 https://pgbackrest.org/ 16 https://github.com/prometheus-community/postgres_exporter 17 https://prometheus.io/ 18 https://temboard.readthedocs.io/ 19 https://powa.readthedocs.io/en/latest/ 20 https://patroni.readthedocs.io/ pour l’API Python et l’interface en ligne de commande et sur https://gitlab.com/dalibo/pglift-ansible/ pour la collection Ansible dalibo.pglift . Références : - Présentation sur le blog Dalibo 21 (Denis Laxalde) - Série « Industrialisation de PostgreSQL » 22 sur le blog Dalibo : installation, sauvegardes, mode user de systemd, supervision - Documentation 23 ### 3.5.2 pglift : fichier de configuration prefix: /srv # fichier /etc/pglift/settings.yaml postgresql: auth: host: scram-sha-256 prometheus: execpath: /usr/bin/prometheus-postgres-exporter pgbackrest: repository: mode: path path: /srv/pgsql-backups powa: {} systemd: {} rsyslog: {} À côté de PostgreSQL, l’instance inclut un ensemble d’outils nécessaires à son utilisation. L’intégration de ces outils satellites est configurée localement via un fichier YAML settings.yaml . Ce fichier définit comment les différents composants de l’instance sont configurés, installés et exécutés. Il permet aussi de définir quels composants satellites facultatifs supportés par pgLift sont inclus dans l’instance. Si un élément est listé dans ce fichier sans paramètre associé, il sera exploité dans sa configuration par défaut. Dans l’exemple ci-dessus, temBoard et Patroni ne sont pas installés, et PoWA est laissé à sa configuration par défaut. 21 https://blog.dalibo.com/2023/10/17/pglift-intro.html 22 https://blog.dalibo.com/2025/06/30/industrialisation_postgresql.html 23 https://pglift.readthedocs.io/ ### 3.5.3 pglift : exemples de commandes - Initialisation pglift instance create main --pgbackrest-stanza=main - Modification de configuration pglift pgconf -i main set log_connections=on - Sauvegarde physique pglift instance backup main - Utilisation des outils de l’instance pglift instance exec main -- psql pglift instance exec main -- pgbackrest info Interface impérative en ligne de commande : La commande suivante permet la création d’une instance pglift : **Exemple 3-27.** Exemple extrait du PDF\, page 130\. ```console $ pglift instance create main --pgbackrest-stanza=main ``` INFO initializing PostgreSQL INFO configuring PostgreSQL authentication INFO configuring PostgreSQL INFO starting PostgreSQL 16-main INFO creating role 'powa' INFO creating role 'prometheus' INFO creating role 'backup' INFO altering role 'backup' INFO creating 'powa' database in 16/main INFO creating extension 'btree_gist' in database powa INFO creating extension 'pg_qualstats' in database powa INFO creating extension 'pg_stat_statements' in database powa INFO creating extension 'pg_stat_kcache' in database powa INFO creating extension 'powa' in database powa INFO configuring Prometheus postgres_exporter 16-main INFO configuring pgBackRest stanza 'main' for pg1-path=/srv/pgsql/16/main/data INFO creating pgBackRest stanza main INFO starting Prometheus postgres_exporter 16-main L’instance pglift inclut l’instance PostgreSQL ainsi que l’ensemble des modules définis dans le fichier de configuration settings.yaml . pglift gère aussi l’intégration au système avec systemd ou rsyslog comme dans notre exemple. Tout ceci fonctionne sans privilège root pour une meilleure séparation des responsabilités et une meilleure sécurité. pglift permet de récupérer l’état d’une instance à un moment donné : **Exemple 3-28.** Exemple extrait du PDF\, page 131\. ```console $ pglift instance get main -o json ``` { "name": "main", "version": "16", "port": 5432, "settings": { "unix_socket_directories": "/run/user/1000/pglift/postgresql", "shared_buffers": "1 GB", "wal_level": "replica", "archive_mode": true, "archive_command": "/usr/bin/pgbackrest --config-path=/etc/pgbackrest \ --stanza=main --pg1-path=/srv/pgsql/16/main/data archive-push %p", "effective_cache_size": "4 GB", "log_destination": "syslog", "logging_collector": true, "log_directory": "/var/log/postgresql", "log_filename": "16-main-%Y-%m-%d_%H%M%S.log", "syslog_ident": "postgresql-16-main", "cluster_name": "main", "lc_messages": "C", "lc_monetary": "C", "lc_numeric": "C", "lc_time": "C", "shared_preload_libraries": "pg_qualstats, pg_stat_statements, pg_stat_kcache" }, "data_checksums": false, "locale": "C", "encoding": "UTF8", "standby": null, "state": "started", "pending_restart": false, "wal_directory": "/srv/pgsql/16/main/wal", "prometheus": { "port": 9187 }, "data_directory": "/srv/pgsql/16/main/data", "powa": {}, "pgbackrest": { "stanza": "main" } } ou de modifier l’instance : **Exemple 3-29.** Exemple extrait du PDF\, page 131\. ```console # activation du paramètre log_connections $ pglift pgconf -i main set log_connections=on ``` INFO configuring PostgreSQL INFO instance 16/main needs reload due to parameter changes: log_connections INFO reloading PostgreSQL configuration for 16-main log_connections: None -> True **Exemple 3-30.** Exemple extrait du PDF\, page 131\. ```console # changement du port prometheus $ pglift instance alter main --prometheus-port 8188 ``` INFO configuring PostgreSQL INFO reconfiguring Prometheus postgres_exporter 16-main INFO instance 16/main needs reload due to parameter changes: log_connections INFO reloading PostgreSQL configuration for 16-main INFO starting Prometheus postgres_exporter 16-main **Exemple 3-31.** Exemple extrait du PDF\, page 132\. ```console $ pglift instance get main # vérification name version port data_checksums locale encoding pending_restart prometheus pgbackrest main 16 5432 False C UTF8 False port: 8188 stanza: main ``` Les instances et objets PostgreSQL peuvent être manipulés à l’aide des outils natifs de PostgreSQL depuis la ligne de commande : **Exemple 3-32.** Exemple extrait du PDF\, page 132\. ```console $ pglift instance exec main -- pgbench -i bench ``` creating tables... generating data (client-side)... **Exemple 3-33.** Exemple extrait du PDF\, page 132\. ```text 100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s) ``` vacuuming... creating primary keys... **Exemple 3-34.** Exemple extrait du PDF\, page 132\. ```console done in 0.18 s (drop tables 0.00 s, create tables 0.01 s, ... vacuum 0.04 s, primary keys 0.05 s). $ pglift instance exec main -- pgbench bench pgbench (16.0 (Debian 16.0-1.pgdg120+1)) ``` starting vacuum...end. transaction type: `` scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 number of failed transactions: 0 (0.000%) latency average = 1.669 ms initial connection time = 4.544 ms **Exemple 3-35.** Exemple extrait du PDF\, page 132\. ```text tps = 599.125277 (without initial connection time) ``` Ceci s’applique aussi à des outils tiers, par exemple avec pgBackRest : **Exemple 3-36.** Exemple extrait du PDF\, page 132\. ```console $ pglift instance exec main -- pgbackrest info ``` stanza: main status: ok cipher: none db (current) wal archive min/max (16): 000000010000000000000001/000000010000000000000007 full backup: 20231016-092726F timestamp start/stop: 2023-10-16 09:27:26+02 / 2023-10-16 09:27:31+02 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 32.0MB, database backup size: 32.0MB repo1: backup set size: 4.2MB, backup size: 4.2MB diff backup: 20231016-092726F_20231016-092821D timestamp start/stop: 2023-10-16 09:28:21+02 / 2023-10-16 09:28:24+02 wal start/stop: 000000010000000000000007 / 000000010000000000000007 database size: 54.5MB, database backup size: 22.6MB repo1: backup set size: 6MB, backup size: 1.8MB backup reference list: 20231016-092726F Le tutoriel de la ligne de commande 24 de la documentation recense tous les exemples d’utilisation des commandes usuelles de pglift. Interface déclarative avec Ansible : pglift comporte une collection de modules Ansible, sous l’espace de noms dalibo.pglift .Voici un exemple de playbook illustrant ses capacités : - name: Set up database instances hosts: dbserver tasks: - name: main instance dalibo.pglift.instance: name: main state: started port: 5444 settings: max_connections: 100 shared_buffers: 1GB shared_preload_libraries: 'pg_stat_statements, passwordcheck' surole_password: '' pgbackrest: stanza: main password: '' prometheus: password: '' port: 9186 roles: - name: admin login: true password: '' connection_limit: 10 validity: '2025-01-01T00:00' in_roles: - pg_read_all_stats - pg_signal_backend databases: - name: main owner: admin settings: work_mem: 3MB extensions: - name: unaccent schema: public Le module dalibo.pglift.instance permet de gérer l’instance, et les objets reliés comme des rôles ou des bases de données. Les données sensibles peuvent être prises en charge par Ansible vault 25 . Les modules Ansible permettent un contrôle plus important que la ligne de commandes, grâce aux champs imbriqués, pouvant inclure la configuration de l’instance, des bases de données, des extensions, etc. Comme tout module Ansible en général, cette interface est complètement déclarative, idempotente et sans état. Ces modules fonctionnent avec d’autres modules Ansible, tels que community.postgresql . 24 https://pglift.readthedocs.io/en/latest/tutorials/cli.html 25 https://docs.ansible.com/ansible/latest/vault_guide/index.html Le tutoriel Ansible 26 de la documentation recense davantage d’exemples d’utilisation. 26 https://pglift.readthedocs.io/en/latest/tutorials/ansible.html ## 3.6 UTILISER POSTGRESQL DANS UN CONTENEUR ### 3.6.1 Docker ```shell docker run --name pg18 -e POSTGRES_PASSWORD=mdpsuperfort -d postgres docker exec -it pg18 psql -U postgres ``` - Image officielle Docker Inc (pas PGDG!) : https://hub.docker.com/_/postgres - Très pratique pour le développement - Beaucoup de possibilités avancées avec docker-compose - Ne jamais lancer 2 conteneurs Docker sur un même PGDATA - Une base de données est-elle faite pour du docker? - supervision système délicate Les conteneurs comme docker et assimilés (podman, containerd, etc.) permettent d’isoler l’installation de PostgreSQL sur un poste sans avoir à gérer de machine virtuelle. Une fois configuré, le conteneur permet d’avoir un contexte d’exécution de PostgreSQL identique peu importe son support. C’est idéal dans le cas de machines « jetables », par exemple les chaînes de CI/CD. **Exemple :** À titre d’exemple, voici les étapes nécessaires à l’installation d’une instance PostgreSQL sous docker. D’abord, récupérer l’image 27 de la dernière version de PostgreSQL maintenue par la communauté PostgreSQL docker : ```shell docker pull postgres ``` La commande permet de créer et de lancer un nouveau conteneur à partir d’une image donnée, ici postgres:18.0 . Certaines options sont passées en paramètres à initdb : ```shell docker run \ --network net18 \ --name pg18 \ -p 127.0.0.1:16501:5432 \ --env-file password.env \ -e POSTGRES_INITDB_ARGS='--data-checksums --wal-segsize=1' \ -v '/var/lib/postgresql/docker/18/pg18':'/var/lib/postgresql/data' \ -d postgres:18.0 \ -c 'work_mem=10MB' \ -c 'shared_buffers=256MB' \ -c 'cluster_name=pg18' ``` Cette commande permet au conteneur d’être attaché à un réseau dédié. Celui-ci doit avoir été créé au préalable avec la commande : 27 https://hub.docker.com/_/postgres docker network create --subnet=192.168.122.0/24 net18 L’option --name permet de nommer le conteneur pour le rendre plus facilement accessible. L’option -p permet de faire suivre le port 5432 ouvert par le container sur le port 16501 du serveur. L’option -d permet de faire de l’image un démon en arrière-plan. Les options -e définissent des variables d’environnement, moyen systématique de passer des informations au conteneur. Ici l’option est utilisée pour le mot de passe et certaines des options d’ initdb . On préférera utiliser un fichier .env pour docker compose ou docker run --env-file pour éviter de définir un secret dans la ligne de commande. L’option --env-file permet de passer en paramètre un fichier contenant des variables d’environnement. Ici nous passons un fichier contenant le mot de passe dont le contenu est le suivant : **Exemple 3-37.** Exemple extrait du PDF\, page 136\. ```text # fichier password.env POSTGRES_PASSWORD=mdpsuperfort ``` L’option -v '/var/lib/postgresql/docker/18/pg18':'/var/lib/postgresql/data' lie un répertoire du disque sur le PGDATA du container : ainsi les fichiers de la base survivront à la disparition du conteneur. Les paramètres de PostgreSQL dans les -c sont transmis directement à la ligne de commande du postmaster. Une fois l’image téléchargée et le conteneur instancié, il est possible d’accéder directement à psql via la commande suivante : docker exec -it pg18 psql -U postgres Ou directement via le serveur hôte s’il dispose de psql : **Exemple 3-38.** Exemple extrait du PDF\, page 136\. ```text psql -h 127.0.0.1 -p 16501 -U postgres ``` En production, il est recommandé de dédier un serveur à chaque instance PostgreSQL. De ce fait, docker permet de reproduire cette isolation pour des cas d’usage de développement et prototypage où dédier une machine à PostgreSQL est soit trop coûteux, soit trop complexe. Exemple avec docker compose : Il est évidement possible de passer par l’outil docker compose pour déployer une instance PostgreSQL conteneurisée. Voici la commande docker run précédente portée sous docker compose dans un fichier YAML. Fichier docker-compose.yml version: '3.3' networks: net16: ipam: driver: default config: - subnet: 192.168.122.0/24 services: pg16: networks: - net18 container_name: pg18 ports: - '127.0.0.1:16501:5432' env_file: - password.env environment: - POSTGRES_INITDB_ARGS=--data-checksums --wal-segsize=1 volumes: - /var/lib/postgresql/docker/18/pg18:/var/lib/postgresql/data' image: postgres:18.0 command: [ -c, work_mem=10MB, -c, shared_buffers=256MB, ] La commande docker compose --file docker-compose.yml up -d permet la création, ou le lancement, des objets définis dans le fichier docker-compose.yml . L’option -d permet de lancer les conteneurs en arrière-plan. **Exemple 3-39.** Exemple extrait du PDF\, page 137\. ```text # Au premier lancement ``` docker compose --file docker-compose.yml up -d Creating network "postgresql_net18" with the default driver Creating pg18 ... done **Exemple 3-40.** Exemple extrait du PDF\, page 137\. ```text # Aux lancements suivants ``` docker compose --file docker-compose.yml up -d Starting pg18 ... done Pour arrêter, il faut utiliser l’option stop : docker compose --file docker-compose.yml stop Stopping pg18 ... done Limites de l’utilisation de PostgreSQL sous docker : Ne lancez jamais 2 instances de PostgreSQL sous docker sur le même PGDATA! Les sécurités habituelles de PostgreSQL ne fonctionnent pas et les deux instances écriront toutes les deux dans les fichiers. La corruption est garantie. En production, si l’utilisation de PostgreSQL sous docker est de nos jours très fréquente, ce n’est pas toujours une bonne idée. Une base de données est l’inverse total d’une machine sans état et jetable, et pour les performances, il vaut mieux en première intention gonfler la base (scale up) que multiplier les instances (scale out), qui sont de toute façon toutes dépendantes de l’instance primaire. Si le choix est fait de fonctionner sous docker, voire Kubernetes, pour des raisons architecturales, la base de données est sans doute le dernier composant à migrer. De plus, pour les performances, la supervision au niveau système devient très compliquée, et le DBA est encore plus aveugle qu’avec une virtualisation classique. L’ajout d’outillage ou d’extensions non fournies avec PostgreSQL devient un peu plus compliquée ( docker stats n’est qu’un bon début). ### 3.6.2 Kubernetes - Dans les très grands environnements - mise à l’échelle automatique - complexité à assumer - mises à jour probablement plus fréquentes - Réclame de l’expérience sur PostgreSQL et Kubernetes - Réservez un worker à PostgreSQL - Quel opérateur? - CloudNativePG (recommandé), CrunchyData, Percona… - nombreux outils, HA… La taille de certains SI, ou le besoin très important d’automatisation, peuvent justifier le déploiement d’un outil comme Kubernetes. Il permet entre autres l’autoscaling lors de variations de charges importantes. Comme déjà dit ci-dessus : les bases de données sont tout l’inverse de machines « jetables ». Il est toutefois possible de les déployer sur Kubernetes, mais vos bases PostgreSQL sont sans doute les derniers éléments de votre infrastructure à migrer vers Kubernetes, si même leur criticité le permet. En effet, PostgreSQL est plutôt adapté à une scalabilité verticale (ajout de mémoire ou processeurs) qu’horizontale, même si la multiplication de secondaires en lecture seule ou du sharding peut être envisageable. Par contre, des bases de données non critiques et/ou à courte durée de vie (par exemple pour du développement), et surtout standardisées, ont intérêt à être gérées par Kubernetes. L’utilisation de Kubernetes apporte une couche d’abstraction supplémentaire et demande nécessairement des connaissances à avoir sur son fonctionnement. La manière de travailler avec PostgreSQL changera aussi. On peut par exemple citer la modification des paramètres de configuration qui pourra, selon la façon dont est déployée l’instance dans Kubernetes, être très différente de la manière « traditionnelle ». Les mises à jour de Kubernetes sont beaucoup plus fréquentes que PostgreSQL, et les versions sont supportées beaucoup moins longtemps. Il faut être prêt à procéder souvent à des migrations techniques, et elles impliquent souvent un redémarrage de PostgreSQL. Une application en 24×7 doit en tenir compte. Il en va de même si vous utilisez un opérateur pour déployer PostgreSQL. Ces outils ont leur propre cycle de vie qui imposent certaines contraintes sur les instances. Kubernetes est une plateforme de déploiement d’applications et ne répondra pas à votre place aux considérations liées à PostgreSQL : - les problèmes de performance dus au SQL ou aux disques trop lents, existeront toujours; - les montées de versions seront toujours à faire régulièrement; - comprendre les concepts essentiels de PostgreSQL (instances, bases, schéma, etc) reste obligatoire; - le besoin d’arbitrage entre peu de grosses instances multibases, et de nombreuses petites instances séparées, reste présent, bien que la facilité de déploiement et la légèreté de PostgreSQL favorise les instances monobases. Même si en théorie on peut s’en passer, il est fortement conseillé de passer par un opérateur Kubernetes dédié à PostgreSQL, un composant qui facilite énormément l’administration de PostgreSQL dans Kubernetes. Plusieurs fournisseurs en proposent un, chacun avec ses avantages, inconvénients et limites. Ils peuvent imposer leur choix d’outil. Ils proposent notamment : - des images de conteneur toutes prêtes, basées sur les paquets du PGDG, ou des images maison, avec ou sans outils annexes (comme le pooler pgBouncer), et plus ou moins d’extensions; - la mise en place de sauvegarde PITR avec des outils spécifiques ; - une mise en place automatique de haute disponibilité, avec la construction automatique d’instances en réplication (lecture seule) et des bascules automatiques, le tout étant - des mécanismes de surveillance à destination de Kubernetes pour la connaissance de la santé des instances; - des outils de supervision ; - des mécanismes de mises à jour mineure et majeure; - et bien d’autres choses encore. Citons notamment les opérateurs suivants : - postgres-operator 28 , de Zalando (avec WAL-E et Patroni, image docker propre); - PGO, the Postgres Operator 29 , de Crunchy Data (avec pgBackRest et Patroni, dépôts propres); - CloudNativePG (CNPG) 30 , initié à l’origine par EDB, incubé dans le projet CNCF 31 , opérateur le plus récent et peut-être le plus « à la mode » et « cloud native » (avec Barman) ; - Percona Operator for PostgreSQL 32 (avec pgBackRest, dépôts propres); - StackGres 33 (avec Patroni) Pour choisir un opérateur, les points d’attention sont : - la licence (opérateur et images docker), car certains fournisseurs poussent bien sûr à la version payante; - la disponibilité et la transparence du code source; - la pérennité de l’opérateur; - la documentation; 28 https://opensource.zalando.com/postgres-operator/ 29 https://github.com/CrunchyData/postgres-operator 30 https://cloudnative-pg.io/ 31 https://www.cncf.io/projects/cloudnativepg/ 32 https://www.percona.com/postgresql/software/percona-operator-for-postgresql 33 https://stackgres.io/ - la rapidité de mise à jour des versions de PostgreSQL et, à l’inverse, le support des vieilles versions de PostgreSQL que vous seriez obligés de conserver; - le niveau de capacité 34 de l’opérateur (Level III, IV, V) ; - le comportement lors des mises à jour pour limiter les interruptions de service; - la facilité d’adaptation de l’image docker sous-jacente (par exemple pour ajouter une extension; - les outils intégrés (sauvegarde, supervision…), leur facilité d’emploi, leur intégration avec l’existant (chaque éditeur a tendance à pousser ses outils propres), votre connaissance préalable de ces outils ; - la présence de télémétrie, rarement bienvenue. Le sujet Kubernetes est trop vaste et évolue trop vite pour être développé ici. Ajoutons juste qu’un conseil fréquent est de réserver un nœud (une machine physique ou virtuelle) à chaque instance PostgreSQL, notamment pour s’assurer que l’instance aura toujours à disposition des capacités RAM et CPU. Dédier un espace de stockage aux bases est également important, car une base de données est très sensible aux performances du stockage. 34 https://operatorframework.io/operator-capabilities/ ## 3.7 INSTALLATION SOUS WINDOWS - Installeur graphique - Binaires - via Windows Subsystem for Linux (WSL) Si Linux est le système privilégié, Windows est supporté par le projet PostgreSQL. Le portage sous Windows a d’ailleurs justifié le passage de la branche 7 à la branche 8 du projet. ### 3.7.1 Installeur graphique d’EDB **Figure 3-1.** Installeur graphique \- bienvenue ![Installeur graphique \- bienvenue](/images/dba1/manual/p141-image-017.png) Depuis : https://www.enterprisedb.com/downloads/postgres-postgresql-downloads La communauté a abandonné l’installeur graphique qu’elle a proposé un temps en raison de la lourdeur de maintenance. Mais EntrepriseDB a continué de proposer gratuitement le sien, pour la version officielle comme pour leur dérivé propriétaire, uniquement en version 64 bits 35 (Windows ou macOS). D’autres installeurs ont été proposés autrefois par d’autres éditeurs. L’installeur d’EnterpriseDB contient le serveur PostgreSQL, compilé pour Windows 36 , avec les modules contrib ainsi que pgAdmin 4, et aussi un outil appelé StackBuilder permettant d’installer d’autres outils. L’utilisation est tout à fait classique. Il y a plusieurs écrans de saisie d’informations : - le répertoire d’installation des binaires; - le choix des outils (copie d’écran ci-dessus), notamment des outils en ligne de commande (à conserver impérativement), des pilotes et de pgAdmin 4 ; - le répertoire des données de la première instance; - le mot de passe de l’utilisateur postgres; - le numéro de port ; - la locale par défaut. Le répertoire d’installation a une valeur par défaut généralement convenable car il n’existe pas vraiment de raison d’installer les binaires PostgreSQL en dehors du répertoire Program Files. Par contre, le répertoire des données de l’instance PostgreSQL. n’a pas à être dans ce même répertoire Program Files ! Il est souvent modifié pour un autre disque que le disque système. Le système de fichiers NTFS est obligatoire. Le numéro de port est par défaut le 5432, sauf si d’autres instances sont déjà installées. Dans ce cas, l’installeur propose un numéro de port non utilisé. Le mot de passe est celui de l’utilisateur postgres au sein de PostgreSQL. En cas de mise à jour, il faut saisir l’ancien mot de passe. Le service lui-même et tous ses processus tourneront avec le compte système générique NETWORK SERVICE (Compte de service réseau). La commande initdb est exécutée pour créer le répertoire des données. Un service est ajouté pour lancer automatiquement le serveur au démarrage de Windows. Un sous-menu du menu Démarrage contient le nécessaire pour interagir avec le serveur PostgreSQL, comme une icône pour recharger la configuration et surtout pgAdmin 4, qui se lancera dans un navigateur. L’outil StackBuilder, lancé dans la foulée, permet de télécharger et d’installer d’autres outils : pilotes pour différents langages (Npgsql pour C#, pgJDBC, psqlODBC), PostGIS… installés dans le répertoire de l’utilisateur en cours. L’installeur peut être utilisé uniquement en ligne de commande (voir les options avec --help ). La documentation de Dalibo pour l’installation de PostgreSQL en version native pour Windows est disponible sur https://public.dalibo.com/archives/etudes/installer_postgresql_9.0_sous_windows.pdf. 35 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 36 https://www.postgresql.org/download/windows/ ### 3.7.2 Installation depuis des binaires Binaires précompilés pour Windows, depuis v9.3 : https://www.enterprisedb.com/download-postgresql-binaries Pour installer PostgreSQL sans installeur ni compilation, EBD propose aussi une archive des binaires compilés 37 , même de versions aussi anciennes que la 9.3, en 32 bits jusqu’en version 10, au cas où il faille réinstaller de très vieilles instances. Il faudra créer soi-même la base avec initdb , créer un service au besoin, etc. ### 3.7.3 Installation dans WSL - WSL : Windows Subsystem for Linux - Une VM Linux rapidement créée - Ubuntu, Debian, AlmaLinux… - Avantages et inconvénients d’une VM - https://dali.bo/kb_wsl Windows Subsystem for Linux (WSL) est fourni par Microsoft. Dans sa version WSL2, il permet de créer très rapidement une machine virtuelle Linux sur un système Windows, en intégrant relativement bien les deux systèmes. La distribution Ubuntu est installée par défaut, mais Debian ou AlmaLinux (dérivé Red Hat) sont aussi disponibles. PostgreSQL peut ensuite y être installé et géré comme sur un Linux classique, y compris utilisant les paquets du PGDG, avec ses nombreux outils et extensions. L’utilisation d’une machine virtuelle complique un peu les accès depuis l’extérieur. Pour des bases PostgreSQL « embarquées » dans des applications essentiellement Windows, ou pour du développement autonome sans trop de souci d’une performance réaliste, WSL est une alternative intéressante à l’installeur de PostgreSQL natif Windows fourni par EDB, pas très pratique, parfois limité, et pas plus rapide à installer. WSL dispose de beaucoup plus d’outils et extensions prêtes à l’utilisation. - Référence : https://dali.bo/kb_wsl 37 https://www.enterprisedb.com/download-postgresql-binaries ## 3.8 PREMIERS RÉGLAGES ### 3.8.1 Au menu - Sécurité - Configuration - accès - connexions - mémoire - journaux - traces - tâches de fond ### 3.8.2 Sécurité - Politique d’accès : - pour l’utilisateur postgres système - pour le rôle postgres - Règles d’accès à l’instance dans pg_hba.conf Selon l’environnement et la politique de sécurité interne à l’entreprise, il faut potentiellement initialiser un mot de passe pour l’utilisateur système postgres : **Exemple 3-41.** Exemple extrait du PDF\, page 144\. ```console $ passwd postgres ``` Sans mot de passe, il faudra passer par un système comme sudo pour pouvoir exécuter des commandes en tant qu’utilisateur postgres, ce qui sera nécessaire au moins au début. Le fait de savoir qu’un utilisateur existe sur un serveur permet à un utilisateur hostile de tenter de forcer une connexion par force brute. Par exemple, ce billet de blog 38 , montre que l’utilisateur postgres est dans le top 10 des logins attaqués. La meilleure solution pour éviter ce type d’attaque est de ne pas définir de mot de passe pour l’utilisateur OS postgres et de se connecter uniquement par des échanges de clés SSH. Il est conseillé de ne fixer aucun mot de passe pour l’utilisateur système. Il en va de même pour le rôle postgres dans l’instance. Une fois connecté au système, nous pourrons utiliser le mode d’authentification local peer pour nous connecter au rôle postgres. Ce mode permet de limiter la surface d’attaque sur son instance. 38 https://blog.sucuri.net/2013/07/ssh-brute-force-the-10-year-old-attack-that-still-persists.html En cas de besoin d’accès distant en mode superutilisateur, il sera possible de créer des rôles supplémentaires avec des droits superutilisateur. Ces noms ne doivent pas être facile à deviner par de potentiels attaquants. Il faut donc éviter les rôles admin ou root. Si vous avez besoin de créer des mots de passe, ils doivent bien sûr être longs et complexes (par exemple en les générant avec les utilitaires pwgen ou apg ). Si vous avez utilisé l’installeur proposé par EnterpriseDB, l’utilisateur système et le rôle PostgreSQL ont déjà un mot de passe, celui demandé par l’installeur. Il n’est donc pas nécessaire de leur en configurer un autre. Enfin, il est important de vérifier les règles d’accès au serveur contenues dans le fichier pg_hba.conf . Ces règles définissent les accès à l’instance en se basant sur plusieurs paramètres : utilisation du réseau ou du socket fichier, en SSL ou non, depuis quel réseau, en utilisant quel rôle, pour quelle base de données et avec quelle méthode d’authentification. ### 3.8.3 Configuration minimale - Fichier postgresql.conf - Configuration du moteur - Plus de 300 paramètres - Quelques paramètres essentiels La configuration du moteur se fait via un seul fichier, le fichier postgresql.conf . Il se trouve généralement dans le répertoire des données du serveur PostgreSQL. Sous certaines distributions (Debian et affiliés principalement), il est déplacé dans /etc/postgresql/ . Ce fichier contient beaucoup de paramètres, plus de 300, mais seuls quelques-uns sont essentiels à connaître pour avoir une instance fiable et performante. ### 3.8.4 Précédence des paramètres ![Illustration extraite du PDF, page 146.](/images/dba1/manual/p146-image-018.png) {#img-p146-018} PostgreSQL offre une certaine granularité dans sa configuration, ainsi certains paramètres peuvent être surchargés par rapport au fichier postgresql.conf . Il est utile de connaître l’ordre de précédence. Par exemple, un utilisateur peut spécifier un paramètre dans sa session avec l’ordre SET , celuici sera prioritaire par rapport à la configuration présente dans le fichier postgresql.conf . ### 3.8.5 Configuration des connexions : accès au serveur - listen_addresses = '*' (systématique) - port = 5432 - password_encryption = scram-sha-256 (v10+) Ouvrir les accès : Par défaut, une instance PostgreSQL n’écoute que sur l’interface de boucle locale ( localhost ) et pas sur les autres interfaces réseaux. Pour autoriser les connexions externes à PostgreSQL, il faut modifier le paramètre listen_addresses , en général ainsi : **Exemple 3-42.** Exemple extrait du PDF\, page 146\. ```text listen_addresses = '*' ``` La valeur * est un joker indiquant que PostgreSQL doit écouter sur toutes les interfaces réseaux disponibles au moment où il est lancé. Il est aussi possible d’indiquer les interfaces, une à une, en les séparant avec des virgules. Cette méthode est intéressante lorsqu’on veut éviter que l’instance écoute sur une interface donnée. Par prudence il est possible de se limiter aux interfaces destinées à être utilisées : **Exemple 3-43.** Exemple extrait du PDF\, page 147\. ```text listen_addresses = 'localhost, 10.1.123.123' ``` La restriction par listen_addresses est un premier niveau de sécurité. Elle est complémentaire de la méthode plus fine par pg_hba.conf , par les IP clientes, utilisateur et base, qu’il faudra de toute façon déployer. De plus, modifier listen_addresses impose de redémarrer l’instance. Port : Le port par défaut des connexions TCP/IP est le 5432 . C’est la valeur traditionnelle et connue de tous les outils courants. La modifier n’a d’intérêt que si vous voulez exécuter plusieurs instances PostgreSQL sur le même serveur (physique ou virtuel). En effet, plusieurs instances sur une même machine ne peuvent pas écouter sur le même couple adresse IP et port. Une instance PostgreSQL n’écoute jamais que sur ce seul port, et tous les clients se connectent dessus. Il n’existe pas de notion de listener ou d’outil de redirection comme sur d’autres bases de données concurrentes, du moins sans outil supplémentaire (par exemple le pooler PgBouncer). S’il y a plusieurs instances dans une même machine, elles devront posséder chacune un couple adresse IP/port unique. En pratique, il vaut mieux attribuer un port par instance. Bien sûr, PostgreSQL refusera de démarrer s’il voit que le port est déjà occupé. Ne confondez pas la connexion à localhost (soit ::1 en IPv6 ou 127.0.0.1 en IPv4), qui utilise les ports TCP/IP, et la connexion dite local , passant par les sockets de l’OS (par défaut /var/run/postgresql/.s.PGSQL.5432 sur les distributions les plus courantes). La distinction est importante dans pg_hba.conf notamment. Chiffrement des mots de passe : Le paramètre password_encryption définit l’algorithme de chiffrement utilisé pour le stockage des mots de passe. La valeur scram-sha-256 est le défaut, et à conserver. ### 3.8.6 Configuration du nombre de connexions - max_connections = 100 - 1 connexion = 1 processus serveur - Compromis entre - CPU / nombre requêtes actives / RAM / complexité - Danger si trop haut ! - performances (même avec des connexions inactives) - risque de saturation - Possibilité de réserver quelques connexions pour l’administration Le nombre de connexions simultanées est limité par le paramètre max_connections . Dès que ce nombre est atteint, les connexions suivantes sont refusées avec un message d’erreur, et ce jusqu’à ce qu’un utilisateur connecté se déconnecte. max_connections vaut par défaut 100, et c’est généralement suffisant en première inb tention. Noter qu’il existe un paramètre superuser_reserved_connections (à 3 par défaut) qui réserve quelques connexions au superutilisateur pour qu’il puisse se connecter malgré une saturation. Depuis PostgreSQL 16, il existe un autre paramètre nommé reserved_connections , (à 0 par défaut) pour réserver quelques connexions aux utilisateurs à qui l’on aura attribué un rôle spécifique, nommé pg_use_reserved_connections . Ce peut être utile pour des utilisateurs non applicatifs (supervision et sauvegarde notamment) à qui l’on ne veut ou peut pas donner le rôle SUPERUSER . Il peut être intéressant de diminuer max_connections pour interdire d’avoir trop de connexions actives. Cela permet de soulager les entrées-sorties, ou de monter work_mem (la mémoire de tri). À l’inverse, il est possible d’augmenter max_connections pour qu’un plus grand nombre d’utilisateurs ou d’applications puisse se connecter en même temps. Au niveau mémoire, un processus consomme par défaut 2 Mo de mémoire vive. Cette consommation peut augmenter suivant son activité. Il faut surtout savoir qu’à chaque connexion se voit associée un processus sur le serveur, processus qui n’est vraiment actif qu’à l’exécution d’une requête. Il s’agit donc d’arbitrer entre : - le nombre de requêtes à exécuter à un instant T ; - le nombre de CPU disponibles ; - la complexité et la longueur des requêtes; - et même le nombre de processus que peut gérer l’OS. L’établissement a un certain coût également. Il faut éviter qu’une application se connecte et se déconnecte sans cesse. Il ne sert à rien d’autoriser des milliers de connexions s’il n’y a que quelques processeurs, ou si les requêtes sont lourdes. Si le nombre de requêtes réellement actives augmente fortement, le serveur peut s’effondrer. Restreindre les connexions permet de préserver le serveur, même si certaines connexions sont refusées. Le paramétrage est compliqué par le fait qu’une même requête peut mobiliser plusieurs processeurs si elle est parallélisée. Certaines requêtes seront limitées par le CPU, d’autres par la bande passante des disques. Enfin, même si une connexion inactive ne consomme pas de CPU et peu de RAM, elle a tout de même un impact. En effet, une connexion active va générer assez fréquemment ce qu’on appelle un snapshot (ou une image) de l’état des transactions de la base. La durée de création de ce snapshot dépend principalement du nombre de connexions, actives ou non, sur le serveur. Donc une connexion active consommera plus de CPU s’il y a 999 autres connexions, actives ou non, que s’il y a 9 connexions, actives ou non. Ce cas a été optimisé, mais il vaut mieux éviter d’avoir des milliers de connexions ouvertes « au cas où ». Intercaler un « pooler » comme PgBouncer entre les clients et l’instance peut se justifier dans certains cas : - connexions/déconnexions très fréquentes; - centaines, voire milliers, de connexions généralement inactives; - limitation du nombre de connexions actives avec mise en attente au niveau du pooler (sans erreur). ### 3.8.7 Configuration de la mémoire partagée - shared_buffers = (?)GB - 25 % de la RAM généralement - max 40 % - complémentaire du cache OS Shared buffers : Chaque fois que PostgreSQL a besoin de lire ou d’écrire des données, il les charge d’abord dans son cache interne. Ce cache ne sert qu’à ça : stocker des blocs disques qui sont accessibles à tous les processus PostgreSQL, ce qui permet d’éviter de trop fréquents accès disques car ces accès sont lents. La taille de ce cache dépend d’un paramètre appelé shared_buffers . La documentation officielle 39 conseille ceci pour dimensionner shared_buffers : Un bon point de départ est 25 % de la mémoire vive totale. Ne pas dépasser 40 %, car le cache du système d’exploitation est aussi utilisé. 39 https://docs.postgresql.fr/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY Sur une machine dédiée de 32 Go de RAM, cela donne donc : **Exemple 3-44.** Exemple extrait du PDF\, page 150\. ```text shared_buffers = 8GB ``` Le défaut de 128 Mo n’est donc pas adapté à un serveur sur une machine récente. Suivant les cas, une valeur inférieure ou supérieure à 25 % sera encore meilleure pour les performances, mais il faudra tester avec votre charge (en lecture, en écriture, et avec le bon nombre de clients). Le cache système limite la plupart du temps l’impact d’un mauvais paramétrage de shared_buffers , et il est moins grave de sous-dimensionner un peu shared_buffers que de le sur-dimensionner. Attention : une valeur élevée de shared_buffers (au-delà de 8 Go) nécessite de paramétrer finement le système d’exploitation (Huge Pages notamment) et d’autres paramètres liés aux journaux et checkpoints comme max_wal_size . Il faut aussi s’assurer qu’il restera de la mémoire pour le reste des opérations (tri…) et donc adapter work_mem . Modifier shared_buffers impose de redémarrer l’instance. ### 3.8.8 Configuration : mémoire des processus - work_mem - par processus, voire nœud - valeur très dépendante de la charge et des requêtes - fichiers temporaires vs saturation RAM - × hash_mem_multiplier - maintenance_work_mem - Pas de limite stricte à la consommation mémoire des sessions - Augmenter prudemment & superviser Les processus de PostgreSQL ont accès à la mémoire partagée, définie principalement par shared_buffers , mais ils ont aussi leur mémoire propre. Cette mémoire n’est utilisable que par le processus l’ayant allouée. - Le paramètre le plus important est work_mem , qui définit la taille maximale de la mémoire de travail que peut utiliser un processus dans un nœud de requête, en particulier pour des tris ( ORDER BY ), certaines agrégations (par hash), certaines jointures (hash join notamment), des déduplications ( DISTINCT ), des CTE matérialisées ( WITH … AS … )… - Autre paramètre capital, maintenance_work_mem définit la mémoire utilisable pour les opérations de maintenance lourdes : VACUUM , CREATE INDEX , REINDEX , ajouts de clé étrangère… Cette mémoire liée au processus est rendue immédiatement après la fin de l’ordre concerné. - Il existe aussi logical_decoding_work_mem (défaut : 64 Mo), utilisable pour chacun des flux de réplication logique (s’il y en a, ils sont rarement nombreux). Opérations de maintenance & maintenance_work_mem : maintenance_work_mem peut être monté à 256 Mo à 1 Go, voire plus sur les machines récentes, car il concerne des opérations lourdes (indexation, nettoyage des index par VACUUM …). Leurs consommations de RAM s’additionnent, mais en pratique ces opérations sont rarement exécutées plusieurs fois simultanément. Monter au-delà de 1 Go n’a d’intérêt que pour la création ou la réindexation de très gros index. autovacuum_work_mem est la mémoire que s’autorise à prendre l’autovacuum pour les nettoyages d’index, et ce pour chaque worker de l’autovacuum (3 maximum par défaut). Par défaut, ce paramètre reprend maintenance_work_mem , et est généralement laissé tel quel. Paramétrage de work_mem : Configurer work_mem est plus compliqué. Si work_mem est trop bas, beaucoup d’opérations ne s’effectueront pas en RAM. Par exemple, si une jointure par hachage impose d’utiliser 100 Mo en mémoire, mais que work_mem vaut 10 Mo, PostgreSQL écrira des dizaines de mégaoctets sur disque à chaque appel de la jointure. Par contre, si work_mem vaut 120 Mo, aucune écriture n’aura lieu sur disque, ce qui accélérera généralement la requête et réduira les I/O. Trop de fichiers temporaires peuvent ralentir les opérations, voire saturer le disque. Supervisez leur présence. Mais il est illusoire de vouloir éviter tous les fichiers temporaires des grosses requêtes en montant work_mem à une valeur déraisonnable. Un work_mem trop bas peut aussi contraindre le planificateur à choisir des plans d’exécution moins optimaux. Si work_mem est trop haut, et que trop de requêtes le consomment simultanément, le danger est de saturer la RAM. Il n’existe en effet pas de limite à la consommation des sessions de PostgreSQL, ni globalement ni par session ! **Exemple 3-45.** Exemple extrait du PDF\, page 152\. ```text Or le paramétrage de l’overcommit sous Linux est par défaut très permissif, et le noyau ne bloque rien. La première conséquence de la saturation de mémoire est l’assèchement du cache système (complémentaire de celui de PostgreSQL), et la dégradation des performances. Puis le système se met à swapper, avec à la clé un ralentissement général et durable. Enfin le noyau, à court de mémoire, est amené à tuer un processus de PostgreSQL. Cela mène à l’arrêt de l’instance, ou plus fréquemment à son redémarrage brutal avec coupure de toutes les connexions et requêtes en cours. ``` Toutefois, si l’administrateur désactive l’overcommit, Linux refuse d’allouer une RAM qu’il n’a pas, et la requête tombe en erreur. Le cache système est préservé, et PostgreSQL ne tombe pas. (Pour la mise en place de l’overcommit sous Linux, voir le module J1 40 , ou la base de connaissance Dalibo 41 .) Suivant la complexité des requêtes, il est possible qu’un processus utilise plusieurs fois work_mem (par exemple si une requête fait une jointure et un tri, ou qu’un nœud est parallélisé). À l’inverse, beaucoup de requêtes ne nécessitent aucune mémoire de travail. La valeur de work_mem dépend donc beaucoup de la mémoire disponible, des requêtes et du nombre de connexions actives. Si le nombre de requêtes simultanées est important, work_mem devra être faible. Avec peu de requêtes simultanées, work_mem pourra être augmenté sans risque. Il n’y a pas de formule de calcul miracle. Une première estimation courante, bien que très conservatrice, peut être : **Équation 3.1.** Estimation de work\_mem\. $$ work_mem = \frac{\text{mémoire}}{\text{max\_connections}} $$ **Exemple 3-46.** Exemple extrait du PDF\, page 152\. ```text On obtient alors, sur un serveur dédié avec 16 Go de RAM et 200 connexions autorisées : work_mem = 80MB ``` Mais max_connections est fréquemment surdimensionné, et beaucoup de sessions sont inactives. work_mem est alors sous-dimensionné. Plus finement, Christophe Pettus propose en première intention 42 : 40 [https://dali.bo/j1_html#configuration-de-la-surréservation-mémoire](https://dali.bo/j1_html#configuration-de-la-surr%C3%A9servation-m%C3%A9moire) 41 https://dali.bo/kb_overcommit 42 https://thebuild.com/blog/2023/03/13/everything-you-know-about-setting-work_mem-is-wrong/ **Équation 3.2.** Estimation prudente de work\_mem\. $$ work_mem = \frac{4 \times \text{mémoire libre}}{\text{max\_connections}} $$ Soit, pour une machine dédiée avec 16 Go de RAM, donc 4 Go de shared buffers, et 200 connections : **Exemple 3-47.** Exemple extrait du PDF\, page 153\. ```text work_mem = 240MB ``` Dans l’idéal, si l’on a le temps pour une étude, il faudra vérifier les requêtes générant la plupart des fichiers (optimisation, indexation…). Puis on peut monter progressivement et prudemment work_mem jusqu’à voir disparaître l’essentiel des fichiers temporaires dans les traces, à condition de rester loin de saturer la RAM lors des pics de charge. En pratique, le défaut de 4 Mo est très conservateur, souvent insuffisant. Généralement, la valeur varie entre 10 et 100 Mo. Au-delà de 100 Mo, il y a souvent un problème ailleurs : des tris sur de trop gros volumes de données, une mémoire insuffisante, un manque d’index (utilisés pour les tris), etc. Des valeurs vraiment grandes ne sont valables que sur des systèmes d’infocentre. Augmenter globalement la valeur du work_mem peut parfois mener à une consommation excessive de mémoire. Il est possible de ne la modifier que le temps d’une session pour les besoins d’une requête ou d’un traitement particulier : **Exemple 3-48.** Exemple extrait du PDF\, page 153\. ```text SET work_mem TO '30MB' ; ``` hash_mem_multiplier : hash_mem_multiplier est un paramètre multiplicateur, qui peut s’appliquer à certaines opérations (le hachage, lors de jointures ou agrégations). Par défaut, il vaut 1 en versions 13 et 14, et 2 à partir de la 15. Le seuil de consommation fixé par work_mem est alors multiplié d’autant. Lors d’un hachage, l’optimiseur considère donc qu’il peut utiliser la mémoire jusqu’à hauteur de work_mem × hash_mem_multiplier et sinon il se rabat sur le disque. L’exécuteur comptabilise la mémoire utilisée, et en cas d’erreur d’estimation conduisant à un dépassement de cette limite de consommation de mémoire, le hachage est terminé sur le disque. L’existence de hash_mem_multiplier permet de donner plus de RAM à ces opérations sans augmenter globalement work_mem . Il peut lui aussi être modifié dans une session. Il existe d’autres paramètres influant sur les besoins en mémoires, moins importants pour une première approche. ### 3.8.9 Configuration des journaux de transactions 1/2 **Exemple 3-49.** Exemple extrait du PDF\, page 154\. ```text fsync = on (si vous tenez à vos données) ``` À chaque fois qu’une transaction est validée ( COMMIT ), PostgreSQL écrit les modifications qu’elle a générées dans les journaux de transactions. Afin de garantir la durabilité, PostgreSQL effectue des écritures synchrones des journaux de transaction, donc une écriture physique des données sur le disque. Cela a un coût important sur les performances en écritures s’il y a de nombreuses transactions mais c’est le prix de la sécurité. Le paramètre fsync permet de désactiver l’envoi de l’ordre de synchronisation au système d’exploitation. Ce paramètre doit rester à on en production. Dans le cas contraire, un arrêt brutal de la machine peut mener à la perte des journaux non encore enregistrés et à la corruption de l’instance. D’autres paramètres et techniques existent pour gagner en performance (et notamment si certaines données peuvent être perdues) sans pour autant risquer de corrompre l’instance. ### 3.8.10 Configuration des journaux de transactions 2/2 **Figure 3-2.** Niveaux de cache et fsync ![Niveaux de cache et fsync](/images/dba1/manual/p155-image-020.png) Une écriture peut être soit synchrone soit asynchrone. Pour comprendre ce mécanisme, nous allons simplifier le cheminement de l’écriture d’un bloc : - Dans le cas d’une écriture asynchrone : Un processus qui modifie un fichier écrit en fait d’abord dans le cache du système de fichiers du système d’exploitation (OS), cache situé en RAM (mémoire volatile). L’OS confirme tout de suite au processus que l’écriture a été réalisée pour lui rendre la main au plus vite : il y a donc un gain en performance important. Cependant, le bloc ne sera écrit sur disque que plus tard afin notamment de grouper les demandes d’écritures des autres processus, et de réduire les déplacements des têtes de lecture/écriture des disques, qui sont des opérations coûteuses en temps. Entre la confirmation de l’écriture et l’écriture réelle sur les disques, il peut se passer un certain délai : si une panne survient durant celui-ci, les données soi-disant écrites seront perdues, car pas encore physiquement sur le disque. - Dans le cas d’une écriture synchrone : Un processus écrit dans le cache du système d’exploitation, puis demande explicitement à l’OS d’effectuer la synchronisation (écriture physique) sur disque. Les blocs sont donc écrits sur les disques immédiatement et le processus n’a la confirmation de l’écriture qu’une fois cela fait. Il attendra donc pendant la durée de cette opération, mais il aura la garantie que la donnée est bien présente physiquement sur les disques. Cette synchronisation est très coûteuse et lente (encore plus avec un disque dur classique et ses têtes de disques à déplacer). Un phénomène équivalent peut se produire à nouveau au niveau matériel (hors du contrôle de l’OS) : pour gagner en performance, les constructeurs ont rajouté un système de cache au sein des cartes RAID. L’OS (et donc le processus qui écrit) a donc confirmation de l’écriture dès que la donnée est présente dans ce cache, alors qu’elle n’est pas encore écrite sur disque. Afin d’éviter la perte de donnée en cas de panne électrique, ce cache est secouru par une batterie qui laissera le temps d’écrire le contenu du cache. Vérifiez qu’elle est bien présente sur vos disques et vos cartes contrôleur RAID. ### 3.8.11 Configuration des traces - Selon système/distribution : - log_destination - logging_collector - emplacement et nom différent pour postgresql-????.log - log_line_prefix à compléter : - log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ' - lc_messages = C (anglais) PostgreSQL dispose de plusieurs moyens pour enregistrer les traces : soit il les envoie sur la sortie des erreurs ( stderr , csvlog et jsonlog ), soit il les envoie à syslog ( syslog , seulement sous Unix), soit il les envoie au journal des événements ( eventlog , sous Windows uniquement). Dans le cas où les traces sont envoyées sur la sortie des erreurs, il peut récupérer les messages via un démon appelé logger process qui va enregistrer les messages dans des fichiers. Ce démon s’active en configurant le paramètre logging_collector à on . Tout cela est configuré par défaut différemment selon le système et la distribution. Red Hat active logging_collector et PostgreSQL dépose ses traces dans des fichiers journaliers `$PGDATA/log/postgresql-.log` . Debian utilise stderr sans autre paramétrage et c’est le système qui dépose les traces dans /var/log/postgresql/postgresql-VERSION-nominstance.log . Les deux variantes fonctionnent. En fonction des habitudes et contraintes locales, il est possible de préférer et d’activer l’une ou l’autre. L’entête de chaque ligne des traces doit contenir au moins la date et l’heure exacte ( %t ou %m suivant la précision désirée) : des traces sans date et heure ne servent à rien. Des entêtes complets sont suggérés par la documentation de l’analyseur de log pgBadger : **Exemple 3-50.** Exemple extrait du PDF\, page 156\. ```text log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ' ``` Beaucoup d’utilisateurs français récupèrent les traces de PostgreSQL en français. Bien que cela semble une bonne idée au départ, cela se révèle être souvent un problème. Non pas à cause de la qualité de la traduction, mais plutôt parce que les outils de traitement des traces fonctionnent uniquement avec des traces en anglais. Même un outil comme pgBadger, pourtant écrit par un Français, ne sait pas interpréter des traces en français. De plus, la moindre recherche sur Internet ramènera plus de liens si le message est en anglais. Positionnez donc lc_messages à C . ### 3.8.12 Configuration des tâches de fond Laisser ces deux paramètres à on : - autovacuum - track_counts En dehors du logger process, PostgreSQL dispose d’autres tâches de fond. Les processus autovacuum jouent un rôle important pour disposer de bonnes performances : ils empêchent une fragmentation excessive des tables et index, et mettent à jour les statistiques sur les données (statistiques servant à l’optimiseur de requêtes). La récupération des statistiques sur l’activité permet le bon fonctionnement de l’autovacuum et donne de nombreuses informations importantes à l’administrateur de bases de données. Ces deux tâches de fond devraient toujours être activés. ### 3.8.13 Se faciliter la vie - Création automatique de configuration - pgtune et https://pgtune.leopard.in.ua/ - http://pgconfigurator.cybertec.at/ - Documentation et analyse de configuration - https://postgresqlco.nf pgtune existe en plusieurs versions. La version en ligne de commande va détecter automatiquement le nombre de CPU et la quantité de RAM, alors que la version web nécessitera que ces informations soient saisies. Suivant le type d’utilisation, pgtune proposera une configuration adaptée. Cette configuration n’est évidemment pas forcément optimale par rapport à vos applications, tout simplement parce qu’il ne connaît que les ressources et le type d’utilisation, mais c’est généralement un bon point de départ. pgconfigurator est un outil plus récent, un peu plus graphique, mais il remplit exactement le même but que pgtune. Enfin, le site postgresql.co.nf 43 est un peu particulier. C’est en quelque sorte une encyclopédie sur les paramètres de PostgreSQL, mais il est aussi possible de lui faire analyser une configuration. Après analyse, des informations supplémentaires seront affichées pour améliorer cette configuration, que ce soit pour la stabilité du serveur comme pour ses performances. 43 https://postgresqlco.nf ## 3.9 MISE À JOUR ### 3.9.1 Mise à jour - Recommandations - Mise à jour mineure - Mise à jour majeure - Mise à jour de l’OS ### 3.9.2 Recommandations - Les Release Notes - Intégrité des données - Bien redémarrer le serveur! Chaque nouvelle version de PostgreSQL est accompagnée d’une note expliquant les améliorations, les corrections et les innovations apportées par cette version, qu’elle soit majeure ou mineure. Ces notes contiennent toujours une section dédiée aux mises à jour dans laquelle se trouvent des conseils essentiels. Les Releases Notes sont présentes dans l’annexe E de la documentation officielle 44 . Les données de votre instance PostgreSQL sont toujours compatibles d’une version mineure à l’autre. Ainsi, les mises à jour vers une version mineure supérieure peuvent se faire sans migration de données, sauf cas exceptionnel qui serait alors précisé dans les notes de version. Par exemple, de la 15.3 à la 15.4, il a été recommandé de reconstruire les index de type BRIN 45 pour prendre en compte une correction de bug les concernant. Autre exemple : à partir de la 10.3 46 , pg_dump a imposé des noms d’objets qualifiés pour des raisons de sécurité, ce qui a posé problème pour certains réimports. Pensez éventuellement à faire une sauvegarde préalable par sécurité. À contrario, si la mise à jour consiste en un changement de version majeure (par exemple, de la 11 à la 17), il est nécessaire de s’assurer que les données seront transférées correctement sans incompatibilité. Là encore, il est important de lire les Releases Notes avant la mise à jour. Le site why-upgrade 47 , basé sur les release notes, permet de compiler les différences entre plusieurs versions de PostgreSQL. Dans tous les cas, pensez à bien redémarrer le serveur. Mettre à jour les binaires ne suffit pas. 44 https://docs.postgresql.fr/current/release.html 45 https://www.postgresql.org/docs/release/15.4/ 46 https://www.postgresql.org/docs/release/10.3/ 47 https://why-upgrade.depesz.com/ ### 3.9.3 Mise à jour mineure - Méthode - arrêter PostgreSQL - mettre à jour les binaires - redémarrer PostgreSQL - Pas besoin de s’occuper des données, sauf cas exceptionnel - bien lire les Release Notes pour s’en assurer Faire une mise à jour mineure est simple et rapide. La première action est de lire les Release Notes pour s’assurer qu’il n’y a pas à se préoccuper des données. C’est généralement le cas mais il est préférable de s’en assurer avant qu’il ne soit trop tard. La deuxième action est de faire la mise à jour. Tout dépend de la façon dont PostgreSQL a été installé : - par compilation, il suffit de remplacer les anciens binaires par les nouveaux; - par paquets précompilés, il suffit d’utiliser le système de paquets ( apt sur Debian et affiliés, yum ou dnf sur Red Hat et affiliés) ; - par l’installeur graphique, en le ré-exécutant. Ceci fait, un redémarrage du serveur est nécessaire. Il est intéressant de noter que les paquets Debian s’occupent directement de cette opération. Il n’est donc pas nécessaire de le refaire. ### 3.9.4 Mise à jour majeure - Bien lire les Release Notes - Bien tester l’application avec la nouvelle version - rechercher les régressions en terme de fonctionnalités et de performances - penser aux extensions et aux outils - Pour mettre à jour - mise à jour des binaires - et mise à jour/traitement des fichiers de données - 3 méthodes - dump/restore - réplication logique - pg_upgrade Faire une mise à jour majeure est une opération complexe à préparer prudemment. La première action là-aussi est de lire les Release Notes pour bien prendre en compte les régressions potentielles en terme de fonctionnalités et/ou de performances. Cela n’arrive presque jamais mais c’est possible malgré toutes les précautions mises en place. La deuxième action est de mettre en place un serveur de tests où se trouve la nouvelle version de PostgreSQL avec les données de production. Ce serveur sert à tester PostgreSQL mais aussi, et même surtout, l’application. Le but est de vérifier encore une fois les régressions possibles. N’oubliez pas de tester les extensions non officielles, voire développées en interne, que vous avez installées. Elles sont souvent moins bien testées. N’oubliez pas non plus de tester les outils d’administration, de monitoring, de modélisation. Ils nécessitent souvent une mise à jour pour être compatibles avec la nouvelle version installée. Une fois que les tests sont concluants, arrive le moment de la mise en production. C’est une étape qui peut être longue car les fichiers de données doivent être traités. Il existe plusieurs méthodes que nous détaillerons après. ### 3.9.5 Mise à jour majeure par dump/restore - Méthode historique - Simple et sans risque - mais d’autant plus longue que le volume de données est important - Outils : - pg_dumpall -g puis pg_dump - psql puis pg_restore Il s’agit de la méthode la plus ancienne et la plus sûre. L’idée est de sauvegarder l’ancienne version avec l’outil de sauvegarde de la nouvelle version. pg_dumpall peut suffire, mais pg_dump est malgré tout recommandé. Le problème de lenteur vient surtout de la restauration. pg_restore est un outil assez lent pour des volumétries importantes. Il convient pour des volumes de données peu conséquents (au plus une centaine de Go), au cas où l’on est patient, ou si les autres méthodes ne sont pas possibles. Il est cependant possible d’accélérer la restauration grâce à la parallélisation (option --jobs ). Ce n’est possible que si la sauvegarde a été faite avec pg_dump -Fd ou -Fc . Il est à noter que cette sauvegarde peut elle aussi être parallélisée (option --jobs là encore). Il existe un piège peu connu lié à toute migration logique d’une grande base : le gel massif des lignes. Pour des raisons techniques de recyclage des numéros de transaction, PostgreSQL doit « geler » les lignes anciennes et jamais modifiées, ce qui implique de réécrire le bloc. Or, toutes les lignes insérées par une migration ont le même « âge ». Si elles ne sont pas modifiées, ces lignes risquent d’être toutes gelées et réécrites en même temps : ce peut être très brutal en terme de saturation disque, de journaux générés, etc. si la base est grosse. Le délai avant le déclenchement du gel automatique dépend de la consommation des numéros de transaction sur l’instance migrée, et varie de quelques semaines à des années. Des ordres VACUUM FREEZE sur les plus grosses tables à des moments calmes permettent d’étaler ces écritures. Si ces ordres sont interrompus, l’essentiel de ce qui a pu être gelé ne sera plus à re-geler plus tard. Pour les détails, voir [https://dali.bo/m4_html#le-wraparound-1](https://dali.bo/m4_html#le-wraparound-1) et [https://dali.bo/m5_html#paramétrage-du-freeze-1](https://dali.bo/m5_html#param%C3%A9trage-du-freeze-1). ### 3.9.6 Mise à jour majeure par réplication logique - Possible entre versions 10 et supérieures - Bascule très rapide - Et retour possible La réplication logique rend possible une migration entre deux instances de version majeure différente avec une indisponibilité très courte. Le principe est de répliquer une base à l’identique vers une instance de version plus récente, alors que la production tourne. La réplication logique n’est disponible en natif qu’à partir de PostgreSQL version 10, la base à migrer doit donc être en version 10 ou supérieure. (Autrefois, on utilisait des outils de réplication par triggers, plus complexes à manier.) Des clés primaires sur chaque table sont très fortement conseillées, mais pas forcément obligatoires. L’idée est d’installer la nouvelle version de PostgreSQL normalement, sur le même serveur ou sur un autre serveur. On déclare la réplication de l’ancienne instance vers la nouvelle. Les utilisateurs peuvent continuer à travailler pendant le transfert initial des données. Ils verront au pire une baisse de performances, due à la lecture et à l’envoi des données vers le nouveau serveur. Une fois le transfert initial réalisé, les données modifiées entre-temps sont transférées vers le nouveau serveur. Toute cette opération peut s’étaler sur plusieurs jours. Une fois les deux serveurs synchronisés, il ne reste plus qu’à déclencher un switchover (bascule); puis d’attendre que les dernières données soient répliquées, ce qui peut être très rapide; et enfin de connecter les applications au nouveau serveur. La réplication peut alors être inversée pour garder l’ancienne production synchrone, permettant de rebasculer dessus en cas de problème sans perdre les données modifiées depuis la bascule. Une fois acté que le nouveau serveur donne pleine satisfaction, il suffit de débrancher la réplication logique des deux côtés. Pour les grosses bases, il existe le même danger d’un gel brutal des lignes comme avec pg_restore . ### 3.9.7 Mise à jour majeure par pg_upgrade - pg_upgrade : fourni avec PostgreSQL - Prérequis : pas de changement de format des fichiers entre versions - Nécessite les deux versions sur le même serveur - Support des serveurs PostgreSQL à migrer : - version minimale 9.2 pour pg_upgrade v15 - version minimale 8.4 sinon pg_upgrade est certainement l’outil le plus rapide pour une mise à jour majeure. Il profite du fait que les formats des fichiers de données n’évolue pas, ou de manière rétrocompatible, entre deux versions majeures. Il n’est donc pas nécessaire de tout réécrire. Grossièrement, son fonctionnement est le suivant. Il récupère la déclaration des objets sur l’ancienne instance avec un pg_dump du schéma de chaque base et de chaque objet global. Il intègre la déclaration des objets dans la nouvelle instance. Il fait un ensemble de traitement sur les identifiants d’objets et de transactions. Puis, il copie les fichiers de données de l’ancienne instance vers la nouvelle instance. La copie est l’opération la plus longue, mais comme il n’est pas nécessaire de reconstruire les index et de vérifier les contraintes, cette opération est bien plus rapide que la restauration d’une sauvegarde style pg_dump . Pour aller encore plus rapidement, il est possible de créer des liens physiques à la place de la copie des fichiers. Ceci fait, la migration est terminée. En 2010, Stefan Kaltenbrunner et Bruce Momjian avaient mesuré qu’une base de 150 Go mettait 5 heures à être mise à jour avec la méthode historique (sauvegarde/restauration). Elle mettait 44 minutes en mode copie et 42 secondes en mode lien lors de l’utilisation de pg_upgrade . C’est une migration physique : le problème du gel ultérieur des lignes comme avec pg_restore ne se pose pas. Vu ses performances, ce serait certainement l’outil à privilégier. Cependant, c’est un outil très complexe et quelques bugs particulièrement méchants ont terni sa réputation. Notre recommandation est de bien tester la mise à jour avant de le faire en production, et uniquement sur des bases suffisamment volumineuses dont le court délai de migration justifie l’utilisation de cet outil. Lors du développement de la version 15, les développeurs ont supprimé certaines vieilles parties du code, ce qui le rend à présent incompatible avec des versions très anciennes (de la 8.4 à la 9.1, cette dernière remontant tout de même à 2012). ### 3.9.8 Mise à jour de l’OS Si vous migrez aussi l’OS ou déplacez les fichiers d’une instance : - compatibilité architecture - compatibilité librairies - réindexation parfois nécessaire - ex : Debian 10 et glibc 2.28 Un projet de migration PostgreSQL est souvent l’occasion de mettre à jour le système d’exploitation. Vous pouvez également en profiter pour déplacer l’instance sur un autre serveur à l’OS plus récent en copiant (à froid) le PGDATA. Il faut bien sûr que l’architecture physique (32/64 bits, big/little indian) reste la même. Cependant, même entre deux versions de la même distribution, certains composants du système d’exploitation peuvent avoir une influence, à commencer par la glibc . Cette librairie fondamentale de Linux définit l’ordre des caractères, ordre utilisé dans les index de champs textes. Une incompatibilité entre deux versions sur ce point oblige donc à reconstruire les index, sous peine d’incohérence avec les fonctions de comparaison sur le nouveau système et de corruption à l’écriture. Daniel Vérité détaille sur son blog 48 le problème pour les mises à jour entre Debian 9 et 10, à cause de la mise à jour de la glibc en version 2.28. (Voir aussi le wiki PostgreSQL 49 .) L’utilisation des collations ICU 50 dans les index contourne le problème, mais elles sont encore peu répandues. Dans beaucoup de cas, la collation C.UTF-8 propre à PostgreSQL (version 17 au moins) ou encore la collation PG_UNICODE_FAST (version 18 et plus) peuvent éliminer facilement tout souci de ce genre. Ce problème ne touche bien sûr pas les migrations ou les restaurations avec pg_dump / pg_restore : les données sont alors transmises de manière logique, indépendamment des caractéristiques physiques des instances source et cible, et les index sont systématiquement reconstruits sur la machine cible. 48 https://blog-postgresql.verite.pro/2018/08/30/glibc-upgrade.html 49 https://wiki.postgresql.org/wiki/Locale_data_changes 50 https://blog-postgresql.verite.pro/2018/07/27/icu_ext.html ## 3.10 CONCLUSION - L’installation est simple… - …mais elle doit être soigneusement préparée - Préférer les paquets officiels - Attention aux données lors d’une mise à jour ! ### 3.10.1 Pour aller plus loin - Documentation officielle, chapitre Installation Vous pouvez retrouver la documentation en ligne sur https://docs.postgresql.fr/current/installation.html. ### 3.10.2 Questions N’hésitez pas, c’est le moment ! ## 3.11 QUIZ https://dali.bo/b_quiz ## 3.12 TRAVAUX PRATIQUES La version en ligne des solutions de ces TP est disponible sur https://dali.bo/b_solutions. ### 3.12.1 Installation depuis les paquets binaires du PGDG (Rocky Linux) But : Installer PostgreSQL à partir des paquets communautaires. Cette instance servira aux TP suivants. Pré-installation Quelle commande permet d’installer les paquets binaires de PostgreSQL? Quelle version est packagée? Quels paquets devront également être installés? Installation Installer le dépôt. Désactiver le module d’installation pour la version PostgreSQL de la distribution. Installer les paquets de PostgreSQL18 : serveur, client, contribs. Quel est le chemin des binaires? Création de la première instance Créer une première instance avec les outils de la famille Red Hat. Les sommes de contrôle (checksums) sont par défaut activées avec la version 18. Vérifier ce qui a été fait dans le journal initdb.log . Démarrage Démarrer l’instance. Activer le démarrage de l’instance au démarrage de la machine. Où sont les fichiers de données ( PGDATA ), et les traces de l’instance? Configuration Vérifier la configuration par défaut de PostgreSQL. Est-ce que le serveur écoute sur le réseau? Quel est l’utilisateur sous lequel tourne l’instance? Connexion En tant que root, tenter une connexion avec psql . En tant que postgres, tenter une connexion avec psql . Quitter. À quelle base se connecte-t-on par défaut? Créer une première base de données et y créer des tables. ### 3.12.2 Installation à partir des sources (optionnel) But : Installer PostgreSQL à partir du code source Note : Pour éviter tout problème lié au positionnement des variables d’environnement dans les exercices suivants, l’installation depuis les sources se fera avec un utilisateur dédié, différent de l’utilisateur utilisé par l’installation depuis les paquets de la distribution. Outils de compilation Installer les outils de compilation suivants, si ce n’est déjà fait. NB : Ceci a été testé avec PostgreSQL 18 sur Rocky Linux 9 et Debian 12. Des dépendances un peu différentes pourraient apparaître pour d’autres versions. Sous Rocky Linux, il faudra utiliser dnf : sudo dnf -y group install "Development Tools" sudo dnf -y install perl readline-devel openssl-devel wget bzip2 Sous Debian ou Ubuntu : sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison \ libxml2-dev libxslt-dev libssl-dev pkg-config Créer l’utilisateur système srcpostgres avec /opt/pgsql pour répertoire HOME . Se connecter en tant que l’utilisateur srcpostgres. Téléchargement - Télécharger l’archive des fichiers sources de la dernière version stable depuis postgresql.org. - (Alternative : récupérer le dépôt git de la dernière branche stable.) - Les placer dans /opt/pgsql/src . Compilation et installation L’installation des binaires compilés se fera dans /opt/pgsql/18/ . - Configurer en conséquence l’environnement de compilation ( ./configure ). - Compiler PostgreSQL. Ne pas oublier les contribs. Installer les fichiers obtenus. Où se trouvent les binaires installés de PostgreSQL? Configurer le système Ajouter les variables d’environnement PATH et LD_LIBRARY_PATH au ~srcpostgres/.bash_profile de l’utilisateur srcpostgres pour accéder facilement à ces binaires. Création d’une instance Avec initdb , initialiser une instance dans /opt/pgsql/18/data en spécifiant postgres comme nom de super-utilisateur. Les sommes de contrôle sont activées par défaut. Démarrer l’instance. - Tenter une première connexion avec psql . - Pourquoi cela échoue-t-il? Se connecter en tant qu’utilisateur postgres. Ressortir. Dans .bash_profile , configurer la variable d’environnement PGUSER pour se connecter toujours en tant que postgres. Première base Créer une première base de donnée nommée test . Se connecter à la base test et créer quelques tables. Arrêt Arrêter cette instance. ## 3.13 TRAVAUX PRATIQUES (SOLUTIONS) ### 3.13.1 Installation depuis les paquets binaires du PGDG (Rocky Linux) Pré-installation Quelle commande permet d’installer les paquets binaires de PostgreSQL? Le présent TP utilise Rocky Linux en version 8 ou 9. C’est une distribution communautaire qui se veut succéder au projet CentOS, clone de Red Hat, interrompu en 2021[^51]. (Une version plus complète, ainsi que l’utilisation de paquets Debian, sont traités dans l’annexe « Installation de PostgreSQL depuis les paquets communautaires ».) Quelle version est packagée? La dernière version stable de PostgreSQL disponible au moment de la rédaction de ces lignes est la 18.1. Par contre, la dernière version disponible dans les dépôts dépend de votre distribution. C’est la raison pour laquelle les dépôts du PGDG sont à privilégier. Quels paquets devront également être installés? Le paquet libpq doit également être installé. Il est aussi nécessaire d’installer les paquets llvmjit (pour la compilation à la volée), qui réclame elle-même la présence du dépôt EPEL, mais c’est une fonctionnalité optionnelle qui ne sera pas traitée ici. Installation Installer le dépôt en vous inspirant des consignes sur : https://www.postgresql.org/download/linux/redhat mais en ajoutant les contribs. Préciser : - Red Hat Enterprise, Rocky or Oracle version 8 (ou 9 selon le cas) - x86_64 - PostgreSQL 18 Nous allons reprendre ligne à ligne ce script et le compléter. Se connecter avec l’utilisateur root sur la machine de formation, et recopier le script proposé par le guide. Dans la commande ci-dessous, les deux lignes doivent être copiées et collées ensemble. **Exemple 3-51.** Exemple extrait du PDF\, page 171\. ```text # Rocky Linux 8 ``` dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm **Exemple 3-52.** Exemple extrait du PDF\, page 171\. ```text # Rocky Linux 9 ``` dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm [^51]: https://blog.centos.org/2020/12/future-is-centos-stream Désactiver le module d’installation pour la version PostgreSQL de la distribution. Cette opération est nécessaire pour Rocky Linux 8 ou 9. dnf -qy module disable postgresql Installer les paquets de PostgreSQL18 : serveur, client, contribs. dnf install -y postgresql18-server postgresql18-contrib **Exemple 3-53.** Exemple extrait du PDF\, page 172\. ```text # optionnel ``` dnf install -y postgresql18-llvmjit Il s’agit respectivement - des binaires du serveur; - des « contribs » et extensions optionnelles (mais chaudement conseillées); - et du paquet nécessaire à la compilation à la volée (JIT). Le paquet postgresql18 (outils client) fait partie des dépendances et est installé automatiquement. Quel est le chemin des binaires? Ils se trouvent dans /usr/pgsql-18/bin/ (chemin propre à ces paquets) : **Exemple 3-54.** Exemple extrait du PDF\, page 172\. ```console ls -1 /usr/pgsql-18/bin/ ``` clusterdb **Exemple 3-55.** Exemple extrait du PDF\, page 172\. ```text createdb ... ``` postgres postgresql-18-check-db-dir postgresql-18-setup **Exemple 3-56.** Exemple extrait du PDF\, page 172\. ```text psql ``` reindexdb vacuumdb vacuumlo Noter qu’il existe des liens dans /usr/bin pointant vers la version la plus récente des outils en cas d’installation de plusieurs versions : **Exemple 3-57.** Exemple extrait du PDF\, page 172\. ```console which psql /usr/bin/psql ``` file /usr/bin/psql **Exemple 3-58.** Exemple extrait du PDF\, page 172\. ```console /usr/bin/psql: symbolic link to /etc/alternatives/pgsql-psql ``` file /etc/alternatives/pgsql-psql /etc/alternatives/pgsql-psql: symbolic link to /usr/pgsql-18/bin/psql Création de la première instance Créer une première instance avec les outils de la famille Red Hat. Les sommes de contrôle (checksums) sont par défaut activées avec la version 18. La création d’une instance passe par un outil spécifique à ces paquets. Cet outil doit être appelé en tant que root (et non postgres). Optionnellement, on peut ajouter des paramètres d’initialisation à cette étape. La mise en place des sommes de contrôle est généralement conseillée pour être averti de toute corruption des fichiers. Toujours en temps que root : **Exemple 3-59.** Exemple extrait du PDF\, page 173\. ```console /usr/pgsql-18/bin/postgresql-18-setup initdb ``` Initializing database ... OK L’ export est nécessaire pour activer les sommes de contrôle. Vérifier ce qui a été fait dans le journal initdb.log . La sortie de la commande précédente est redirigée vers le fichier initdb.log situé dans le répertoire qui contient celui de la base ( PGDATA ). Il est possible d’y vérifier l’ensemble des étapes réalisées, notamment l’activation des sommes de contrôle. **Exemple 3-60.** Exemple extrait du PDF\, page 173\. ```console $ cat /var/lib/pgsql/18/initdb.log ``` The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with this locale configuration: locale provider: libc LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 LC_MESSAGES: en_US.UTF-8 LC_MONETARY: en_US.UTF-8 LC_NUMERIC: en_US.UTF-8 LC_TIME: en_US.UTF-8 The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are enabled. fixing permissions on existing directory /var/lib/pgsql/18/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: **Exemple 3-61.** Exemple extrait du PDF\, page 174\. ```console /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data/ -l logfile start ``` Ne pas tenir compte de la dernière ligne, qui est une suggestion qui ne tient pas compte des outils prévus pour cet OS. Démarrage Démarrer l’instance. Attention, si vous avez créé une instance, par exemple à partir des sources, elle doit impérativement être arrêtée pour pouvoir démarrer la nouvelle instance! Elles ne peuvent pas être démarrées en même temps, sauf à modifier le port dans la configuration de l’une d’entre elles. En tant que root : **Exemple 3-62.** Exemple extrait du PDF\, page 174\. ```console systemctl start postgresql-18 ``` Si aucune erreur ne s’affiche, tout va bien à priori. Pour connaître l’état de l’instance : **Exemple 3-63.** Exemple extrait du PDF\, page 174\. ```console systemctl status postgresql-18 ``` ● postgresql-18.service - PostgreSQL 18 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; disabled; preset: disabled) Drop-In: /run/systemd/system/service.d └─zzz-lxc-service.conf Active: active (running) since Wed 2026-01-28 10:05:20 UTC; 25s ago Docs: https://www.postgresql.org/docs/18/static/ Process: 1088 ExecStartPre=/usr/pgsql-18/bin/postgresql-18-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 1093 (postgres) Tasks: 10 (limit: 99788) Memory: 22.2M (peak: 22.7M) CPU: 46ms CGroup: /system.slice/postgresql-18.service ├─1093 /usr/pgsql-18/bin/postgres -D /var/lib/pgsql/18/data/ ├─1094 "postgres: logger " ├─1095 "postgres: io worker 0" ├─1096 "postgres: io worker 1" ├─1097 "postgres: io worker 2" ├─1098 "postgres: checkpointer " ├─1099 "postgres: background writer " ├─1101 "postgres: walwriter " ├─1102 "postgres: autovacuum launcher " └─1103 "postgres: logical replication launcher " Jan 28 10:05:20 rocky9 systemd[1]: Starting PostgreSQL 18 database server... Jan 28 10:05:20 rocky9 postgres[1093]: 2026-01-28 10:05:20.886 UTC [1093] LOG: redirecting log output to logging collector process Jan 28 10:05:20 rocky9 postgres[1093]: 2026-01-28 10:05:20.886 UTC [1093] HINT: Future log output will appear in directory "log". Jan 28 10:05:20 rocky9 systemd[1]: Started PostgreSQL 18 database server. Activer le démarrage de l’instance au démarrage de la machine. Le packaging Red Hat ne prévoie pas l’activation du service au boot, il faut le demander explicitement : **Exemple 3-64.** Exemple extrait du PDF\, page 175\. ```console systemctl enable postgresql-18 ``` Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-18.service → **Exemple 3-65.** Exemple extrait du PDF\, page 175\. ```console /usr/lib/systemd/system/postgresql-18.service. ``` Où sont les fichiers de données ( PGDATA ), et les traces de l’instance? Les données et fichiers de configuration sont dans /var/lib/pgsql/18/data/ . **Exemple 3-66.** Exemple extrait du PDF\, page 175\. ```text ls -1 /var/lib/pgsql/18/data/ ``` base current_logfiles global log pg_commit_ts pg_dynshmem pg_hba.conf pg_ident.conf pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase PG_VERSION pg_wal pg_xact postgresql.auto.conf postgresql.conf postmaster.opts postmaster.pid Les traces sont par défaut dans le sous-répertoire log/ du PGDATA. **Exemple 3-67.** Exemple extrait du PDF\, page 176\. ```text ls -l /var/lib/pgsql/18/data/log/ ``` total 4 -rw------- 1 postgres postgres 701 Jan 28 10:05 postgresql-Wed.log NB : Dans les paquets RPM, le nom exact du fichier dépend du jour de la semaine. Configuration Vérifier la configuration par défaut de PostgreSQL. Est-ce que le serveur écoute sur le réseau? Il est possible de vérifier dans le fichier postgresql.conf que par défaut, le serveur écoute uniquement l’interface réseau localhost (la valeur est commentée car c’est celle par défaut) : **Exemple 3-68.** Exemple extrait du PDF\, page 176\. ```text grep listen_addresses /var/lib/pgsql/18/data/postgresql.conf ``` #listen_addresses = 'localhost' # what IP address(es) to listen on; Il faudra donc modifier ainsi et redémarrer pour que des utilisateurs puissent se connecter depuis d’autres machines : **Exemple 3-69.** Exemple extrait du PDF\, page 176\. ```console listen_addresses = '*' # what IP address(es) to listen on; systemctl restart postgresql-18 ``` Il est aussi possible de vérifier au niveau système en utilisant la commande netstat (qui nécessite l’installation du paquet net-tools ) : **Tableau 3-6.** Tableau ou résultat de commande extrait du PDF\, page 176\. ```text netstat -anp|grep -E '(Active|Proto|postgres)' ``` Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1207/postgres tcp6 0 0 :::5432 :::* LISTEN 1207/postgres Active UNIX domain sockets (servers and established) Proto RefCnt Flags Type State I-Node PID/Program name Path unix 3 [ ] STREAM CONNECTED 2712947 1208/postgres: logg unix 2 [ ACC ] STREAM LISTENING 2715671 1207/postgres /run/postgresql/.s.PGSQL.5432 unix 2 [ ACC ] STREAM LISTENING 2709678 1207/postgres /tmp/.s.PGSQL.5432 … (La présence de lignes tcp6 dépend de la configuration de la machine.) **Exemple 3-70.** Exemple extrait du PDF\, page 176\. ```text On notera que la socket écoute à deux endroits, dans /run/postgresql/ et dans /tmp . C’est un ``` paramétrage par défaut lié aux paquets RPM. Quel est l’utilisateur sous lequel tourne l’instance? C’est l’utilisateur nommé postgres : ps -U postgres -f -o pid,user,cmd PID USER CMD **Exemple 3-71.** Exemple extrait du PDF\, page 177\. ```console 1207 postgres /usr/pgsql-18/bin/postgres -D /var/lib/pgsql/18/data/ 1208 postgres \_ postgres: logger 1209 postgres \_ postgres: io worker 0 1210 postgres \_ postgres: io worker 1 1211 postgres \_ postgres: io worker 2 1212 postgres \_ postgres: checkpointer 1213 postgres \_ postgres: background writer 1215 postgres \_ postgres: walwriter 1216 postgres \_ postgres: autovacuum launcher 1217 postgres \_ postgres: logical replication launcher ``` Il possède aussi le PGDATA : **Exemple 3-72.** Exemple extrait du PDF\, page 177\. ```text ls -l /var/lib/pgsql/18/ ``` total 12 drwx------ 2 postgres postgres 4096 Nov 13 11:41 backups drwx------ 20 postgres postgres 4096 Jan 28 10:07 data -rw------- 1 postgres postgres 913 Jan 28 10:04 initdb.log postgres est le nom traditionnel sur la plupart des distributions, mais il n’est pas obligatoire (par exemple, le TP par compilation utilise un autre utilisateur). Connexion En tant que root, tenter une connexion avec psql . **Exemple 3-73.** Exemple extrait du PDF\, page 177\. ```text # psql psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist ``` Cela échoue car psql tente de se connecter avec l’utilisateur système en cours, soit root. Ça ne marchera pas mieux cependant en essayant de se connecter avec l’utilisateur postgres : **Exemple 3-74.** Exemple extrait du PDF\, page 177\. ```text psql -U postgres psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" ``` En effet, le pg_hba.conf est configuré de telle manière que l’utilisateur de PostgreSQL et celui du système doivent porter le même nom (connexion peer ici). En tant que postgres, tenter une connexion avec psql . Quitter. sudo -iu postgres psql **Exemple 3-75.** Exemple extrait du PDF\, page 177\. ```text psql (18.1) ``` Type "help" for help. **Exemple 3-76.** Exemple extrait du PDF\, page 177\. ```console postgres=# exit ``` La connexion fonctionne donc indirectement depuis tout utilisateur pouvant effectuer un sudo . À quelle base se connecte-t-on par défaut? sudo -iu postgres psql **Exemple 3-77.** Exemple extrait du PDF\, page 178\. ```text psql (18.1) ``` Type "help" for help. **Tableau 3-7.** Tableau ou résultat de commande extrait du PDF\, page 178\. ```text Parameter | Value ----------------------+----------------- Database | postgres Client User | postgres Socket Directory | /run/postgresql Server Port | 5432 Options | Protocol Version | 3.0 Password Used | false GSSAPI Authenticated | false Backend PID | 1741 SSL Connection | false Superuser | on Hot Standby | off ``` **Exemple 3-78.** Exemple extrait du PDF\, page 178\. ```console postgres=# \conninfo Connection Information ``` (12 rows) Là encore, la présence d’une base nommée postgres est une tradition et non une obligation. Première base Créer une première base de données et y créer des tables. sudo -iu postgres psql **Exemple 3-79.** Exemple extrait du PDF\, page 178\. ```console postgres=# CREATE DATABASE test ; ``` CREATE DATABASE Alternativement : sudo -iu postgres createdb test Se connecter explicitement à la bonne base : sudo -iu postgres psql -d test **Exemple 3-80.** Exemple extrait du PDF\, page 178\. ```sql test=# CREATE TABLE mapremieretable (x int); CREATE TABLE test=# \d+ ``` List of relations **Tableau 3-8.** Tableau ou résultat de commande extrait du PDF\, page 178\. ```text Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-----------------+-------+----------+-------------+---------------+-------- -+------------- public | mapremieretable | table | postgres | permanent | heap | 0 bytes | ``` ### 3.13.2 Installation à partir des sources (optionnel) Outils de compilation Installer les outils de compilation suivants, si ce n’est déjà fait. Ces actions doivent être effectuées en tant qu’utilisateur privilégié (soit directement en tant que root, soit en utilisant la commande sudo ). NB : Ceci a été testé avec PostgreSQL 18 sur Rocky Linux 9 et Debian 12. Des dépendances un peu différentes pourraient apparaître pour d’autres versions. Sous Rocky Linux, il faudra utiliser dnf : sudo dnf -y group install "Development Tools" sudo dnf -y install perl readline-devel openssl-devel wget bzip2 Sous Debian ou Ubuntu : sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison \ libxml2-dev libxslt-dev libssl-dev pkg-config Une fois ces outils installés, tout ce qui suit devrait fonctionner sur toute version de Linux. Créer l’utilisateur système srcpostgres avec /opt/pgsql pour répertoire HOME . sudo useradd --home-dir /opt/pgsql --system --create-home srcpostgres sudo usermod --shell /bin/bash srcpostgres Se connecter en tant que l’utilisateur srcpostgres. Se connecter en tant qu’utilisateur srcpostgres : sudo su - srcpostgres Téléchargement - Télécharger l’archive des fichiers sources de la dernière version stable depuis postgresql.org. - (Alternative : récupérer la dernière version stable dans le dépôt git.) - Les placer dans /opt/pgsql/src . En tant qu’utilisateur srcpostgres, créer un répertoire dédié aux sources : **Exemple 3-81.** Exemple extrait du PDF\, page 179\. ```console mkdir ~srcpostgres/src cd ~srcpostgres/src ``` Aller sur https ://postgresql.org 52 , cliquer Download et récupérer le lien vers l’archive des fichiers sources de la dernière version stable (PostgreSQL 18.1 au moment où ceci est écrit). Il est possible de le faire en ligne de commande : 52 https://www.postgresql.org/ftp/source/ **Exemple 3-82.** Exemple extrait du PDF\, page 180\. ```text wget https://ftp.postgresql.org/pub/source/v18.1/postgresql-18.1.tar.bz2 ``` Il faut décompresser l’archive : **Exemple 3-83.** Exemple extrait du PDF\, page 180\. ```text tar xjvf postgresql-18.1.tar.bz2 cd postgresql-18.1 ``` Alternativement, le dépôt git se récupère ainsi : git clone --branch REL_18_1 --depth 1 \ https://git.postgresql.org/git/postgresql.git postgresql-18.1 **Exemple 3-84.** Exemple extrait du PDF\, page 180\. ```text cd postgresql-18.1 ``` ou de manière moins économe : git clone https://git.postgresql.org/git/postgresql.git **Exemple 3-85.** Exemple extrait du PDF\, page 180\. ```text cd postgresql ``` git checkout REL_18_1 **Exemple 3-86.** Exemple extrait du PDF\, page 180\. ```text cd postgresql ``` (Au besoin git tag liste les versions disponibles.) Compilation et installation L’installation des binaires compilés se fera dans /opt/pgsql/18/ . - Configurer en conséquence l’environnement de compilation ( ./configure ). - Compiler PostgreSQL. Ne pas oublier les contribs. Configuration : **Exemple 3-87.** Exemple extrait du PDF\, page 180\. ```text ./configure --prefix /opt/pgsql/18 ``` checking build system type... x86_64-pc-linux-gnu checking host system type... x86_64-pc-linux-gnu checking which template to use... linux checking whether NLS is wanted... no checking for default port number... 5432 … … config.status: linking src/include/port/linux.h to src/include/pg_config_os.h config.status: linking src/makefiles/Makefile.linux to src/Makefile.port Vous pourriez également avoir besoin des paquets libicu et libicu-devel . Des fichiers sont générés, notamment le Makefile. La compilation se lance de manière classique. Elle peut prendre un certain temps sur les machines un peu anciennes : **Exemple 3-88.** Exemple extrait du PDF\, page 180\. ```text make # make -j3 selon le nombre de cœurs make -C ./src/backend generated-headers make[1]: Entering directory '/opt/pgsql/postgresql-18.1/src/backend' make -C ../include/catalog generated-headers make[2]: Entering directory '/opt/pgsql/postgresql-18.1/src/include/catalog' ``` … **Exemple 3-89.** Exemple extrait du PDF\, page 181\. ```text make[1]: Leaving directory '/opt/pgsql/postgresql-18.1/src' make -C config all make[1]: Entering directory '/opt/pgsql/postgresql-18.1/config' make[1]: Nothing to be done for 'all'. make[1]: Leaving directory '/opt/pgsql/postgresql-18.1/config' cd contrib/ make make -C ../src/backend generated-headers make[1]: Entering directory '/opt/pgsql/postgresql-18.1/src/backend' make -C ../include/catalog generated-headers make[2]: Entering directory '/opt/pgsql/postgresql-18.1/src/include/catalog' ``` … **Exemple 3-90.** Exemple extrait du PDF\, page 181\. ```text make[2]: Entering directory '/opt/pgsql/postgresql-18.1/src/backend/nodes' make[2]: Nothing to be done for 'generated-header-symlinks'. make[2]: Leaving directory '/opt/pgsql/postgresql-18.1/src/backend/nodes' ``` Installer les fichiers obtenus. L’installation peut se faire en tant que srcpostgres (et non root) car nous avons défini comme cible le répertoire /opt/pgsql/18/ qui lui appartient : **Exemple 3-91.** Exemple extrait du PDF\, page 181\. ```text cd ~srcpostgres/postgresql-18.1 make install cd contrib make install ``` Dans ce TP, nous nous sommes attachés à changer le moins possible d’utilisateur système. Il se peut que vous ayez à installer les fichiers obtenus en tant qu’utilisateur root dans d’autres environnements en fonction de la politique de sécurité adoptée. Où se trouvent les binaires installés de PostgreSQL? Les binaires installés sont situés dans le répertoire /opt/pgsql/18/bin . **Exemple 3-92.** Exemple extrait du PDF\, page 181\. ```text ls -1 /opt/pgsql/18/bin ``` clusterdb **Exemple 3-93.** Exemple extrait du PDF\, page 181\. ```text createdb createuser ``` … pg_verifybackup pg_waldump pg_walsummary **Exemple 3-94.** Exemple extrait du PDF\, page 181\. ```text pgbench ``` postgres **Exemple 3-95.** Exemple extrait du PDF\, page 181\. ```text psql ``` reindexdb vacuumdb vacuumlo Configurer le système Ajouter les variables d’environnement PATH et LD_LIBRARY_PATH au ~srcpostgres/.bash_profile de l’utilisateur srcpostgres pour accéder facilement à ces binaires. Ajouter les lignes suivantes à la fin du fichier ~srcpostgres/.bash_profile (ce fichier peut ne pas exister préalablement, et un autre fichier peut être nécessaire selon l’environnement utilisé) : **Exemple 3-96.** Exemple extrait du PDF\, page 182\. ```console export PGDATA=/opt/pgsql/18/data export PATH=/opt/pgsql/18/bin:$PATH export LD_LIBRARY_PATH=/opt/pgsql/18/lib:$LD_LIBRARY_PATH ``` Il faut ensuite recharger le fichier à l’aide de la commande suivante (ne pas oublier le point et l’espace au début de la commande); ou se déconnecter et se reconnecter. . ~srcpostgres/.bash_profile Vérifier que les chemins sont bons : **Exemple 3-97.** Exemple extrait du PDF\, page 182\. ```text which psql ``` ~/18/bin/psql Création d’une instance Avec initdb , initialiser une instance dans /opt/pgsql/18/data en spécifiant postgres comme nom de super-utilisateur. Les sommes de contrôle sont activées par défaut. **Exemple 3-98.** Exemple extrait du PDF\, page 182\. ```console $ initdb -D $PGDATA -U postgres ``` The files belonging to this database system will be owned by user "srcpostgres". This user must also own the server process. The database cluster will be initialized with this locale configuration: locale provider: libc LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 LC_MESSAGES: en_US.UTF-8 LC_MONETARY: en_US.UTF-8 LC_NUMERIC: en_US.UTF-8 LC_TIME: en_US.UTF-8 The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are enabled. creating directory /opt/pgsql/18/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok **Exemple 3-99.** Exemple extrait du PDF\, page 183\. ```text initdb: warning: enabling "trust" authentication for local connections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. ``` Success. You can now start the database server using: **Exemple 3-100.** Exemple extrait du PDF\, page 183\. ```console pg_ctl -D /opt/pgsql/18/data -l logfile start ``` Démarrer l’instance. Attention : s’il y a déjà une autre instance sur cette machine et qu’elle est démarrée, le port 5432 est occupé et votre nouvelle instance ne pourra pas fonctionner. Arrêter l’autre instance ou modifier le port 5432 d’une des instances. **Exemple 3-101.** Exemple extrait du PDF\, page 183\. ```console pg_ctl -D $PGDATA -l $PGDATA/server.log start ``` waiting for server to start.... done server started **Exemple 3-102.** Exemple extrait du PDF\, page 183\. ```text cat $PGDATA/server.log ``` 2026-01-28 10:40:01.430 UTC [24790] LOG: starting PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-11), 64-bit 2026-01-28 10:40:01.434 UTC [24790] LOG: could not bind IPv6 address "::1": Address already in use 2026-01-28 10:40:01.434 UTC [24790] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2026-01-28 10:40:01.434 UTC [24790] LOG: could not bind IPv4 address "127.0.0.1": Address already in use 2026-01-28 10:40:01.434 UTC [24790] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2026-01-28 10:40:01.434 UTC [24790] WARNING: could not create listen socket for "localhost" 2026-01-28 10:40:01.434 UTC [24790] FATAL: could not create any TCP/IP sockets 2026-01-28 10:40:01.435 UTC [24790] LOG: database system is shut down 2026-01-28 10:40:34.072 UTC [24844] LOG: starting PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-11), 64-bit 2026-01-28 10:40:34.073 UTC [24844] LOG: listening on IPv6 address "::1", port 5432 2026-01-28 10:40:34.073 UTC [24844] LOG: listening on IPv4 address "127.0.0.1", port 5432 2026-01-28 10:40:34.074 UTC [24844] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2026-01-28 10:40:34.076 UTC [24850] LOG: database system was shut down at 2026-01-28 10:39:42 UTC 2026-01-28 10:40:34.078 UTC [24844] LOG: database system is ready to accept connections - Tenter une première connexion avec psql . - Pourquoi cela échoue-t-il? **Exemple 3-103.** Exemple extrait du PDF\, page 183\. ```text psql ``` **Exemple 3-104.** Exemple extrait du PDF\, page 184\. ```text psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "srcpostgres" does not exist ``` Par défaut, psql demande à se connecter avec un nom d’utilisateur (au sens PostgreSQL) identique à l’utilisateur système en cours, mais la base de données ne connaît pas d’utilisateur srcpostgres. Par défaut, elle ne connaît que postgres. Se connecter en tant qu’utilisateur postgres. Ressortir. **Exemple 3-105.** Exemple extrait du PDF\, page 184\. ```text psql -U postgres psql (18.1) ``` Type "help" for help. **Exemple 3-106.** Exemple extrait du PDF\, page 184\. ```console postgres=# exit ``` Noter que la connexion fonctionne parce que le pg_hba.conf livré avec les sources est par défaut très laxiste (méthode trust en local et via localhost !). (Il y a d’ailleurs eu un avertissement lors de la création de la base.) Dans .bash_profile , configurer la variable d’environnement PGUSER pour se connecter toujours en tant que postgres. Retester la connextion directe avec psql . Ajouter ceci à la fin du fichier ~srcpostgres/.bash_profile : **Exemple 3-107.** Exemple extrait du PDF\, page 184\. ```console export PGUSER=postgres ``` Et recharger le fichier à l’aide de la commande suivante (ne pas oublier le point et l’espace au début de la commande) : . ~/.bash_profile La connexion doit fonctionner sur le champ : **Exemple 3-108.** Exemple extrait du PDF\, page 184\. ```text psql psql (18.1) ``` Type "help" for help. **Exemple 3-109.** Exemple extrait du PDF\, page 184\. ```console postgres=# \conninfo Connection Information ``` **Tableau 3-9.** Tableau ou résultat de commande extrait du PDF\, page 184\. ```text Parameter | Value ----------------------+---------- Database | postgres Client User | postgres Socket Directory | /tmp Server Port | 5432 Options | Protocol Version | 3.0 Password Used | false GSSAPI Authenticated | false Backend PID | 24860 SSL Connection | false ``` **Tableau 3-10.** Tableau ou résultat de commande extrait du PDF\, page 185\. ```text Superuser | on Hot Standby | off ``` (12 rows) Se déconnecté de l’instance : **Exemple 3-110.** Exemple extrait du PDF\, page 185\. ```console postgres=# \q ``` Première base Créer une première base de donnée nommée test . En ligne de commande shell : **Exemple 3-111.** Exemple extrait du PDF\, page 185\. ```sql createdb --echo test SELECT pg_catalog.set_config('search_path', '', false); CREATE DATABASE test; ``` Alternativement, depuis psql : **Exemple 3-112.** Exemple extrait du PDF\, page 185\. ```console postgres=# CREATE DATABASE test ; ``` CREATE DATABASE Se connecter à la base test et créer quelques tables. **Exemple 3-113.** Exemple extrait du PDF\, page 185\. ```sql psql -d test test=# CREATE TABLE premieretable (x int) ; CREATE TABLE ``` Arrêt Arrêter cette instance. **Exemple 3-114.** Exemple extrait du PDF\, page 185\. ```console $ pg_ctl stop ``` waiting for server to shut down.... done server stopped tail $PGDATA/server.log 2026-01-28 10:43:39.576 UTC [24844] LOG: received fast shutdown request 2026-01-28 10:43:39.578 UTC [24844] LOG: aborting any active transactions 2026-01-28 10:43:39.579 UTC [24844] LOG: background worker "logical replication launcher" (PID 24853) exited with exit code 1 2026-01-28 10:43:39.579 UTC [24848] LOG: shutting down 2026-01-28 10:43:39.580 UTC [24848] LOG: checkpoint starting: shutdown immediate 2026-01-28 10:43:39.602 UTC [24848] LOG: checkpoint complete: wrote 991 buffers (6.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; **Exemple 3-115.** Exemple extrait du PDF\, page 185\. ```text write=0.011 s, sync=0.010 s, total=0.023 s; sync files=313, longest=0.001 s, average=0.001 s; distance=4700 kB, estimate=4700 kB; lsn=0/1BF02F8, redo lsn=0/1BF02F8 ``` 2026-01-28 10:43:39.608 UTC [24844] LOG: database system is shut down ## 3.14 INSTALLATION DE POSTGRESQL DEPUIS LES PAQUETS COMMUNAUTAIRES L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comme Oracle Linux et Fedora), et Debian/Ubuntu. Elle ne dure que quelques minutes. ### 3.14.1 Sur Rocky Linux 8, 9 ou 10 ATTENTION : Red Hat, CentOS, Rocky Linux fournissent souvent par défaut des versions de PostgreSQL qui ne sont plus supportées. Ne jamais installer les packages postgresql , postgresql-client et postgresql-server ! L’utilisation des dépôts du PGDG est fortement conseillée. Installation du dépôt communautaire : Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/. Il n’y a guère que les informations de version qui diffèrent en fonction de : - la version majeure de PostgreSQL (ici la 18) ; - la distribution (ici Rocky Linux 9) ; - l’architecture (ici x86_64, la plus courante). Les commandes sont à lancer sous root : **Exemple 3-116.** Exemple extrait du PDF\, page 186\. ```text # dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm # dnf -qy module disable postgresql ``` Installation de PostgreSQL 18 (client, serveur, librairies, extensions) : **Exemple 3-117.** Exemple extrait du PDF\, page 186\. ```text # dnf install -y postgresql18-server postgresql18-contrib ``` Les outils clients et les librairies nécessaires seront automatiquement installés. Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé. **Exemple 3-118.** Exemple extrait du PDF\, page 186\. ```text # dnf install postgresql18-llvmjit ``` Création d’une première instance : C’est optionnel, mais il est conseillé de déclarer PGSETUP_INITDB_OPTIONS , notamment pour forcer les traces en anglais. Depuis la version 18, les sommes de contrôle sont automatiquement activées. **Exemple 3-119.** Exemple extrait du PDF\, page 187\. ```console # export PGSETUP_INITDB_OPTIONS='--lc-messages=C' # /usr/pgsql-18/bin/postgresql-18-setup initdb # cat /var/lib/pgsql/18/initdb.log ``` Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par : Success. You can now start the database server using: **Exemple 3-120.** Exemple extrait du PDF\, page 187\. ```console /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data/ -l logfile start ``` Chemins : Objet Chemin Binaires /usr/pgsql-18/bin Répertoire de l’utilisateur postgres /var/lib/pgsql PGDATA par défaut /var/lib/pgsql/18/data Fichiers de configuration dans PGDATA/ Traces dans PGDATA/log Configuration : Modifier postgresql.conf est facultatif pour un premier lancement. Commandes d’administration habituelles : Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage : **Tableau 3-11.** Tableau ou résultat de commande extrait du PDF\, page 187\. ```text # systemctl --all |grep postgres ``` **Exemple 3-121.** Exemple extrait du PDF\, page 187\. ```console # systemctl start postgresql-18 # systemctl stop postgresql-18 # systemctl status postgresql-18 # systemctl reload postgresql-18 # systemctl restart postgresql-18 Test rapide de bon fonctionnement et connexion à psql : # sudo -iu postgres psql ``` Démarrage de l’instance au lancement du système d’exploitation : **Exemple 3-122.** Exemple extrait du PDF\, page 187\. ```console # systemctl enable postgresql-18 ``` Ouverture du firewall pour le port 5432 : Voir si le firewall est actif : **Exemple 3-123.** Exemple extrait du PDF\, page 187\. ```console # systemctl status firewalld ``` Si c’est le cas, autoriser un accès extérieur : **Exemple 3-124.** Exemple extrait du PDF\, page 187\. ```text # firewall-cmd --zone=public --add-port=5432/tcp --permanent # firewall-cmd --reload # firewall-cmd --list-all ``` (Rappelons que listen_addresses doit être également modifié dans postgresql.conf .) Création d’autres instances : Si des instances de versions majeures différentes doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans dnf install … ) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf pour que les instances puissent tourner simultanément. Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des PGDATA différents. - Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd). - Respecter les normes et conventions de l’OS : placer les instances dans un nouveau sousrépertoire de /var/lib/pgsqsl/18/ (ou l’équivalent pour d’autres versions majeures). Pour créer une seconde instance, nommée par exemple infocentre : - Création du fichier service de la deuxième instance : **Exemple 3-125.** Exemple extrait du PDF\, page 188\. ```text # cp /lib/systemd/system/postgresql-18.service \ /etc/systemd/system/postgresql-18-infocentre.service ``` - Modification de ce dernier fichier avec le nouveau chemin : **Exemple 3-126.** Exemple extrait du PDF\, page 188\. ```text Environment=PGDATA=/var/lib/pgsql/18/infocentre ``` - Option 1 : création d’une nouvelle instance vierge : **Exemple 3-127.** Exemple extrait du PDF\, page 188\. ```console # export PGSETUP_INITDB_OPTIONS='--lc-messages=C' # /usr/pgsql-18/bin/postgresql-18-setup initdb postgresql-18-infocentre ``` - Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil. - Adaptation de /var/lib/pgsql/18/infocentre/postgresql.conf (port surtout). - Commandes de maintenance de cette instance : **Tableau 3-12.** Tableau ou résultat de commande extrait du PDF\, page 188\. ```text # systemctl [start|stop|reload|status] postgresql-18-infocentre # systemctl [enable|disable] postgresql-18-infocentre ``` - Ouvrir le nouveau port dans le firewall au besoin. ### 3.14.2 Sur Debian / Ubuntu L’installation ne varie quasiment pas selon les versions de Debian et Ubuntu. Ce qui suit a été testé sur Debian 10 à 13 et Ubuntu LTS 22.04 et 24.04. Sauf précision, tout est à effectuer en tant qu’utilisateur root. Référence : https://apt.postgresql.org/ Installation du dépôt communautaire : L’installation des dépôts du PGDG est prévue dans le paquet Debian postgresql-common : **Exemple 3-128.** Exemple extrait du PDF\, page 189\. ```console # apt update # apt install -y gnupg2 postgresql-common # /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh ``` Ce dernier ordre créera le fichier du dépôt /etc/apt/sources.list.d/pgdg.list adapté à la distribution en place. Installation de PostgreSQL 18 : La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation : Dans /etc/postgresql-common/createcluster.conf , paramétrer au moins les traces en anglais. Depuis la version 18, les sommes de contrôle sont automatiquement activées. **Exemple 3-129.** Exemple extrait du PDF\, page 189\. ```text initdb_options = '--lc-messages=C' ``` Puis installer les paquets serveur et clients et leurs dépendances : **Exemple 3-130.** Exemple extrait du PDF\, page 189\. ```text # apt install postgresql-18 postgresql-client-18 ``` La première instance est automatiquement créée, avec un nom ( main par défaut‘), déclarée comme **Exemple 3-131.** Exemple extrait du PDF\, page 189\. ```console service à lancer au démarrage du système, puis démarrée. $ pg_lsclusters ``` Ver Cluster Port Status Owner Data directory Log file **Exemple 3-132.** Exemple extrait du PDF\, page 189\. ```console 18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log $ systemctl status postgresql@18-main ``` ● postgresql@18-main.service - PostgreSQL Cluster 18-main Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled) Active: active (running) since Fri 2026-01-30 15:18:43 UTC; 2min 13s ago … L’utilisateur système postgres peut y accéder immédiatement. Chemins : Objet Chemin Binaires /usr/lib/postgresql/18/bin/ Objet Chemin Répertoire de l’utilisateur postgres /var/lib/postgresql PGDATA de l’instance par défaut /var/lib/postgresql/18/main Fichiers de configuration dans /etc/postgresql/18/main/ Traces dans /var/log/postgresql/ Configuration Modifier postgresql.conf est facultatif pour un premier essai. Démarrage/arrêt de l’instance, rechargement de configuration : Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance : **Tableau 3-13.** Tableau ou résultat de commande extrait du PDF\, page 190\. ```text # pg_ctlcluster 18 main [start|stop|reload|status|restart] ``` Démarrage de l’instance avec le serveur : C’est en place par défaut, et modifiable dans /etc/postgresql/18/main/start.conf . Ouverture du firewall : Debian et Ubuntu n’installent pas de firewall par défaut. Statut des instances du serveur : **Exemple 3-133.** Exemple extrait du PDF\, page 190\. ```text # pg_lsclusters ``` Ver Cluster Port Status Owner Data directory Log file **Tableau 3-14.** Tableau ou résultat de commande extrait du PDF\, page 190\. ```text # systemctl --all |grep postgres ``` **Exemple 3-134.** Exemple extrait du PDF\, page 190\. ```console 18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log Test rapide de bon fonctionnement et connexion à psql : # sudo -iu postgres psql ``` Destruction d’une instance : **Exemple 3-135.** Exemple extrait du PDF\, page 190\. ```text # pg_dropcluster 18 main ``` Création d’autres instances : Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour activer les sommes de contrôles (checksums) avant la version v18 : - optionnellement, /etc/postgresql-common/createcluster.conf permet de mettre en place tout d’entrée les messages en anglais, le format des traces, l’emplacement séparé pour les journaux ou les checksums pour une version inférieure à 18 : **Exemple 3-136.** Exemple extrait du PDF\, page 190\. ```text initdb_options = '--data-checksums --lc-messages=C' log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal' ``` - créer une instance : **Exemple 3-137.** Exemple extrait du PDF\, page 191\. ```text # pg_createcluster 18 infocentre ``` Il est également possible de préciser certains paramètres du fichier postgresql.conf , voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut) : **Exemple 3-138.** Exemple extrait du PDF\, page 191\. ```text # pg_createcluster 18 infocentre \ --port=12345 \ --datadir=/PGDATA/18/infocentre \ --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \ -- --data-checksums --waldir=/ssd/postgresql/18/infocentre/journaux ``` - adapter au besoin /etc/postgresql/18/infocentre/postgresql.conf ; - démarrage : **Exemple 3-139.** Exemple extrait du PDF\, page 191\. ```console # pg_ctlcluster 18 infocentre start ``` ### 3.14.3 Accès à l’instance depuis le serveur même (toutes distributions) Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire : **Exemple 3-140.** Exemple extrait du PDF\, page 192\. ```console $ sudo -iu postgres psql psql (18.1) ``` Type "help" for help. **Exemple 3-141.** Exemple extrait du PDF\, page 192\. ```console postgres=# ``` Ce qui suit permet la connexion directement depuis un utilisateur du système : Pour des tests (pas en production!), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf : local all postgres trust La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée : dalibo:~$ psql -U postgres **Exemple 3-142.** Exemple extrait du PDF\, page 192\. ```text psql (18.1) ``` Type "help" for help. **Exemple 3-143.** Exemple extrait du PDF\, page 192\. ```console postgres=# ``` Une authentification par mot de passe est plus sécurisée : - dans pg_hba.conf , paramétrer une authentification par mot de passe pour les accès depuis localhost (déjà en place sous Debian) : **Exemple 3-144.** Exemple extrait du PDF\, page 192\. ```text # IPv4 local connections: ``` host all all 127.0.0.1/32 scram-sha-256 **Exemple 3-145.** Exemple extrait du PDF\, page 192\. ```text # IPv6 local connections: ``` host all all ::1/128 scram-sha-256 (Ne pas oublier de recharger la configuration en cas de modification.) - ajouter un mot de passe à l’utilisateur postgres de l’instance : dalibo:~$ sudo -iu postgres psql **Exemple 3-146.** Exemple extrait du PDF\, page 192\. ```text psql (18.1) ``` Type "help" for help. **Exemple 3-147.** Exemple extrait du PDF\, page 192\. ```console postgres=# \password ``` Enter new password for user "postgres": Enter it again: **Exemple 3-148.** Exemple extrait du PDF\, page 192\. ```console postgres=# quit ``` dalibo:~$ psql -h localhost -U postgres Password for user postgres: **Exemple 3-149.** Exemple extrait du PDF\, page 192\. ```text psql (18.1) ``` Type "help" for help. **Exemple 3-150.** Exemple extrait du PDF\, page 192\. ```console postgres=# ``` - Pour se connecter sans taper le mot de passe à une instance, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion : localhost:5432:*:postgres:motdepassetrèslong Ce fichier doit être protégé des autres utilisateurs : **Exemple 3-151.** Exemple extrait du PDF\, page 193\. ```console $ chmod 600 ~/.pgpass ``` - Pour n’avoir à taper que psql , on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc : **Exemple 3-152.** Exemple extrait du PDF\, page 193\. ```console export PGUSER=postgres export PGDATABASE=postgres export PGHOST=localhost ``` Rappels : - en cas de problème, consulter les traces (dans /var/lib/pgsql/18/data/log ou /var/log/postgresql/ ); - toute modification de pg_hba.conf ou postgresql.conf impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système : root:~# systemctl reload postgresql-18 root:~# pg_ctlcluster 18 main reload postgres:~$ psql -c 'SELECT pg_reload_conf()' --- Source : [manuel complet PDF](/downloads/dalibo/manuals/dba1-handout.pdf), révision 26.09, pages PDF 107-194.