PostgreSQL – Tout comprendre – 2/3 – Installation et paramétrages

Installation de PostgreSQL

Installation

Lors de l’installation de PostgreSQL, plusieurs éléments sont fournis :

• PostgreSQL : le serveur de base de données (le SGBD)
• PSQL : c’est un logiciel client spécifique à PostgreSQL qui permet de communiquer en ligne de commande avec le serveur (je vous vois transpirer mais ne vous inquiétez pas).
• pgAdmin : c’est également un logiciel client spécifique à PostgreSQL. Il apporte une interface graphique pour une utilisation simplifiée.
• Des exécutables permettant d’effectuer des traitements sur des données de la base.

Pour installer PostgreSQL, rendez-vous à cette adresse et téléchargez la dernière version (9.6 à la rédaction de cet article, 13 lors de la mise à jour de l’article) :

https://www.postgresql.org/download/

Sachez que la gestion des versions de PostgreSQL fonctionne de la façon suivante :

• Version majeure de PostgreSQL (impliquant de grands changements dans la structuration de la base de donnée et donc de plus ou moins gros traitements lors du passage à une nouvelle version majeure).
• Version mineure : apportant quelques améliorations sur les versions mineures précédentes mais sans modification de la structuration de la base de données.

Suivez les instructions d’installation (en conservant les paramétrages de base qui seront parfait pour notre utilisation).
A la fin de l’installation, l’utilitaire vous proposera d’utiliser « Stack Builder » pour installer des compléments, acceptez et choisissez d’installer Postgis, nous en aurons besoin pour la gestion des données géographiques.

Pensez à retenir les paramétrages indiqués lors de l’installation notamment le n° du port (5432 par défaut) et le mot de passe du superutilisateur « postgres » (vous pouvez définir le mot de passe suivant : « postgres » pour plus de simplicité, nous verrons comment le changer plus tard).

Maintenant que PostgreSQL est installé, regardons de plus près l’installation qui se trouve dans : C:\Program Files\PostgreSQL\X.X (avec X.X le numéro de version).

Répertoires et éléments

Voici les différents éléments de votre serveur :

• /bin : contient de nombreux outils nécessaires à l’exécution de PostgreSQL. On y trouve également des exécutables permettant de faire certains traitement particulier sur nos données (par exemple : pg_dump.exe permettant de « dumper » c’est à dire de sauvegarder la base).
• /lib : contient les librairies de PostgreSQL, se sont des bibliothèques de fonctions utiles.
• /data : c’est le cœur de votre serveur :
  • /base : contient vos données
  • /pg_log : ce sont les logs. Référez-vous à ces fichiers lorsque votre serveur ne réagit pas correctement.
  • /pg_hba.conf : fichier de gestion des autorisations d’authentification à votre serveur.
  • /postgresql.conf : fichier de configuration de votre serveur.

Sachez qu’il est possible de stocker vos données ailleurs que dans le répertoire de PostgreSQL, sur un disque dur annexe par exemple. C’est simplement l’emplacement du dossier /data qui sera modifié. Nous verrons comment le faire en fin d’article.

pg_hba.conf

L’accès aux bases de données PostgreSQL se fait avec la notion de rôles et de rôles groupes. Il existe cependant un niveau de sécurité supplémentaire : le fichier pg_hba.conf.

Ce fichier contient la liste des adresses IP autorisées à contacter l’instance PostgreSQL.

Voici comment quelques éléments pour le gérer correctement.

• Chaque ligne commençant par un # est une ligne de commentaire, PostgreSQL ne la prendra donc pas en compte.
• La configuration se fait de la façon suivante : on rajoute une ligne pour autoriser une (ou plusieurs) machine(s) cliente(s) puis on relance la base PostgreSQL (ou on recharge la configuration).
• La configuration par défaut n’autorise que la machine sur laquelle est installée PostgreSQL à contacter le serveur, toutes les autres sont interdites d’accès.

Voici le contenu d’une ligne :

# TYPE      DATABASE      USER         ADDRESS            METHOD
host        ma_base       mon_user     127.0.0.1/32       md5

Cette ligne est composée des éléments suivants :

• host : méthode de contact de la base de données
• ma_base : base pouvant être contactée par la machine (all pour toutes les bases)
• mon_user : utilisateur autorisé à être utilisé par la machine (all pour n’importe quel utilisateur)
• 127.0.0.1/32 : adresse IP et masque de la machine cliente (qui va contacter PostgreSQL)
• md5 : méthode d’authentification à utiliser

Certains de ces éléments méritent que l’on s’y attarde un peu.

TYPE

Voici les différents types de contact :

