Configurez PostgreSQL

PostgreSQL est conçu pour fonctionner sur un grand nombre de plateformes, y compris celles ayant les performances les plus limitées. Il est donc important de bien le configurer lors de son installation pour que l’instance exploite au mieux les capacités de la machine sur laquelle elle se trouve.

Pour cela, de nombreux paramètres peuvent être modifiés et ce, à différents niveaux. Voici un guide rapide pour s’y retrouver.

Surcharge des paramètres

Lors de la compilation de PostgreSQL (~création du programme) les paramètres de configuration sont initialisés avec une valeur par défaut. Leurs modifications peut se faire à différents niveaux mais respectent toujours l’ordre de surcharge suivant :

1. Valeurs définies lors de la compilation.
2. Paramètres définis lors de l’initialisation d’un répertoire de base de données (avec la commande initdb).
3. Variables définies dans les variables d’environnement (voir la documentation pour plus d’informations).
4. Fichier de configuration postgresql.conf.
5. Commande ALTER SYSTEM SET (fichier de configuration postgresql.auto.conf).
6. Options passées en paramètre de la ligne de commande pg_ctl, utilisées pour démarrer l’instance.
7. Commande ALTER DATABASE xyz SET (paramètre spécifié pour une base).
8. Commande ALTER ROLE abc SET (paramètre spécifié pour un rôle).
9. Commande ALTER ROLE abc IN DATABASE xyz SET (paramètre spécifié pour un rôle pour une base).
10. Options passées dans la chaine de connexion à l’instance par le client.
11. Commande SET (paramètre pour une session).
12. Commande SET LOCAL (paramètre pour une transaction).
13. Options définies pour un bloc de fonction.

Récupérer la valeur d’un paramètre

Avec tous ces points d’entrée, il peut être difficile de savoir quelle est la valeur d’un paramètre. Pour cela, vous pouvez utiliser plusieurs requêtes.

Utiliser SHOW :

-- Visualiser les paramètres du serveur :
SHOW mon_parametre;

-- Visualiser tous les paramètres :
SHOW ALL;

Utiliser la fonction current_setting('mon_parametre') :

-- Récupérer les principaux paramètres du serveur.
SELECT
	current_setting('SERVER_VERSION') as "SERVER_VERSION",
	current_setting('SERVER_ENCODING') as "SERVER_ENCODING",
	current_setting('LC_COLLATE') as "LC_COLLATE",
	current_setting('data_directory') as "data_directory",
	current_setting('config_file') as "config_file",
	current_setting('hba_file') as "hba_file",
	current_setting('listen_addresses') as "listen_addresses",
	current_setting('port') as "port",
	current_setting('max_connections') as "max_connections",
	current_setting('shared_buffers') as "shared_buffers",
	current_setting('work_mem') as "work_mem",
	current_setting('log_destination') as "log_destination",
	current_setting('log_directory') as "log_directory",
	current_setting('log_filename') as "log_filename",
	current_setting('log_rotation_age') as "log_rotation_age",
	current_setting('log_rotation_size') as "log_rotation_size",
	current_setting('log_min_messages') as "log_min_messages",
	current_setting('search_path') as "search_path",
	current_setting('TimeZone') as "TimeZone"
;

Vous pouvez également visualiser la vue pg_settings qui liste l’ensemble des paramètres. Cette vue est très intéressante car elle vous indiquera également l’origine de la définition du paramètre, les valeurs possibles (ou min/max) des différentes options ainsi que l’obligation ou non de redémarrer l’instance ou de se reconnecter pour prendre en compte la nouvelle valeur du paramètre.

SELECT 
  * 
FROM 
  pg_catalog.pg_settings

Les paramètres utilisables sont ceux qui se trouvent dans le fichier de configuration de PostgreSQL (voir plus bas).

Configuration personnalisée

Fichiers de configuration

Il existe plusieurs fichiers de configuration :

• postgresql.conf : il s’agit du fichier principal qui contient tous les paramètres de
  l’instance.
• postgresql.auto.conf : ce fichier est géré par l’instance qui y stocke les paramètres
  de configuration modifiés depuis la commande ALTER SYSTEM.
• pg_hba.conf : ce fichier contient les autorisations d’authentification à la base. Il
  s’agit d’une sorte de pare-feu listant les hôtes autorisés à contacter l’instance et par
  quel moyen. J’ai déjà détaillé son contenu dans cet article.
• pg_ident.conf : ce fichier est en lien avec pg_hba.conf et est utiliser en cas d’utilisation
  d’un mécanisme d’authentification externe à la base (via un annuaire par
  exemple). Il contient la correspondance entre les utilisateurs d’un ou plusieurs annuaires
  avec les utilisateurs de l’instance PostgreSQL.

Le fichier postgresql.conf se trouve dans le répertoire « data » de PostgreSQL (avec les données, les logs et tout ce qui se rapporte aux bases gérées par l’instance) qui peut se trouver à différents endroits selon le système d’exploitation. En général :

• Windows : C:\Program Files\PostgreSQL\X\data\postgresql.conf
• Linux (Red Hat, Cent OS) : /var/lib/pgsql/X/data/postgresql.conf
• Linux (Debian, Ubuntu) : /etc/postgresql/X/main/postgresql.conf

