Fichier postgresql.conf et guide de configuration utilisateur générale (Global User Configuration [GUC]) annotés

PostgreSQL 8.0.x


Table des matières

Emplacement des fichiers
Connexions et authentification
Paramètres de connexion
Sécurité et authentification
Utilisation des ressources
Mémoire
Carte de l'espace libre Free Space Map
Utilisation des ressources du noyau
Délai de nettoyage (vacuum)
Processus d'écriture en arrière-plan (Background Writer)
Options WAL
Paramétrage
Points de contrôle
Archivage
Optimisation des requêtes
Méthodes de planification
Constantes de coût du planificateur
Optimisation génétique de requêtes (Genetic Estimate Query Optimizer, GEQO)
Autres modificateurs de requête
Options de journalisation et de débogage
Où journaliser ?
Quand journaliser ?
Que journaliser ?
Statistiques
Journalisation des statistiques
Statistiques de requête et d'index
Valeurs par défaut des connexions client
Comportement des instructions
Locale et formatage
Autres valeurs par défaut
Gestion des verrous
Compatibilité entre versions et plateformes
Versions antérieures de PostgreSQL
Compatibilité entre plateformes et clients

Emplacement des fichiers

Tableau 1. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
data_directoryRépertoireConfigDirDémarrage  Répertoire de stockage des données Ces nouveaux paramètres de configuration des emplacements de fichiers facilitent l'administration d'une installation de PostgreSQL lorsque les fichiers de configuration et de surveillance sont séparés de la base, généralement dans un but d'ajustement à une spécification particulière du fichier d'administration ou pour automatiser la conduite de tests avec plusieurs configurations. Lorsque ce paramètre est utilisé, seul l'emplacement du fichier postgresql.conf doit être précisé au démarrage du postmaster (à l'aide de -D ou PGDATA). Cette approche est supérieure à l'utilisation de lien symbolique, unique option jusque là disponible.
hba_fileNom de fichierConfigDir/pg_hba.confDémarrage  Fichier de configuration pour l'authentification basée sur l'hôte (habituellement appelé pg_hba.conf)  
ident_fileNom de fichierConfigDir/pg_ident.confDémarrage  Fichier de configuration pour l'authentification par ident (habituellement appelé pg_ident.conf)  
external_pid_fileNom de fichierAucunDémarrage  Nom du fichier additionnel d'identifiant de processus (PID) que le postmaster crée pour les programmes d'administration serveur Ce paramètre est utile pour les programmes d'administration et les interfaces utilisateur graphiques qui s'attendent à trouver le PID de PostgreSQL à un emplacement particulier, en général /var. Ce n'est qu'une copie du PID, en aucun cas le fichier utilisé par pg_ctl au démarrage. Ce dernier est situé dans le répertoire des données.

Connexions et authentification

Paramètres de connexion

Tableau 2. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
listen_addresses localhostDémarrage-h x -i Adresse(s) TCP/IP sur la(es)quelle(s) le serveur écoute les connexions en provenance des clients. La valeur à la forme d'une liste de noms d'hôte ou d'adresses IP séparés par des virgules. La valeur spéciale '*' correspond à toutes les interfaces IP disponibles. Si la liste est vide, le serveur n'écoute aucune interface IP. Dans ce cas, seules les sockets de domaine UNIX peuvent être utilisées pour se connecter. La valeur par défaut est 'localhost', ce qui n'autorise que les connexions « loopback ».

Ce paramètre remplace les deux paramètres « tcp_ip » et « virtual_host » de la version 7.4. La plupart des utilisateurs peuvent utiliser '*' pour écouter toutes les adresses, ou laisser 'localhost' pour une machine sécurisée. À la différence des versions précédentes, la valeur par défaut autorise désormais les connexions TCP/IP sur 127.0.0.1. Le serveur web local peut ainsi se connecter sans paramétrage particulier.

Pour un accès sécurisé, ce paramètre doit être modifié après la configuration du fichier pg_hba.conf.

port129 à 327685432Démarrage-p # Le port TCP sur lequel le serveur écoute. 5432 par défaut. Ce port est utilisé pour toutes les adresses IP que le serveur écoute.

Un port alternatif est essentiellement utilisé lorsqu'il est nécessaire de faire tourner plusieurs serveurs PostgreSQL sur la même machine, pendant une mise à niveau par exemple.

Une alternative à cette configuration est l'utilisation de l'option de compilation « with-port ». Cette option fixe le port alternatif dans toutes les bibliothèques évitant ainsi de préciser l'option -p pour tous les clients.