• local : connexion qui utilise les sockets du domaine Unix (connexion en local).
• host : connexion par TCP/IP (la plupart des connexion utilise ce type).
• hostssl : connexions par TCP/IP qui utilisent le chiffrement SSL.
• hostnossl : connexion par TCP/IP qui n’utilisent pas SSL.
• hostgssenc : connexion par TCP/IP qui utilisent le chiffrement GSSAPI.
• hostnogssenc : connexion par TCP/IP qui n’utilisent pas GSSAPI.

Attention, si vous spécifiez des connexions host, vous devez vérifier dans la configuration de PostgreSQL (voir partie suivante) que le serveur écoute les connexions TCP/IP distantes.

DATABASE

Vous pouvez préciser ici la base ou les bases qui peut/peuvent être contactée(s) :

• ma_base : Accès à une seule base.
• ma_base,ma_base2 : Accès à deux bases.
• all : Accès à toutes les bases.

USER

Vous pouvez préciser ici l’utilisateur ou les utilisateurs qui peuvent être utilisée(s) :

• mon_user : Une seul user peut se connecter.
• mon_user1,mon_user2 : Deux users peuvent se connecter.
• all : Tous les users peuvent se connecter.

ADDRESS

L’adresse peut être une adresse IP accompagnée de son masque ou un nom de domaine.

L’adresse IP est toujours accompagnée de son masque (valeur qui suit le slash). Le masque représente le nombre de bits de l’adresse IP de la machine cliente devant correspondre à celle mentionnée pour autoriser l’accès. Vous pouvez retenir ceci :

• 148.3.12.21/32 : l’adresse doit correspondre entièrement : 148.3.12.21
• 148.3.12.0/24 : Seuls les trois premiers octets doivent correspondre : 148.3.12.xxx
• 148.3.0.0/16 : Seuls les deux premiers octets doivent correspondre : 148.3.xxx.xxx
• 148.0.0.0/8 : Seul le premier octet doit correspondre : 148.xxx.xxx.xxx
• 0.0.0.0/0 : Aucun octet n’est obligé de correspondre, tout est autorisé : xxx.xxx.xxx.xxx

Ses éléments sont valables pour les IPv6, le masque devant simplement être adapté.

Vous pouvez également écrire le masque en entier sous la forme suivante :

• 148.3.12.21         255.255.255.255         pour une correspondance totale
• 148.3.12.0           255.255.255.0              pour les trois premiers octets.
• 148.3.0.0             255.255.0.0                  pour les deux premiers octets.
• 148.0.0.0             255.0.0.0                       pour le premier octet.
• 0.0.0.0                  0.0.0.0                            pour aucun octet.

Les noms de domaines peuvent être complets ou tronqués :

• www.arthurbazin.com : ciblera www.arthurbazin.com
• .arthurbazin.com : ciblera tous les sous domaine de arthurbazin.com
• arthurbazin.com : ciblera uniquement arthurbazin.com

Là encore vous pouvez autoriser une ou plusieurs adresses en les séparant par une virgule.

METHOD

Voici différentes méthodes d’authentification (il en existe d’autres listées dans la documentation officielle) :

• trust : toutes les connexions sont autorisées, il n’y a pas besoin de mot de passe.
• reject : aucune connexion n’est acceptée, tout est rejeté.
• password : le client doit fournir un mot de passe non chiffré (donc en clair sur le réseau).
• scram-sha-256 : le client doit fournir un mot de passe chiffré avec la méthode SCRAM-SHA-256.
• md5 : le client doit fournir un mot de passe doublement chiffré avec la méthode MD5 ou SCRAM-SHA-256.
• ident : le serveur récupère le nom de l’utilisateur en contactant le serveur d’identification sur le poste client. Uniquement pour les connexions TCP/IP.
• peer : idem que ident mais pour les connexions locale (effectuée depuis la machine du serveur).
• ldap : utilisation d’un serveur LDAP.
• cert : utilisation d’un certificat client SSL.
• pam : utilisation du module d’authentification PAM fournit par le système.

postgresql.conf

Ce fichier contient la configuration du serveur. Certains paramètres sont définis par défaut et peuvent ainsi être modifiés grâce à ce fichier. Vous trouverez ainsi une liste de paramètres se présentant sous la forme suivante : nom_du_paramètre = valeur.

Tout comme dans le fichier pg_hba.conf, les commentaires commencent par un #. Ainsi, certains paramètres sont présents mais non actif car commentés.

Pour vous aider à optimiser la configuration, je vous invite à vous rendre sur le site PGTune qui vous calcule la configuration optimale selon l’environnement dans lequel s’exécutera PostgreSQL.

En parallèle, voici les principaux paramètres et certaines valeurs recommandées.

listen_adresses

Définit les adresses IP à écouter. Vous pouvez ainsi définir les valeurs suivantes :

• * : toutes les IP.
• 148.3.12.21 : une seule IP.
• 148.3.12.21,148.3.12.22 : plusieurs IP.