Le plus simple pour le trouver est d’utiliser la requête suivante :

SHOW config_file;

config_file |
------------------------------------------------------+
C:\Program Files\PostgreSQL\13\data\postgresql.conf |

Ce fichier contient un ensemble de paramètres définis de la façon suivante :

#Ligne de commentaire
parametre = valeur

Il est également possible de scinder le fichier en plusieurs parties puis de les inclure dans le fichier principal avec paramètres suivants :

• include = 'mon_fichier.conf' : récupérer les valeurs de mon_fichier.conf.
• include_if_exists = 'mon_fichier' : récupérer les valeurs de mon_fichier.conf sauf si le fichier n’existe pas.
  La directive précédente renvoie une erreur si le fichier n’existe pas, pas celle-ci.
• include_dir = 'mon_repertoire' : récupérer les valeurs de tous les fichiers « .conf » dans le répertoire mon_repertoire.

Commandes de configuration

Plutôt que de modifier le fichier de configuration principal, il est possible d’utiliser des requêtes SQL pour paramétrer l’instance.

Configurations permanentes

Voici la liste des commandes disponibles pour modifier un paramètre de façon permanente.

Pour toute l’instance (dans le fichier postgresql.auto.conf) :

ALTER SYSTEM SET parametre = valeur;

Pour une seule base de données :

ALTER DATABASE xyz SET parametre = valeur;

Pour un seul rôle :

ALTER ROLE abc SET parametre = valeur;

Pour un seul rôle au sein d’une base de données spécifique :

ALTER ROLE abc IN DATABASE xyz SET parametre = valeur;

Configurations temporaires

Il est aussi possible de modifier un paramètre de façon temporaire :

Pour une session (le paramètre sera réinitialisé lors de la prochaine connexion) :

SET parametre = valeur;

Pour une transaction (entre les mots clés BEGIN et END) :

SET LOCAL parametre = valeur;

Pour une fonction :

CREATE OR REPLACE FUNCTION ma_fonction_test()
  RETURNS BOOLEAN
as $$
BEGIN
  SELECT 1;
  RETURN true;
END;
$$
LANGUAGE plpgsql
SET parametre = valeur
;

Paramètres intéressants

Voici quelques liens que vous pouvez suivre pour avoir plus d’informations sur les paramètres :

• La documentation de PostgreSQL : https://www.postgresql.org/docs/current/runtime-config.html
• Guide de Dalibo sur le paramétrage de PostgreSQL : https://public.dalibo.com/exports/formation/manuels/modules/m2/m2.handout.html
• Tous les paramètres de configuration expliqués : https://postgresqlco.nf/doc/fr/param/
• Optimisation de PostgreSQL en fonction de la configuration du serveur :
  • https://pgtune.leopard.in.ua/#/
  • https://pgconfigurator.cybertec-postgresql.com/
• Paramètres à vérifier pour l’utilisation de l’extension PostGIS :
  • https://postgis.net/docs/postgis_administration.html#database_tuning_configuration
  • https://postgis.net/workshops/postgis-intro/tuning.html

Voici également quelques uns des principaux paramètres et certaines valeurs recommandées ou en exemple.

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.

Paramètres pour PostGIS

PostGIS repose sur des librairies (GEOS, GDAL…) indépendantes. Certains de leurs paramètres peuvent être contrôlés par l’intermédiaire de variables spécifiques.

Les variables sont les suivantes :

postgis.backend

Permet de définir la librairie à utiliser entre GEOS et SFCGAL pour les fonctions portants les mêmes noms entre ces deux librairies. Défaut : GEOS.

Recommandé : postgis.backend = geos

postgis.gdal_datapath

Permet de spécifier le répertoire data de GDAL. Si non défini, la variable d’environnement GDAL_DATA peut être utilisée.

Il est recommandé d’utiliser le répertoire gdal-data fourni par PostGIS lors de son installation et situé dans le répertoire de PostgreSQL.

Recommandé : postgis.gdal_datapath = "chemin\vers\PostgreSQL\xx\gdal-data"

postgis.gdal_enabled_drivers

Permet de définir la liste des pilotes GDAL pouvant être utilisés. Si non défini, la variable d’environnement POSTGIS_GDAL_ENABLED_DRIVERS peut être utilisée.

Si vous souhaitez utiliser PostGIS raster et le stockage Out-DB, vous pouvez indiquer la liste des formats spécifiques à utiliser :

• Un ou plusieurs formats (« short name », séparés par des espaces) parmi ceux listés sur cette page.
• DISABLE_ALL : pour désactiver tous les pilotes.
• ENABLE_ALL : pour activer tous les pilotes.

postgis.enable_outdb_rasters

Permet d’activer les rasters avec stockage Out-DB.

postgis.gdal_config_options

Permet de spécifier les options de configuration pour GDAL lors de l’utilisation des raster avec stockage Out-DB.

Pour aller plus loin

Cet article fait parti du cours sur PostgreSQL, partie 8 du Vade-mecum.

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 :

BDDPostGISPostgreSQL configurationgdaloptimisationserveur