max_connections2 à 26214332Démarrage-N Nombre maximum de connexions concurrentes à un serveur de bases de données. Typiquement 100 par défaut, il peut être réduit si la configuration du noyau l'impose (initdb tente de le déterminer). Ce paramètre doit être maintenu près du minimum requis par l'application. En effet, chaque connexion nécessite des ressources système significatives. Les applications web qui servent des centaines d'utilisateurs peuvent utiliser une réserve de connexions (connection pool) pour réduire le nombre de connexions demandées. L'augmentation du paramètre demande un ajustement des limites mémoire du système.
superuser_reserved_connections0 à max_connections - 12Démarrage  Nombre de connexions réservées aux superutilisateurs PostgreSQL. Au plus max_connections connexions peuvent être actives simultanément. Lorsque le nombre de connexions concurrentes atteint max_connections moins superuser_reserved_connections, seules les connexions de superutilisateurs sont encore autorisées. Cela protège l'accès des superutilisateurs en cas d'engorgement de la base. Ce paramètre ne doit être positionné à 0 que lorsqu'il est certain que toutes les connexions ne sont jamais utilisées (NDR : je positionne souvent ce paramètre à 1 puisque je ne me connecte en superutilisateur à la base qu'en cas de problème.) Le paramétrage à 2 par défaut prévoit le cas d'utilitaire administratif connecté en permanence, autovacuum par exemple.
unix_socket_directory ''Démarrage-k $ Répertoire du socket de domaine Unix sur lequel le serveur écoute les connexions de clients. Par défaut, c'est /tmp, mais le paramètre peut être modifié à la compilation. Aucune recommandation particulière.
unix_socket_group ''Démarrage  Groupe propriétaire du socket de domaine Unix (l'utilisateur propriétaire de ce socket est toujours celui qui exécute le serveur). Combiné avec l'option UNIX_SOCKET_PERMISSIONS, ce paramètre peut être utilisé comme mécanisme supplémentaire de contrôle des accès pour ce type de socket. Par défaut, c'est une chaîne vide, donc le groupe par défaut de l'utilisateur. Aucune recommandation particulière.
unix_socket_permissions 0777Démarrage 

Droits d'accès au socket de domaine Unix. Les sockets de domaine Unix utilisent le système habituel de gestion des droits des systèmes de fichiers Unix. La valeur de l'option doit être précisée sous la forme numérique acceptée par les outils système chmod et umask. L'utilisation du format octal impose un 0 (zéro) en début de nombre.

Les droits par défaut sont 0777, tout le monde peut se connecter. Des alternatives acceptables sont 0770 (autorisations pour l'utilisateur et le groupe, voir aussi unix_socket_group) et 0700 (utilisateur seul). En général, dans le cas d'une socket de domaine Unix, seul le droit d'écriture importe. Il n'y a donc aucun intérêt à positionner ou supprimer les droits d'écriture ou de lecture.

Aucune recommandation particulère.
rendezvous_name ''Démarrage  Nom du diffuseur Rendezvous. La valeur par défaut, indiquée par une chaîne vide '', est le nom de l'ordinateur. Cette option n'a d'intérêt que pour les plateformes qui supportent Rendezvous. Aucune recommandation particulière.

Sécurité et authentification

Tableau 3. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
authentication_timeout1-600 sec600Rechargement  Temps maximum laissé à un client pour réussir l'authentification, en secondes. Si un client potentiel n'a pas terminé la séquence d'authentification pendant ce temps, le serveur met fin à la connexion. Cela permet d'éviter qu'un client bloqué n'occupe indéfiniment une connexion. Le temps d'attente peut être réduit s'il s'agit d'exécuter un site web à grand traffic. Afin d'éviter une indisponibilité non souhaitée, ou une attente trop longue lorsque le serveur est chargé, il peut être utile de faire correspondre ce temps d'attente avec celui du middleware.
ssltrue, falsefalseDémarrage-lActive les connexions SSL SSL est une alternative chiffrée à l'accès direct au port TCP/IP, nécessaire pour les clients accédant à des données sécurisées, en particulier à travers un réseau sans fil. PostgreSQL envoie les requêtes et les données en texte, même lors de l'utilisation d'un mot de passe chiffré. SSL peut être difficile à configurer. De plus, tous les clients ne supportent pas l'accès SSL.
password_encryptiontrue, falsetrueExécution  Détermine le chiffrement du mot de passe lorsque ni ENCRYPTED ni UNENCRYPTED ne sont précisés lors de l'indication d'un mot de passe avec les commandes CREATE USER et ALTER USER. Il est préférable de laisser la valeur à true (vrai), à la fois dans le fichier de configuration et à la connexion. Il n'y a quasiment jamais de raison de ne pas chiffrer les mots de passe des utilisateurs de la base de données.
krb_server_keyfile ''Démarrage  Positionne l'implantation du fichier de clés du serveur Kerberos. Utilisé uniquement pour l'authentification Kerberos des utilisateurs.
db_user_namespacetrue, falsefalseRechargement  Lorsque cette option est activée, les utilisateurs doivent être créés comme nom_utilisateur@nom_base. Lorsque le nom d'utilisateur est fourni par un client, @ et le nom de la base sont ajoutés au nom de l'utilisateur. C'est ce nom d'utilisateur, dépendant d'une base de données, qui est ensuite recherché par le serveur. Lors de la création dans l'environnement SQL d'utilisateurs dont le nom contient @, il est nécessaire de placer le nom de l'utilisateur entre des guillemets simples. Cette option prend en charge les installations (telles que les FAI) qui nécessitent des utilisateurs définis par base de données. C'est assez contraignant, et cela devrait être supprimé lorsqu'une meilleure solution sera implantée. Il est ainsi préférable de ne pas utiliser cette option lorsqu'elle n'est pas vitale.

Utilisation des ressources

Mémoire

Note

Augmenter la valeur de la plupart des paramètres suivants oblige à modifier les options du noyau du système d'exploitation pour augmenter la mémoire alloué à un processus ou à un utilisateur. La documentation en ligne fournit des informations sur les commandes à utiliser pour de nombreux systèmes d'exploitation. Sauf indication contraire, la plupart de ces options s'additionnent pour déterminer la quantité totale de mémoire utilisée par PostgreSQL.

Tableau 4. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documetationCommentaires
shared_buffers16 à 2621431000 Démarrage -B x Positionne le nombre de tampons de mémoire partagée utilisé par le serveur de bases de données. Le minimum est 2 X max_connections. La valeur par défaut est généralement 1000, mais elle peut être inférieure si la configuration du noyau l'impose (ce qu'initdb détermine). Chaque tampon représente 8192 octets, à moins qu'une valeur différente de BLCKSZ n'ait été choisie à la compilation. La valeur minimale est de 16 et de deux fois la valeur de max_connections. Néanmoins, un paramétrage significativement supérieur au minimum est souvent nécessaire pour assurer des performances satisfaisantes. En production, il est recommandé d'utiliser une valeur de quelques milliers.

Le paramétrage de shared_buffers nécessite une discussion bien plus complète que ne l'autorise la place ici disponible. On peut se référer à d'autres articles sur le sujet.

Quelques règles empiriques : sur un serveur PostgreSQL dédié, une valeur convenable se situe en général entre 1 000 et 50 000 (8Mo et 400Mo). Les facteurs qui incitent à augmenter la valeur sont des connexions plus nombreuses, des parties actives de la base plus grandes, des requêtes longues et complexes, et des grandes tables. La RAM disponible limite le ombre maximum de shared_buffers ; 1/3 de la RAM disponible est la limite maximale à utiliser.

work_mem 64 à Int Max1024 Exécution -S #

Précise la quantité de mémoire utilisable par les opérations de tri interne et les tables de hachage avant d'utiliser des fichiers temporaires. La valeur est indiquée en Ko, la valeur par défaut est 1024 Ko (1 Mo). En cas de requête complexe, de nombreuses opérations de tri ou de hachage peuvent s'exécuter en parallèle ; chacune peut utiliser la quantité de mémoire indiquée par la valeur de ce paramètre avant de commencer à utiliser des fichiers temporaires. De plus, de nombreuses sessions peuvent effectuer ces opérations en parallèle. La mémoire totale utilisée peut être plusieurs fois la valeur de work_mem ; il faut en tenir compte lors du choix de la valeur. Les opérations de tri sont utilisées par ORDER BY, DISTINCT et les jointures. Les tables de hachage sont utilisées dans les jointures de hachage, les aggrégations par hachage et la résolution par hachage des sous-requêtes IN.

À l'origine appelé sort_mem, le paramètre a été renommé pour refléter l'extension de son rôle au-delà des simples tris.

work_mem est un compromis. Une plus grande valeur est utilisée pour : les bases de données volumineuses, les requêtes complexes, une grande quantité de RAM disponible. Une plus petite valeur est recherchée pour : une faible quantité de RAM ou de nombreux accès concurrents. Trouver le bon compromis peut s'avérer délicat.

Une autre façon d'ajuster cette valeur consiste à surveiller les fichiers temporaires de PostgreSQL (dans PGDATA/base/OID_BASE/pgsql_tmp) et d'accroître la valeur de work_mem si de nombreuses requêtes permutent avec ces fichiers.

Il ne faut pas oublier que ce paramètre peut être positionné par connexion. Ainsi, dans le cas où seules quelques requêtes très complexes sont à exécuter, la valeur peut être augmentée avant leur exécution, mais conservée basse pour les autres connexions.

maintenance_work_mem 1024 à Int Max8192 Exécution   Indique la quantité maximale de mémoire à utiliser dans les opérations de maintenance, telles que VACUUM, CREATE INDEX et ALTER TABLE ADD FOREIGN KEY. La valeur est précisée en Ko. La valeur par défaut est 16384 ko (16 Mo). Puisque seule une de ces opérations peut être effectuée à la fois au cours d'une session, et qu'en général peu se produisent simultanément sur une même installation, il n'y a aucun risque à positionner ce paramètre à une valeur nettement supérieure à celle de work_mem. Une valeur élevée peut améliorer les performances du nettoyage (vacuum) et de la restauration des sauvegardes.

À l'origine appelé vacuum_mem, le paramètre a été renommé pour refléter l'extension de son rôle à l'allocation de mémoire lors du chargement des index.

La valeur par défaut est généralement trop basse. Il en résulte un blocage des E/S du système par les opérations de VACUUM et de créations d'index et/ou des blocages d'objets pendant la permutation. Une valeur convenable est généralement comprise entre 32 Mo et 256 Mo ; cela dépend autant de la RAM disponible que du plus grand volume (attendu) des objets de la base.

Tout comme work_mem, ce paramètre peut être fixé à l'exécution, ce qui permet de l'accroître temporairement lors du chargement d'index ou de la création de clés sur des tables volumineuses.

Carte de l'espace libre Free Space Map

Tableau 5. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
max_fsm_pages1000 à Int Max20000 Démarrage   Positionne le nombre maximum de pages disque pour lesquels l'espace libre est recherché dans la mappe de l'espace libre partagé. Six octets de mémoire partagée sont consommés pour chaque connecteur de page. La valeur doit être supérieure à 16 X max_fsm_relations.

Un paramétrage correct de la FSM peut éliminer, ou au moins, retarder l'obligation d'exécuter VACUUM FULL et REINDEX. La meilleure façon de le régler est la suivante :

  1. calculer la fréquence du VACUUM (normal) de la base en fonction des écritures ;

  2. utiliser la base en fonctionnement normal et exécuter VACUUM VERBOSE ANALYZE à la place de VACUUM, en sauvegardant la sortie dans un fichier ;

  3. calculer le nombre de pages maximum réclamées par VACUUM et utiliser ce chiffre.

Alternativement, en cas d'utilisation d'autovacuum, la valeur peut être issue d'un pourcentage du nombre total de pages dans la base, pour coïncider avec le pourcentage d'autovacuum. Quoiqu'il en soit, une page demande peu de mémoire (environ 6 octets). il est donc préférable d'être généreux plutôt que radin.

Pour les base de données qui connaissent des « pics » d'activité (rafales d'un million de mises à jour mais pas d'autre activité des minutes ou des heures durant), ce nombre peut être impossible à optimiser. Les lignes insérées n'ont pas d'impact sur la FSM. Enfin, si le serveur est peu fourni en RAM, augmenter cette valeur peut s'avérer contre-productif.

max_fsm_relations10 à Int Max1000 Démarrage   Positionne le nombre maximum de relations (tables et index) pour lesquels l'espace libre est recherché dans la carte de l'espace libre partagé. Chaque connecteur utilise approximativement 50 octets de mémoire. Peu d'utilisateurs peuvent avoir besoin d'ajuster ce paramètre, mais il est intéressant de le considérer. FSM_relations doit être au moins équivalent au nombre de tables dans l'ensemble des bases, bases squelettes et schéma système compris. PostgreSQL peut avoir des performances aléatoires si le nombre de FSM_relations est trop faible.
max_stack_depth     Indique la profondeur maximale que peut atteindre la pile d'exécution du serveur en toute sécurité. Le réglage idéal du parmètre correspond à la limite réelle de la pile imposée par le noyau (positionné par ulimit -s ou équivalent), diminué d'une marge de sécurité d'un Mo environ. Cette marge de sécurité est nécessaire parce que la profondeur de la pile n'est pas vérifiée pour chaque routine du serveur, mais uniquement pour les routines potentiellement récursives, telles que les évaluations d'expressions. Une valeur plus grande que la limite réelle du noyau peut conduire une fonction récursive à occasionner un plantage d'un processus serveur. La valeur par défaut de 2048 ko (2 Mo) est résolument basse et ne risque pas d'occasionner un plantage. Néanmoins, elle peut s'avérer trop petite pour autoriser l'exécution de fonctions complexes.

La paramètre s'est appelé max_expr_depth, et son unité ne correspondait pas à celle utilisée par la plupart des noyaux systèmes.

En cas de dépassement de ce paramètre, un message d'erreur spécifique est produit. A ce moment-là, il peut être augmenté avec précaution ; de nombreux systèmes d'exploitation ont des limites aussi basses que 8 Mo.

Utilisation des ressources du noyau

Tableau 6. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
max_files_per_process25 à Int Max1000 Démarrage   Fixe le nombre maximum de fichiers simultanément ouverts par chaque sous-processus du serveur. La valeur par défaut est 1 000. Si le noyau impose une limite par processus, il n'est pas nécessaire de s'inquiéter de ce paramètre. Mais sur la plupart des plateformes (et notamment BSD), le noyau autorise des processus individuels à ouvrir beaucoup plus de fichiers que le système ne peut en prendre en charge lorsque de nombreux processus essayent tous d'ouvrir ce nombre de fichiers. Si le message d'erreur « Trop de fichiers ouverts » ("Too many open files") apparaît, il faut alors essayer de réduire ce paramètre. Il ne peut être corrigé qu'au démarrage du serveur. D'après la documentation, à utiliser essentiellement pour BSD. À considérer uniquement à l'apparition de messages « Trop de fichiers ouverts » ("Too many open files").
preload_librariesChemin de fichierVide Démarrage  

Indique les bibliothèques à précharger au démarrage du serveur. Une fonction d'initialisation sans paramètre peut optionnellement être appelée pour chaque bibliothèque. Pour cela, il suffit d'ajouter un double-point et le nom de la fonction d'initialisation après le nom de la bibliothèque. '$libdir/mylib:mylib_init' implique, par exemple, le préchargement de mylib et l'exécution de la fonction mylib_init. Si plusieurs bibliothèques doivent être chargées, leurs noms sont séparés par des virgules.

Si une bibliothèque ou une fonction d'initialisation ainsi indiquée n'est pas trouvée, le serveur ne peut pas démarrer. Les bibliothèques du langage procédural de PostgreSQL peuvent être préchargées de cette façon, typiquement en utilisant la syntaxe '$libdir/plXXX:plXXX_init' avec XXX qui peut être pgsql, perl, tcl ou python.

Le préchargement (et l'initialisation éventuelle) d'une bibliothèque partagée permet d'éviter le temps de chargement de la bibliothèque la première fois qu'elle est utilisée. Néanmoins, le temps de démarrage de chaque processus serveur peut croître légèrement, même si ce processus n'utilise jamais cette bibliothèque. Cette option n'est donc recommandée que pour les bibliothèques utilisées dans la plupart des sessions.

Cela n'est utile que dans le cas de bases de données spécialisées. Une base de cartographie peut, par exemple, gagner légèrement en performances en préchargeant les bibliothèques GIS. Pour la plupart des systèmes, il est préférable de ne pas renseigner cette option.

Délai de nettoyage (vacuum)

Tableau 7. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
vacuum_cost_delay 0Exécution  Le temps, en millisecondes, pendant lequel le processus est endormi lorsque la limite de coût est atteinte. La valeur par défaut est 0, ce qui désactive la fonctionnalité de délai de nettoyage en fonction du coût. Une valeur positive active le nettoyage fonction du coût. Sur la plupart des systèmes, la résolution réelle du délai est de 10 millisecondes ; une valeur de vacuum_cost_delay qui n'est pas un multiple de 10 a le même comportement que le plus petit multiple de 10 supérieur à cette valeur. Ce paramètre est très utile lors du nettoyage de tables volumineuses qui, autrement, peut bloquer les E/S pendant de longues périodes ou maintenir des verrous bloquant de nombreuses requêtes. Pour l'essentiel, l'activation de ce paramètre découpe le nettoyage d'une table volumineuse en segments définis comme des unités de travail spécifiques, entre lesquelles le nettoyage est mis en veille pour le temps défini par le paramètre. Cela a pour effet d'augmenter parfois considérablement le temps nécessaire au nettoyage, mais aussi de réduire l'impact du nettoyage sur le système, de l'ordre de 85%. Une valeur raisonnable est comprise entre 50 ms et 200 ms.
vacuum_cost_page_hit 1Exécution  Indique le coût estimé de nettoyage d'un tampon trouvé dans le cache de tampon partagé. Ce paramètre représente le coût du verrouillage du groupe de tampons, la recherche de la table de hachage partagée et le parcours du contenu de la page. Il est préférable de ne pas modifier ce paramètre, mais plutôt vacuum_cost_limit.
vacuum_cost_page_miss 10Exécution  Indique le coût estimé de nettoyage d'un tampon à lire sur le disque. Ce paramètre représente le coût de verrouillage du groupe de tampons, la recherche de la table de hachage partagée, la lecture du bloc souhaité sur le disque et le parcours de son contenu. Il est préférable de ne pas modifier ce paramètre, mais plutôt vacuum_cost_limit.
vacuum_cost_page_dirty 20Exécution  Indique le coût estimé de la modification par le nettoyeur d'un bloc précédemment nettoyé. Ce paramètre représente les E/S supplémentaires requises pour supprimer une nouvelle fois du disque les blocs inutiles. Il est préférable de ne pas modifier ce paramètre, mais plutôt vacuum_cost_limit.
vacuum_cost_limit 200Exécution  Indique le coût cumulé qui impose la mise en sommeil du processus de nettoyage. La valeur de ce paramètre est diminuée pour fragmenter le nettoyage en des « segments » plus nombreux. Une combinaison très aggressive consiste à positionner vacuum_cost_delay à 200 ms et vacuum_cost_limit à 50 ms ; cela implique un nettoyage 10 fois plus long mais sans impact sur les performances de la base de données. Dans la plupart des cas, le DBA peut être plus modéré.

Processus d'écriture en arrière-plan (Background Writer)

Tableau 8. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
bgwriter_delay 200Démarrage  Indique le délai entre les périodes d'activité du processus d'écriture en arrière-plan. À chaque tour, ce processus écrit sur le disque un certain nombre de tampons modifiés (nombre ajustable à l'aide des paramètres qui suivent). Les tampons sélectionnés sont toujours les plus anciens des tampons modifiés. Le Processus d'écriture est alors mis en veille pour bgwriter_delay millisecondes, et ainsi de suite.

Fonctionnalité nouvelle, le processus d'écriture en arrière-plan est destiné à alléger les pics de contrôle.

L'OSDL effectue toujours des tests de configuration du bgwriter ; aucune recommendation ne peut être faite à ce jour.

bgwriter_percent 1Démarrage  Au maximum ce pourcentage de tampons modifiés est écrit à chaque tour (si besoin, le nombre est arrondi à l'entier supérieur). L'OSDL effectue toujours des tests de configuration du bgwriter ; aucune recommendation ne peut être faite à ce jour.
bgwriter_maxpages 100Démarrage  Au maximum ce nombre de tampons modifiés est écrit à chaque tour. L'OSDL effectue toujours des tests de configuration du bgwriter ; aucune recommendation ne peut être faite à ce jour.

Options WAL

Paramétrage

Tableau 9. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
fsynctrue, falsetrueDémarrage-F (off)

Lorsque cette option est activée, le serveur PostgreSQL utilise les appels système fsync() pour s'assurer que les mises à jour sont physiquement écrites sur le disque. Cela garantit la récupération d'une bases de données cohérente après une panne matérielle ou système.

Néanmoins, l'utilisation de fsync() implique une baisse de performances : lorsqu'une transaction est validée, PostgreSQL doit attendre que le système purge le journal d'écriture anticipée. Lorsque fsync est désactivé, a toute latitude dans la mise en mémoire tampon, le tri et le report des écritures. Il peut en découler des performances accrues. Toutefois, si le système plante, le résultat des dernières transactions validées peut être perdu, en tout ou partie. Dans le pire des cas, les données peuvent être irrémédiablement corrompues. Les plantages du serveur de base de données ne présentent ici aucun risque ; seul un plantage du système d'exploitation présente un risque de corruption.

La journalisation des écritures anticipées (Write-Ahead Logging ou WAL) ne doit être désactivée que sur les bases en lecture seule ou celles qu'il est possible de régénérer à l'aide de logiciels externes. Tandis que du RAID associé à un système de haute-disponibilité électrique peuvent aider à la protection des données, la désactivation de fsync impose la restauration des données à partir de sauvegardes en cas de pannes matériel ou électrique.

D'un autre côté, le WAL implique une baisse de performances lors des écritures, à plus forte raison sur des systèmes mono-disques. Pour l'essentiel, l'activité nécessaire aux opérations de lecture/écriture est doublée à chaque mise à jour. De plus, les fonctionnalités d'amélioration des performances par cache disque matériel ou logiciel sont désactivées. C'est pourquoi, dans le cas de données dont la conservation n'est pas une priorité, il est intéressant de considérer la désactivation de fsync.

Si le WAL est arrêté, les options qui suivent n'ont pas d'intérêt.

wal_sync_methodfsync, fdatasync, open_sync, open_datasyncDépend de la plateformeDémarrage  Méthode utilisée pour forcer la mise à jour des WAL sur le disque. Les valeurs possibles sont FSYNC (fsync() est appelé à chaque validation), FDATASYNC (fdatasync() est appelé à chaque validation), OPEN_SYNC (écrit les fichiers WAL avec l'option O_SYNC d'open()) et OPEN_DATASYNC (écrit les fichiers WAL avec l'option O_DSYNC d'open()). Ces choix ne sont pas tous disponibles sur toutes les plateformes. Il s'agit de l'appel système utilisé pour synchroniser les WAL sur disque. La valeur par défaut a été réglée pour chaque OS en fonction de la documentation de l'OS. En revanche, aucun test comparatif n'a été mené. Il est possible que le changement de méthode accélère l'écriture sur une plateforme spécifique, mais cela ne doit être tenté que si les ressources nécessaires à des tests comparatifs et de régression sont disponibles.
wal_buffers4 à Int Max8Démarrage  Nombre de tampons de pages disque alloués dans la mémoire partagée pour les données du WAL. La valeur par défaut est 8. La valeur doit être juste assez grande pour contenir la quantité de données WAL engendrée par une transaction. L'accroissement de ce paramètre n'a que peu d'influence, même dans le cas de systèmes OLTP (On-Line Transaction Processing) chargés. Dans le cas de transactions conséquentes, on peut accroître ce paramètre par sécurité (de 16 à 64), mais il est préférable de se concentrer sur checkpoint_segments.
commit_delay0 - 1000000Démarrage  Indique le délai, en microsecondes, entre l'écriture d'une validation dans le tampon WAL et la purge du tampon sur disque. Un délai positif peut permettre la validation de plusieurs transactions avec un seul appel système fsync(), si la charge système est suffisamment élevée pour que de nouvelles transactions soient prêtes pendant cet interval. Mais ce délai est perdu dans le cas contraire. Ainsi, le délai n'est exécuté que si au moins commit_siblings autres transactions sont actives au moment où le processus serveur a écrit sa validation. Ces deux paramètres sont configurés ensemble pour un environnement à fort volume de petites transactions. Activés, ils permettent de purger en même temps sur disques des transactions sans relation entre elles, qui autrement ne le seraient pas, avec à la clé un potentiel accroissement des performances. Néanmoins, c'est une manière de pallier l'attente de quelques millisecondes supplémentaires entre chaque transaction. Afin de tester l'amélioration des performances dans un cas précis, un point de départ peut être un commit_delay de 500 (soit 1/2 milliseconde).
commit_siblings1 - 10005Démarrage  Indique le nombre minimum de transactions concurrentes ouvertes avant l'exécution du délai COMMIT_DELAY. Plus la valeur est grande, plus la probabilité de valider une autre transaction pendant cet interval grandit. Si commit_delay est utilisé, ce paramètre peut être modifié en fonction de la longueur moyenne d'une transaction. Dans le cas de transactions courtes (requêtes insert/update d'une ligne) une valeur basse peut être utilisée puisque des validations simultanées sont possibles ; s'il existe des transactions plus longues, la valeur peut être augmentée pour éviter le d'inutiles commit_delay.

Points de contrôle

Tableau 10. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
checkpoint_segments1 à Int Max3Démarrage  Distance maximale entre deux points de contrôle WAL automatiques, en segments de fichier journal (chaque segment représente normalement 16 Mo). C'est le paramètre qui permet d'ajuster le plus finement le comportement lors de mises à jour volumineuses, de chargements de données et de grande activité OLTP. Sur les systèmes où les écritures sont nombreuses, ce paramètre peut être poussé à 8, au moins ; sur les systèmes où sont chargés de grandes volumétries (telles que le chargement de plusieurs Go de données), cela peut aller jusqu'à 128 (256 est la valeur utilisée lors des tests DBT2). Cela requiert toutefois une quantité non négligeable d'espace disque pour les fichiers xlog ( ( 2 x segments + 1 ) x 16Mo, pour être précis). Si les fichiers xlog ne sont pas sur un disque distinct de celui des données, l'amélioration est minime.
checkpoint_timeout30 à 3600300Démarrage  Temps maximal entre deux points de contôle WAL automatiques, en secondes. Ce paramètre peut être augmenté fortement (jusqu'à 30 minutes) pour les chargements de gros volumes. Dans les autres cas, un réglage entre 3 et 10 minutes représente une bonne échelle. Lorsque des bloquages d'écriture apparaissent, ce paramètre peut être augmenté. L'augmentation de la valeur du paramètre est actuellement limitée par l'impact croissant que cela engendre sur la synchronisation du disque.
checkpoint_warning0 à Int Max0Démarrage  Un message est envoyé dans les journaux du serveur si la fréquence des points de contrôle engendrés par le remplissage des fichiers de segments de points de contrôle est supérieure à ce temps, en secondes. Zéro désactive les alertes. Ce paramètre permet de détecter si checkpoint_segments doit être augmenté. Ce paramètre peut être activé pendant la phase de développement. Des alertes nombreuses dans les journaux incitent alors à accroitre le paramètre checkpoint_segments.

Archivage

Tableau 11. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
archive_commandCommande shell''Démarrage 

Indique la commande shell à exécuter pour archiver un segment terminé des séries de fichiers WAL. Une chaîne vide (défaut) désactive l'archivage des WAL. %p dans la chaîne est remplacé par le chemin absolu du fichier à archiver, %f par le nom du fichier seul. %% permet d'échapper un caractère %. Pour de plus amples informations, on peut se reporter à la section 22.3.1, « Configurer l'archivage WAL ».

Cette commande doit impérativement ne retourner une valeur de sortie nulle qu'en cas de succès.

Ce paramètre active la nouvelle fonctionnalité de restauration à un instant donné (Point In Time Recovery, PITR) en fournissant une commande shell d'archivage (copie) des segments WAL finalisés vers une autre destination. De plus amples informations concernant leur utilisation sont disponibles dans

Optimisation des requêtes

Méthodes de planification

Tableau 12. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires

enable_hashagg

enable_hashjoin

enable_indexscan

enable_mergejoin

enable_nestloop

enable_seqscan

enable_sort

enable_tidscan

true, falsetrueExécution

-fi

-fm

-fn

-fs

-ft*

Active ou désactive l'utilisation par le planificateur de requêtes des types de plan respectifs. Par défaut, ils sont actifs. Cela permet de déboguer le planificateur de requêtes.

Ces options ne devraient être utilisées que pour tester les requêtes ; il est courant, par exemple, de positionner « enable_seqscan = false » pour déterminer si le planificateur n'omet pas inutilement un index. Quoi qu'il en soit, seules des circonstances extraordinaires nécessitent qu'un de ces paramètres soit positionné à false dans le fichier de configuration. En fait, si cela devait arriver, c'est probablement que d'autres paramètres d'optimisation ont été négligés.

Constantes de coût du planificateur

Tableau 13. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
effective_cache_size0 à Double1000Exécution  Fournit à l'optimiseur une estimation de la taille efficace du cache disque (c'est-à-dire la partie du cache disque du noyau utilisée pour les fichiers de données PostgreSQL). Elle est mesurée en pages disque, chacune de 8 ko. Fournir au planificateur une hypothèse concernant la probabilité qu'un index ou une table particulière se trouve en mémoire a une forte influence sur le choix par le planificateur des index préférentiellement aux parcours séquentiels, et sur quelques autres structures de requête. De ce fait, positionner ce paramètre à environ 2/3 de la RAM disponible permet de s'assurer que le planificateur est correctement informé. La plupart du temps, les DBA qui souhaitent désactiver enable_seqscan peuvent commencer par modifier ce paramètre.

random_page_cost

0 à Double4Exécution  Fournit au planificateur une estimation du coût de récupération non séquentielle d'une page disque. Elle est mesurée en multiples du coût de récupération séquentielle d'une page. Plus grande est la valeur, plus grande est la probabilité d'un parcours séquentiel. Plus petite est la valeur, plus grande est la probabilité d'utilisation des index.

L'échelle utile se situe entre 2.0 et 4.0. La valeur la plus basse pour un CPU rapide, des E/S rapides et une base de données qui tient entièrement en mémoire. La valeur haute lorsque le CPU ou le disque sont limités, ou si les tables principales et leurs index ont une taille beaucoup plus grande que la RAM disponible (plusieurs fois). Ce paramètre ne doit jamais être positionné à une valeur inférieure à 1.5 ; si des problèmes avec certaines requêtes semblent obliger à étudier cette possibilité, c'est qu'il y a probablement d'autres paramètres à régler (effective_cache_size, par exemple).

Lorsque l'effet de différents réglages est testé, il est important de tester plusieurs requêtes, et pas uniquement celle qui pose problème.

cpu_tuple_cost

cpu_index_tuple_cost

cpu_operator_cost

0 à Double

0.01

0.001

0.0025

Exécution  Fournit à l'optimiseur une estimation du coût CPU de traitement de chaque ligne, le parcours des index et le traitement de chaque élément (respectivement) where pendant la requête. La mesure s'effectue en fraction du coût de parcours séquentiel d'une page. Les coûts par défaut sont assez arbitraires ; c'est pourquoi ils sont modifiables. Toutefois, personne, au sein de la communauté n'a pu présenté de meilleur coûts par défaut, et la plupart du temps, les modifications on un effet inverse sur quelques requêtes. Ainsi, à moins de disposer de beaucoup de temps pour tester les requêtes, il est préférable de ne pas toucher à ces trois paramètres.

Optimisation génétique de requêtes (Genetic Estimate Query Optimizer, GEQO)

Tableau 14. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
geqotrue, falsetrueExécution  Active ou désactive l'optimisation génétique de requêtes. Il s'agit en fait d'un algorithme génétique de planification de requêtes qui tente d'éviter les recherches exhaustives. Activé par défaut. Divers paramètres GEQO_ permettent d'en affiner le comportement.

GEQO a été introduit dans PostgreSQL 6.5 pour optimiser les requêtes de jointures qui utilisent trop de tables pour permettre une analyse exhaustive par le planificateur. Par définition, les requêtes GEQO sont plus lentes que les requêtes habituelles. Son but est d'intervenir quand la planification d'une requête peut réquisitionner toute la CPU.

Lorsque l'application semble faire un usage immodéré de GEQO, il peut être intéressant d'écrire les requêtes en explicitant l'ordre de la jointure. L'utilisateur a, en effet, un pouvoir discriminant plus grand que l'algorithme.

geqo_threshold2 a Int Max11Exécution  L'optimisation génétique de requête est utilisée pour planifier les requêtes qui contiennent au minimum ce nombre d'éléments dans la clause FROM. Une jointure compte pour un élément FROM. La valeur par défaut est 11. Pour les requêtes plus simples, il est généralement plus efficace d'utiliser une stratégie de planification déterministique, exhaustive. Ce paramètre contrôle également la façon dont l'optimiseur tente de fusionner les sous-requêtes de clause FROM avec la requête principale. S'il est possible d'augmenter légèrement ce seuil (jusqu'à 14) sur les machines à CPU rapides (dual Opteron par exemple), le conseil précédent de le monter à 20 se fondait sur un cas très particulier et a été depuis désapprouvé.

geqo_selection_bias

geqo_pool_size

geqo_effort

geqo_generations

geqo_random_seed

1.5-2.0

2.0

0

1

0

-1

Exécution  Divers paramètres d'affinage de l'algorithme d'optimisation génétique : la taille du pool correspond au nombre d'individus d'une population. L'échelle s'étend de 128 à 1024. Lorsqu'elle est positionnée à 0 (valeur par défaut), la taille du pool est obtenu par le calcul. Elle vaut alors 2^(QS+1), avec QS le nombre d'éléments FROM de la requête. L'effort est utilisé pour calculer une valeur par défaut pour les générations. L'échelle s'étend de 1 à 80, 40 étant la valeur par défaut. generations précise le nombre d'itérations de l'algorithme. Ce nombre doit être entier et positif. Si une valeur nulle est utilisée, generations est calculé. Le nombre vaut alors effort * Log2(PoolSize). Le temps d'exécution de l'algorithme est approximativement proportionnel à la somme de la taille du pool et de generations. selection bias affine la sélectivité au sein d'une population. Ses valeurs vont de 1.5 à 2.0 (2.0 étant la valeur par défaut). random seed permet d'ajuster l'algorithme pour obtenir des résultats reproductibles. Positionné à -1, l'algorithme a un comportement non déterministe.  

Autres modificateurs de requête

Tableau 15. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
default_statistics_target1-100010Exécution  Positionne la cible par défaut des statistiques pour les tables dont la cible spécifique à la colonne n'a pas été positionnée à l'aide de ALTER TABLE SET STATISTICS. Plus la valeur est grande, plus long est le temps nécessaire à l'ANALYZE. En revanche, la qualité des estimations du planificateur s'améliore. N'a aucun effet jusqu'au prochain ANALYZE. Il est en général déconseillé d'utiliser cette option pour améliorer l'ensemble des statistiques, sauf dans des cas particuliers ; la collecte de statistiques sur de grandes colonnes (large text, par exemple) peut devenir excessive et donc contre-productive. Pour une base quasiment entièrement numérique, une augmentation modeste (jusqu'à 100, par exemple) peut être globalement positive ; dans les autres cas, l'augmentation des statistiques sur des colonnes spécifiques peut être envisagée.
from_collapse_limit0 à Int Max8Exécution  Le planificateur fusionne les sous-requêtes dans la requête principale si la liste FROM résultante ne contient pas plus de ce nombre d'éléments. Une valeur inférieure réduit le temps de planification, mais peut conduire à des plans de moindre qualité. 8 est la valeur par défaut. Il est préférable de conserver une valeur inférieure à GEQO_THRESHOLD. Comme pour la plupart des autres paramètres de cette section, il est préférable de ne changer sa valeur à l'exécution que pour certaines requêtes insolubles.
join_collapse_limit1 à Int Max8Exécution  Le planificateur résout les jointures internes (inner JOIN) explicites en listes d'éléments FROM si la liste résultante ne contient pas plus de ce nombre d'éléments. En général, ce paramètre a la même valeur que FROM_COLLAPSE_LIMIT. Le positionner à 1 empêche toute résolution des jointures internes, ce qui permet d'utiliser la syntaxe JOIN explicitement pour contrôler l'ordre de la jointure. Les valeurs intermédiaires permettent d'arbitrer entre le temps de planification et la qualité du plan. Cette option est conçue pour ceux qui préfèrent écrire leurs requêtes en utilisant la syntaxe des jointures explicites (e.g. « a join b using (1) join c using (2) »), mais apprécient néanmoins que le planificateur choisisse le meilleur ordre de jointure. En particulier, les gens migrant de MS SQL Server peuvent utiliser ce paramètre en le positionnant à une valeur modérément élevée, puisque ce genre de bases trie les jointures automatiquement. Comme vu plus haut, ce paramètre est maintenu à une valeur inférieure à geqo_threshold.

Options de journalisation et de débogage

Où journaliser ?

Tableau 16. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
log_destinationstderr, syslog, eventlogstderrDémarrage  PostgreSQL accepte différentes méthodes de journalisation des messages du serveur, y compris stderr et syslog. Sous Windows, eventlog est également accepté. Ce paramètre est composé d'une liste, séparée par des virgules, de destinations souhaitées. Ce paramètre est analogue à l'ancien « syslog », mais en plus compréhensible. Il supporte également l'« eventlog » Win32. Il est important de décider de la journalisation au moment de l'installation : soit avec syslog, ce qui facilite la gestion globale du système, soit vers un fichier propre à PostgreSQL, ce qui facilite le débogage du serveur de bases de données. Il est évidemment possible d'utiliser les deux, mais la sortie est certainement excessive.
redirect_stderr     Cette option permet de capturer les messages envoyés sur stderr et de les rediger vers des fichiers de journalisation. En combinaison avec la journalisation vers stderr est souvent plus utile que la journalisation vers syslog, puisque certains types de messages peuvent ne pas apparaître dans la sortie de syslog (les messages de défaillance de l'éditeur de liens dynamiques en est un bon exemple). C'est la nouvelle fonctionnalité de rotation de journaux. Elle remplace également l'option -l de la ligne de commande de pg_ctl, et/ou la redirection de ligne de commande. Elle ne s'applique que lorsque « stderr » est choisi plus haut. Les cinq options qui suivent ne s'appliquent que si cette option est positionnée. Le comportement de l'ancienne option -l peut être reproduit avec redirect_stderr et en annulant la rotation.
log_directoryrépertoirepg_logDémarrage  Lorsque redirect_stderr est activé, cette option précise le répertoire de création des journaux. Il peut être précisé comme un chemin absolu, ou relatif au répertoire de données du cluster. Par défaut, il s'agit du répertoire « pg_log » dans PGDATA, ce qui n'est probablement pas le meilleur choix lorsque d'autres disques/baies sont disponibles. /var/pg_log est fréquemment rencontré.
log_filenamespecialpostgresql-%Y-%m-%d_%H%M%S.logDémarrage  Lorsque redirect_stderr est activé, cette option définit les noms des journaux créés. La valeur est traitée comme un motif strftime, ce qui permet d'utiliser les caractères d'échappement % pour préciser des noms dépendant du temps. En l'absence de caractères d'échappement, PostgreSQL ajoute l'époque d'ouverture du nouveau fichier. Ainsi, dans le cas où log_filename est server_log, le nom de fichier est server_log.1093827753 pour un journal démarrant à 19:02:33 le dimanche 29 août 2004 (MST). Nom de fichier pour chaque segment de journal rotationnel, avec les caractères d'échappement. La valeur par défaut peut convenir à tout DBA. Si les journaux ne grandissent pas trop, il peut être plus simple de n'ajouter que la date. Une autre possibilité consiste à n'utiliser que l'heure ou le jour de la semaine pour restreindre le nombre de journaux. Voir log_truncate plus bas.
log_rotation_age0 à Int Max1440Démarrage  lorsque redirect_stderr est activé, cette option précise la durée de vie maximale d'un journal individuel. Après ce temps (en minutes), un nouveau fichier est créé. La positionner à zéro désactive la création de nouveaux journaux en fonction du temps. La valeur par défaut (24 heures) convient pour la plupart des installations.
log_rotation_size0 à Int Max10240Démarrage  Lorsque redirect_stderr est activé, cette option précise la taille maximale d'un journal individuel. Lorsqu'il atteint cette taille (en Ko), un nouveau fichier est créé. La positionner à zéro désactive la création de fichiers en fonction de la taille. La valeur par défaut (10 Mo) convient pour la plupart des installations.
log_truncate_on_rotationTrue, FalseFalseDémarrage  Lorsque redirect_stderr est activé, cette option impose à PostgreSQL de tronquer (réécrire), plutôt qu'ajouter, tout journal du même nom. Néanmoins, la troncature n'intervient que lorsqu'un nouveau fichier est ouvert par rotation fondée sur le temps, pas au démarrage ou lors d'une rotation fondée sur la taille du fichier. Lorsqu'elle est positionnée à False, l'adjonction est faite au fichier existant, dans tous les cas. Ainsi, utiliser cette option en combinaison avec un log_filename tel que postgresql-%H.log consiste à engendrer des journaux de 24 heures et à les réécrire cycliquement. Ce paramètre peut être combiné à log_filename, plus haut, pour créer un remplacement continu de 7 jours, 24 heures (ou 60 minutes) des journaux.
syslog_facilityLOCAL#LOCAL0 Démarrage Lorsque la journalisation utilise syslog, cette option précise la « fonction » de syslog à utiliser. LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6 et LOCAL7 sont possibles. La valeur par défaut est LOCAL0. Plus d'informations dans la documentation du démon syslog du système. Pas de recommendation particulière.
syslog_ident postgresDémarrage  Lorsque la journalisation utilise syslog, cette option précise le nom à utiliser pour identifier les messages de PostgreSQL dans le journal. Par défaut, il s'agit de postgres. Lorsque plusieurs instances de PostgreSQL tournent sur le même serveur, la personnalisation de cette chaîne permet d'identifier les serveurs.

Quand journaliser ?

Tableau 17. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires

client_min_messages

log_min_messages

log_min_error_statement

debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic

notice

notice

panic

Exécution

Superutilisateur

Superutilisateur

-d x

Permet de contrôler le détail des messages écrits dans les journaux du serveur et sur le client. Les valeurs valides sont DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL et PANIC. Les dernières envoient moins de détails dans les journaux. La valeur par défaut est NOTICE. LOG n'a pas le même comportement ici que dans CLIENT_MIN_MESSAGES.

client_min_messages envoie vers la session cliente ; log_min_messages vers le journal et log_min_error_statement contrôle l'enregistrement des erreurs SQL dans le journal.

Les niveaux debug sont intéressant en phase de test des applications ; DEBUG1 est un paramètre statisfaisant pour les problèmes généraux. NOTICE convient en environnement de production. Les environnements éprouvés peuvent passer à ERROR ou FATAL.

Le coût en est une plus grande occupation du disque et un léger impact sur les performances (généralement inférieur à 5%). Néanmoins, l'impact sur les performances augmente considérablement si les journaux se situent sur le même disque/la même baie que les WAL de la base, puisqu'une forte sortie de débogage ampute l'activité E/S de la base. L'impact de DEBUG5 sur un système monodisque fortement transactionnel peut être très élevé. Cet avertissement vaut pour toutes les options de journalisation vues plus haut.

log_error_verbosityterse, default, verbosedefaultSuperutilisateur  Contrôle la quantité de détails écrit dans les journaux pour chaque message. Les valeurs valides sont TERSE, DEFAULT et VERBOSE. Chacune ajoute des champs aux messages affichés. Le paramétrage utilisé dépend de l'environnement de production et des outils de surveillance des journaux.
log_min_duration_statement-1 to Int Max-1Superutilisateur  Définit le temps d'exécution minimum (en millisecondes) d'une requête avant sa journalisation. Toutes les requêtes SQL qui durent au moins aussi longtemps sont journalisées, avec le temps d'exécution. Lorsque le paramètre est positionné à 0, toutes les requêtes, et leur temps d'exécution, sont écrites. -1 (valeur par défaut) désactive la fonctionnalité. Ainsi, positionné à 250, toutes les requêtes qui durent 250 ms ou plus sont journalisées. Ce paramètre peut être utilisé pour trouver les requêtes d'une application qui n'ont pas été optimisées. Ce paramètre est très utile pour l'optimisation secondaire de bases de données. Lorsque les problèmes d'indexation et de performance sont résolus, log_min_duration_statement permet de tracer les requêtes les plus lentes (et probablement celles qui ne fonctionnent toujours pas).
silent_modeTrue, FalseFalseDémarrage  Le serveur tourne en silence. Lorsque cette option est positionnée, le serveur tourne en tâche de fond, les terminaux de contrôle étant dissociés (même résultat que lors de l'utilisation de l'option -S du postmaster). Les sortie et erreur standard du serveur sont redirigées vers /dev/null, tout message qui leur est envoyé est de fait perdu. Il est préférable de ne pas utiliser cette option si ni la journalisation par syslog, ni redirect_stderr ne sont activés, les messages d'erreur n'étant alors plus visibles. La documentation couvre assez bien ce sujet.

Que journaliser ?

Tableau 18. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires

debug_print_parse

debug_print_rewritten

debug_print_plan

debug_pretty_print

true, falsefalse   Ces drapeaux activent la sortie d'informations de débogage vers les journaux du serveur. Pour chaque requête exécutée, écrit le texte de la requête, l'arbre syntaxique résultant, la sortie du rédacteur de requête ou le plan d'exécution. DEBUG_PRETTY_PRINT indente les affichages pour faciliter la lecture, au prix d'une écriture plus longue. Utile pour détecter les requêtes lentes, sous réserve de réussir à parcourir un journal volumineux. Particulièrement utile dans un mode interactif de surveillance des journaux lorsque les procédures stagnent ; il est parfois possible de voir à quel endroit la procédure stagne (parfois, cela n'est pas possible, parce que le journal attend une information de la base).

log_connections

log_disconnections

true, falsefalseDémarrage  log_connections envoie une ligne au journal du serveur détaillant toute connexion réussie. log_disconnections envoie une ligne similaire à log_connections au journal du serveur à la fin de la session. Il ajoute également la durée de la session. Cette journalisation est essentielle pour toute application sécurisée.
log_hostnametrue, falsefalseDémarrage  Par défaut, les journaux de connexions ne présentent que l'adresse IP de provenance de la connexion. En activant cette option, le nom de l'hôte est également présenté. Mais, en fonction du paramétrage de la résolution de noms, cela peut induire une baisse non négligeable des performances. Cela peut être utile pour la gestion des débogages et de la sécurité, mais dans le cas où le DNS n'est pas local, les nouvelles connexions peuvent se trouver considérablement ralenties.
log_statementNone, DDL, Mod, AllFalseSuperutilisateur  Contrôle les instructions à journaliser. Les valeurs valides sont none, ddl, mod et all. ddl enregistre toutes les commandes de définition des données, telles que CREATE, ALTER et DROP. mod enregistre toutes les instructions ddl, ainsi que INSERT, UPDATE, DELETE, TRUNCATE et COPY FROM. PREPARE et EXPLAIN ANALYZE sont également enregistrées si les commandes qu'elles contiennent sont d'un type approprié. Ce paramètre a été amélioré et étendu par la possibilité de n'enregistrer que les modifications de la base ou simplement les mises à jour/insertions/suppressions (updates/inserts/deletes). La documentation fournit les limitations de cette fonctionnalité.
log_durationTrue, FalseFalseSuperutilisateur  La durée de toute instruction réalisée qui satisfait log_statement est enregistrée. Il est recommandé, lorsque cette option est utilisée sans syslog d'enregistrer également le PID ou l'ID de session à l'aide de log_line_prefix. Cela permet ainsi de faire le lien entre l'instruction et la durée à l'aide du PID ou de l'ID de session. Essentiel lors de l'optimisation de premier niveau de la base. L'outil PQA de traitement de journaux, par exemple, nécessite log_statement et log_duration pour extraire la liste des requêtes les plus lentes et les plus fréquentes. Ne prend effet que si log_statement est positionné au minimum à « DDL ».
log_line_prefixSpecial''Superutilisateur  Chaîne de style printf écrite au début de chaque ligne de journal. Par défaut, une chaîne vide. Tout caractère d'échappement reconnu est remplacé comme indiqué dans la documentation - tout autre caractère qui ressemblerait à un caractère d'échappement est ignoré. Les autres caractères sont directement copiés dans la ligne du journal. Certains caractères d'échappement ne sont interprétés que par les processus de session et ne s'appliquent pas aux processus d'arrière-plan tel que postmaster. Syslog produit ces propres estampilles temporelles et ID de processus. Il est alors préférable de ne pas utiliser ces caractères en combinaison avec syslog.

Remplace log_pid, log_source_port, log_timestamp et un ensemble de techniques de journalisation maison pour fournir le niveau de détails souhaité pour chaque ligne du journal.

Ainsi, pour diagnostiquer un problème de verrou mort, on peut utiliser « %t %p %u %d %x » pour récupérer l'information intéressante. Il ne fait aucun doute que les futurs outils de journalisation contiendront les formats spécifiques à ces informations, mais à ce jour, aucun ne le propose.

Statistiques

Journalisation des statistiques

Tableau 19. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires

log_parser_stats

log_planner_stats

log_executor_stats

log_statement_stats

true, falsefalseSuperutilisateur

-tpa

-tpl

-te

-s

Pour chaque requête, les statistiques de performance du module respectif sont écrites dans le journal du serveur. C'est un outil d'analyse redoutable. log_statement_stats fournit l'ensemble des statistiques de l'instruction, les autres des statistiques par état. log_statement_stats ne peut être activé en même temps que les autres options. Toutes sont désactivées par défaut. Seuls les superutilisateurs peuvent désactiver ces options si elles sont activées par l'administrateur. En l'absence d'un outil performant d'analyse des journaux engendrés par ces options, le volume important des journaux en limite l'utilité.

Statistiques de requête et d'index

Tableau 20. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
stats_start_collectortrue, falsetrueDémarrage  Contrôle le démarrage par le serveur du processus de collecte des statistiques. Sauf si les quelques 5% de surcroit d'activité créés par le collecteur de statistiques sont critiques pour le système, il n'est pas inutile de d'activer au minimum start_collector et stats_command_string.
stats_reset_on_server_start trueDémarrage  Activé, les statistiques sont réinitialisées au redémarrage du serveur. Désactivé, les statistiques sont cumulatives. Si les redémarrages de la base font partie du plan de maintenance, il est probablement utile de désactiver cette option ; cela permet d'accumuler une quantité utile de statistiques. Dans le cas contraire, la laisser active.
stats_command_string falseDémarrage  Active la collecte de statistiques sur la commande en cours d'exécution, avec sa date de démarrage, pour chaque session. Cette option est désactivée par défaut. Même activée, l'information qu'elle produit n'est pas visible par tous les utilisateurs, mais uniquement par les superutilisateurs et l'utilisateur propriétaire de la session. Cela ne représente donc pas un risque de sécurité. Ces données sont accessibles au travers de la vue système pg_stat_activity. Cette option permet d'utiliser la vue pg_stat_activity pour analyser les requêtes en cours. L'aide apportée à la résolution de problèmes est inestimable pour la plupart des DBA.
stats_row_level falseDémarrage  Active la collecte des statistiques d'activité de la base au niveau des lignes. Désactivée par défaut, cette option produit des données accessibles par les vues systèmes de la famille pg_stat et pg_statio. Cette option active la collecte de quelques statistiques sur l'utilisation des index et tables. D'une importance vitale lors de l'optimisation initiale de la base, elle l'est beaucoup moins en production et peut alors être désactivée.
stats_block_level falseDémarrage  Active la collecte des statistiques d'activité de la base au niveau des blocs. Désactivée par défaut. Fournit des statistiques de niveau bloc, utiles pour surveiller les E/S et les performance du cache. Cela permet de configurer les variables système et matériel. Là aussi, ce paramètre peut être activé lors des tests et désactivé en production.

Valeurs par défaut des connexions client

Comportement des instructions

Tableau 21. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
search_pathpath'$user,public'Exécution 

Cette variable précise l'ordre de consultation des schémas lorsqu'il est fait référence à un objet (table, type de données, fonction...) par un nom simple, sans composante schéma. Lorsque plusieurs objets portent le même nom dans différents schémas, le premier trouvé dans l'ordre du chemin est utilisé. Il ne peut être fait référence à un objet qui ne se trouve dans aucun des schémas du chemin qu'en qualifiant son nom avec le schéma qui le contient (schema.objet).

La valeur de search_path est une liste de noms de schéma séparés par des virgules. Si un des éléments de la liste est la valeur spéciale $user, alors le schéma dont le nom correspond à la valeur retournée par SESSION_USER est utilisé s'il existe (sinon, $user est ignoré). Le schéma du catalogue système, pg_catalog, est toujours parcouru, qu'il soit ou non mentionné dans le chemin.

Cette variable doit être modifiée après la création du schéma de la base, mais pas nécessairement dans ce fichier. La définition, par exemple, de search_path par utilisateur se fait à l'aide de la commande ALTER USER, et non par le fichier de configuration générale.

Lorsque plusieurs schémas doivent être visibles par tous les utilisateurs, il est nécessaire de les ajouter tous au search_path du fichier postgresql.conf.

default_tablespace ''Exécution  Cette variable précise le tablespace par défaut dans lequel seront créés tous les objets (tables et index) créés à l'aide d'une commande CREATE qui n'explicite pas le tablespace. La valeur peut être le nom d'un tablespace ou une chaîne vide signifiant l'utilisation du talespace par défaut de la base utilisée. Si la valeur ne correspond à aucun tablespace, PostgreSQL utilise automatiquement le tablespace par défaut de la base. Il est peu vraisemblable que le tablespace soit précisé dans le fichier de configuration. La documentation de la commande ALTER DATABASE explique comment préciser le tablespace d'une base.
check_function_bodiesTrue, FalseTrueExécution  Ce paramètre est normalement positionné à true. Dans le cas contraire, la validation du corps d'une fonction lors de sa création avec CREATE FUNCTION est désactivée. Cela peut s'avérer utile pour éviter les problèmes (références, par exemple) lors de la restauration des définitions de fonctions à partir d'une sauvegarde. Comme pour les autres paramètres, il ne faut pas le positionner dans le fichier de configuration dans le cadre d'une utilisation générale.
default_transaction_isolationread committed, serializable'read committed'Exécution  Chaque transaction SQL dispose d'un niveau d'isolation. Il peut être « read uncommitted » (lecture des entrées non validées), « read committed » (lecture des entrées validées), « repeatable read » (lecture reproductible) ou « serializable » (sérialisable). Ce paramètre contrôle le niveau d'isolation par défaut de chaque nouvelle transaction. La valeur par défaut est « read committed ». La valeur par défaut, ici, est en accord avec le comportement MVCC. « serializable » est surtout utile lors du lancement de procédures longues et éventuellement successives, ou lorsqu'une mise à jour entraîne un risque conséquent et régulier de verrou mort. En environnement fortement concurrentiel (nombreux utilisateurs), son utilisation peut avoir des conséquences sur les performances, puisque de nombreuses transactions doivent attendre la fin de la transaction sérialisée. Dans le cas d'une base mono-utilisateur, cela n'a que peu de conséquence. Dans tous les cas, il est préférable de positionner ce paramètre à l'exécution plutôt que dans le fichier de configuration.
default_transaction_read_onlytrue, falsefalseExécution  Une transaction SQL en lecture seule ne peut modifier une table temporaire. Ce paramètre contrôle le statut de lecture seule par défaut de toute nouvelle transaction. Par défaut à false (lecture/écriture). Éventuellement utile pour les connexions individuelles. En revanche, le positionner dans le fichier de configuration n'est pas d'un grand intérêt, à moins de vouloir forcer un grand nombre d'utilisateurs en mode lecture seule (à supposer également qu'ils ne sachent pas se servir de la commande SET).
statement_timeout0 to Int Max0Exécution  Interrompt toute instruction qui dure plus que le nombre de millisecondes précisées. La valeur zéro désactive le chronomètre. Conçu pour faciliter le fonctionnement d'une application lorsque les utilisateurs peuvent exécuter des requêtes qui monopolisent la CPU pendant plusieurs minutes, telles que les applications qui autorisent les requêtes dynamiques. Positionner ce paramètre à une valeur finie peut empêcher les utilisateurs de monopoliser les ressources, mais il faut alors gérer l'exception, qui est la même erreur que « query cancelled by user » (requête annulée par l'utilisateur).

Locale et formatage

Tableau 22. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires

datestyle

timezone

australian_timezones

 

'iso, us'

unknown

false

Yes 

Positionne le format d'affichage des dates et les règles d'interprétation des dates ambigues en entrée .

Positionne le fuseau horaire pour l'affichage et l'interprétation des estampilles temporelles. Le comportement par défaut utilise le fuseau horaire fourni par le système.

Positionné à true, CST, EST et SAT sont interprétés comme des fuseaux horaires australiens et non comme des fuseaux North American Central/Eastern et samedi (Saturday).

Permet de modifier l'affichage par défaut des dates et l'interprétation des fuseaux horaires pour coller aux standard et à la localisation de l'utilisateur.
extra_float_digits-14 à 20Yes  Ce paramètre permet d'ajuster le nombre de chiffres affichés pour les valeurs à virgule flottante, ce qui inclut les float4, float8 et les types de données géométriques. La valeur du paramètre est ajoutée au nombre standard de chiffres (FLT_DIG ou DBL_DIG, selon les cas). La valeur 2, la plus haute possible, permet d'inclure les chiffres partiellement significatifs ; cela est particulièrement utile pour copier des flottants dont la restauration doit être exacte. Les valeurs négatives permettent de supprimer les chiffres non souhaités.  

lc_messages

lc_monetary

lc_time

lc_numeric

Dépendant du systèmeSpécialOui  Positionne la locale à utiliser pour le formatage des messages d'erreur, des valeurs monétaires, des valeurs de temps et des valeurs numériques. Les valeurs acceptables dépendent du système. Lorsque la valeur est constituée d'une chaîne vide (valeur par défaut), la valeur est héritée de l'environnement d'exécution du serveur. Ces paramètres sont positionnés par le script initdb lors de la création du répertoire PGDATA. Correspond en général à la langue, la monnaie... de l'utilisateur. Pour certaines installations, la locale 'C' est utilisée.
client_encodingDépendant du système d'exploitationsql_asciiDémarrage  Positionne le codage côté client pour les jeux de caractères multi-octet. Par défaut, le codage de la base est utilisé. Généralement ignoré au profit du codage de la base. Ne doit être positionné par client que dans les applications multilingues, ce qui requiert un effort considérable de gestion des différents codages.

Autres valeurs par défaut

Tableau 23. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
explain_pretty_printTrue,FalseFalseExécution  Règle le format de sortie d'EXPLAIN VERBOSE, indenté ou non, pour l'affichage des arbres de requête détaillés. Lors de l'utilisation de EXPLAIN VERBOSE, pretty_print est essentiel à la facilité de lecture. Le paramètre peut alors être positionné à true. Quoiqu'il en soit, VERBOSE est rarement nécessaire.
dynamic_library_pathpath'$libdir'Superutilisateur  Si un module chargeable dynamiquement doit être ouvert et que le nom ne précise pas le répertoire (i.e. le nom ne contient pas de slash), le système recherche le fichier dans ce chemin. Le nom utilisé est celui précisé par la commande CREATE FUNCTION ou LOAD. Peut être positionné par le superutilisateur.

Gestion des verrous

Tableau 24. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
deadlock_timeout1 à Int Max1000Non  Temps d'attente, en millisecondes, avant de vérifier qu'un verrou est en condition de verrou mort. La recherche de verrou mort étant relativement lente, le serveur ne la lance pas à chaque fois qu'il attend le relâchement d'un verrou. Les auteurs estiment (de façon optimiste ?) que les verrous morts ne sont pas légion en production et de fait patientent quelques instants après la pose d'un verrou avant de rechercher les verrous morts. L'augmentation de cette valeur réduit le temps perdu à rechercher inutilement les verrous morts, mais retarde la découverte des erreurs de verrous réellement morts. La valeur par défaut est 1000 (i.e., une seconde), ce qui est probablement la plus petite valeur acceptable en production. Sur un serveur en charge, cette valeur peut être augmentée. Dans l'idéal, la valeur dépasse le temps habituel d'une transaction, de façon à accroître les chances que le verrou soit relâché avant que le serveur ne recherche les verrous morts. Pas de recommendations autres que celles dans la documentation.
max_locks_per_transaction10 à Int Max64No  La table partagée de verrous est dimensionnée d'après l'hypothèse qu'au maximum max_locks_per_transaction * max_connections objets distincts sont verrouillés au même moment. La valeur par défaut, 64, qui a prouvé au fil des ans qu'elle était suffisante, peut toutefois être augmentée lorsque différents clients attaquent de nombreuses tables au sein d'une même transaction. L'option ne peut être positionnée qu'au démarrage du serveur. Parfois, il peut être nécessaire d'augmenter ce paramètre pour les bases à schéma étoilé, qui contiennent des centaines de tables parcourues. Il est préférable, toutefois, d'agir en réponse à une erreur que de chercher à l'anticiper.

Compatibilité entre versions et plateformes

Versions antérieures de PostgreSQL

Tableau 25. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
add_missing_fromtrue, falsetrueExécution  Autorise le planificateur à « ajouter les clauses FROM manquantes » (add missing from clause) lorsqu'une table est omise dans la requête. Sera positionné à false dans les prochaines versions. Lorsqu'il est positionné à true, les tables référencées par une requête sont automatiquement ajoutées à la clause FROM si elles n'y figurent pas déjà. La valeur par défaut, true, assure la compatibilité avec les versions précédentes de PostgreSQL. Toutefois, ce comportement ne respecte pas le standard SQL. De plus, de nombreuses personnes détestent cette option parce qu'elle risque de masquer des erreurs (référencer une table au lieu de son alias, par exemple). Positionné à false, il permet de respecter le comportement prescrit par le standard, à savoir rejeter les références aux tables qui n'apparaissent pas dans la clause FROM. Toujours positionner ce paramètre à false. Positionné à true, la moindre erreur de référence d'un alias de table peut résulter en une jointure non contrainte et conduire à une requête folle qui va monopoliser les ressources système. Heureusement, il sera positionné à false par défaut dans les prochaines versions.
regex_flavoradvanced, extended, basicadvancedExécution  La « flavor » d'une expression rationnelle peut être positionnée à advanced (avancée), extended (étendue) ou basic (basique). La valeur par défaut est advanced. L'option extended peut être utile pour une compatibilité exacte avec les versions pre-7.4 de PostgreSQL. Le réglage correspond aux habitudes de l'utilisateur concernant le comportement des expressions rationelles. Les programmeurs Perl, Java ou d'autres langages sont plus familiers avec advanced ; les autres utilisateurs peuvent préférer la syntaxe moins complexe de basic. Ce paramètre ne devra pas être changé après des tests concluants sur la base, puisqu'il peut influer sur les résultats des requêtes.
sql_inheritancetrue, falsetrueExécution  Contrôle la sémantique de l'héritage, en particulier l'inclusion par défaut ou non des tables filles par diverses commandes. Elles ne l'étaient pas dans les versions antérieures à la 7.1. Pour retrouver l'ancien comportement, ce paramètre est positionné à off. Toutefois, sur le long terme, il est préférable de modifier les applications afin d'utiliser le mot-clé ONLY pour exclure les tables filles. Nécessaire uniquement pour les personnes effectuant une montée de version depuis des applications 7.0.
default_with_oidsTrue, falseTrueExécution  Contrôle l'inclusion de colonnes OID dans les tables nouvellement créées par CREATE TABLE et CREATE TABLE AS, lorsque ni WITH OIDS ni WITHOUT OIDS ne sont précisés. Détermine également si les OID doivent être inclus dans les tables créées par SELECT INTO. La valeur par défaut sous PostgreSQL 8.0.0 est true. C'est également le comportement des versions précédentes de PostgreSQL. Toutefois, il est fortement conseillé de ne considérer pas que les tables contiennent des OID par défaut. Cette option sera probablement positionnée à false par défaut dans les prochaines versions. Dans une base qui contient de très grosses tables, il peut être utile de positionner ce paramètre à false. Cela permet d'économiser environ 8 octets par ligne, ce qui peut faire une différence sur plusieurs millions de lignes.

Compatibilité entre plateformes et clients

Tableau 26. 

ParamètreÉchelleValeur par défautPositionné au-oCe que dit la documentationCommentaires
transform_null_equalstrue, falsefalseYes  Lorsque ce paramètre est positionné, les expressions de la forme expr = NULL (ou NULL = expr) sont traitées comme expr IS NULL, c'est-à-dire qu'elles retournent true si expr s'évalue à null et false dans le cas contraire. Le comportement correct de expr = NULL est de toujours retourner null (unknown).  
custom_variable_classes ''Démarrage  Précisent un ou plusieurs noms de classe à utiliser pour les variables personnalisées, sous la forme d'une liste séparée par des virgules. Une variable personnalisée est une variable que PostgreSQL ne connaît pas mais utilisée par certains modules additifs. Le nom de telles variables doit être constitué d'un nom de classe, un point et un nom de variable. custom_variable_classes précise tous les noms de classes utilisables dans une installation particulière. Les modules additifs qui requierent ce paramètre (PL/java, par exemple) doivent expliquer comment le positionner.