Recommandé : tout dépend de votre besoin. Pour un maximum de sécurité, ne renseignez que les adresses IP devant contacter le serveur. Sinon vous pouvez autoriser toutes les adresses et gérer la liste dans le fichier pg_hba.conf.

port

Définit le port sur lequel PostgreSQL peut être contacté. Vous pouvez indiquer n’importe quel port disponible sur le serveur. Changer le port par défaut apporte un léger gain de sécurité car il faut connaitre le port pour se connecter au serveur.

Défaut : port = 5432
Recommandé : un port différent comme 5433 ou 5434 ou un autre.

max_connections

Définit le nombre connexions simultanées à la base de données. Attention, un client peut utiliser plusieurs connexions simultanées pour accélérer le temps de réponse du serveur.

Défaut : max_connections = 100
Recommandé : max_connections = 100

superuser_reserved_connections

Définit le nombre de connexions simultanées réservées pour le (ou les) super utilisateur(s).

Défaut : superuser_reserved_connections = 3
Recommandé : superuser_reserved_connections = 3

shared_buffers

Définit la quantité de mémoire vive allouée à PostgreSQL. N’hésitez pas à augmenter ce paramètre. Comptez environ 75% de la mémoire vive disponible (mémoire vive disponible = mémoire vive total – mémoire vive utilisée par le système – mémoire vive utilisée par les différents logiciels).

Défaut : shared_buffers = 128MB
Recommandé : shared_buffers = dépend de votre machine

work_mem

Définit la quantité de mémoire vive allouée aux opérations de tri internes (order by, distinct…). Vous pouvez l’augmenter légèrement pour améliorer les performances.

Défaut : work_mem = 4MB
Recommandé : work_mem = 16MB

maintenance_work_mem

Définit la quantité de mémoire vive allouée aux opérations de maintenance (VACUUM, CREATE INDEX…). Cette valeur peut être largement augmentée car seule une de ces opérations peut être exécutée à la fois dans une session. Attention, les autovacuum utiliseront également cet quantité d’espace et ce, autant de fois que de processus d’autovacuum peuvent être lancés en même temps (voire paramètre autovacuum_max_workers).

Défaut : maintenance_work_mem = 64MB
Recommandé : maintenance_work_mem = 512MB

autovacuum_work_mem

Définit la quantité de mémoire vive allouée pour chaque processus d’autovacuum. Ceci permet d’avoir une valeur de maintenance_work_mem plus importante sans pour autant allouer trop de ressource aux autovacuum.

Défaut : autovacuum_work_mem = -1 (on utilise la valeur de maintenance_work_mem)
Recommandé : autovacuum_work_mem = 128MB

wal_level

Définit la quantité d’information écrite dans les journaux de transaction (wal). Ces journaux permettent de récupérer les données lors d’un crash du serveur PostgreSQL. Plusieurs niveaux existent :

• Minimal : ne conserve que les informations nécessaires pour survivre à un arrêt brutal ou immédiat du serveur.
• Replica : minimal + quelques enregistrements pour permettre l’archivage des journaux de transactions et l’exécution des requêtes en attente.
• Logical : replica + des informations pour extraire les modifications logiques.
• Archive = replica
• Hot_standby = replica

Défaut : wal_level = minimal
Recommandé : wal_level = replica

wal_buffers

Définit la quantité de mémoire vive utilisée pour les données des journaux de transactions qui n’ont pas été écrites sur le disque.

Défaut : wal_buffers = -1 (= 1/32^ème de shared_buffers)
Recommandé : wal_buffers = au moins 2 MB (si 1/32^ème de shared_buffer < 2MB, sinon -1)

log_destination

Destination des fichiers log (format de sortie en quelque sorte). Vous pouvez choisir parmi :

• stderr : sortie par défaut (créé un fichier .log)
• csvlog : sortie au format CSV.
• syslog : sortie dans les journaux système.
• Eventlog : (Windows uniquement) sortie dans les journaux d’évènement Windows.

logging_collector

Active le « collecteur de traces » qui capture toutes les traces envoyées vers stderr et les envoi dans un fichier. En d’autres termes : l’activation de cette option permet d’avoir des fichiers de log.

log_directory

Répertoire d’enregistrement des logs. Vous pouvez soit utiliser un chemin relatif au répertoire PG_DATA soit un chemin absolut (C:\mon\chemin).

Défaut : log_directory = 'pg_log'
Recommandé : L’emplacement qui vous plait mais la valeur par défaut permet de conserver tous les éléments au même endroit.

log_filename

Nom des fichiers de log. Vous pouvez ajouter la date et l’heure via les paramètres suivants :

• %H : heure (au format 24h) – Exemple : 12
• %M : minute – Exemple  : 24
• %S : seconde – Exemple  : 46
• %R : heure : minute – Exemple  : 12 : 24
• %T : heure : minute : seconde – Exemple  : 12 : 24 : 46
• %y : année (4 chiffres) – Exemple : 2016
• %Y : année (2 chiffres) – Exemple  : 16
• %m : mois – Exemple  : 07
• %d : jour – Exemple  : 14
• %F : année – mois – jour – Exemple  : 2016 - 07 - 14

Le nom du fichier est un motif strftime. Vous pourrez trouver d’autres échappements « % » dans ceux listés par la spécification de strftime par l’Open Group.

Si le format de sortie est le CSV alors le format .csv est automatiquement ajouté au nom du journal.

Exemple : log_filename = 'Postgresql - %Y-%m-%d.log'

log_rotation_age

Détermine la durée de vie maximal (en minutes) d’un fichier de log. Passé ce délai, un nouveau journal (fichier) est créé.

La valeur « 0 » (zéro) désactive la limite de durée de vie.

Recommandé : log_rotation_age = 1d

log_rotation_size

Détermine la taille maximal d’un fichier de log. Passé cette taille, un nouveau journal (fichier) est créé.

La valeur « 0 » (zéro) désactive la limite de taille.

Recommandé : log_rotation_size = 3MB

log_truncate_on_rotation

Active l’écrasement des fichiers de log plutôt que la création d’un nouveau fichier. L’écrasement ne se produit que pour des fichiers dont le nom existe déjà et uniquement lorsqu’il s’agit d’une limite de temps (log_rotation_age).

En utilisant le paramètre log_filename = 'mon_log_%H.log' et le paramètre log_truncate_on_rotation = on, il est possible de créer un fichier de log par heure écrasés toutes les 24 h.

client_min_messages

Définit le niveau de verbosité du serveur avec les clients (et donc les utilisateurs). De DEBUG5 : une vraie pipelette à ERROR : extrêmement peu loquace. Ce sont les messages qui seront renvoyés aux utilisateurs dans leur client.

log_min_messages

Définit le niveau de verbosité du log serveur. De DEBUG5 : une vraie pipelette à PANIC : extrêmement peu loquace.

log_min_error_statement

Définit à partir de quel niveau le log doit contenir l’instruction SQL à l’origine de la ligne de log. Utilisez PANIC pour désactiver le traçage des instructions échouées.

log_min_duration_statement

Définit la durée minimale en milliseconde d’une instruction SQL terminée pour qu’elle soit loguée. Ceci permet de tracer les requêtes non optimisées des applications. Utilisez la valeur 0 (zéro) pour tracer toutes les requêtes et -1 pour n’en tracer aucune.

log_connections

Logue chaque tentative de connexion au serveur.

log_disconnections

Logue chaque déconnexion du serveur.

log_duration

Logue la durée de toute instruction exécutée. Le texte des requêtes ne sera pas logué (sauf si log_min_duration_statement est défini et que sa valeur est dépassée).

log_line_prefix

Définit le préfixe utilisé pour chaque ligne de code.

Recommandé : log_line_prefix =  '%t - bdd=%d user=%u app=%a client=%h - '

log_error_verbosity

Définit la quantité de détails écrit pour chaque ligne de log.

Recommandé : log_error_verbosity = verbose

log_statement

Définit les instruction SQL à tracer :

• none : aucune
• ddl : commande de définition : CREATE, ALTER, DROP
• mod : commande de données : INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM
• all : toutes les commandes

Recommandé : log_statement = 'none'

search_path

Spécifie le schéma dans lequel les objets seront recherchés par défaut lorsque aucun préfixe n’est spécifié dans la requête. Par défaut, le serveur recherche d’abord dans le schéma dont le nom est égal au rôle de connexion utilisé puis dans le schéma « public ».

include_dir

Permet d’inclure un répertoire de configuration supplémentaire. Tous les fichiers de ce répertoire qui finissent par « .conf » seront ainsi inclus comme s’ils faisaient parti du fichier principal.

include

Permet d’inclure un fichier de configuration supplémentaire comme s’ils faisait parti du fichier principal.

Placer ses données sur un autre disque

Les données stockées dans PostgreSQL sont situées dans le répertoire /data du répertoire d’installation de PostgreSQL. Si vous souhaitez dissocier l’emplacement du logiciel de celui des données, il faut déplacer ce répertoire.

sudo -u postgres psql
postgres=# SHOW data_directory;

sudo systemctl stop postgresql
sudo systemctl status postgresql

sudo cp -av /var/lib/postgresql/13/main /new/repertoire/13/main

mv /var/lib/postgresql/13/main /var/lib/postgresql/13/main.backup

ln -s /new/repertoire/pgsql/13/main /var/lib/postgresql/13/main

Votre serveur est maintenant près et configuré. Nous pouvons maintenant nous intéresser au contenu et voir comment s’y connecter et sa structuration.

Sommaire général

Voici le sommaire général du cours :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQL tuto