PG_VERSION est un fichier. Il contient en texte lisible la version majeure devant être utilisée pour accéder au répertoire (par exemple 9.6). On trouve ces fichiers PG_VERSION à de nombreux endroits de l’arborescence de PostgreSQL, par exemple dans chaque répertoire de base du répertoire PGDATA/base/ ou à la racine de chaque tablespace.

Le fichier postmaster.pid est créé au démarrage de PostgreSQL. PostgreSQL y indique le PID du processus maître sur la première ligne, l’emplacement du répertoire des données sur la deuxième ligne et des informations sur le segment de mémoire partagée sur la troisième ligne. Par exemple :

postgres@pegase:~/10/data$ cat /var/lib/postgresql/10/data/postmaster.pid
7771
/var/lib/postgresql/10/data
1503584802
5432
/tmp
localhost
  5432001  54919263
ready   

$ ps -HFC postgres
UID  PID    SZ     RSS PSR STIME TIME   CMD
pos 7771 0 42486 16536   3 16:26 00:00 /usr/local/pgsql/bin/postgres
                                               -D /var/lib/postgresql/10/data
pos 7773 0 42486  4656   0 16:26 00:00  postgres: checkpointer process
pos 7774 0 42486  5044   1 16:26 00:00  postgres: writer process
pos 7775 0 42486  8224   1 16:26 00:00  postgres: wal writer process
pos 7776 0 42850  5640   1 16:26 00:00  postgres: autovacuum launcher process
pos 7777 0  6227  2328   3 16:26 00:00  postgres: stats collector process
pos 7778 0 42559  3684   0 16:26 00:00  postgres: bgworker: logical replication launcher

$ipcs -p |grep  7771
54919263   postgres   7771       10640   

$ipcs  | grep 54919263
0x0052e2c1 54919263   postgres   600        56         6   

Le processus maître de cette instance PostgreSQL a comme PID le 7771. Ce processus a bien réclamé une sémaphore d’identifiant 54919263. Cette sémaphore correspond à des segments de mémoire partagée pour un total de 56 octets. Le répertoire de données se trouve bien dans /var/lib/postgresql/10/data.

Le fichier postmaster.pid est supprimé lors de l’arrêt de PostgreSQL. Cependant, ce n’est pas le cas après un arrêt brutal. Dans ce genre de cas, PostgreSQL détecte le fichier et indique qu’il va malgré tout essayer de se lancer s’il ne trouve pas de processus en cours d’exécution avec ce PID.

Quant au fichier postmaster.opts, il contient les arguments en ligne de commande correspondant au dernier lancement de PostgreSQL. Il n’est jamais supprimé. Par exemple :

$ cat $PGDATA/postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/var/lib/postgresql/10/data"

• Ces paramètres sont en lecture seule, mais peuvent être consultés par la commande SHOW, ou en interrogeant la vue pg_settings. On peut aussi obtenir l’information via la commande pg_controldata.
• Ils sont fixés à la compilation du moteur.
• block_size est la taille d’un bloc de données de la base, par défaut 8192 octets.
• wal_block_size est la taille d’un bloc de journal, par défaut 8192 octets.
• segment_size est la taille maximum d’un fichier de données, par défaut 1 Go.
• wal_segment_size est la taille d’un fichier de journal de transactions, par défaut 16 Mo.

C’est le fichier le plus important. Il contient le paramétrage de l’instance. Le format est un paramètre par ligne, sous le format clé = valeur. Les commentaires commencent par « # » (croisillon).

Par exemple :

listen_addresses = 'localhost'

Ainsi, l’ordre des surcharge est le suivant :

paramètre par défaut
  -> postgresql.conf
    -> option de pg_ctl / postmaster
      -> paramètre par base
        -> paramètre par rôle
          -> paramètre base+rôle
            -> paramètre de session

La meilleure source d’information est la vue pg_settings :

SELECT name,source,context,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('client_min_messages', 'wal_keep_segments', 'wal_segment_size');

        name         | source  | context  | setting | boot_val | reset_val
---------------------+---------+----------+---------+----------+-----------
 client_min_messages | session | user     | debug   | notice   | notice
 wal_keep_segments   | default | sighup   | 0       | 0        | 0
 wal_segment_size    | default | internal | 2048    | 2048     | 2048
(3 rows)

On constate par exemple que dans la session ayant effectué la requête, client_min_messages a été modifié à la valeur debug. On peut aussi voir le contexte dans lequel le paramètre est modifiable : le client_min_messages est modifiable par l’utilisateur dans sa session. Le wal_keep_segments seulement par sighup, c’est-à-dire par un pg_ctl reload, et le wal_segment_size n’est pas modifiable, c’est un paramètre interne.

De nombreuses autres colonnes sont disponibles dans pg_settings, comme une description détaillée du paramètre, l’unité de la valeur, ou le fichier et la ligne d’où proviennent le paramètre. Depuis la version 9.5, une nouvelle colonne est apparue, nommée pending_restart. Elle indique si un paramètre a été modifié mais nécessite un redémarrage pour être appliqué.

On peut aussi inclure d’autres fichiers dans le fichier postgresql.conf, par la syntaxe

include 'filename'

Ce fichier est alors inclus à l’endroit où la directive include est positionnée. Si le fichier n’existe pas, une erreur FATAL est levée. La clause include_if_exists ne fait que notifier l’absence du fichier, mais poursuit la lecture du fichier de base.

Parfois, il n’est pas facile de trouver l’emplacement de ce fichier. Le plus simple dans ce cas est de se connecter à une base et de regarder la valeur du paramètre config_file :

postgres=# SHOW config_file;
               config_file
------------------------------------------
 /var/lib/postgresql/10/data/postgresql.conf
(1 row)

À partir de la version 9.5, il existe aussi la vue pg_file_settings. Elle indique la configuration présente dans les fichiers de configuration. Elle peut être utile lorsque la configuration est réalisée dans plusieurs fichiers. Par exemple, suite à un ALTER SYSTEM, les paramètres sont ajoutés dans postgresql.auto.conf mais un rechargement de la configuration n'est pas forcément suffisant pour qu'ils soient pris en compte :

postgres=# ALTER SYSTEM SET work_mem TO '16MB' ;
ALTER SYSTEM

postgres=# ALTER SYSTEM SET max_connections TO 200 ;
ALTER SYSTEM

postgres=# select pg_reload_conf() ;
 pg_reload_conf
----------------
 t
(1 ligne)

postgres=# SELECT * FROM pg_file_settings
WHERE name IN ('work_mem','max_connections')
ORDER BY name ;

-[ RECORD 1 ]------------------------------------------------
sourcefile | /var/lib/postgresql/10/data/postgresql.conf
sourceline | 65
seqno      | 2
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]------------------------------------------------
sourcefile | /var/lib/postgresql/10/data/postgresql.auto.conf
sourceline | 3
seqno      | 14
name       | max_connections
setting    | 200
applied    | f
error      | setting could not be applied
-[ RECORD 3 ]------------------------------------------------
sourcefile | /var/lib/postgresql/10/data/postgresql.auto.conf
sourceline | 4
seqno      | 15
name       | work_mem
setting    | 16MB
applied    | t
error      |

L’authentification est paramétrée au moyen du fichier pg_hba.conf. Dans ce fichier, pour une tentative de connexion à une base donnée, pour un utilisateur donné, pour un transport (IP, IPV6, Socket Unix, SSL ou non), et pour une source donnée, ce fichier permet de spécifier le mécanisme d’authentification attendu.

Si le mécanisme d’authentification s’appuie sur un système externe (LDAP, Kerberos, Radius…), des tables de correspondances entre utilisateur de la base et utilisateur demandant la connexion peuvent être spécifiées dans pg_ident.conf.

Ces noms de fichiers ne sont que les noms par défaut. Ils peuvent tout à fait être remplacés en spécifiant de nouvelles valeurs de hba_file et ident_file dans postgresql.conf.

Un tablespace, vu de PostgreSQL, est un espace de stockage des objets (tables et indexes principalement).

Vu du système d’exploitation, il s’agit d’un répertoire. Il n’aura d’intérêt que s’il est placé sur un système de fichiers différent du système de fichiers contenant PGDATA.

Les tablespaces sont déclarés dans la table système pg_tablespace. Ils sont aussi stockés au niveau du système de fichiers, sous forme de liens symboliques (ou de reparse points sous Windows), dans le répertoire PGDATA/pg_tblspc.

Le paramètre default_tablespace permet d’utiliser un autre tablespace par défaut. On peut aussi définir un ou plusieurs tablespaces pour les opérations de tri, dans le paramètre temp_tablespaces (il faudra donner des droits dessus aux utilisateurs avec GRANT CREATE ON TABLESPACE ... TO ...).

Par ailleurs, on peut aussi modifier le tablespace par défaut par base avec les commandes CREATE DATABASE ou ALTER DATABASE :

CREATE DATABASE nom TABLESPACE 'tbl_nom';
-- ou
ALTER DATABASE nom SET default_tablespace TO 'tbl_nom';

Le paramétrage des journaux est très fin. Ils sont traités dans un prochain chapitre.

Si logging_collector est activé, c’est-à-dire que PostgreSQL collecte lui-même ses traces, l’emplacement de ces journaux se paramètre grâce à log_directory, le répertoire où les stocker, et log_filename, le nom de fichier à utiliser, ce nom pouvant utiliser des échappements comme %d pour le jour de la date, par exemple. Les droits attribués au fichier sont précisés par le paramètre log_file_mode.

Un exemple pour log_filename avec date et heure serait :

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

La liste des échappements pour le paramètre log_filename est disponible dans la page de manuel de la fonction strftime sur la plupart des plateformes de type UNIX.

PostgreSQL dispose de son propre mécanisme de cache. Toute donnée lue l’est de ce cache. Si la donnée n’est pas dans le cache, le processus devant effectuer cette lecture l’y recopie avant d’y accéder dans le cache.

L’unité de travail du cache est le bloc (de 8 ko par défaut) de données. C’est-à-dire qu’un processus charge toujours un bloc dans son entier quand il veut lire un enregistrement. Chaque bloc du cache correspond donc exactement à un bloc d’un fichier d’un objet. Cette information est d’ailleurs, bien sûr, stockée en en-tête du bloc de cache.

Tous les processus accèdent à ce cache unique. C’est la zone la plus importante, par la taille, de la mémoire partagée. Toute modification de données est tracée dans le journal de transaction, puis modifiée dans ce cache. Elle n’est pas écrite sur le disque par le processus effectuant la modification. Tout accès à un bloc nécessite la prise de verrous. Un pin lock, qui est un simple compteur, indique qu’un processus se sert du buffer, et qu’il n’est donc pas réutilisable. C’est un verrou potentiellement de longue durée. Il existe de nombreux autres verrous, de plus courte durée, pour obtenir le droit de modifier le contenu d’un buffer, d’un enregistrement dans un buffer, le droit de recycler un buffer… mais tous ces verrous n’apparaissent pas dans la table pg_locks, car ils sont soit de très courte durée, soit partagés (comme le pin lock). Il est donc très rare qu’ils soient sources de contention, mais le diagnostic d’une contention à ce niveau est difficile.

Les lectures et écritures de PostgreSQL passent toutefois toujours par le cache du système. Les deux caches risquent donc de stocker les mêmes informations. Les algorithmes d’éviction sont différents entre le système et PostgreSQL, PostgreSQL disposant de davantage d’informations sur l’utilisation des données, et le type d’accès qui y est fait. La redondance est donc habituellement limitée, et il est conseillé de restreindre shared_buffers à 1/4 de la mémoire totale du serveur au maximum.

Dimensionner correctement ce cache est important pour de nombreuses raisons.

Un cache trop petit :

• ralentit l’accès aux données, car des données importantes risquent de ne plus s’y trouver.
• force l’écriture de données sur le disque, ralentissant les sessions qui auraient pu n’effectuer que des opérations en mémoire.
• limite le regroupement d’écritures, dans le cas où un bloc viendrait à être modifié plusieurs fois.

Un cache trop grand :

• limite l’efficacité du cache système en augmentant la redondance de données entre les deux caches.
• peut ralentir PostgreSQL si shared_buffers dépasse 8 Go sous Linux, et 1 Go sous Windows.

Ce paramétrage du cache est malgré tout moins critique que sur de nombreux autres SGBD : le cache système limite la plupart du temps l’impact d’un mauvais paramétrage de shared_buffers, et il est donc préférable de sous-dimensionner shared_buffers que de le sur-dimensionner.

Un cache supplémentaire est disponible pour PostgreSQL : celui du système d’exploitation. Il est donc intéressant de préciser à PostgreSQL la taille approximative du cache, ou du moins de la part du cache qu’occupera PostgreSQL. Le paramètre effective_cache_size n’a pas besoin d’être très précis, mais il permet une meilleure estimation des coûts par le moteur. On le paramètre habituellement aux alentours des 2/3 de la taille du cache du système d’exploitation, pour un serveur dédié.

Les principales notions à connaître pour comprendre le mécanisme de gestion du cache de PostgreSQL sont :

• Buffer pin : chaque processus voulant accéder à un buffer (un bloc du cache) doit d’abord en forcer le maintien en cache (to pin signifie épingler). Chaque processus accédant à un buffer incrémente ce compteur, et le décrémente quand il a fini. Un buffer dont le pin est différent de 0 ne peut donc pas être recyclé.
• Buffer dirty/clean : un buffer est soit propre (clean), soit sale (dirty). Il est sale si son contenu dans le cache ne correspond pas à son contenu sur disque (il a été modifié dans le cache, mais pas encore resynchronisé). La différence fondamentale est qu’un buffer propre peut être supprimé du cache sans plus de formalité, alors qu’un buffer sale doit d’abord être resynchronisé, ce qui est bien plus coûteux.
• Compteur d’utilisation : À chaque fois qu’un processus a fini de se servir d’un buffer (quand il enlève son pin), ce compteur est incrémenté (à hauteur de 5 dans l’implémentation actuelle). Un autre mécanisme décrémente ce compteur. Seuls un buffer dont ce compteur est à zéro peut voir son contenu remplacé par un nouveau bloc.
• Clocksweep : on parle aussi d’algorithme de balayage. Un processus ayant besoin de charger un bloc de données dans le cache doit trouver un buffer disponible. Soit il y a encore des buffers vides (cela arrive principalement au démarrage d’une instance), soit il faut libérer un buffer. L’algorithme clocksweep parcourt la liste des buffers de façon cyclique à la recherche d’un buffer dont le compteur d’utilisation est à zéro. Tout buffer visité voit son compteur décrémenté de un. Le système effectue autant de passes que nécessaire sur tous les blocs jusqu’à trouver un buffer à 0. Ce clocksweep est effectué par chaque processus, au moment où ce dernier a besoin d’un nouveau buffer.

Afin de limiter les attentes des sessions interactives, PostgreSQL dispose de deux processus, le Background Writer et le Checkpointer, tous deux essayant d’effectuer de façon asynchrone les écritures des buffers sur le disque. Le but étant que les temps de traitement ressentis par les utilisateurs soient les plus courts possibles, et d’essayer de lisser les écritures sur de plus grandes plages de temps (pour ne pas saturer les disques).

Le Background Writer est donc responsable d’un mécanisme :

Il anticipe les besoins de buffers des sessions. À intervalle régulier, il se réveille et synchronise un nombre de buffers proportionnel à l’activité sur l’intervalle précédent, dans ceux qui seront examinés par les sessions pour les prochaines allocations. Trois paramètres sont responsables de ce comportement :

• bgwriter_delay (défaut : 200 ms) : la fréquence à laquelle se réveille le Background Writer.
• bgwriter_lru_maxpages (défaut : 100) : le nombre maximum de pages pouvant être écrites sur chaque tour d’activité. Ce paramètre permet d’éviter que le Background Writer ne veuille synchroniser trop de pages si l’activité des sessions est trop intense : dans ce cas, autant les laisser effectuer elles-mêmes les synchronisations, étant donné que la charge est forte.
• bgwriter_lru_multiplier (defaut : 2) : le coefficient multiplicateur utilisé pour calculer le nombre de buffers à libérer par rapport aux demandes d’allocation sur la période précédente.
• bgwriter_flush_after (défaut : 512 ko sous Linux, 0 ailleurs) : à partir de quelle quantité de données écrites une synchronisation sur disque est demandée.

Le Checkpointer est responsable d’un autre mécanisme :

Il synchronise tous les buffers dirty lors des checkpoints. Son rôle est d’effectuer cette synchronisation, en évitant de saturer les disques. Le paramètre checkpoint_completion_target (par défaut 0.5) est la cible en temps entre deux checkpoints à viser pour l’accomplissement de chaque checkpoint. Par exemple, à 0.5, un checkpoint devrait se terminer à la moitié du temps séparant habituellement deux checkpoints. À la fois checkpoint_timeout et checkpoint_segments sont pris en compte dans le calcul, et le Background Writer tente de se terminer avant que checkpoint_timeout x checkpoint_completion_target et que checkpoint_segments x checkpoint_completion_target ne soient atteints. La synchronisation sur disque ne se fait qu’à partir d’une certaine quantité de données écrites, dépendant du paramètre checkpointer_flush_after (par défaut 256 ko sous Linux, 0 ailleurs).

Les valeurs par défaut sont la plupart du temps satisfaisantes. Toutefois, passer checkpoint_completion_target à 0.9 (sa valeur maximale) améliore parfois les performances, sans ralentissement à craindre.

Pour les paramètres bgwriter_lru_maxpages et bgwriter_lru_multiplier, lru signifie Least Recently Used que l’on pourrait traduire par « moins récemment utilisé ». Ainsi, pour ce mécanisme, le Background Writer synchronisera les pages qui ont été utilisées le moins récemment.

Les paramètres bgwriter_flush_after et checkpointer_flush_after permettent de configurer une synchronisation automatique sur disque plutôt que de laisser ce soin au système d’exploitation et à sa configuration.

Nous expliquerons plus en détail le fonctionnement d’un checkpoint dans le chapitre suivant.

Noter qu'avant la 9.2 les deux rôles étaient assurés par le seul Background Writer.

La journalisation, sous PostgreSQL, permet de garantir l’intégrité des fichiers, et la durabilité des opérations :

• L’intégrité : quoi qu’il arrive, excepté la perte des disques de stockage bien sûr, la base reste cohérente. Un arrêt d’urgence ne corrompra pas la base.
• Toute donnée validée (COMMIT) est écrite. Un arrêt d’urgence ne va pas la faire disparaître.

Pour cela, le mécanisme est relativement simple : toute modification affectant un fichier sera d’abord écrite dans le journal. Les modifications affectant les vrais fichiers de données ne sont écrites que dans les shared buffers. Elles seront écrites de façon asynchrone, soit par un processus recherchant un buffer libre, soit par le Background Writer.

Les écritures dans le journal, bien que synchrones, sont relativement performantes, car elles sont séquentielles (moins de déplacement de têtes pour les disques).

PostgreSQL trace les modifications de données dans les journaux WAL. Si le système ou la base sont arrêtés brutalement, il faut que PostgreSQL puisse appliquer le contenu des journaux non traités sur les fichiers de données. Il a donc besoin de savoir à partir d’où rejouer ces données. Ce point est ce qu’on appelle un checkpoint, ou point de reprise.

• Toute entrée dans les journaux est idempotente, c’est-à-dire qu’elle peut être appliquée plusieurs fois, sans que le résultat final ne soit changé. C’est nécessaire, au cas où la récupération serait interrompue, ou si un fichier sur lequel la reprise est effectuée était plus récent que l’entrée qu’on souhaite appliquer.
• Tout fichier de journal antérieur au dernier point de reprise valide peut être supprimé ou recyclé, car il n’est plus nécessaire à la récupération.
• PostgreSQL a besoin de fichiers de données qui contiennent toutes les données jusqu’au point de reprise. Ils peuvent être plus récents et contenir des informations supplémentaires, ce n’est pas un problème.
• Un checkpoint n’est pas un « instantané » cohérent de l’ensemble des fichiers. C’est simplement l’endroit à partir duquel on doit rejouer les journaux. Il faut donc pouvoir garantir que tous les buffers dirty au démarrage du checkpoint auront été synchronisés sur le disque quand le checkpoint sera terminé, et donc marqué comme dernier checkpoint valide. Un checkpoint peut donc durer plusieurs minutes, sans que cela ne bloque l’activité.
• C’est le processus Checkpointer qui est responsable de l’écriture des buffers devant être synchronisés durant un checkpoint.

Les paramètres suivants ont une influence sur les checkpoints :

• min_wal_size : quantité de WAL conservés pour le recyclage. Par défaut 80 Mo ;
• max_wal_size : quantité maximale de WAL avant un checkpoint. Par défaut 1 Go (Attention : le terme peut porter à confusion, le volume de WAL peut dépasser max_wal_size en cas de forte activité, ce n’est pas une valeur plafond.) ;
• checkpoint_timeout : le temps maximum en secondes entre deux checkpoints. Par défaut, 300 secondes ;
• checkpoint_completion_target : la fraction de l’espace entre deux checkpoints que doit prendre l’écriture d’un checkpoint. Par défaut, 0.5, ce qui signifie qu’un checkpoint se termine quand la moitié de max_wal_size a été écrit ou la moitié de checkpoint_timeout est écoulé (c’est le premier atteint qui prime). La valeur préconisée est 0.9, car elle permet de lisser davantage les écritures dues aux checkpoints dans le temps ;
• checkpoint_warning : si deux checkpoints sont rapprochés d’un intervalle de temps inférieur à celui-ci, un message d’avertissement sera écrit dans le journal. Par défaut, 30 secondes ;
• checkpointer_flush_after : quantité de données écrites à partir de laquelle une synchronisation sur disque est demandée.

La journalisation s’effectue par écriture dans les journaux de transactions. Toutefois, afin de ne pas effectuer des écritures synchrones pour chaque opération dans les fichiers de journaux, les écritures sont préparées dans des tampons (buffers) en mémoire. Les processus écrivent donc leur travail de journalisation dans ces buffers. Ces buffers, ou WAL buffers, sont vidés quand une session demande validation de son travail (COMMIT), ou quand il n’y a plus de buffer disponible.

Écrire un bloc ou plusieurs séquentiels de façon synchrone sur un disque a le même coût à peu de chose près. Ce mécanisme permet donc de réduire fortement les demandes d’écriture synchrone sur le journal, et augmente donc les performances.

Afin d’éviter qu’un processus n’ait tous les buffers à écrire à l’appel de COMMIT, et que cette opération ne dure trop longtemps, un processus d’arrière plan appelé WAL Writer écrit à intervalle régulier tous les buffers à synchroniser de WAL buffers.

Les paramètres relatifs à ceci sont :

• wal_buffers : la taille des WAL buffers. On conservera en général la valeur par défaut -1 qui lui affectera 1/32e de shared_buffers avec un maximum de 16 Mo (la taille d'un segment) ; sauf avant la 9.1 où on fournira cette valeur ; des valeurs supérieures peuvent être intéressantes pour les très grosses charges ;
• wal_writer_delay : l’intervalle auquel le WAL Writer se réveille pour écrire les buffers non synchronisés. Par défaut 200ms.
• wal_sync_method : l’appel système à utiliser pour demander l’écriture synchrone. Sauf très rare exception, PostgreSQL détecte tout seul le bon appel système à utiliser.
• synchronous_commit : la validation de la transaction en cours doit-elle déclencher une écriture synchrone dans le journal. C’est un paramètre de session, qui peut être modifié à la volée par une commande SET. Il est donc possible de le désactiver si on peut accepter une perte de données de 3 × wal_writer_delay ou de wal_writer_flush_after octets écrits. La base restera, quoi qu’il arrive, cohérente.
• full_page_writes : doit-on réécrire une image complète d’une page suite à sa première modification après un checkpoint ? Sauf cas très particulier, comme un système de fichiers Copy On Write comme ZFS ou Btrfs, ce paramètre doit rester à on.
• wal_compression (9.5+) : Active la compression des blocs complets enregistrés dans les journaux de transactions. Ce paramètre permet de réduire le volume de WAL et la charge en écriture sur les disques. Le rejeu des WAL est plus rapide, ce qui améliore la réplication et le processus de rejeu après un crash. Cette option entraîne aussi une augmentation de la consommation des ressources CPU pour la compression.
• commit_delay, commit_siblings : mécanisme de regroupement de transactions. Si on a au moins commit_siblings transactions en cours, attendre commit_delay (en microsecondes) au moment de valider une transaction pour permettre à d’autres transactions de s’y rattacher. Ce mécanisme est désactivé par défaut, et apporte un gain de performances minime.
• fsync : doit-on réellement effectuer les écritures synchrones ? S’il est passé à off, les performances sont très accélérées. Par contre, les données seront totalement corrompues si le serveur subit un arrêt d’urgence. Il n’est donc intéressant de le passer à off que très temporairement, pendant le chargement initial d’un cluster par exemple.

Les journaux permettent de rejouer, suite à un arrêt brutal de la base, toutes les modifications depuis le dernier checkpoint. Les journaux devenus obsolète depuis le dernier checkpoint sont à terme recyclés ou supprimés, car ils ne sont plus nécessaires à la réparation de la base.

Le but de l’archivage est de stocker ces journaux, afin de pouvoir rejouer leur contenu, non plus depuis le dernier checkpoint, mais depuis une sauvegarde, bien plus ancienne. Le mécanisme d’archivage permet donc de repartir d’une sauvegarde binaire de la base (les fichiers, pas un pg_dump), et de réappliquer le contenu des journaux archivés.

Ce mécanisme permet par ailleurs d’effectuer une sauvegarde base ouverte (c’est-à-dire pendant que les fichiers de la base sont en cours de modification). Il suffit de rejouer tous les journaux depuis le checkpoint précédent la sauvegarde jusqu’à la fin de la sauvegarde. L’application de ces journaux permet de rendre à nouveau cohérents les fichiers de données, même si ils ont été sauvegardés en cours de modification.

Ce mécanisme permet aussi de fournir une sauvegarde continue de la base. En effet, rien n’oblige à rejouer tout ce qui se trouve dans l’archive. Lors de la récupération, on peut préciser le point exact (en temps ou en numéro de transaction) où l’on souhaite arrêter la récupération. Une base en archivage peut donc être restaurée à n’importe quel point dans le temps. On parle aussi de Point In Time Recovery (récupération à un point dans le temps) dans la documentation.

Ce mécanisme permet enfin de créer une copie de la base de production, en transférant les fichiers archivés et en les appliquant sur cette seconde base. Il suffit de restaurer une sauvegarde à chaud de la base de production sur le serveur dit « de standby », puis d’appliquer les journaux sur cette base de standby au fur et à mesure de leur génération.

Tout ceci est revu dans le module « Point In Time Recovery ».

Les paramètres associés sont :

• wal_level : minimal (défaut jusqu'en version 9.6 incluse), replica (défaut en 10) ou logical, suivant ce qu’on souhaite faire des journaux : juste récupérer suite à un crash, les archiver ou alimenter une base de Hot Standby, ou avoir de la réplication logique.
• archive_mode : (off par défaut). L’archivage doit-il être activé.
• archive_command : la commande pour archiver un journal, accepte des « jokers », rsync très fortement recommandé.
• archive_timeout : au bout de combien de temps doit-on forcer un changement de journal, même s’il n’est pas fini, afin de l’archiver.

Le mécanisme de Streaming Replication a été une des nouveautés majeures de la version 9.0. Il n’est plus nécessaire d’attendre qu’un fichier de journal soit généré entièrement sur le maître pour appliquer son contenu sur l’esclave. Celui-ci se connecte sur le maître, et lui demande à intervalle régulier de lui envoyer tout ce qui a été généré depuis la dernière interrogation.

Voici les paramètres concernés :

• max_wal_senders : le nombre maximum de processus d’envoi de journaux démarrés sur le maître (1 par esclave).
• wal_keep_segments : le nombre de journaux à garder en ligne pour pouvoir les envoyer aux esclaves, même si ils ne sont plus nécessaires à la récupération sur le maître (ils sont antérieurs au dernier checkpoint) ;
• wal_sender_delay : la fréquence à laquelle le maître se réveille pour envoyer les nouvelles entrées de journal aux esclaves ;
• wal_level : doit être au moins à archive, pour pouvoir être envoyé aux esclaves.

Et un fichier recovery.conf correctement paramétré sur l’esclave, indiquant comment récupérer les fichiers de l’archive, et comment se connecter au maître pour récupérer des journaux.

L’autre importante nouveauté de PostgreSQL 9.0 a été la possibilité pour une base de Standby d’être accessible en lecture seule, alors qu’elle applique les journaux provenant de la base maître. Il est possible que des modifications devant être appliquées par les journaux entrent en conflit avec les requêtes en cours. Le retard acceptable est déclaré par la configuration.

Voici les paramètres concernés :

• hot_standby : on ou off Active ou non la fonctionnalité lorsque la base est en récupération.
• max_standby_archive_delay : quel est le délai maximum acceptable dans l’application de journaux venant de l’archive? Passé ce délai, les requêtes gênantes sur l’esclave seront automatiquement annulées.
• max_standby_streaming_delay : quel est le délai maximum acceptable dans l’application de journaux venant par le mécanisme de Streaming Replication? Passé ce délai, les requêtes gênantes sur l’esclave seront automatiquement annulées.

Et sur le maître :

• wal_level : doit être à replica afin de générer quelques informations supplémentaires à destination de la base de Standby

Et un fichier recovery.conf correctement paramétré sur l’esclave.

PostgreSQL collecte deux types de statistiques différentes :

• Les statistiques d’activité : elles permettent de mesurer l’activité de la base.
  • Combien de fois cette table a-t-elle été parcourue séquentiellement ?
  • Combien de blocs ont été trouvés dans le cache pour ce parcours d’index, et combien ont du être demandés au système d’exploitation ?
  • Quelles sont les requêtes en cours d’exécution ?
  • Combien de buffers ont été écrits par le Background Writer ? Par les processus eux-mêmes ? durant un checkpoint?
• Les statistiques sur les données : elles sont utilisées par l’optimiseur de requêtes dans sa recherche du meilleur plan d’exécution. Elles contiennent entre autres les informations suivantes :
  • Taille des tables
  • Taille moyenne d’un enregistrement de table
  • Taille moyenne d’un attribut

Chaque session collecte des statistiques, dès qu’elle effectue une opération.

Ces informations, si elles sont transitoires, comme la requête en cours, sont directement stockées dans la mémoire partagée de PostgreSQL.

Si elles doivent être agrégées et stockées, elles sont remontées au processus responsable de cette tâche, le Stats Collector.

Voici les paramètres concernés :

• track_activities : les processus doivent-ils mettre à jour leur activité dans pg_stat_activity? (on par défaut)
• track_activity_query_size : quelle est la taille maximum du texte de requête pouvant être stocké dans pg_stat_activity (1024 caractères par défaut, que l'on doit souvent monter vers 10000 si les requêtes sont longues) ; nécessite un redémarrage)
• track_counts : les processus doivent-ils collecter des informations sur leur activité ? (on par défaut)
• track_io_timing : les processus doivent-ils collecter des informations de chronométrage sur les lectures et écritures ? Ce paramètre complète les champs blk_read_time et blk_write_time dans pg_stat_database, pg_stat_statements (mêmes champs) et les plans d'exécutions appelés avec EXPLAIN (ANALYZE,BUFFERS) (off par défaut ; avant de l'activer sur une machine peu performante, vérifiez l'impact avec l'outil pg_test_timing)
• track_functions : les processus doivent-ils aussi collecter des informations sur l’exécution des procédures stockées (none par défaut, pl pour ne tracer que les procédures en langages procéduraux, all pour tracer aussi les procédures en C et en SQL)
• update_process_title : le titre du processus (visible par exemple avec ps -ef sous Unix) sera modifié si cette option est à on (défaut sous Unix ; mettre à off sous Windows pour des raisons de performance)
• stats_temp_directory : le fichier de statistiques pouvant être source de contention (il peut devenir gros, et est réécrit fréquemment), il peut être stocké ailleurs que dans le répertoire de l’instance PostgreSQL, par exemple sur un ramdisk ou tmpfs (défaut sous Debian).

Pour les statistiques aux objets, le système fournit à chaque fois trois vues différentes :

• Une pour tous les objets du type. Elle contient all dans le nom, pg_statio_all_tables par exemple.
• Une pour uniquement les objets systèmes. Elle contient sys dans le nom, pg_statio_sys_tables par exemple.
• Une pour uniquement les objets non-systèmes. Elle contient user dans le nom, pg_statio_user_tables par exemple.

Les statistiques accessibles sont :

• Les accès logiques aux objets (tables, index et fonctions). Ce sont les vues pg_stat_xxx_tables, pg_stat_xxx_indexes et pg_stat_user_functions.
• Les accès physiques aux objets (tables, index et séquences). Ce sont les vues pg_statio_xxx_tables, pg_statio_xxx_indexes, et pg_statio_xxx_sequences.
• pg_stat_bgwriter stocke les statistiques d’écriture des buffers : par le Background Writer en fonctionnement normal, par le Background Writer durant les checkpoints, ou par les sessions elles-mêmes.
• Des statistiques globales par base sont aussi disponibles, dans pg_stat_database : le nombre de transactions validées et annulées, le nombre de sessions en cours, et quelques statistiques sur les accès physiques et en cache, ainsi que sur les opérations logiques.
• Les statistiques sur les conflits entre application de la réplication et requêtes en lecture seule sont disponibles dans pg_stat_database_conflicts.
• pg_stat_activity donne des informations sur les processus en cours sur l’instance, que ce soit des processus en tâche de fond ou des processus backend : numéro de processus, adresse et port, date de début d’ordre, de transaction, de session, requête en cours, état, ordre SQL et nom de l’application si elle l’a renseigné (avant la version 10, cette vue n'affichait que les processus backend ; à partir de la version 10 apparaissent des workers, le checkpointer, le walwriter...).
• pg_stat_ssl donne des informations sur les connexions SSL : version SSL, suite de chiffrement, nombre de bits pour l’algorithme de chiffrement, compression, Distinguished Name (DN) du certificat client.
• pg_stat_replication donne des informations sur les esclaves connectés.

Afin de calculer les plans d’exécution des requêtes au mieux, le moteur a besoin de statistiques sur les données qu’il va interroger. Il est très important pour lui de pouvoir estimer la sélectivité d’une clause WHERE, l’augmentation ou la diminution du nombre d’enregistrements entraînée par une jointure, tout cela afin de déterminer le coût approximatif d’une requête, et donc de choisir un bon plan d’exécution.

Les statistiques sont collectées dans la table pg_statistic. La vue `pg_stats`` affiche le contenu de cette table système de façon plus accessible.

Les statistiques sont collectées sur :

• Chaque colonne de chaque table
• Les index fonctionnels

Les statistiques sont calculées sur un échantillon égal à 300 fois le paramètre STATISTICS de la colonne (ou, s’il n’est pas précisé, du paramètre default_statistics_target).

La vue pg_stats affiche les statistiques collectées :

postgres=# \d pg_stats
                         Vue « pg_catalog.pg_stats »
        Colonne         |   Type   | Collationnement | NULL-able | Par défaut
------------------------+----------+-----------------+-----------+------------
 schemaname             | name     |                 |           |
 tablename              | name     |                 |           |
 attname                | name     |                 |           |
 inherited              | boolean  |                 |           |
 null_frac              | real     |                 |           |
 avg_width              | integer  |                 |           |
 n_distinct             | real     |                 |           |
 most_common_vals       | anyarray |                 |           |
 most_common_freqs      | real[]   |                 |           |
 histogram_bounds       | anyarray |                 |           |
 correlation            | real     |                 |           |
 most_common_elems      | anyarray |                 |           |
 most_common_elem_freqs | real[]   |                 |           |
 elem_count_histogram   | real[]   |                 |           |

• inherited : la statistique concerne-t-elle un objet utilisant l’héritage (table parente, dont héritent plusieurs tables).
• null_frac : fraction d’enregistrements nuls.
• avg_width : taille moyenne de cet attribut dans l’échantillon collecté.
• n_distinct : si positif, nombre de valeurs distinctes, si négatif, fraction de valeurs distinctes pour cette colonne dans la table. On peut forcer le nombre de valeurs distinctes, si on constate que la collecte des statistiques n’y arrive pas : ALTER TABLE xxx ALTER COLUMN yyy SET (n_distinct = -0.5) ; ANALYZE xxx; par exemple indique à l’optimiseur que chaque valeur apparaît statistiquement deux fois.
• most_common_vals et most_common_freqs : les valeurs les plus fréquentes de la table, et leur fréquence. Le nombre de valeurs collecté est au maximum celle indiquée par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target.
• histogram_bounds : les limites d’histogramme sur la colonne. Les histogrammes permettent d’évaluer la sélectivité d’un filtre par rapport à sa valeur précise. Ils permettent par exemple à l’optimiseur de déterminer que 4,3 % des enregistrements d’une colonne noms commencent par un A, ou 0,2 % par AL. Le principe est de regrouper les enregistrements triés dans des groupes de tailles approximativement identiques, et de stocker les limites de ces groupes (on ignore les most_common_vals, pour lesquelles on a déjà une mesure plus précise). Le nombre d’histogram_bounds est calculé de la même façon que les most_common_vals.
• correlation : le facteur de corrélation statistique entre l’ordre physique et l’ordre logique des enregistrements de la colonne. Il vaudra par exemple 1 si les enregistrements sont physiquement stockés dans l’ordre croissant, -1 si ils sont dans l’ordre décroissant, ou 0 si ils sont totalement aléatoirement répartis. Ceci sert à affiner le coût d’accès aux enregistrements.
• most_common_elems et most_common_elems_freqs : les valeurs les plus fréquentes si la colonne est un tableau (NULL dans les autres cas), et leur fréquence. Le nombre de valeurs collecté est au maximum celle indiquée par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target.
• elem_count_histogram : les limites d’histogramme sur la colonne si elle est de type tableau.

Le langage SQL décrit le résultat souhaité. Par exemple :

SELECT path, filename
FROM file
JOIN path ON (file.pathid=path.pathid)
WHERE path LIKE '/usr/%'

Cet ordre décrit le résultat souhaité. On ne précise pas au moteur comment accéder aux tables path et file (par index ou parcours complet par exemple), ni comment effectuer la jointure (il existe plusieurs méthodes pour PostgreSQL). C’est à l’optimiseur de prendre la décision, en fonction des informations qu’il possède.

Les informations les plus importantes pour lui, dans le contexte de cette requête, seront :

• quelle fraction de la table path est ramenée par le critère path LIKE '/usr/%' ?
• y a-t-il un index utilisable sur cette colonne ?
• y a-t-il des index sur file.pathid, sur path.pathid ?
• quelles sont les tailles des deux tables ?

On comprend bien que la stratégie la plus efficace ne sera pas la même suivant les informations retournées par toutes ces questions…

Il pourrait être intéressant de charger les deux tables séquentiellement, supprimer les enregistrements de path ne correspondant pas à la clause LIKE, trier les deux jeux d’enregistrements et fusionner les deux jeux de données triés (c’est appelé un merge join). Cependant, si les tables sont assez volumineuses, et que le LIKE est très discriminant (il ramène peu d’enregistrements de la table path), la stratégie d’accès sera totalement différente : on pourrait préférer récupérer les quelques enregistrements de path correspondant au LIKE par un index, puis pour chacun de ces enregistrements, aller chercher les informations correspondantes dans la table file (c’est appelé un nested loop).

Afin de choisir un bon plan, le moteur essaye des plans d’exécution. Il estime, pour chacun de ces plans, le coût associé. Afin d’évaluer correctement ces coûts, il utilise plusieurs informations :

• Les statistiques sur les données, qui lui permettent d’estimer le nombre d’enregistrements ramenés par chaque étape du plan et le nombre d’opérations de lecture à effectuer pour chaque étape de ce plan
• Des informations de paramétrage lui permettant d’associer un coût arbitraire à chacune des informations à effectuer. Ces informations sont les suivantes :
  • seq_page_cost : coût de la lecture d’une page disque de façon séquentielle (lors d’un parcours séquentiel de table par exemple). Par défaut 1.0.
  • random_page_cost : coût de la lecture d’une page disque de façon aléatoire (lors d’un accès à une page d’index par exemple). Par défaut 4.0.
  • cpu_tuple_cost : coût de traitement par le processeur d’un enregistrement de table. Par défaut 0.01.
  • cpu_index_tuple_cost : coût de traitement par le processeur d’un enregistrement d’index. Par défaut 0.005.
  • cpu_operator_cost : coût de traitement par le processeur de l’exécution d’un opérateur. Par défaut 0.0025.

Ce sont les coûts relatifs de ces différentes opérations qui sont importants : l’accès à une page de façon aléatoire est par exemple 4 fois plus coûteuse que de façon séquentielle, du fait du déplacement des têtes de lecture sur un disque dur. seq_page_cost, random_page_cost et effective_io_concurrency peuvent être paramétrés par tablespace (depuis la version 9.0 de PostgreSQL pour les deux premiers, et depuis la version 9.6 pour le dernier), afin de refléter les caractéristiques de disques différents. Sur une base fortement en cache, on peut donc être tenté d’abaisser le random_page_cost à 3, voire 2,5, ou des valeurs encore bien moindres dans le cas de bases totalement en mémoire.

La mise en place du parallélisme représente un coût : Il faut mettre en place une mémoire partagée, lancer des processus... Ce coût est pris en compte par le planificateur à l'aide du paramètre parallel_setup_cost. Par ailleurs, le transfert d'enregistrement entre un worker et un autre processus a également un coût représenté par le paramètre parallel_tuple_cost.

Ainsi une lecture complète d'une grosse table peut être moins couteuse sans parallélisation du fait que le nombre de lignes retournées par les workers est très important. En revanche, en filtrant les résultats, le nombre de lignes retournées peut être moins important et le planificateur peut être amené à choisir un plan comprenant la parallélisation.

Certaines autres informations permettent de nuancer les valeurs précédentes. effective_cache_size est la taille du cache du système d’exploitation. Il permet à PostgreSQL de modéliser plus finement le coût réel d’une opération disque, en prenant en compte la probabilité que cette information se trouve dans le cache du système d’exploitation, et soit donc moins coûteuse à accéder.

Le parcours de l’espace des solutions est un parcours exhaustif. Sa complexité est principalement liée au nombre de jointures de la requête et est de type exponentiel. Par exemple, planifier de façon exhaustive une requête à une jointure dure 200 microsecondes environ, contre 7 secondes pour 12 jointures. Une autre stratégie, l’optimiseur génétique, est donc utilisée pour éviter le parcours exhaustif quand le nombre de jointure devient trop élevé.

Pour plus de détails voir l’article sur les coûts de planification issu de la base de connaissance Dalibo.

Tous les paramètres suivants peuvent être modifiés par session.

Pour partitionner une table sous PostgreSQL, on crée une table parente et des tables filles héritent de celle-ci. Sur ces tables filles, on définit des contraintes CHECK, que tous les enregistrements de la table fille doivent vérifier. Ce sont les critères de partitionnement. Par exemple CHECK (date >= '2011-01-01' and date < '2011-02-01') pour une table fille d’un partitionnement par mois.

Afin que PostgreSQL ne parcoure que les partitions correspondant à la clause WHERE d’une requête, le paramètre constraint_exclusion doit valoir partition (la valeur par défaut) ou on. partition est moins coûteux dans un contexte d’utilisation classique car les contraintes d’exclusion ne seront examinées que dans le cas de requêtes UNION ALL, qui sont les requêtes générées par le partitionnement.

Pour limiter la complexité des plans d’exécution à étudier, il est possible de limiter la quantité de réécriture autorisée par l’optimiseur via les paramètres from_collapse_limit et join_collapse_limit. Le premier interdit que plus de 8 (par défaut) tables provenant d’une sous-requête ne soient déplacées dans la requête principale. Le second interdit que plus de 8 (par défaut) tables provenant de clauses JOIN ne soient déplacées vers la clause FROM. Ceci réduit la qualité du plan d’exécution généré, mais permet qu’il soit généré dans un temps fini.

Lors de l’utilisation de curseurs, le moteur n’a aucun moyen de connaître le nombre d’enregistrements que souhaite récupérer réellement l’utilisateur. Il est tout-à-fait possible que seuls les premiers enregistrements générés soient récupérés. Et si c’est le cas, le plan d’exécution optimal ne sera plus le même. Le paramètre cursor_tuple_fraction, par défaut à 0.1, permet d’indiquer à l’optimiseur la fraction du nombre d’enregistrements qu’un curseur souhaitera vraisemblablement récupérer, et lui permettra donc de choisir un plan en conséquence.

Quand plusieurs requêtes souhaitent accéder séquentiellement à la même table, les processus se rattachent à ceux déjà en cours de parcours, afin de profiter des entrées-sorties que ces processus effectuent, le but étant que le système se comporte comme si un seul parcours de la table était en cours, et réduise donc fortement la charge disque. Le seul problème de ce mécanisme est que les processus se rattachant ne parcourent pas la table dans son ordre physique : elles commencent leur parcours de la table à l’endroit où se trouve le processus auquel elles se rattachent, puis rebouclent sur le début de la table. Les résultats n’arrivent donc pas forcément toujours dans le même ordre, ce qui n’est normalement pas un problème (on est censé utiliser ORDER BY dans ce cas). Mais il est toujours possible de désactiver ce mécanisme en passant synchronize_seqscans à off.

PostgreSQL, pour les requêtes trop complexes, bascule vers un optimiseur appelé GEQO (GEnetic Query Optimizer). Comme tout algorithme génétique, il fonctionne par introduction de mutations aléatoires sur un état initial donné. Il permet de planifier rapidement une requête complexe, et de fournir un plan d’exécution acceptable.

Malgré l’introduction de ces mutations aléatoires, le moteur arrive tout de même à conserver un fonctionnement déterministe (voir la documentation dans le code source de PostgreSQL).

Tant que le paramètre geqo_seed ainsi que les autres paramètres contrôlant GEQO restent inchangés, le plan obtenu pour une requête donnée restera inchangé. Il est possible de faire varier la valeur de geqo_seed pour obtenir d’autres plans (voir la documentation officielle).

Il est configuré par les paramètres dont le nom commence par geqo dans le moteur. Excepté geqo_threshold et geqo, il est déconseillé de modifier les paramètres sans une grande connaissance des algorithmes génétiques.

• geqo, par défaut à on, permet de désactiver complètement GEQO.
• geqo_threshold, par défaut à 12, est le nombre d’éléments minimum à joindre dans un FROM avant d’optimiser celui-ci par GEQO au lieu du planificateur exhaustif.

Ces paramètres dissuadent le moteur d’utiliser un type de nœud d’exécution (en augmentant énormément son coût). Ils permettent de vérifier ou d’invalider une erreur de l’optimiseur. Par exemple :

marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a<3;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..84641.51 rows=4999971 width=4)
                   (actual time=0.011..454.286 rows=5000003 loops=1)
   Filter: (a < 3)
   Rows Removed by Filter: 998
 Planning time: 0.248 ms
 Execution time: 601.176 ms
(5 rows)

Le moteur a choisi un parcours séquentiel de table. Si on veut vérifier qu’un parcours par l’index sur la colonne a n’est pas plus rentable :

marc=# SET enable_seqscan TO off;
SET
marc=# SET enable_indexonlyscan TO off;
SET
marc=# SET enable_bitmapscan TO off;
SET
marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a<3;
                         QUERY PLAN
---------------------------------------------------------
 Index Scan using test_a_idx on test
        (cost=0.43..164479.92 rows=4999971 width=4)
        (actual time=0.297..669.051 rows=5000003 loops=1)
   Index Cond: (a < 3)
 Planning time: 0.065 ms
 Execution time: 816.566 ms
(4 rows)

Attention aux effets du cache : le parcours par index est ici relativement performant parce que les données ont été trouvées dans le cache disque. La requête, sinon, aurait été bien plus coûteuse. La requête initiale est donc non seulement plus rapide, mais aussi plus sûre : son temps d’exécution restera prévisible même en cas d’erreur d’estimation sur le nombre d’enregistrements.

Si on supprime l’index, on constate que le sequential scan n’a pas été désactivé. Il a juste été rendu très coûteux par ces options de déboggage :

marc=# DROP INDEX test_a_idx ;
DROP INDEX
marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a<3;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on test  (cost=10000000000.00..10000084641.51 rows=5000198 width=4)
                   (actual time=0.012..455.615 rows=5000003 loops=1)
   Filter: (a < 3)
   Rows Removed by Filter: 998
 Planning time: 0.046 ms
 Execution time: 603.004 ms
(5 rows)

Le « très coûteux » est un coût majoré de 10 000 000 000 pour l’exécution d’un nœud interdit.

Voici la liste des options de désactivation :

• enable_bitmapscan
• enable_hashagg
• enable_hashjoin
• enable_indexonlyscan
• enable_indexscan
• enable_material
• enable_mergejoin
• enable_nestloop
• enable_seqscan
• enable_sort
• enable_tidscan

Le processus postmaster est en écoute sur les différentes sockets déclarées dans la configuration. Cette déclaration se fait au moyen des paramètres suivants :

• port : le port TCP. Il sera aussi utilisé dans le nom du fichier socket Unix (par exemple : /tmp/.s.PGSQL.5432 ou /var/run/postgresql/.s.PGSQL.5432 selon les distributions) ;
• listen_adresses : la liste des adresses IP du serveur auxquelles s’attacher ;
• unix_socket_directory : le répertoire où sera stockée la socket Unix ;
• unix_socket_group : le groupe (système) autorisé à accéder à la socket Unix ;
• unix_socket_permissions : les droits d’accès à la socket Unix.

On peut préciser les propriétés keepalive des sockets TCP, pour peu que le système d’exploitation les gère. Le keepalive est un mécanisme de maintien et de vérification des session TCP, par l’envoi régulier de messages de vérification sur une session TCP inactive. tcp_keepalives_idle est le temps en secondes d’inactivité d’une session TCP avant l’envoi d’un message de keepalive. tcp_keepalives_interval est le temps entre un keepalive et le suivant, en cas de non-réponse. tcp_keepalives_count est le nombre maximum de paquets sans réponse acceptés avant que la session ne soit déclarée comme morte.

Les valeurs par défaut (0) reviennent à utiliser les valeurs par défaut du système d'exploitation.

Le mécanisme de keepalive a deux intérêts :

• il permet de détecter les clients déconnectés même si ceux-ci ne notifient pas la déconnexion (plantage du système d’exploitation, fermeture de la session par un firewall…) ;
• il permet de maintenir une session active au travers de firewalls, qui seraient fermées sinon : la plupart des firewalls ferment une session inactive après 5 minutes, alors que la norme TCP prévoit plusieurs jours.

Il existe des options pour activer SSL et le paramétrer. ssl vaut on ou off, ssl_ciphers est la liste des algorithmes de chiffrement autorisés, et ssl_renegotiation_limit le volume maximum de données échangées sur une session avant renégociation entre le client et le serveur. Le paramétrage SSL impose aussi la présence d’un certificat. Pour plus de détails, consultez la documentation officielle.

De nombreux autres paramètres sont disponibles, qui configurent principalement l’authentification (les paramètres ldap ou GSSAPI par exemple), mais aussi le paramétrage de l’authentification proprement dite, pilotée par le fichier de configuration pg_hba.conf.

Énoncés

Processus

• Lancez PostgreSQL (si ce n'est pas déjà fait).

• Listez les processus du serveur PostgreSQL.
• Qu'observe-t-on ?
• Ouvrez une connexion
• Listez de nouveau les processus du serveur.
• Qu'observe-t-on ?
• Créez une table et ajoutez-y beaucoup de lignes.
• Pendant l'insertion, listez de nouveau les processus du serveur.
• Qu'observe-t-on ?
• Configurez max_connections à 11.
• Redémarrez PostgreSQL.
• Connectez-vous cinq fois à PostgreSQL.
• Essayez une sixième connexion

• Qu'observe-t-on ?

Mémoire partagée

• Créez une table avec une colonne id de type integer.

• Insérez 500 lignes (pensez à utiliser generate_series)
• Réinitialisez les statistiques pour t2 uniquement.
• Redémarrez PostgreSQL (pour vider le cache).
• Lisez la table entière
• Récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables)
• Qu'observe-t-on ?
• Lisez la table entière une deuxième fois et récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables).
• Qu'observe-t-on ?
• Lisez la table entière une troisième fois, à partir d'une autre session, et récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables).

• Qu'observe-t-on ?

Mémoire par processus

• Activez la trace des fichiers temporaires ainsi que l'affichage du niveau LOG pour le client (vous pouvez le faire sur la session uniquement).

• Insérez un million de lignes dans la table précédente.
• Listez les données de la table en triant par la colonne.
• Qu'observe-t-on ?
• Augmentez la valeur du paramètre work_mem.
• Listez les données de la table en triant par la colonne.

• Qu'observe-t-on ?

Fichiers

• Allez dans le répertoire des données.

• Listez les fichiers.
• Allez dans base.
• Listez les fichiers.
• À quelle base est lié chaque répertoire de base ? (oid2name ou pg_database)
• Créez une nouvelle base.
• Qu'est-il survenu dans le répertoire base ?
• Connectez-vous sur cette nouvelle base et créez une table avec une seule colonne.
• Récupérer le chemin vers le fichier correspond à cette table.
• Regardez la taille du fichier.
• Pourquoi est-il vide ?
• Ajoutons une ligne.
• Quelle taille fait le fichier ?
• Ajoutons 500 lignes.
• Quelle taille fait le fichier ?
• Pourquoi cette taille pour simplement 501 fois un entier (ie, 4 octets) ?.
• Ajoutez un tablespace.
• Ajoutez-y une table.
• Récupérer le chemin vers le fichier correspond à cette table.
• Insérer dix millions de lignes dans la table t2.
• Que se passe-t-il au niveau du répertoire pg_wal ?
• Exécutez un CHECKPOINT.

• Que se passe-t-il au niveau du répertoire pg_wal ?

Cache disque de PostgreSQL

• Installez l'extension de pg_buffercache

• Redémarrez PostgreSQL
• Videz le cache système
• Que contient le cache de PostgreSQL ?
• Lisez complètement t2, en récupérant la durée d'exécution de la requête
• Que contient le cache de PostgreSQL ?
• Extrayez de nouveau toutes les données de la table t2
• Que contient le cache de PostgreSQL ?
• Changez shared_buffers, puis redémarrez PostgreSQL.
• Extrayez de nouveau toutes les données de la table t2
• Que contient le cache de PostgreSQL ?
• Faire une mise à jour.
• Que contient le cache de PostgreSQL ?
• Faites un CHECKPOINT

• Que contient le cache de PostgreSQL ?

Statistiques d'activités

• Créer une table

• Insérer des données

• Lire ses statistiques d'activité

Statistiques sur les données

• Créer une table avec une seule colonne de type integer

• Empêcher autovacuum d'analyser automatiquement la table
• Mettre des données différentes sur c1
• Lire la table avec un filtre sur c1
• Exécuter la commande ANALYZE
• Lire la table avec un filtre sur c1. Que constatez-vous
• Ajouter un index sur c1
• Lire la table avec un filtre sur c1
• Modifier la colonne c1 avec la valeur 1 pour toutes les lignes
• Lire la table avec un filtre sur c1
• Exécuter la commande ANALYZE
• Lire la table avec un filtre sur c1

Solutions

Processus

Lancez PostgreSQL (si ce n'est pas déjà fait).

/etc/init.d/postgresql start

Listez les processus du serveur PostgreSQL.

$ ps -o pid,cmd fx
  PID CMD
 1792 -bash
 2324  \_ ps -o pid,cmd fx
 1992 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data
 1994  \_ postgres: logger process                              
 1996  \_ postgres: checkpointer process                        
 1997  \_ postgres: writer process                              
 1998  \_ postgres: wal writer process                          
 1999  \_ postgres: autovacuum launcher process                 
 2000  \_ postgres: stats collector process                     
 2001  \_ postgres: bgworker: logical replication launcher      

Qu'observe-t-on ?

Les processus de gestion du serveur PostgreSQL.

Ouvrez une connexion

$ psql postgres
psql (10)
Type "help" for help.

postgres=#

Listez de nouveau les processus du serveur.

$ ps -o pid,cmd fx
  PID CMD
 2031 -bash
 2326  \_ psql postgres
 1792 -bash
 2328  \_ ps -o pid,cmd fx
 1992 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data
 1994  \_ postgres: logger process                              
 1996  \_ postgres: checkpointer process                        
 1997  \_ postgres: writer process                              
 1998  \_ postgres: wal writer process                          
 1999  \_ postgres: autovacuum launcher process                 
 2000  \_ postgres: stats collector process                     
 2001  \_ postgres: bgworker: logical replication launcher      
 2327  \_ postgres: postgres postgres [local] idle              

Qu'observe-t-on ?

Il y a un nouveau processus (PID 457) qui va gérer l'exécution des requêtes du client psql.

Créez une table et ajoutez-y beaucoup de lignes.

postgres=# CREATE TABLE t1 (id integer);
CREATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 10000000);
INSERT 0 10000000

Pendant l'insertion, listez de nouveau les processus du serveur.

$ ps -o pid,cmd fx PID CMD 2031 -bash 2326 \_ psql postgres 1792 -bash 2363 \_ ps -o pid,cmd fx 1992 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data 1994 \_ postgres: logger process 1996 \_ postgres: checkpointer process 1997 \_ postgres: writer process 1998 \_ postgres: wal writer process 1999 \_ postgres: autovacuum launcher process 2000 \_ postgres: stats collector process 2001 \_ postgres: bgworker: logical replication launcher 2327 \_ postgres: postgres postgres [local] INSERT

Qu'observe-t-on ?

Le processus serveur exécute l'INSERT, ce qui se voit au niveau du nom du processus. Seul l'ordre SQL est affiché (ie, le mot INSERT et non pas la requête complète).

Configurez max_connections à 11.

Pour cela, il faut ouvrir le fichier de configuration postgresql.conf et modifier la valeur du paramètre max_connections à 11.

Redémarrez PostgreSQL.

# service postgresql-10 restart

Connectez-vous cinq fois à PostgreSQL.

Très simple avec ce petit script shell :

$ for i in $(seq 1 11); do psql -c "SELECT pg_sleep(1000);" postgres & done
[1] 998
[2] 999
[3] 1000
[4] 1001
[5] 1002
[6] 1003
[7] 1004
[8] 1005
[9] 1006
[10] 1007
[11] 1008

Essayez une douzième connexion

$ psql postgres
psql: FATAL:  sorry, too many clients already

Qu'observe-t-on ?

Il est impossible de se connecter une fois que le nombre de connexions a atteint sa limite configurée avec max_connections. Il faut donc attendre que les utilisateurs se déconnectent pour accéder de nouveau au serveur.

Mémoire partagée

Créez une table avec une colonne id de type integer.

$ psql postgres
psql (10)
Type "help" for help.

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

postgres=# CREATE TABLE t2 (id integer);
CREATE TABLE

Insérez 500 lignes (pensez à utiliser generate_series)

postgres=# INSERT INTO t2 SELECT generate_series(1, 500);
INSERT 0 500

Réinitialisez les statistiques pour t2 uniquement.

postgres=# SELECT pg_stat_reset_single_table_counters(oid) FROM pg_class
WHERE relname='t2';
 pg_stat_reset_single_table_counters
-------------------------------------

(1 row)

Redémarrez PostgreSQL (pour vider le cache).

# service postgresql-10 restart

Lisez la table entière

postgres=# SELECT * FROM t2;
[...]

Récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables)

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_statio_user_tables WHERE relname='t2';
-[ RECORD 1 ]---+-------
relid           | 24576
schemaname      | public
relname         | t2
heap_blks_read  | 3
heap_blks_hit   | 0
idx_blks_read   |
idx_blks_hit    |
toast_blks_read |
toast_blks_hit  |
tidx_blks_read  |
tidx_blks_hit   |

Qu'observe-t-on ?

3 blocs ont été lus en dehors du cache de PostgreSQL (colonne heap_blks_read).

Lisez la table entière une deuxième fois et récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables).

postgres=# \x
Expanded display is off.
postgres=# SELECT * FROM t2;
[...]
postgres=# SELECT * FROM pg_statio_user_tables WHERE relname='t2';
-[ RECORD 1 ]---+-------
relid           | 24576
schemaname      | public
relname         | t2
heap_blks_read  | 3
heap_blks_hit   | 3
idx_blks_read   |
idx_blks_hit    |
toast_blks_read |
toast_blks_hit  |
tidx_blks_read  |
tidx_blks_hit   |

Qu'observe-t-on ?

Les trois blocs sont maintenant lus à partir du cache de PostgreSQL.

Lisez la table entière une troisième fois, à partir d'une autre session, et récupérez les statistiques IO pour cette table (pensez à utiliser pg_statio_user_tables).

postgres=# \x
Expanded display is off.
postgres=# SELECT * FROM t2;
[...]
postgres=# SELECT * FROM pg_statio_user_tables WHERE relname='t2';
-[ RECORD 1 ]---+-------
relid           | 24576
schemaname      | public
relname         | t2
heap_blks_read  | 3
heap_blks_hit   | 6
idx_blks_read   |
idx_blks_hit    |
toast_blks_read |
toast_blks_hit  |
tidx_blks_read  |
tidx_blks_hit   |

Qu'observe-t-on ?

Quelque soit la session, le cache étant partagé, tout le monde profite des données en cache.

Mémoire par processus

Activez la trace des fichiers temporaires ainsi que l'affichage du niveau LOG pour le client (vous pouvez le faire sur la session uniquement).

postgres=# SET client_min_messages TO log;
SET
postgres=# SET log_temp_files TO 0;
SET

Insérez un million de lignes dans la table précédente.

postgres=# INSERT INTO t2 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Listez les données de la table en triant par la colonne.

postgres=# SELECT * FROM t2 ORDER BY id;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1197.0", size 14032896
   id
---------
       1
       1
       2
       2
       3
[...]

Qu'observe-t-on ?

PostgreSQL a dû créer un fichier temporaire pour stocker le résultat temporaire du tri. Ce fichier s'appelle /pgsql_tmp/pgsql_tmp11331.1. Il est spécifique à la session et sera détruit dès qu'il ne sera plus utile. Il fait 14 Mo.

Augmentez la valeur du paramètre work_mem.

postgres=# SET work_mem TO '100MB';
SET

Listez les données de la table en triant par la colonne.

postgres=# SELECT * FROM t2 ORDER BY id;
   id
---------
       1
       1
       2
       2
       3
       3
[...]

Qu'observe-t-on ?

Il n'y a plus de fichier temporaire généré. La durée d'exécution est bien moindre.

Fichiers

Allez dans le répertoire des données.

$ cd $PGDATA

Listez les fichiers.

$ ll
total 140
drwx------ 9 postgres postgres  4096  8 sept. 09:52 base
-rw------- 1 postgres postgres    30 19 sept. 05:06 current_logfiles
drwx------ 2 postgres postgres  4096 19 sept. 05:06 global
drwx------ 2 postgres postgres  4096 15 sept. 05:00 log
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_commit_ts
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_dynshmem
-rw------- 1 postgres postgres  4381  8 sept. 07:24 pg_hba.conf
-rw------- 1 postgres postgres  1636  8 sept. 06:15 pg_ident.conf
drwx------ 4 postgres postgres  4096 19 sept. 05:06 pg_logical
drwx------ 4 postgres postgres  4096  8 sept. 06:12 pg_multixact
drwx------ 2 postgres postgres  4096 19 sept. 05:06 pg_notify
drwx------ 2 postgres postgres  4096  8 sept. 06:15 pg_replslot
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_serial
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_snapshots
drwx------ 2 postgres postgres  4096 19 sept. 05:06 pg_stat
drwx------ 2 postgres postgres  4096 19 sept. 05:10 pg_stat_tmp
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_subtrans
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_tblspc
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_twophase
-rw------- 1 postgres postgres     3  8 sept. 06:12 PG_VERSION
drwx------ 3 postgres postgres  4096  8 sept. 10:52 pg_wal
drwx------ 2 postgres postgres  4096  8 sept. 06:12 pg_xact
-rw------- 1 postgres postgres    88  8 sept. 06:15 postgresql.auto.conf
-rw------- 1 postgres postgres 22773 19 sept. 05:03 postgresql.conf
-rw------- 1 postgres postgres    57 19 sept. 05:06 postmaster.opts
-rw------- 1 postgres postgres   103 19 sept. 05:06 postmaster.pid

Allez dans base.

$ cd base

Listez les fichiers.

$ ll
total 44
drwx------ 2 postgres postgres  4096  8 sept. 06:12 1
drwx------ 2 postgres postgres 12288 19 sept. 05:08 13451
drwx------ 2 postgres postgres 12288 19 sept. 05:06 16384
drwx------ 2 postgres postgres  4096 19 sept. 05:08 pgsql_tmp

À quelle base est lié chaque répertoire de base ? (oid2name ou pg_database)

Chaque répertoire correspond à une base de données. Le numéro indiqué est un identifiant système (OID). Il existe deux moyens pour récupérer cette information :

• directement dans le catalogue système pg_database

$ psql postgres
psql (10)
Type "help" for help.

postgres=# SELECT oid, datname FROM pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 13451 | template0
 16384 | postgres
(3 rows)

• avec l'outil oid2name

$ oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13451       postgres  pg_default
  13450      template0  pg_default
      1      template1  pg_default

Done le répertoire 1 correspond à la base template1, le répertoire 13450 à la base template0 et le répertoire 13451 à la base postgres.

Créez une nouvelle base.

$ createdb b1

Qu'est-il survenu dans le répertoire base ?

$ ll
total 44
drwx------ 2 postgres postgres  4096  8 sept. 06:12 1
drwx------ 2 postgres postgres  4096  8 sept. 06:12 13450
drwx------ 2 postgres postgres 12288 19 sept. 05:13 13451
drwx------ 2 postgres postgres 12288 19 sept. 05:06 16384
drwx------ 2 postgres postgres  4096 19 sept. 05:08 pgsql_tmp
$ oid2name | grep b1
  16384             b1  pg_default

Un nouveau sous-répertoire est apparu, nommé 16384. Il correspond bien à la base b1 d'après oid2name.

Connectez-vous sur cette nouvelle base et créez une table avec une seule colonne.

$ psql b1
psql (10)
Type "help" for help.

b1=# CREATE TABLE t1(id integer);
CREATE TABLE

Récupérer le chemin vers le fichier correspond à cette table.

b1=# SELECT current_setting('data_directory')||'/'||pg_relation_filepath('t1');
                        ?column?
--------------------------------------------------------
 /var/lib/pgsql/10/data/base/16384/24724
(1 row)

Regardez la taille du fichier.

$ ll /var/lib/pgsql/10/data/base/16384/24724
-rw------- 1 postgres postgres 0  8 sept. 10:34 /var/lib/pgsql/10/data/base/16384/24724

Pourquoi est-il vide ?

La table vient d'être créée. Aucune donnée n'a encore été ajoutée. Les méta-données se trouvent sur d'autres tables (des catalogues systèmes). Donc il est logique que le fichier soit vide.

Ajoutons une ligne.

$ psql b1
psql (10)
Type "help" for help.

b1=# INSERT INTO t1 VALUES (1);
INSERT 0 1

Quelle taille fait le fichier ?

$ ll /var/lib/pgsql/10/data/base/16384/24724
-rw------- 1 postgres postgres 8192 19 sept. 05:24 /var/lib/pgsql/10/data/base/16384/24724

Il fait 8 Ko. En fait, PostgreSQL travaille par bloc de 8 Ko. On ajoute une nouvelle ligne, il crée un nouveau bloc et y place la ligne. Les prochaines lignes iront dans le bloc, jusqu'à ce que ce dernier soit plein. Une fois que le bloc en cours est plein, il ajoute un nouveau bloc et y intègre les nouvelles lignes.

Ajoutons 500 lignes.

b1=# INSERT INTO t1 SELECT generate_series(1, 500);
INSERT 0 500

Quelle taille fait le fichier ?

[gui@localhost base]$ ll /var/lib/pgsql/10/data/base/16384/24724
-rw------- 1 postgres postgres 24576 19 sept. 05:25 /var/lib/pgsql/10/data/base/16384/24724

Le fichier fait maintenant 24 Ko, soit 3 blocs de 8 Ko.

Pourquoi cette taille pour simplement 501 fois un entier (ie, 4 octets) ?.

On a enregistré 501 entiers dans la table. Un entier de type int4 prend 4 octets. Donc nous avons 2004 octets de données utilisateurs. Et pourtant, nous arrivons à un fichier de 24 Ko.

En fait, PostgreSQL enregistre aussi dans chaque bloc des informations systèmes en plus des données utilisateurs. Chaque bloc contient un en-tête, des pointeurs, et l'ensemble des lignes du bloc. Chaque ligne contient les colonnes utilisateurs mais aussi des colonnes systèmes. La requête suivante permet d'en savoir plus :

b1=# SELECT CASE WHEN attnum<0 THEN 'systeme' ELSE 'utilisateur' END AS type,
         attname, attnum, typname, typlen,
         sum(typlen) OVER (PARTITION BY attnum<0)
  FROM pg_attribute a
  JOIN pg_type t ON t.oid=a.atttypid
  WHERE attrelid IN (SELECT oid FROM pg_class WHERE relname='t1')
  ORDER BY attnum;
    type     | attname  | attnum | typname | typlen | sum 
-------------+----------+--------+---------+--------+-----
 systeme     | tableoid |     -7 | oid     |      4 |  26
 systeme     | cmax     |     -6 | cid     |      4 |  26
 systeme     | xmax     |     -5 | xid     |      4 |  26
 systeme     | cmin     |     -4 | cid     |      4 |  26
 systeme     | xmin     |     -3 | xid     |      4 |  26
 systeme     | ctid     |     -1 | tid     |      6 |  26
 utilisateur | c1       |      1 | int4    |      4 |   3
 utilisateur | c2       |      2 | text    |     -1 |   3
(8 rows)

L'en-tête de chaque ligne pèse 26 octets dans le meilleur des cas. Il peut peser 30 si on demande à avoir un OID généré pour chaque ligne de la table. Dans notre cas très particulier avec une seule petite colonne, c'est très défavorable mais ce n'est généralement pas le cas.

Donc 30 octets par lignes, 501 lignes, on obtient 15 Ko. Avec l'entête de bloc et les pointeurs, on dépasse facilement 16 Ko, ce qui explique pourquoi nous en sommes à 24 Ko.

Ajoutez un tablespace.

# mkdir /opt/ts1
# chown postgres: /opt/ts1
$ psql b1
psql (10)
Type "help" for help.

b1=# CREATE TABLESPACE ts1 LOCATION '/opt/ts1';
CREATE TABLESPACE

Ajoutez-y une table.

b1=# CREATE TABLE t2 (id integer) TABLESPACE ts1;
CREATE TABLE

Récupérer le chemin vers le fichier correspond à cette table.

b1=# SELECT current_setting('data_directory')||'/'||pg_relation_filepath('t2');
                              ?column?                              
--------------------------------------------------------------------
 /var/lib/pgsql/10/data/pg_tblspc/24764/PG_10_201707211/16384/24765

Le fichier n'a pas été créée dans un sous-répertoire du répertoire base. Il est mis dans le tablespace indiqué par la commande CREATE TABLE. Il s'agit là-aussi d'un fichier :

$ ll /var/lib/pgsql/10/data/pg_tblspc/24764/PG_10_201707211/16384/24765
-rw------- 1 postgres postgres 0 19 sept. 05:28 /var/lib/pgsql/10/data/pg_tblspc/24764/PG_10_201707211/16384/24765
$ ll /opt/ts1/PG_10_201707211/16384/24765
-rw------- 1 postgres postgres 0 19 sept. 05:28 /opt/ts1/PG_10_201707211/16384/24765
$ ll /var/lib/pgsql/10/data/pg_tblspc/
total 0
lrwxrwxrwx 1 postgres postgres 8 19 sept. 05:28 24764 -> /opt/ts1

Il est à noter que ce fichier se trouve réellement dans un sous-répertoire de /opt/ts1 mais que PostgreSQL le retrouve à partir de pg_tblspc grâce à un lien symbolique.

Insérer dix millions de lignes dans la table t2.

b1=# INSERT INTO t2 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Que se passe-t-il au niveau du répertoire pg_wal ?

$ ll pg_wal
total 131076
-rw-------. 1 gui gui 16777216 Feb 12 16:39 00000001000000000000002B
-rw-------. 1 gui gui 16777216 Feb 12 16:39 00000001000000000000002C
-rw-------. 1 gui gui 16777216 Feb 12 16:39 00000001000000000000002D
-rw-------. 1 gui gui 16777216 Feb 12 16:39 00000001000000000000002E
-rw-------. 1 gui gui 16777216 Feb 12 16:39 00000001000000000000002F
-rw-------. 1 gui gui 16777216 Feb 12 15:14 000000010000000000000030
-rw-------. 1 gui gui 16777216 Feb 12 15:14 000000010000000000000031
-rw-------. 1 gui gui 16777216 Feb 12 15:14 000000010000000000000032
drwx------. 2 gui gui     4096 Feb 12 13:59 archive_status

Des journaux de transactions sont écrits lors des écritures dans la base.

Exécutez un CHECKPOINT.

b1=# CHECKPOINT;
CHECKPOINT

Que se passe-t-il au niveau du répertoire pg_wal ?

$ ll pg_wal
total 131076
-rw-------. 1 gui gui 16777216 19 sept. 16:39 00000001000000000000002E
-rw-------. 1 gui gui 16777216 19 sept. 16:39 00000001000000000000002F
-rw-------. 1 gui gui 16777216 19 sept. 15:14 000000010000000000000030
-rw-------. 1 gui gui 16777216 19 sept. 15:14 000000010000000000000031
-rw-------. 1 gui gui 16777216 19 sept. 15:14 000000010000000000000032
-rw-------. 1 gui gui 16777216 19 sept. 16:39 000000010000000000000033
-rw-------. 1 gui gui 16777216 19 sept. 16:39 000000010000000000000034
-rw-------. 1 gui gui 16777216 19 sept. 16:39 000000010000000000000035
drwx------. 2 gui gui     4096 19 sept. 13:59 archive_status

Les anciens journaux devenus obsolètes sont recyclés.

Cache disque de PostgreSQL

Installez l'extension de pg_buffercache

b1=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

Redémarrez PostgreSQL

# service postgresql-10 restart

Videz le cache système

# sync
# echo 3 > /proc/sys/vm/drop_caches

Que contient le cache de PostgreSQL ?

b1=# SELECT relfilenode, count(*) FROM pg_buffercache GROUP BY 1;
 relfilenode | count
-------------+-------
             | 16282
       12808 |     1
       12690 |     2
       12709 |     5
       12774 |     1
[...]

La colonne relfilenode correspond à l'identifiant système de la table. La deuxième colonne indique le nombre de blocs. Il y a 16282 blocs non utilisés pour l'instant dans le cache, ce qui est logique vu qu'on vient de redémarrer PostgreSQL. Il y a quelques blocs utilisés par des tables systèmes, mais aucune table utilisateur (ie, celle dont l'OID est supérieur à 16384).

Lisez complètement t2, en récupérant la durée d'exécution de la requête

b1=# \timing
Timing is on.
b1=# SELECT * FROM t2;
   id
---------
       1
       2
       3
       4
       5
[...]
Time: 356.927 ms

Que contient le cache de PostgreSQL ?

b1=# SELECT relfilenode, count(*) FROM pg_buffercache GROUP BY 1 ORDER BY 1;
 relfilenode | count
-------------+-------
       12682 |     1
       12687 |     3
[...]
       12947 |     2
       12948 |     2
       24765 |  4425
             | 16190
(42 rows)

b1=# SELECT pg_table_size('t2');
 pg_table_size
---------------
      36282368
(1 row)

32 blocs ont été alloués pour la lecture de la table t2. Cela représente 256 Ko alors que la table fait 35 Mo :

b1=# SELECT pg_size_pretty(pg_table_size('t2'));
 pg_size_pretty
----------------
 35 MB
(1 row)

Extrayez de nouveau toutes les données de la table t2

b1=# SELECT * FROM t2;
   id
---------
       1
       2
       3
       4
       5
[...]
Time: 184.529 ms

La lecture est bien plus rapide car la table est en cache, en partie au niveau PostgreSQL, mais surtout au niveau système d'exploitation.

Que contient le cache de PostgreSQL ?

b1=# SELECT relfilenode, count(*) FROM pg_buffercache
  WHERE relfilenode=24765 GROUP BY 1 ORDER BY 1;
 relfilenode | count
-------------+-------
       24585 |    64
(1 row)

On en a un peu plus dans le cache. En fait, plus vous l'exécutez, et plus le nombre de blocs présents en cache augmentera.

Changez shared_buffers, puis redémarrez PostgreSQL.

Pour cela, il faut ouvrir le fichier de configuration postgresql.conf et modifier la valeur du paramètre shared_buffers à un quart de la mémoire.

Ensuite, il faut redémarrer PostgreSQL.

Extrayez de nouveau toutes les données de la table t2

b1=# SELECT * FROM t2;
   id
---------
       1
       2
       3
       4
       5
[...]
Time: 203.189 ms

Le temps d'exécution est un peu moins rapide que précédemment. En effet, le cache PostgreSQL a été vidé mais pas le cache du système d'exploitation.

Que contient le cache de PostgreSQL ?

b1=# SELECT relfilenode, count(*) FROM pg_buffercache
  WHERE relfilenode=24765 GROUP BY 1 ORDER BY 1;
 relfilenode | count
-------------+-------
       24585 |  4425
(1 row)

On se retrouve avec énormément plus de blocs directement dans le cache de PostgreSQL, et ce dès la première exécution. PostgreSQL est optimisé principalement pour du multi-utilisateurs. Dans ce cadre, il faut pouvoir exécuter plusieurs requêtes en même temps et donc chaque requête ne peut pas monopoliser tout le cache. De ce fait, chaque requête ne peut prendre qu'une partie réduite du cache. Mais plus le cache est gros, plus la partie est grosse.

Faire une mise à jour.

b1=# UPDATE t2 SET id=0 WHERE id<1000;
UPDATE 999

Que contient le cache de PostgreSQL ?

b1=# SELECT isdirty, count(*) FROM pg_buffercache
  WHERE relfilenode=24765 GROUP BY 1 ORDER BY 1;
 isdirty | count
---------+-------
 f       |  4421
 t       |    11
(2 rows)

Faites un CHECKPOINT

b1=# CHECKPOINT;
CHECKPOINT

Que contient le cache de PostgreSQL ?

b1=# SELECT isdirty, count(*) FROM pg_buffercache
b1=# WHERE relfilenode=24585 GROUP BY 1 ORDER BY 1;
 isdirty | count
---------+-------
 f       |  4432
(1 row)

Statistiques d'activités

Créer une table

b1=# CREATE TABLE t3 (id integer);
CREATE TABLE

Insérer des données

b1=# INSERT INTO t3 SELECT generate_series(1, 1000);
INSERT 0 1000

Lire ses statistiques d'activité

b1=# \x
Expanded display is on.
b1=# SELECT * FROM pg_stat_user_tables WHERE relname='t3';
-[ RECORD 1 ]-----+-------
relid             | 24594
schemaname        | public
relname           | t3
seq_scan          | 0
seq_tup_read      | 0
idx_scan          |
idx_tup_fetch     |
n_tup_ins         | 1000
n_tup_upd         | 0
n_tup_del         | 0
n_tup_hot_upd     | 0
n_live_tup        | 1000
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0

Les statistiques indiquent bien que 1000 lignes ont été insérées.

Statistiques sur les données

Créer une table avec une seule colonne de type integer.

b1=# CREATE TABLE t4 (c1 integer);
CREATE TABLE

Empêcher autovacuum d'analyser automatiquement la table.

b1=# ALTER TABLE t4 SET (autovacuum_enabled=false);
ALTER TABLE

Mettre des données différentes sur c1.

b1=# INSERT INTO t4 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Lire la table avec un filtre sur c1.

b1=# EXPLAIN SELECT * FROM t4 WHERE c1=100000;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Gather  (cost=1000.00..11866.15 rows=5642 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t4  (cost=0.00..10301.95 rows=2351 width=4)
         Filter: (c1 = 100000)
(4 rows)

Exécuter la commande ANALYZE.

b1=# ANALYZE t4;
ANALYZE

Lire la table avec un filtre sur c1. Que constatez-vous ?

b1=# EXPLAIN SELECT * FROM t4 WHERE c1=100000;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Gather  (cost=1000.00..10633.43 rows=1 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t4  (cost=0.00..9633.33 rows=1 width=4)
         Filter: (c1 = 100000)
(4 rows)

Les statistiques sont beaucoup plus précises. Il sait qu'il ne va récupérer qu'une seule ligne, sur le million de lignes dans la table. C'est le cas typique où un index est intéressant.

Ajouter un index sur c1.

b1=# CREATE INDEX ON t4(c1);
CREATE INDEX

Lire la table avec un filtre sur c1.

b1=# EXPLAIN SELECT * FROM t4 WHERE c1=100000;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Only Scan using t4_c1_idx on t4  (cost=0.42..8.44 rows=1 width=4)
   Index Cond: (c1 = 100000)
(2 rows)

Après création de l'index, on constate que PostgreSQL choisir un autre plan qui permet d'utiliser cet index.

Modifier la colonne c1 avec la valeur 1 pour toutes les lignes.

b1=# UPDATE t4 SET c1=100000;
UPDATE 1000000

Toutes les lignes ont la même valeur.

Lire la table avec un filtre sur c1.

b1=# EXPLAIN ANALYZE SELECT * FROM t4 WHERE c1=100000;
                  QUERY PLAN
---------------------------------------------------------
 Index Only Scan using t4_c1_idx on t4
       (cost=0.43..8.45 rows=1 width=4)
       (actual time=0.040..265.573 rows=1000000 loops=1)
   Index Cond: (c1 = 100000)
   Heap Fetches: 1000001
 Planning time: 0.066 ms
 Execution time: 303.026 ms
(5 rows)

Là, un parcours séquentiel serait plus performant. Mais comme PostgreSQL n'a plus de statistiques à jour, il se trompe de plan et utilise toujours l'index.

Exécuter la commande ANALYZE.

b1=# ANALYZE t4;
ANALYZE

Lire la table avec un filtre sur c1.

b1=# EXPLAIN ANALYZE SELECT * FROM t4 WHERE c1=100000;
                     QUERY PLAN 
----------------------------------------------------------
 Seq Scan on t4
       (cost=0.00..21350.00 rows=1000000 width=4)
       (actual time=75.185..186.019 rows=1000000 loops=1)
   Filter: (c1 = 100000)
 Planning time: 0.122 ms
 Execution time: 223.357 ms
(4 rows)

Avec des statistiques à jour et malgré la presence de l'index, PostgreSQL va utiliser un parcours séquentiel qui, au final, sera plus performant.

PostgreSQL s’appuie sur un modèle de gestion de transactions appelé MVCC. Nous allons expliquer cet acronyme, puis étudier en profondeur son implémentation dans le moteur.

Cette technologie a en effet un impact sur le fonctionnement et l’administration de PostgreSQL.

MVCC est un acronyme signifiant « MultiVersion Concurrency Control », ou « contrôle de concurrence multi-version ».

Le principe est de faciliter l’accès concurrent de plusieurs utilisateurs (sessions) à la base en disposant en permanence de plusieurs versions différentes d’un même enregistrement. Chaque session peut travailler simultanément sur la version qui s’applique à son contexte (on parle d’instantané ou de snapshot).

Par exemple, une transaction modifiant un enregistrement va créer une nouvelle version de cet enregistrement. Mais celui-ci ne devra pas être visible des autres transactions tant que le travail de modification n’est pas validé en base. Les autres transactions verront donc une ancienne version de cet enregistrement. La dénomination technique est lecture cohérente (consistent read en anglais).

Avant d’expliquer en détail MVCC, voyons l’autre solution de gestion de la concurrence qui s’offre à nous, afin de comprendre le problème que MVCC essaye de résoudre.

Une table contient une liste d’enregistrements.

• Une transaction voulant consulter un enregistrement doit le verrouiller (pour s’assurer qu’il n’est pas modifié) de façon partagée, le consulter, puis le déverrouiller.
• Une transaction voulant modifier un enregistrement doit le verrouiller de façon exclusive (personne d’autre ne doit pouvoir le modifier ou le consulter), le modifier, puis le déverrouiller.

De nombreux moteurs de base de données fonctionnent sur ce mode. MVCC devient progressivement la norme, de nombreux autres moteurs l’adoptant.

Cette solution a l’avantage de la simplicité : il suffit d’un gestionnaire de verrou pour gérer l’accès concurrent aux données. Elle a aussi l’avantage de la performance, dans le cas où les attentes de verrous sont peu nombreuses, la pénalité de verrouillage à payer étant peu coûteuse.

Elle a par contre des inconvénients :

• Les verrous sont en mémoire. Leur nombre est donc probablement limité. Que se passe-t-il si une transaction doit verrouiller 10 millions d’enregistrements ? On implémente habituellement des mécanismes de promotion de verrou. Les verrous lignes deviennent des verrous bloc, puis des verrous table. Le nombre de verrous est limité, et une promotion de verrou peut avoir des conséquences dramatiques.
• Un processus devant lire un enregistrement devra attendre la fin de la modification de celui-ci. Ceci entraîne rapidement de gros problèmes de contention. Les écrivains bloquent les lecteurs, et les lecteurs bloquent les écrivains. Évidemment, les écrivains se bloquent entre eux, mais cela est normal (on ne veut pas que deux transactions modifient le même enregistrement simultanément, chacune sans conscience de ce qu’a effectué l’autre).
• Un ordre SQL (surtout s’il dure longtemps) n’a aucune garantie de voir des données cohérentes du début à la fin de son exécution : si, par exemple, durant un SELECT long, un écrivain modifie à la fois des données déjà lues par le SELECT, et des données qu’il va lire, le SELECT n’aura pas une vue cohérente de la table. On pourrait avoir un total faux sur une table comptable par exemple, le SELECT ayant vu seulement une partie des données validées par une nouvelle transaction.
• Comment annuler une transaction ? Il faut un moyen de défaire ce qu’une transaction a effectué, au cas où elle ne se terminerait pas par une validation mais par une annulation.

C’est l’implémentation d’Oracle, par exemple. Un enregistrement, quand il doit être modifié, est recopié précédemment dans le tablespace d’UNDO. La nouvelle version de l’enregistrement est ensuite écrite par-dessus. Ceci implémente le MVCC (les anciennes versions de l’enregistrement sont toujours disponibles), et présente plusieurs avantages :

• Les enregistrements ne sont pas dupliqués dans la table. Celle-ci ne grandit donc pas suite à une mise à jour (si la nouvelle version n’est pas plus grande que la version précédente).
• Les enregistrements gardent la même adresse physique dans la table. Les index correspondant à des données non modifiées de l’enregistrement n’ont donc pas à être modifiés eux-mêmes, les index permettant justement de trouver l’adresse physique d’un enregistrement par rapport à une valeur.

Elle a aussi des défauts :

• La gestion de l’UNDO est très complexe : comment décider ce qui peut être purgé ? Il arrive que la purge soit trop agressive, et que des transactions n’aient plus accès aux vieux enregistrements (erreur SNAPSHOT TOO OLD sous Oracle, par exemple).
• La lecture cohérente est complexe à mettre en œuvre : il faut, pour tout enregistrement modifié, disposer des informations permettant de retrouver l’image avant modification de l’enregistrement (et la bonne image, il pourrait y en avoir plusieurs). Il faut ensuite pouvoir le reconstituer en mémoire.
• Il est difficile de dimensionner correctement le fichier d’UNDO. Il arrive d’ailleurs qu’il soit trop petit, déclenchant l’annulation d’une grosse transaction. Il est aussi potentiellement une source de contention entre les sessions.
• L’annulation (ROLLBACK) est très lente : il faut, pour toutes les modifications d’une transaction, défaire le travail, donc restaurer les images contenues dans l’UNDO, les réappliquer aux tables (ce qui génère de nouvelles écritures). Le temps d’annulation est habituellement très supérieur au temps de traitement initial devant être annulé.

Dans une table PostgreSQL, un enregistrement peut être stocké dans plusieurs versions. Une modification d’un enregistrement entraîne l’écriture d’une nouvelle version de celui-ci. Une ancienne version ne peut être recyclée que lorsqu’aucune transaction ne peut plus en avoir besoin, c’est-à-dire qu’aucune transaction n’a un instantané de la base plus ancien que l’opération de modification de cet enregistrement, et que cette version est donc invisible pour tout le monde. Chaque version d’enregistrement contient bien sûr des informations permettant de déterminer s’il est visible ou non dans un contexte donné.

Les avantages de cette implémentation stockant plusieurs versions dans la table principale sont multiples :

• La lecture cohérente est très simple à mettre en œuvre : à chaque session de lire la version qui l’intéresse. La visibilité d’une version d’enregistrement est simple à déterminer.
• Il n’y a pas d’UNDO. C’est un aspect de moins à gérer dans l’administration de la base.
• Il n’y a pas de contention possible sur l’UNDO.
• Il n’y a pas de recopie dans l’UNDO avant la mise à jour d’un enregistrement. La mise à jour est donc moins coûteuse.
• L’annulation d’une transaction est instantanée : les anciens enregistrements sont toujours disponibles.

Cette implémentation a quelques défauts :

• Il faut supprimer régulièrement les versions obsolètes des enregistrements.
• Il y a davantage de maintenance d’index (mais moins de contentions sur leur mise à jour).
• Les enregistrements embarquent des informations de visibilité, qui les rendent plus volumineux.

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé sera un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction).

Ce niveau d’isolation n’est nécessaire que pour les SGBD non-MVCC. Il est très dangereux : on peut lire des données invalides, ou temporaires, puisqu’on lit tous les enregistrements de la table, quel que soit leur état. Il est utilisé dans certains cas où les performances sont cruciales, au détriment de la justesse des données.

Sous PostgreSQL, ce mode est totalement inutile. Une transaction qui demande le niveau d’isolation READ UNCOMMITTED s’exécute en fait en READ COMMITTED.

Ce mode est le mode par défaut, et est suffisant dans de nombreux contextes. PostgreSQL étant MVCC, les écrivains et les lecteurs ne se bloquent pas mutuellement, et chaque ordre s’exécute sur un instantané de la base (ce n’est pas un pré-requis de READ COMMITTED dans la norme SQL). On ne souffre plus des lectures d’enregistrements non valides (dirty reads). On peut toutefois avoir deux problèmes majeurs d’isolation dans ce mode :

• Les lectures non-répétables (non-repeatable reads) : une transaction peut ne pas voir les mêmes enregistrements d’une requête sur l’autre, si d’autres transaction ont validé des modifications entre temps.
• Les lectures fantômes (phantom reads) : des enregistrements peuvent ne plus satisfaire une clause WHERE entre deux requêtes d’une même transaction.

Ce mode, comme son nom l’indique, permet de ne plus avoir de lectures non-répétables. Deux ordres SQL consécutifs dans la même transaction retourneront les mêmes enregistrements, dans la même version. En lecture seule, ces transactions ne peuvent pas échouer (elles sont entre autres utilisées pour réaliser des exports des données, par pg_dump).

En écriture, par contre (ou SELECT FOR UPDATE, FOR SHARE), si une autre transaction a modifié les enregistrements ciblés entre temps, une transaction en REPEATABLE READ va échouer avec l’erreur suivante :

ERROR: could not serialize access due to concurrent update

Il faut donc que l’application soit capable de la rejouer au besoin.

Ce niveau d’isolation souffre toujours des lectures fantômes, c’est-à-dire de lecture d’enregistrements qui ne satisfont plus la même clause WHERE entre deux exécutions de requêtes. Cependant, PostgreSQL est plus strict que la norme et ne permet pas ces lectures fantômes en REPEATABLE READ.

Le niveau SERIALIZABLE permet de développer comme si chaque transaction se déroulait seule sur la base. En cas d’incompatibilité entre les opérations réalisées par plusieurs transactions, PostgreSQL annule celle qui déclenchera le moins de perte de données. Tout comme dans le mode REPEATABLE READ, il est essentiel de pouvoir rejouer une transaction si on développe en mode SERIALIZABLE. Par contre, on simplifie énormément tous les autres points du développement.

Ce mode empêche les erreurs dues à une transaction effectuant un SELECT d’enregistrements, puis d’autres traitements, pendant qu’une autre transaction modifie les enregistrements vus par le SELECT : il est probable que le SELECT initial de notre transaction utilise les enregistrements récupérés, et que le reste du traitement réalisé par notre transaction dépende de ces enregistrements. Si ces enregistrements sont modifiés par une transaction concurrente, notre transaction ne s’est plus déroulée comme si elle était seule sur la base, et on a donc une violation de sérialisation.

Pour tous les détails (et des exemples)

PostgreSQL stocke des informations de visibilité dans chaque version d’enregistrement.

• xmin : l’identifiant de la transaction créant cette version.
• xmax : l’identifiant de la transaction invalidant cette version.

Ici, les deux enregistrements ont été créés par la transaction 100. Il s’agit peut-être, par exemple, de la transaction ayant importé tous les soldes à l’initialisation de la base.

On décide d’enregistrer un virement de 200 € du compte de M. Durand vers celui de M. Dupond. Ceci doit être effectué dans une seule transaction : l’opération doit être atomique, sans quoi de l’argent pourrait apparaître ou disparaître de la table.

Nous allons donc tout d’abord démarrer une transaction (ordre SQL BEGIN). PostgreSQL fournit donc à notre session un nouveau numéro de transaction (150 dans notre exemple). Puis nous effectuerons :

UPDATE soldes SET solde=solde-200 WHERE nom = 'M. Durand';

Puis nous effectuerons :

UPDATE soldes SET solde=solde+200 WHERE nom = 'M. Dupond';

Nous avons maintenant deux versions de chaque enregistrement.

Notre session ne voit bien sûr plus que les nouvelles versions de ces enregistrements, sauf si elle décidait d’annuler la transaction, auquel cas elle reverrait les anciennes données.

Pour une autre session, la version visible de ces enregistrements dépend de plusieurs critères :

• La transaction 150 a-t-elle été validée ? Sinon elle est invisible
• La transaction 150 est-elle postérieure à la nôtre (numéro supérieur au notre), et sommes-nous dans un niveau d’isolation (serializable) qui nous interdit de voir les modifications faites depuis le début de notre transaction ?
• La transaction 150 a-t-elle été validée après le démarrage de la requête en cours ? Une requête, sous PostgreSQL, voit un instantané cohérent de la base, ce qui implique que toute transaction validée après le démarrage de la requête doit être ignorée.

Dans le cas le plus simple, 150 ayant été validée, une transaction 160 ne verra pas les premières versions : xmax valant 150, ces enregistrements ne sont pas visibles. Elle verra les secondes versions, puisque xmin=150, et pas de xmax.

• La suppression d’un enregistrement s’effectue simplement par l’écriture d’un xmax dans la version courante.
• Il n’y a rien à écrire dans les tables pour annuler une transaction. Il suffit de marquer la transaction comme étant annulée dans la CLOG.

La CLOG est stockée dans une série de fichiers de 256 ko, stockés dans le répertoire pg_xact de PGDATA (répertoire racine de l’instance PostgreSQL).

Chaque transaction est créée dans ce fichier dès son démarrage et est encodée sur deux bits puisqu’une transaction peut avoir quatre états.

• TRANSACTION_STATUS_IN_PROGRESS : transaction en cours, c’est l’état initial
• TRANSACTION_STATUS_COMMITTED : la transaction a été validée
• TRANSACTION_STATUS_ABORTED : la transaction a été annulée
• TRANSACTION_STATUS_SUB_COMMITTED : ceci est utilisé dans le cas où la transaction comporte des sous-transactions, afin de valider l’ensemble des sous-transactions de façon atomique.

On a donc un million d’états de transactions par fichier de 256 ko.

Annuler une transaction (ROLLBACK) est quasiment instantané sous PostgreSQL : il suffit d’écrire TRANSACTION_STATUS_ABORTED dans l’entrée de CLOG correspondant à la transaction.

Toute modification dans la CLOG, comme toute modification d’un fichier de données (table, index, séquence), est bien sûr enregistrée tout d’abord dans les journaux de transactions (fichiers XLOG dans le répertoire pg_wal).

• Les lecteurs ne bloquent pas les écrivains, ni les écrivains les lecteurs.
• Le code gérant les instantanés est simple, ce qui est excellent pour la fiabilité, la maintenabilité et les performances.
• Les différentes sessions ne se gênent pas pour l’accès à une ressource commune (l’UNDO).
• Un enregistrement est facilement identifiable comme étant verrouillé en écriture : il suffit qu’il ait une version ayant un xmax correspondant à une transaction en cours.
• L’annulation est instantanée : il suffit d’écrire le nouvel état de la transaction dans la clog. Pas besoin de restaurer les valeurs précédentes, elles redeviennent automatiquement visibles.
• Les anciennes versions restent en ligne aussi longtemps que nécessaire. Elles ne pourront être effacées de la base qu’une fois qu’aucune transaction ne les considérera comme visibles.

Comme toute solution complexe, l’implémentation MVCC de PostgreSQL est un compromis. Les avantages cités précédemment sont obtenus au prix de concessions :

• Il faut nettoyer les tables de leurs enregistrements morts. C’est le travail de la commande VACUUM. On peut aussi voir ce point comme un avantage : contrairement à la solution UNDO, ce travail de nettoyage n’est pas effectué par le client faisant des mises à jour (et créant donc des enregistrements morts). Le ressenti est donc meilleur.
• Les tables sont forcément plus volumineuses que dans l’implémentation par UNDO, pour deux raisons :
  • Les informations de visibilité qui y sont stockées. Il y a un surcoût d’une douzaine d’octets par enregistrement.
  • Il y a toujours des enregistrements morts dans une table, une sorte de fond de roulement, qui se stabilise quand l’application est en régime stationnaire. Ces enregistrements sont recyclés à chaque passage de VACUUM.
• Les index n’ont pas d’information de visibilité. Il est donc nécessaire d’aller vérifier dans la table associée que l’enregistrement trouvé dans l’index est bien visible. Cela a un impact sur le temps d’exécutions de requêtes comme SELECT count(*) sur une table : il est nécessaire d’aller visiter tous les enregistrements pour s’assurer qu’ils sont bien visibles.

Le traitement VACUUM se déroule en trois passes. Cette première passe parcourt la table à nettoyer, à la recherche d’enregistrements morts. Un enregistrement est mort s’il possède un xmax qui correspond à une transaction validée, et que cet enregistrement n’est plus visible dans l’instantané d’aucune transaction en cours sur la base.

L’enregistrement mort ne peut pas être supprimé immédiatement : des enregistrements d’index pointent vers lui et doivent aussi être nettoyés. Les adresses (tid ou tuple id) des enregistrements sont donc mémorisés par la session effectuant le vacuum, dans un espace mémoire dont la taille est à hauteur de maintenance_work_mem. Si maintenance_work_mem est trop petit pour contenir tous les enregistrements morts en une seule passe, vacuum effectue plusieurs séries de ces trois passes.

Un tid est composé du numéro de bloc et du numéro d’enregistrement dans le bloc.

La seconde passe se charge de nettoyer les entrées d’index. Vacuum possède une liste de tid à invalider. Il parcourt donc tous les index de la table à la recherche de ces tid et les supprime. En effet, les index sont triés afin de mettre en correspondance une valeur de clé (la colonne indexée par exemple) avec un tid. Il n’est par contre pas possible de trouver un tid directement. Les pages entièrement vides sont supprimées de l’arbre et stockées dans la liste des pages réutilisables, la Free Space Map (FSM).

Maintenant qu’il n’y a plus d’entrée d’index pointant sur les enregistrements identifiés, nous pouvons supprimer les enregistrements de la table elle-même. C’est le rôle de cette passe, qui quant à elle, peut accéder directement aux enregistrements. Quand un enregistrement est supprimé d’un bloc, ce bloc est réorganisé afin de consolider l’espace libre, et cet espace libre est consolidé dans la Free Space Map (FSM).

Une fois cette passe terminée, si le parcours de la table n’a pas été terminé lors de la passe 1 (la maintenance_work_mem était pleine), le travail reprend où il en était du parcours de la table.

La version 9.6 intègre une nouvelle vue pour suivre la progression d’une commande VACUUM. Cette vue s’appelle pg_stat_progress_vacuum et contient une ligne par VACUUM en cours d’exécution.

Voici un exemple :

pid                | 4299
datid              | 13356
datname            | postgres
relid              | 16384
phase              | scanning heap
heap_blks_total    | 127293
heap_blks_scanned  | 86665
heap_blks_vacuumed | 86664
index_vacuum_count | 0
max_dead_tuples    | 291
num_dead_tuples    | 53

Dans cet exemple, le VACUUM exécuté par le PID 4299 a parcouru 86665 blocs (soit 68 % de la table), et en a traité 86664.

Les améliorations suivantes ont été ajoutées au fil des versions :

• En 8.3, les Heap-Only Tuples (HOT). Il s’agit de pouvoir stocker, sous condition, plusieurs versions du même enregistrement dans le même bloc. Ceci permettant au fur et à mesure des mises à jour de supprimer automatiquement les anciennes versions, sans besoin de VACUUM. Cela permet aussi de ne pas toucher aux index, qui pointent donc grâce à cela sur plusieurs versions du même enregistrement. Les conditions sont que :
  • Le bloc contienne assez de place pour la nouvelle version (on ne chaîne pas les enregistrements entre plusieurs blocs)
  • Aucune colonne indexée n’a été modifiée par l’opération.
• En 8.4, la Free Space Map est devenue dynamique. Jusqu’à ce moment-là, la Free Space Map était un segment de mémoire partagée statique. S’il était plein, les conséquences pouvaient être dramatiques (base qui enfle rapidement). Sa surveillance était donc une des tâches cruciales. Depuis la version 8.4, la Free Space Map est totalement dynamique, stockée dans des fichiers. Elle s’étend automatiquement au besoin.
• En 8.4, la Visibility Map a été introduite. Pour chaque bloc de chaque table, la Visibility Map permet de savoir que l’ensemble des enregistrements de ce bloc est visible. En cas de doute, ou d’enregistrement non visible, le bloc n’est pas marqué comme totalement visible. Cela permet à la phase 1 du traitement de VACUUM de ne plus parcourir toute la table, mais uniquement les enregistrements pour lesquels la Visibility Map est à faux (des données sont potentiellement obsolètes dans le bloc). VACUUM repositionne la Visibility Map à vrai après nettoyage d’un bloc, si tous les enregistrements sont visibles pour toutes les sessions.

Toutes ces optimisations visent le même but : rendre VACUUM le moins pénalisant possible, et simplifier la maintenance. La Visibility Map a permis en plus l’ajout des parcours d’index seuls en 9.2.

Le compteur de transactions de PostgreSQL est stocké sur 32 bits. Il peut donc, en théorie, y avoir un dépassement de ce compteur au bout de 4 milliards de transactions. En fait, le compteur est cyclique, et toute transaction considère que les 2 milliards de transactions supérieures à la sienne sont dans le futur, et les 2 milliards inférieures dans le passé. Le risque de bouclage est donc plus proche des 2 milliards.

En théorie, si on bouclait, de nombreux enregistrements deviendraient invisibles, car validés par des transactions futures. Heureusement PostgreSQL l’empêche. Au fil des versions, la protection est devenue plus efficace.

• Le moteur trace la plus vieille transaction d’une base, et refuse toute nouvelle transaction à partir du moment où le stock de transaction disponible est à 10 millions. Il suffit de lancer un VACUUM sur la base incriminée à ce point, qui débloquera la situation, en nettoyant les plus anciens xmin.
• Depuis l’arrivée d’AUTOVACUUM, celui-ci déclenche automatiquement un VACUUM quand le Wraparound se rapproche trop. Ceci se produit même si AUTOVACUUM est désactivé.

Si vous voulez en savoir plus, la documentation officielle contient un paragraphe sur ce sujet.

Ce qu’il convient d’en retenir, c’est que le système empêchera le wraparound en se bloquant à partir de la version 8.1 et que la protection contre ce phénomène est automatique depuis la version 8.2, mais déclenche automatiquement un VACUUM, quel que soit le paramétrage d’AUTOVACUUM. Les versions précédentes imposent une surveillance régulière des journaux.

maintenance_work_mem est la quantité de mémoire qu’un processus effectuant une opération de maintenance (c’est-à-dire n’exécutant pas des requêtes classiques comme SELECT, INSERT, UPDATE…) est autorisé à allouer pour sa tâche de maintenance.

Cette limite est utilisée dans deux contextes :

• La construction d’index, afin d’effectuer les tris de données.
• VACUUM.

Dans le cas de VACUUM, cette mémoire est utilisée pour stocker les tid (adresses d’enregistrement) des enregistrements pouvant être recyclés. Cette mémoire est remplie pendant la phase 1 du processus de VACUUM tel qu’expliqué au chapitre précédent. La taille d’un tid est 6 octets.

Si tous les enregistrements morts d’une table ne tiennent pas dans maintenance_work_mem, VACUUM est obligé de faire plusieurs passes de nettoyage. Le VACUUM devient plus coûteux, car chaque passe impose un parcours complet de chaque index. Une valeur assez élevée (256 Mo ou plus) de maintenance_work_mem est souvent conseillée, cette mémoire n’étant utilisée que lors des VACUUM et reconstructions d’index. On peut stocker plusieurs dizaines de millions d’enregistrements à effacer dans 256 Mo.

Ces paramètres permettent de limiter l’agressivité disque de Vacuum.

vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty permettent d’affecter un coût arbitraire aux trois actions suivantes :

• vacuum_cost_page_hit : le coût d’accès à une page qui est dans le cache (défaut : 1)

• vacuum_cost_page_miss : le coût d’accès à une page qui n’est pas dans le cache (défaut : 10)

• vacuum_cost_page_dirty : le coût de modification d’une page, et donc d’avoir à l’écrire (défaut : 20)

Ces paramètres de coût permettent de « mesurer » l’activité de VACUUM, et le mettre en pause quand il aura atteint cette limite. Ce second point est gouverné par :

• vacuum_cost_limit : le coût à atteindre avant de déclencher une pause (défaut : 200)
• vacuum_cost_delay : le temps à attendre une fois que le coût est atteint (défaut : 0 ms, c’est à dire pas d’attente).

Ces paramètres permettent de limiter l’agressivité de VACUUM, en l’obligeant à faire des pauses à intervalle de travail régulier. Par défaut, VACUUM fonctionne aussi vite que possible.

Il est déconseillé de modifier les paramètres vacuum_cost_page_*. Habituellement, on modifie soit le délai, soit la limite, si on souhaite réduire la vitesse de VACUUM. Ce paramètre ne s’applique que pour les VACUUM lancés « manuellement » (en ligne de commande, via vacuumdb), pas pour des VACUUM lancés par le processus autovacuum.

Il est conseillé de laisser vacuum_cost_limit et vacuum_cost_delay aux valeurs par défaut (pas de limitation de débit) : quand VACUUM est lancé à la main, habituellement, c’est soit pour une procédure de traitement de nuit, soit en urgence. Dans les deux cas, il est la plupart du temps préférable que ces traitements s’exécutent aussi vite que possible.

On peut aussi utiliser ces deux paramètres pour la recherche des paramètres optimaux d’autovacuum (voir plus loin).

Afin d’éviter le « wraparound », VACUUM modifie le xmin des vieux enregistrements afin que ceux-ci ne se retrouvent pas brusquement dans le futur.

Il pourrait positionner le xmin à une valeur plus récente, ce qui serait suffisant. Toutefois, un identifiant spécial de transaction a été réservé à cet usage (l’identifiant frozenxid), identifiant qui est toujours dans le passé.

Il reste à déterminer :

• à partir de quel âge un enregistrement peut et doit être gelé si VACUUM l’aperçoit : c’est le rôle de vacuum_freeze_min_age ;
• à partir de quel moment VACUUM doit déclencher un traitement de nettoyage de toute la table (et non pas uniquement les blocs modifiés depuis le dernier VACUUM), afin de nettoyer tous les vieux enregistrements. C’est le rôle de vacuum_freeze_table_age.

Les deux valeurs par défaut sont satisfaisantes pour la plupart des installations.

Autovacuum est un processus de l’instance PostgreSQL. Il est disponible en module contrib à partir de la version 7.4, et intégré au moteur depuis la version 8.1, et activé par défaut (donc recommandé) depuis la version 8.3.

Le principe est le suivant :

• Le démon autovacuum se réveille à intervalle régulier, et inspecte les statistiques sur les tables des différentes bases (vous pouvez les consulter dans la vue pg_stat_all_tables). Ces statistiques sont liées au nombre d’INSERT, UPDATE et DELETE pour chaque table.
• Une fois qu’une table a dépassé la limite paramétrée de mises à jour, autovacuum démarre un « worker » (processus chargé d’effectuer un traitement), afin qu’il déclenche le traitement sur la table. Le nombre de ces workers est limité, afin de ne pas engendrer de charge trop élevée.
• autovacuum ne se cantonne pas à exécuter des commandes VACUUM. Il s’occupe aussi de la collecte des statistiques sur les données. Suivant la quantité et le type de modifications, il déclenchera soit un VACUUM ANALYZE (vacuum et collecte des statistiques en une seule commande), soit seulement un ANALYZE.

• autovacuum : on/off. Détermine si autovacuum doit être activé. « on » par défaut depuis la version 8.3
• autovacuum_naptime : temps d’attente entre deux périodes de vérification. Depuis la version 8.3, il s’agit du temps entre deux passages sur la même base de l’instance. Précédemment, il fallait multiplier naptime par le nombre de bases de l’instance pour obtenir la fréquence de passage sur une base.
• autovacuum_max_workers : nombre maximum de « workers » qu’autovacuum pourra déclencher simultanément. La valeur par défaut (3) est généralement suffisante. Néanmoins, si vous constatez qu'il y a fréquemment trois autovacuum workers travaillant en même temps, il peut être nécessaire d'augmenter ce paramètre.
• autovacuum_work_mem : permet de surcharger maintenance_work_mem spécifiquement pour l'autovacuum (désactivé par défaut)

Ces paramètres sont les critères de déclenchement d’autovacuum sur une table.

• VACUUM ANALYZE :
  • autovacuum_vacuum_threshold : nombre minimum d’enregistrements devant être morts (mis à jour ou supprimés) dans la table avant de déclencher un VACUUM (50 par défaut).
  • autovacuum_vacuum_scale_factor : fraction du nombre d’enregistrements de la table à ajouter à autovacuum_vacuum_threshold avant de déclencher un VACUUM (0.2 par défaut).
  • Un VACUUM est donc déclenché si :

nb_enregistrements_morts (n_dead_tup) >=
    autovacuum_vacuum_threshold + nb_enregs × autovacuum_vacuum_scale_factor

• ANALYZE :
  • autovacuum_analyze_threshold : nombre minimum d’enregistrements devant être ajoutés, modifiés ou supprimés dans la table avant de déclencher un ANALYZE (25 par défaut).
  • autovacuum_analyze_scale_factor : fraction du nombre d’enregistrements de la table à ajouter à autovacuum_analyze_threshold avant de déclencher un ANALYZE (0.1 par défaut).
  • Un ANALYZE est donc déclenché si :

nb_insert+nb_updates+nb_delete >=
    autovacuum_analyze_threshold + nb_enregs × autovacuum_analyze_scale_factor

Attention : les INSERT sont pris en compte pour ANALYZE, puisqu’ils modifient le contenu de la table. Ils ne sont pas pris en compte pour VACUUM, puisqu’ils ne créent pas d’enregistrement mort.

Tout comme les VACUUM interactifs, les VACUUM d’autovacuum peuvent être limités en débit. Contrairement aux VACUUM interactifs, les VACUUM d’autovacuum sont par défaut limités en débit : autovacuum_vacuum_cost_delay vaut 20 ms.

La signification des paramètres est la même que pour vacuum_cost_limit et vacuum_cost_delay.

autovacuum_vacuum_cost_limit et autovacuum_vacuum_cost_delay peuvent en plus prendre la valeur « -1 », signifiant qu’ils héritent leur valeur de vacuum_cost_limit et vacuum_cost_delay.

autovacuum_freeze_max_age est l’âge maximum que peut avoir le plus vieil enregistrement d’une table avant qu’un VACUUM soit lancé sur cette table pour éviter un Wraparound. Ce traitement est lancé même si autovacuum est désactivé (c’est-à-dire positionné à off).

PostgreSQL dispose d’un gestionnaire de verrous, comme tout SGBD.

Ce gestionnaire de verrous est capable de gérer des verrous sur des tables, sur des enregistrements, sur des ressources virtuelles. De nombreux types de verrous - 8 - sont disponibles, chacun entrant en conflit avec d’autres.

Chaque opération doit tout d’abord prendre un verrou sur les objets à manipuler.

Le gestionnaire de verrous détecte tout verrou mortel (deadlock) entre deux sessions. Un deadlock est la suite de prise de verrous entraînant le blocage mutuel d’au moins deux sessions, chacune étant en attente d’un des verrous acquis par l’autre.

On peut accéder aux verrous actuellement utilisés sur un cluster par la vue pg_locks.

Le gestionnaire de verrous fournit des verrous sur enregistrement. Ceux-ci sont utilisés pour verrouiller un enregistrement le temps d’y écrire un xmax, puis libérés immédiatement.

Le verrouillage réel est implémenté comme suit :

• Chaque transaction verrouille son objet « identifiant de transaction » de façon exclusive.
• Une transaction voulant mettre à jour un enregistrement consulte le xmax. S’il constate que ce xmax est celui d’une transaction en cours, il demande un verrou exclusif sur l’objet « identifiant de transaction » de cette transaction. Qui ne lui est naturellement pas accordé. Il est donc placé en attente.
• Quand la transaction possédant le verrou se termine (COMMIT ou ROLLBACK), son verrou sur l’objet « identifiant de transaction » est libéré, débloquant ainsi l’autre transaction, qui peut reprendre son travail.

Ce mécanisme ne nécessite pas un nombre de verrous mémoire proportionnel au nombre d’enregistrements à verrouiller, et simplifie le travail du gestionnaire de verrous, celui-ci ayant un nombre bien plus faible de verrous à gérer.

Le mécanisme exposé ici est légèrement simplifié. Pour une explication approfondie, n’hésitez pas à consulter l’article suivant issu de la base de connaissance Dalibo.

• max_locks_per_transaction et max_pred_locks_per_transaction servent à dimensionner l’espace en mémoire partagée réservé aux verrous. Le nombre de verrous total est :

(max_locks_per_transaction + max_pred_locks_per_transaction) × max_connections

Le nombre maximum de verrous d’une session n’est pas limité à max_locks_per_transaction, une session peut acquérir autant de verrous qu’elle le souhaite. La valeur par défaut de 64 est largement suffisante la plupart du temps.

• Si une session attend un verrou depuis plus longtemps que lock_timeout, la requête est annulée.

• Si une session reste plus de deadlock_timeout en attente, le système vérifie que cette transaction n’est pas en deadlock. La valeur par défaut est 1 seconde, ce qui est largement suffisant la plupart du temps : les deadlocks sont assez rares, il n’est donc pas intéressant d’être trop agressif dans leur vérification.

• log_lock_waits : si une session reste plus de deadlock_timeout en attente de verrou et que ce paramètre est à on, et qu’elle n’est pas effectivement victime d’un verrou mortel (deadlock), le système trace cet événement dans le journal. Est aussi tracé le moment où la session obtient réellement son verrou. La valeur est off par défaut.

Énoncés

Niveaux d'isolation

• Créez une nouvelle base.

• Créez une table avec deux colonnes.
• Ajoutez cinq lignes dans cette table.
• Ouvrez une transaction
• Lire la table t1.
• Avec une autre session, modifiez quelques lignes de la table t1.
• Revenez à la première session et lisez de nouveau toute la table.
• Fermer la transaction et ouvrez-en une nouvelle, cette fois-ci en REPEATABLE READ.
• Lisez la table.
• Avec une autre session, modifiez quelques lignes de la table t1.
• Revenez à la première session et lisez de nouveau toute la table.

• Que s'est-il passé ?

Effets de MVCC

• Créez une nouvelle table avec deux colonnes.

• Ajoutez cinq lignes dans cette table.
• Lisez la table.
• Commencez une transaction et modifiez une ligne.
• Lisez la table.
• Que remarquez-vous ?
• Ouvrez une autre session et lisez la table.
• Qu'observez-vous ?
• Récupérez quelques informations systèmes xmin et xmax pour les deux sessions.
• Récupérez maintenant en plus le CTID.
• Validez la transaction.
• Installez l'extension pageinspect.

• Décodez le bloc 0 de la table t2 à l'aide de cette extension.

Traiter la fragmentation

• Exécutez un VACUUM VERBOSE sur la table t2.

• Trouvez la ligne intéressante dans les traces de la commande, et indiquez pourquoi.
• Créez une autre table (une seule colonne de type integer est nécessaire).
• Désactivez l'autovacuum pour cette table.
• Insérez un million de lignes dans cette table.
• Récupérez la taille de la table.
• Supprimez les 500000 premières lignes.
• Récupérez la taille de la table. Qu'en déduisez-vous ?
• Exécutez un VACUUM.
• Récupérez la taille de la table. Qu'en déduisez-vous ?
• Exécutez un VACUUM FULL.
• Récupérez la taille de la table. Qu'en déduisez-vous ?
• Créez encore une autre table (une seule colonne de type integer est nécessaire).
• Désactivez l'autovacuum pour cette table.
• Insérez un million de lignes dans cette table.
• Récupérez la taille de la table.
• Supprimez les 500000 dernières lignes.
• Récupérez la taille de la table. Qu'en déduisez-vous ?
• Exécutez un VACUUM.

• Récupérez la taille de la table. Qu'en déduisez-vous ?

Détecter la fragmentation

• Installez l'extension pg_freespacemap.

• Créer une autre table à deux colonnes (integer et text).
• Désactivez l'autovacuum pour cette table.
• Insérer un million de lignes dans cette table.
• Que rapporte pg_freespacemap quant à l'espace libre de la table ?
• Modifier des données (par exemple 200000 lignes).
• Que rapporte pg_freespacemap quant à l'espace libre de la table ?
• Exécutez un VACUUM sur la table.
• Que rapporte pg_freespacemap quant à l'espace libre de la table ? Qu'en déduisez-vous ?
• Récupérez la taille de la table.
• Exécutez un VACUUM FULL sur la table.

• Récupérez la taille de la table et l'espace libre rapporté par pg_freespacemap. Qu'en déduisez-vous ?

Gestion de l'autovacuum

• Créez une table avec une colonne de type integer.

• Insérer un million de lignes dans cette table.
• Que contient la vue pg_stat_user_tables pour cette table ?
• Modifiez 200000 lignes de cette table.
• Attendez une minute.
• Que contient la vue pg_stat_user_tables pour cette table ?
• Modifier 60 lignes supplémentaires de cette table.
• Attendez une minute.
• Que contient la vue pg_stat_user_tables pour cette table ? Qu'en déduisez-vous ?
• Descendez le facteur d'échelle de cette table à 10% pour le VACUUM.
• Modifiez 200000 lignes de cette table ?
• Attendez une minute.

• Que contient la vue pg_stat_user_tables pour cette table ? Qu'en déduisez-vous ?

Verrous

• Ouvrez une transaction et lisez la table t1.

• Ouvrez une autre transaction, et tentez de supprimer la table t1.
• Listez les processus du serveur PostgreSQL. Que remarquez-vous ?
• Récupérez la liste des sessions en attente d'un verrou avec la vue pg_stat_activity.
• Récupérez la liste des verrous en attente pour la requête bloquée.
• Récupérez le nom de l'objet dont on n'arrive pas à récupérer le verrou.
• Récupérez la liste des verrous sur cet objet. Quel processus a verrouillé la table t1 ?
• Retrouvez les informations sur la session bloquante.

Solutions

Niveaux d'isolation

Créez une nouvelle base.

$ createdb b2

Créez une table avec deux colonnes.

b2=# CREATE TABLE t1 (c1 integer, c2 text);
CREATE TABLE

Ajoutez cinq lignes dans cette table.

b2=# INSERT INTO t1 VALUES
  (1, 'un'), (2, 'deux'), (3, 'trois'), (4, 'quatre'), (5, 'cinq');
INSERT 0 5

Ouvrez une transaction

b2=# BEGIN;
BEGIN

Lire la table t1.

b2=# SELECT * FROM t1;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  3 | trois
  4 | quatre
  5 | cinq
(5 rows)

Avec une autre session, modifiez quelques lignes de la table t1.

$ psql b2
psql (10)
Type "help" for help.

b2=# UPDATE t1 SET c2=upper(c2) WHERE c1=3;
UPDATE 1

Revenez à la première session et lisez de nouveau toute la table.

b2=# SELECT * FROM t1;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  4 | quatre
  5 | cinq
  3 | TROIS
(5 rows)

Les modifications réalisées par la deuxième transaction sont immédiatement visibles par la première transaction. C'est le cas des transactions en niveau d'isolation READ COMMITED.

Fermer la transaction et ouvrez-en une nouvelle.

b2=# ROLLBACK;
ROLLBACK
b2=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Lisez la table.

b2=# SELECT * FROM t1;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  4 | quatre
  5 | cinq
  3 | TROIS
(5 rows)

Avec une autre session, modifiez quelques lignes de la table t1.

$ psql b2
psql (10)
Type "help" for help.

b2=# UPDATE t1 SET c2=upper(c2) WHERE c1=4;
UPDATE 1

Revenez à la première session et lisez de nouveau toute la table.

b2=# SELECT * FROM t1;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  4 | quatre
  5 | cinq
  3 | TROIS
(5 rows)

Que s'est-il passé ?

En niveau d'isolation REPEATABLE READ, la transaction est certaine de ne pas voir les modifications réalisées par d'autres transactions (à partir de la première lecture de la table).

Effets de MVCC

Créez une nouvelle table avec deux colonnes.

b2=# CREATE TABLE t2 (c1 integer, c2 text);
CREATE TABLE

Ajoutez cinq lignes dans cette table.

b2=# INSERT INTO t2 VALUES
  (1, 'un'), (2, 'deux'), (3, 'trois'), (4, 'quatre'), (5, 'cinq');
INSERT 0 5

Lisez la table.

b2=# SELECT * FROM t2;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  3 | trois
  4 | quatre
  5 | cinq
(5 rows)

Commencez une transaction et modifiez une ligne.

b2=# BEGIN;
BEGIN
b2=# UPDATE t2 SET c2=upper(c2) WHERE c1=3;
UPDATE 1

Lisez la table.

b2=# SELECT * FROM t2;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  4 | quatre
  5 | cinq
  3 | TROIS
(5 rows)

Que remarquez-vous ?

La ligne mise à jour n'apparaît plus, ce qui est normal. Elle apparaît en fin de table. En effet, quand un UPDATE est exécuté, la ligne courante est considérée comme morte et une nouvelle ligne est ajoutée, avec les valeurs modifiées. Comme nous n'avons pas demandé de récupérer les résultats dans un certain ordre, les lignes sont affichées dans leur ordre de stockage dans les blocs de la table.

Ouvrez une autre session et lisez la table.

$ psql b2
psql (10)
Type "help" for help.

b2=# SELECT * FROM t2;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  3 | trois
  4 | quatre
  5 | cinq
(5 rows)

Qu'observez-vous ?

Les autres sessions voient toujours l'ancienne version de ligne, tant que la transaction n'a pas été validée. Et du coup, l'ordre des lignes en retour n'est pas le même vu que cette version de ligne était introduite avant.

Récupérez quelques informations systèmes xmin et xmax pour les deux sessions.

Voici ce que renvoie la session qui a fait la modification :

b2=# SELECT xmin, xmax, * FROM t2;
 xmin | xmax | c1 |   c2
------+------+----+--------
 1930 |    0 |  1 | un
 1930 |    0 |  2 | deux
 1930 |    0 |  4 | quatre
 1930 |    0 |  5 | cinq
 1931 |    0 |  3 | TROIS
(5 rows)

Et voici ce que renvoie l'autre session :

b2=# SELECT xmin, xmax, * FROM t2;
 xmin | xmax | c1 |   c2
------+------+----+--------
 1930 |    0 |  1 | un
 1930 |    0 |  2 | deux
 1930 | 1931 |  3 | trois
 1930 |    0 |  4 | quatre
 1930 |    0 |  5 | cinq
(5 rows)

La transaction 1931 est celle qui a réalisé la modification. La colonne xmin de la nouvelle version de ligne contient ce numéro. De même pour la colonne xmax de l'ancienne version de ligne. PostgreSQL se base sur cette information pour savoir si telle transaction peut lire telle ou telle ligne.

Récupérez maintenant en plus le CTID.

Voici ce que renvoie la session qui a fait la modification :

b2=# SELECT ctid, xmin, xmax, * FROM t2;
 ctid  | xmin | xmax | c1 |   c2
-------+------+------+----+--------
 (0,1) | 1930 |    0 |  1 | un
 (0,2) | 1930 |    0 |  2 | deux
 (0,4) | 1930 |    0 |  4 | quatre
 (0,5) | 1930 |    0 |  5 | cinq
 (0,6) | 1931 |    0 |  3 | TROIS
(5 rows)

Et voici ce que renvoie l'autre session :

b2=# SELECT ctid, xmin, xmax, * FROM t2;
 ctid  | xmin | xmax | c1 |   c2
-------+------+------+----+--------
 (0,1) | 1930 |    0 |  1 | un
 (0,2) | 1930 |    0 |  2 | deux
 (0,3) | 1930 | 1931 |  3 | trois
 (0,4) | 1930 |    0 |  4 | quatre
 (0,5) | 1930 |    0 |  5 | cinq
(5 rows)

La colonne ctid contient une paire d'entiers. Le premier indique le numéro de bloc, le second le numéro de l'enregistrement dans le bloc. Autrement, elle précise la position de l'enregistrement sur le fichier de la table.

En récupérant cette colonne, on voit bien que la première session voit la nouvelle position (enregistrement 6 du bloc 0) et que la deuxième session voit l'ancienne (enregistrement 3 du bloc 0).

Validez la transaction.

b2=# COMMIT;
COMMIT

Installez l'extension pageinspect.

b2=# CREATE EXTENSION pageinspect;
CREATE EXTENSION

Décodez le bloc 0 de la table t2 à l'aide de cette extension.

b4=# SELECT * FROM heap_page_items(get_raw_page('t2',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | 
----+--------+----------+--------+--------+--------+----------+--------+-
  1 |   8160 |        1 |     31 |   2169 |      0 |        0 | (0,1)  |
  2 |   8120 |        1 |     33 |   2169 |      0 |        0 | (0,2)  |
  3 |   8080 |        1 |     34 |   2169 |   2170 |        0 | (0,6)  |
  4 |   8040 |        1 |     35 |   2169 |      0 |        0 | (0,4)  |
  5 |   8000 |        1 |     33 |   2169 |      0 |        0 | (0,5)  |
  6 |   7960 |        1 |     34 |   2170 |      0 |        0 | (0,6)  |
    
 lp | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
----+-------------+------------+--------+--------+-------
  1 |           2 |       2306 |     24 |        |
  2 |           2 |       2306 |     24 |        |
  3 |       16386 |        258 |     24 |        |
  4 |           2 |       2306 |     24 |        |
  5 |           2 |       2306 |     24 |        |
  6 |       32770 |      10242 |     24 |        |
(6 rows)

Que peut-on remarquer ?

• les six lignes sont bien présentes ;
• le t_ctid ne contient plus (0,3) mais l'adresse de la nouvelle ligne (ie, (0,6] ;
• t_infomask2 est un champ de bits, la valeur 16386 pour l'ancienne version nous indique que le changement a eu lieu en utilisant la technologie HOT.

Traiter la fragmentation

Exécutez un VACUUM VERBOSE sur la table t2.

b2=# VACUUM VERBOSE t2;
INFO:  vacuuming "public.t2"
INFO:  "t2": found 1 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
         There were 0 unused item pointers.
         0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_24628"
INFO:  index "pg_toast_24628_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
         0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_24628": found 0 removable, 0 nonremovable row versions
                         in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
         There were 0 unused item pointers.
         0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Trouvez la ligne intéressante dans les traces de la commande, et indiquez pourquoi.

Il s'agit de la ligne suivante :

INFO:  "t2": found 1 removable, 5 nonremovable row versions in 1 out of 1 pages

Il y a en effet une ligne obsolète (et récupérable) et cinq lignes vivantes sur le seul bloc de la table.

Créez une autre table (une seule colonne de type integer est nécessaire).

b2=# CREATE TABLE t3(id integer);
CREATE TABLE

Désactivez l'autovacuum pour cette table.

b2=# ALTER TABLE t3 SET (autovacuum_enabled=false);
ALTER TABLE

Insérez un million de lignes dans cette table.

b2=# INSERT INTO t3 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Récupérez la taille de la table.

b2=# SELECT pg_size_pretty(pg_table_size('t3'));
 pg_size_pretty
----------------
 35 MB
(1 row)

Supprimez les 500000 premières lignes.

b2=# DELETE FROM t3 WHERE id<500000;
DELETE 499999

Récupérez la taille de la table. Qu'en déduisez-vous ?

b2=# SELECT pg_size_pretty(pg_table_size('t3'));
 pg_size_pretty
----------------
 35 MB
(1 row)

Un DELETE ne permet pas de regagner de la place sur le disque. Les lignes supprimées sont uniquement marquées comme étant mortes.

Exécutez un VACUUM.

b2=# VACUUM t3;
VACUUM

Récupérez la taille de la table. Qu'en déduisez-vous ?

b2=# SELECT pg_size_pretty(pg_table_size('t3'));
 pg_size_pretty
----------------
 35 MB
(1 row)

VACUUM ne permet pas non plus de gagner en espace disque. Principalement, il renseigne la structure FSM sur les emplacements libres dans les fichiers des tables.

Exécutez un VACUUM FULL.

b2=# VACUUM FULL t3;
VACUUM

Récupérez la taille de la table. Qu'en déduisez-vous ?

b2=# SELECT pg_size_pretty(pg_table_size('t3'));
 pg_size_pretty
----------------
 17 MB
(1 row)

Là, par contre, on gagne en place disque. Le VACUUM FULL défragmente la table et du coup, on récupère les espaces morts.

Créez encore une autre table (une seule colonne de type integer est nécessaire).

b2=# CREATE TABLE t4(id integer);
CREATE TABLE

Désactivez l'autovacuum pour cette table.

b2=# ALTER TABLE t4 SET (autovacuum_enabled=false);
ALTER TABLE

Insérez un million de lignes dans cette table.

b2=# INSERT INTO t4 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Récupérez la taille de la table.

b2=# SELECT pg_size_pretty(pg_table_size('t4'));
 pg_size_pretty
----------------
 35 MB
(1 row)

Supprimez les 500000 dernières lignes.

b2=# DELETE FROM t4 WHERE id>500000;
DELETE 500000

Récupérez la taille de la table. Qu'en déduisez-vous ?

b2=# SELECT pg_size_pretty(pg_table_size('t4'));
 pg_size_pretty
----------------
 35 MB
(1 row)

Là-aussi, on n'a rien perdu.

Exécutez un VACUUM.

b2=# VACUUM t4;
VACUUM

Récupérez la taille de la table. Qu'en déduisez-vous ?

b2=# SELECT pg_size_pretty(pg_table_size('t4'));
 pg_size_pretty
----------------
 17 MB
(1 row)

En fait, il existe un cas où on peut gagner de l'espace disque suite à un VACUUM simple : quand l'espace récupéré se trouve en fin de table et qu'il est possible de prendre rapidement un verrou exclusif sur la table pour la tronquer. C'est assez peu fréquent mais c'est une optimisation intéressante.

Détecter la fragmentation

Installez l'extension pg_freespacemap.

b2=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION

Créer une autre table à deux colonnes (integer et text).

b2=# CREATE TABLE t5 (c1 integer, c2 text);
CREATE TABLE

Désactivez l'autovacuum pour cette table.

b2=# ALTER TABLE t5 SET (autovacuum_enabled=false);
ALTER TABLE

Insérer un million de lignes dans cette table.

b2=# INSERT INTO t5 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
INSERT 0 1000000

Que rapporte pg_freespacemap quant à l'espace libre de la table ?

b2=# SELECT sum(avail) FROM pg_freespace('t5'::regclass);
 sum
-----
   0
(1 row)

Modifier des données (par exemple 200000 lignes).

b2=# UPDATE t5 SET c2=upper(c2) WHERE c1<200000;
UPDATE 199999

Que rapporte pg_freespacemap quant à l'espace libre de la table ?

b2=# SELECT sum(avail) FROM pg_freespace('t5'::regclass);
 sum
-----
  32
(1 row)

Exécutez un VACUUM sur la table.

b2=# VACUUM t5;
VACUUM

Que rapporte pg_freespacemap quant à l'espace libre de la table ? Qu'en déduisez-vous ?

b2=# SELECT sum(avail) FROM pg_freespace('t5'::regclass);
   sum
---------
 8806784
(1 row)

Il faut exécuter un VACUUM pour que PostgreSQL renseigne la structure FSM, ce qui nous permet de connaître le taux de fragmentation de la table.

Récupérez la taille de la table.

b2=# SELECT pg_size_pretty(pg_table_size('t5'));
 pg_size_pretty
----------------
 58 MB
(1 row)

Exécutez un VACUUM FULL sur la table.

b2=# VACUUM FULL t5;
VACUUM

Récupérez la taille de la table et l'espace libre rapporté par pg_freespacemap. Qu'en déduisez-vous ?

b2=# SELECT sum(avail) FROM pg_freespace('t5'::regclass);
 sum
-----
   0
(1 row)

b2=# SELECT pg_size_pretty(pg_table_size('t5'));
 pg_size_pretty
----------------
 49 MB
(1 row)

VACUUM FULL a supprimé les espaces morts, ce qui nous a fait gagner entre 8 et 9 Mo. La taille de la table maintenant correspond bien à celle de l'ancienne table, moins la place prise par les lignes mortes.

Gestion de l'autovacuum

Créez une table avec une colonne de type integer.

b2=# CREATE TABLE t6 (id integer);
CREATE TABLE

Insérer un million de lignes dans cette table.

b2=# INSERT INTO t6 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Que contient la vue pg_stat_user_tables pour cette table ?

b2=# \x
Expanded display is on.
b2=# SELECT * FROM pg_stat_user_tables WHERE relname='t6';
-[ RECORD 1 ]-------+--------
relid               | 24851
schemaname          | public
relname             | t6
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1000000
n_dead_tup          | 0
n_mod_since_analyze | 1000000
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Modifiez 200000 lignes de cette table.

b2=# UPDATE t6 SET id=2000000 WHERE id<200001;
UPDATE 200000

Attendez une minute.

b2=# SELECT pg_sleep(60);

Que contient la vue pg_stat_user_tables pour cette table ?

b2=# SELECT * FROM pg_stat_user_tables WHERE relname='t6';
-[ RECORD 1 ]-------+------------------------------
relid               | 24851
schemaname          | public
relname             | t6
seq_scan            | 1
seq_tup_read        | 1000000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000000
n_tup_upd           | 200000
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1000000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 2017-09-19 09:53:22.70433-04
last_analyze        | 
last_autoanalyze    | 2017-09-19 09:53:23.325561-04
vacuum_count        | 0
autovacuum_count    | 1
analyze_count       | 0
autoanalyze_count   | 1

Modifier 60 lignes supplémentaires de cette table ?

b2=# UPDATE t6 SET id=2000000 WHERE id<200060;
UPDATE 59

Attendez une minute.

b2=# SELECT pg_sleep(60);

Que contient la vue pg_stat_user_tables pour cette table ? Qu'en déduisez-vous ?

b2=# SELECT * FROM pg_stat_user_tables WHERE relname='t6';
-[ RECORD 1 ]-------+------------------------------
relid               | 24851
schemaname          | public
relname             | t6
seq_scan            | 2
seq_tup_read        | 2000000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000000
n_tup_upd           | 200059
n_tup_del           | 0
n_tup_hot_upd       | 10
n_live_tup          | 1000000
n_dead_tup          | 59
n_mod_since_analyze | 59
last_vacuum         | 
last_autovacuum     | 2017-09-19 09:53:22.70433-04
last_analyze        | 
last_autoanalyze    | 2017-09-19 09:53:23.325561-04
vacuum_count        | 0
autovacuum_count    | 1
analyze_count       | 0
autoanalyze_count   | 1

Un VACUUM a été automatiquement exécuté sur cette table, suite à la suppression de plus de 200050 lignes (threshold + scale factor * #lines). Il a fallu attendre que l'autovacuum vérifie l'état des tables, d'où l'attente de 60 secondes.

Notez aussi que n_dead_tup est revenu à 0 après le VACUUM. C'est le compteur qui est comparé à la limite avant exécution d'un VACUUM.

Descendez le facteur d'échelle de cette table à 10% pour le VACUUM.

b2=# ALTER TABLE t6 SET (autovacuum_vacuum_scale_factor=0.1);
ALTER TABLE

Modifiez 200000 lignes de cette table ?

b2=# UPDATE t6 SET id=2000000 WHERE id<=400060;
UPDATE 200000

Attendez une minute.

b2=# SELECT pg_sleep(60);

Que contient la vue pg_stat_user_tables pour cette table ? Qu'en déduisez-vous ?

b2=# SELECT * FROM pg_stat_user_tables WHERE relname='t6';
-[ RECORD 1 ]-------+------------------------------
relid               | 24851
schemaname          | public
relname             | t6
seq_scan            | 3
seq_tup_read        | 3000000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1000000
n_tup_upd           | 400060
n_tup_del           | 0
n_tup_hot_upd       | 54
n_live_tup          | 1000000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 2017-09-19 09:55:25.256378-04
last_analyze        | 
last_autoanalyze    | 2017-09-19 09:55:25.878329-04
vacuum_count        | 0
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 2

Avec un facteur d'échelle à 10%, il ne faut plus attendre que la modification de 100050 lignes.

Verrous

Ouvrez une transaction et lisez la table t1.

b2=# BEGIN;
BEGIN
b2=# SELECT * FROM t1;
 c1 |   c2
----+--------
  1 | un
  2 | deux
  3 | TROIS
  4 | QUATRE
  5 | CINQ
(5 rows)

Ouvrez une autre transaction, et tentez de supprimer la table t1.

$ psql b2
psql (10)
Type "help" for help.

b2=# DROP TABLE t1;

La suppression semble bloquée.

Listez les processus du serveur PostgreSQL. Que remarquez-vous ?

$ ps -o pid,cmd fx
  PID CMD
 2052  \_ psql b2
 2123  \_ ps -o pid,cmd fx
 2028  \_ psql b2
 1992 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data
 1994  \_ postgres: logger process                              
 1996  \_ postgres: checkpointer process                        
 1997  \_ postgres: writer process                              
 1998  \_ postgres: wal writer process                          
 1999  \_ postgres: autovacuum launcher process                 
 2000  \_ postgres: stats collector process                     
 2001  \_ postgres: bgworker: logical replication launcher      
 2029  \_ postgres: postgres b2 [local] idle in transaction     
 2053  \_ postgres: postgres b2 [local] DROP TABLE waiting      

La ligne intéressante est la ligne du DROP TABLE. Elle contient le mot clé waiting. Ce dernier indique que l'exécution de la requête est en attente d'un verrou sur un objet.

Récupérez la liste des sessions en attente d'un verrou avec la vue pg_stat_activity.

$ psql b2
psql (10)
Type "help" for help.

b2=# \x
Expanded display is on.
b2=# SELECT * FROM pg_stat_activity
  WHERE application_name='psql' AND wait_event IS NOT NULL;
-[ RECORD 1 ]----+------------------------------
datid            | 24781
datname          | b2
pid              | 2029
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-09-19 09:36:21.876533-04
xact_start       | 2017-09-19 09:55:49.204131-04
query_start      | 2017-09-19 09:55:56.803826-04
state_change     | 2017-09-19 09:55:56.804157-04
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query            | SELECT * FROM t1;
backend_type     | client backend
-[ RECORD 2 ]----+------------------------------
datid            | 24781
datname          | b2
pid              | 2053
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-09-19 09:37:14.512091-04
xact_start       | 2017-09-19 09:56:12.79626-04
query_start      | 2017-09-19 09:56:12.79626-04
state_change     | 2017-09-19 09:56:12.796262-04
wait_event_type  | Lock
wait_event       | relation
state            | active
backend_xid      | 841
backend_xmin     | 841
query            | DROP TABLE t1;
backend_type     | client backend

Récupérez la liste des verrous en attente pour la requête bloquée.

b2=# SELECT * FROM pg_locks WHERE pid=2053 AND NOT granted;
-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 24781
relation           | 24782
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 4/37
pid                | 2053
mode               | AccessExclusiveLock
granted            | f
fastpath           | f

Récupérez le nom de l'objet dont on n'arrive pas à récupérer le verrou.

b2=# SELECT relname FROM pg_class WHERE oid=24782;
-[ RECORD 1 ]
relname | t1

Récupérez la liste des verrous sur cet objet. Quel processus a verrouillé la table t1 ?

b2=# SELECT * FROM pg_locks WHERE relation=24782;
-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 24781
relation           | 24782
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 4/37
pid                | 2053
mode               | AccessExclusiveLock
granted            | f
fastpath           | f
-[ RECORD 2 ]------+--------------------
locktype           | relation
database           | 24781
relation           | 24782
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/212
pid                | 2029
mode               | AccessShareLock
granted            | t
fastpath           | f

Le processus de PID 2029 a un verrou sur t1. Ce processus avait été listé plus haut en attente du client (idle in transaction).

Retrouvez les informations sur la session bloquante.

b2=# SELECT * FROM pg_stat_activity WHERE pid=2029;
-[ RECORD 1 ]----+------------------------------
datid            | 24781
datname          | b2
pid              | 2029
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-09-19 09:36:21.876533-04
xact_start       | 2017-09-19 09:55:49.204131-04
query_start      | 2017-09-19 09:55:56.803826-04
state_change     | 2017-09-19 09:55:56.804157-04
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query            | SELECT * FROM t1;
backend_type     | client backend

À partir de là, il est possible d'arrêter l'exécution de l'ordre DROP TABLE avec la fonction pg_cancel_backend() ou de déconnecter le processus en cours de transaction avec la fonction pg_terminate_backend().

La sauvegarde traditionnelle, qu'elle soit logique ou physique, répond à beaucoup de besoins. Cependant, ce type de sauvegarde montre de plus en plus ses faiblesses pour les gros volumes : la sauvegarde est longue à réaliser et encore plus longue à restaurer. Et plus une sauvegarde met du temps, moins fréquemment on l'exécute. La fenêtre de perte de données devient plus importante.

PostgreSQL propose une solution à ce problème avec la sauvegarde PITR.

Ce module fait le tour de la sauvegarde PITR, de la mise en place de l'archivage (de manière manuelle ou avec l'outil pg_receivewal) à la sauvegarde des fichiers (là aussi, en manuel, ou avec l'outil pg_basebackup). Il discute aussi de la restauration d'une telle sauvegarde. Et enfin, il montre quelques outils disponibles, comme barman et pitrery.

NB : pg_receivewal s'appelait pg_receivexlog avant PostgreSQL 10.

PITR est l'acronyme de Point In Time Recovery, autrement dit restauration à un point dans le temps.

C'est une sauvegarde à chaud et surtout en continu. Là où une sauvegarde logique du type pg_dump se fait au mieux une fois toutes les 24 h, la sauvegarde PITR se fait en continue grâce à l'archivage des journaux de transactions. De ce fait, ce type de sauvegarde diminue très fortement la fenêtre de perte de données.

Bien qu'elle se fasse à chaud, la sauvegarde est cohérente.

Quand une transaction est validée, les données à écrire dans les fichiers de données sont d'abord écrites dans un journal de transactions. Ces journaux décrivent donc toutes les modifications survenant sur les fichiers de données, que ce soit les objets utilisateurs comme les objets systèmes. Pour reconstruire un système, il suffit donc d'avoir ces journaux et d'avoir un état des fichiers du répertoire des données à un instant t. Toutes les actions effectuées après cet instant t pourront être rejouées en demandant à PostgreSQL d'appliquer les actions contenues dans les journaux. Les opérations stockées dans les journaux correspondent à des modifications physiques de fichiers, il faut donc partir d'une sauvegarde au niveau du système de fichier, un export avec pg_dump n'est pas utilisable.

Il est donc nécessaire de conserver ces journaux de transactions. Or PostgreSQL les recycle dès qu'il n'en a plus besoin. La solution est de demander au moteur de les archiver ailleurs avant ce recyclage. On doit aussi disposer de l'ensemble des fichiers qui composent le répertoire des données (incluant les tablespaces si ces derniers sont utilisés).

La restauration a besoin des journaux de transactions archivés. Il ne sera pas possible de restaurer et éventuellement revenir à un point donné avec la sauvegarde seule. En revanche, une fois la sauvegarde des fichiers restaurée et la configuration réalisée pour rejouer les journaux archivés, il sera possible de les rejouer tous ou seulement une partie d'entre eux (en s'arrêtant à un certain moment).

Tout le travail est réalisé à chaud, que ce soit l'archivage des journaux ou la sauvegarde des fichiers de la base. En effet, il importe peu que les fichiers de données soient modifiés pendant la sauvegarde car les journaux de transactions archivés permettront de corriger tout incohérence par leur application.

Il est possible de rejouer un très grand nombre de journaux (une journée, une semaine, un mois, etc.). Évidemment, plus il y a de journaux à appliquer, plus cela prendra du temps. Mais il n'y a pas de limite au nombre de journaux à rejouer.

Dernier avantage, c'est le système de sauvegarde qui occasionnera le moins de perte de données. Généralement, une sauvegarde pg_dump s'exécute toutes les nuits, disons à 3 h du matin. Supposons qu'un gros problème survient à midi. S'il faut restaurer la dernière sauvegarde, la perte de données sera de 9 h. Le volume maximum de données perdu correspond à l'espacement des sauvegardes. Avec l'archivage continu des journaux de transactions, la fenêtre de perte de données va être fortement réduite. Plus l'activité est intense, plus la fenêtre de temps sera petite : il faut changer de fichier de journal pour que le journal précédent soit archivé et les fichiers de journaux sont de taille fixe.

Pour les systèmes n'ayant pas une grosse activité, il est aussi possible de forcer un changement de journal à intervalle régulier, ce qui a pour effet de forcer son archivage, et donc dans les faits de pouvoir s'assurer une perte maximale correspondant à cet intervalle.

Certains inconvénients viennent directement du fait qu'on copie les fichiers : sauvegarde et restauration complète (impossible de ne restaurer qu'une seule base ou que quelques tables), restauration sur la même architecture (32/64 bits, little/big endian), voire probablement le même système d'exploitation.

Elle nécessite en plus un plus grand espace de stockage car il faut sauvegarder les fichiers (dont les index) ainsi que les journaux de transactions sur une certaine période, ce qui peut être volumineux (en tout cas beaucoup plus que des pg_dump).

En cas de problème dans l'archivage et selon la méthode choisie, l'instance ne voudra pas effacer les journaux non archivés. Il y a donc un risque d'accumulation de ceux-ci. Il faudra surveiller la taille du pg_wal.

Enfin, cette méthode est plus complexe à mettre en place qu'une sauvegarde pg_dump. Elle nécessite plus d'étapes, une réflexion sur l'archicture à mettre en œuvre et une meilleure compréhension des mécanismes internes à PostgreSQL pour en avoir la maîtrise.

Même si la mise en place est plus complexe qu'un pg_dump, elle demande peu d'étapes. La première chose à faire est de mettre en place l'archivage des journaux de transactions. Un choix est à faire entre un archivage classique et l'utilisation de l'outil pg_receivewal.

Lorsque cette étape est réalisée (et fonctionnelle), il est possible de passer à la seconde : la sauvegarde des fichiers. Là-aussi, il y a différentes possibilités : soit manuellement, soit pg_basebackup, soit son propre script.

La méthode historique est la méthode utilisant le processus archiver. Ce processus fonctionne sur le serveur à sauvegarder et est de la responsabilité du serveur PostgreSQL. Seule sa (bonne) configuration incombe au DBA.

Une méthode plus récente a vu le jour : pg_receivewal. Cet outil se comporte comme un serveur secondaire. Il reconstitue les journaux de transactions à partir du flux de réplication.

Chaque solution a ses avantages et inconvénients qu'on étudiera après avoir détaillé leur mise en place.

Dans le cas de l'archivage historique, le serveur PostgreSQL va exécuter une commande qui va copier les journaux à l'extérieur de son répertoire de travail :

• sur un disque différent du même serveur ;
• sur un disque d'un autre serveur ;
• sur des bandes, un CDROM, etc.

Dans le cas de l'archivage avec pg_receivewal, c'est cet outil qui va écrire les journaux dans un répertoire de travail. Cette écriture ne peut se faire qu'en local. Cependant, le répertoire peut se trouver dans un montage NFS.

L'exemple pris ici utilise le répertoire /mnt/nfs1/archivage comme répertoire de copie. Ce répertoire est en fait un montage NFS. Il faut donc commencer par créer ce répertoire et s'assurer que l'utilisateur Unix (ou Windows) postgres peut écrire dedans :

$ mkdir /media/nfs1/archivage
$ chown postgres:postgres /media/nfs/archivage

Après avoir créé le répertoire d'archivage, il faut configurer PostgreSQL pour lui indiquer comment archiver.

Le premier paramètre à modifier est wal_level. Ce paramètre indique le niveau des informations écrites dans les journaux de transactions. Avec un niveau minimal, PostgreSQL peut simplement utiliser les journaux en cas de crash pour rendre les fichiers de données cohérents au redémarrage. Dans le cas d'un archivage, il faut écrire plus d'informations, d'où l'utilisation du niveau replica.

Après cela, il faut activer le mode d'archivage en positionnant le paramètre archive_mode à on. Depuis la version 9.5, il est aussi possible de mettre la valeur always pour qu'un esclave puisse aussi archiver les journaux de transactions. Enfin, la commande d'archivage s'indique au niveau du paramètre archive_command. PostgreSQL laisse le soin à l'administrateur de définir la méthode d'archivage des journaux de transaction suivant son contexte. Une simple commande de copie suffit dans la plupart des cas. La directive archive_command peut alors être positionnée comme suit :

archive_command = 'cp %p /mnt/nfs1/archivage/%f'

Le joker %p est remplacé par le chemin complet vers le journal de transactions à archiver, alors que le joker %f correspond au nom du fichier correspondant au journal de transactions une fois archivé.

Une copie du fichier ne suffit pas. Par exemple, dans le cas de la commande cp, le nouveau fichier n'est pas immédiatement écrit sur disque. La copie est effectuée dans le cache disque du système d'exploitation. En cas de crash rapidement après la copie, il est tout à fait possible de perdre l'archive. Il est donc essentiel d'ajouter une étape de synchronisation du cache sur disque.

Il est aussi possible d'y placer le nom d'un script bash, perl ou autres. L'intérêt est de pouvoir faire plus qu'une simple copie. On peut y ajouter la demande de synchronisation du cache sur disque. Il peut aussi être intéressant de tracer l'action de l'archivage par exemple, ou encore de compresser le journal avant archivage. Il faut s'assurer d'une seule chose : la commande d'archivage doit retourner 0 en cas de réussite et surtout une valeur différente de 0 en cas d'échec. Si la commande renvoie autre chose que 0, PostgreSQL va tenter périodiquement d'archiver le fichier jusqu'à ce que la commande réussisse (autrement dit, renvoie 0). Du coup, il est important de surveiller le processus d'archivage et de faire remonter les problèmes à un opérateur (disque plein, changement de bande, etc.).

Surveiller que la commande fonctionne bien peut se faire simplement en vérifiant la taille du répertoire pg_wal. Si ce répertoire commence à grossir fortement, c'est que PostgreSQL n'arrive plus à recycler ses journaux de transactions et ce comportement est un indicateur assez fort d'une commande d'archivage n'arrivant pas à faire son travail. Autre possibilité plus sûre et plus simple: vérifier le nombre de fichiers apparaissant dans le répertoire pg_wal/archive_status dont le suffixe est .ready. Ces fichiers, de taille nulle, indiquent en permanence quels sont les journaux prêts à être archivés. Théoriquement, leur nombre doit donc rester autour de 0 ou 1. La sonde check_pgactivity propose d'ailleurs une action pour faire ce test automatiquement. Voir la sonde ready_archives pour plus de détails.

Si l'administrateur souhaite s'assurer qu'un archivage a lieu au moins à une certaine fréquence, il peut configurer un délai maximum avec le paramètre archive_timeout. L'impact de ce paramètre est d'archiver des journaux de transactions partiellement remplis. Or, ces fichiers ayant une taille fixe, nous archivons toujours 16 Mo par fichier pour un ratio de données utiles beaucoup moins important. La consommation en terme d'espace disque est donc plus importante et le temps de restauration plus long. Ce comportement est désactivé par défaut.

Il ne reste plus qu'à indiquer à PostgreSQL de recharger sa configuration pour que l'archivage soit en place (avec SELECT pg_reload_conf(); ou la commande reload adaptée au système).

Dans le cas où l'un des paramètres wal_level et archive_mode a été modifié, il faudra relancer PostgreSQL.

pg_receivewal est un nouvel outil permettant de se faire passer pour un esclave dans le but d'archiver des journaux de transactions au plus près du maître grâce à l'utilisation du protocole de réplication en flux.

Comme il utilise le protocole de réplication, les journaux archivés ont un retard bien inférieur à celui induit par la configuration du paramètre archive_command, les journaux de transactions étant écrits au fil de l'eau. Cela permet donc de faire de l'archivage PITR avec une perte de données minimum en cas d'incident sur le maître.

Cet outil utilise les même options de connexion que la plupart des outils PostgreSQL, avec en plus l'option -D pour spécifier le répertoire où sauvegarder les journaux de transactions. L'utilisateur spécifié doit bien évidemment avoir les attributs LOGIN et REPLICATION.

Comme il s'agit de conserver toutes les modifications effectuées par le serveur dans le cadre d'une sauvegarde permanente, il est nécessaire de s'assurer qu'on ne puisse pas perdre des journaux de transactions. Il n'y a qu'un seul moyen pour cela : utiliser la technologie des slots de réplication. En utilisant un slot de réplication, pg_receivewal s'assure que le serveur ne va pas recycler des journaux dont pg_receivewal n'aurait pas reçu les enregistrements.

Voici l'aide de cet outil :

pg_receivewal receives PostgreSQL streaming transaction logs.

Usage:
  pg_receivewal [OPTION]...

Options:
 -D, --directory=DIR    receive transaction log files into this directory
     --if-not-exists    do not error if slot already exists when creating a slot
 -n, --no-loop          do not loop on connection lost
 -s, --status-interval=SECS
                        time between status packets sent to server (default: 10)
 -S, --slot=SLOTNAME    replication slot to use
     --synchronous      flush transaction log immediately after writing
 -v, --verbose          output verbose messages
 -V, --version          output version information, then exit
 -?, --help             show this help, then exit

Connection options:
  -d, --dbname=CONNSTR   connection string
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)

Optional actions:
   --create-slot      create a new replication slot (for the slot's name see
                      --slot)
   --drop-slot        drop the replication slot (for the slot's name see --slot)

Report bugs to <pgsql-bugs@postgresql.org>.

Le paramètre max_wal_senders indique le nombre maximum de connexions de réplication sur le serveur. Logiquement, une valeur de 1 serait suffisante. Mais il faut compter sur quelques soucis réseau qui pourraient faire perdre la connexion à pg_receivewal sans que le serveur primaire n'en soit mis au courant. 3 est une valeur moyenne intéressante. Le paramètre max_replication_slots indique le nombre maximum de slots de réplication. Nous n'allons en créer qu'un seul.

Les connexions de réplication nécessitent une configuration particulière au niveau des accès. D'où la modification du fichier pg_hba.conf. Le sous-réseau (192.168.0.0/24) est à modifier suivant l'adressage utilisé. Il est d'ailleurs préférable de n'indiquer que le serveur où est installé pg_receivewal (plutôt que l'intégralité d'un sous-réseau).

L'utilisation d'un utilisateur de réplication est strictement pour des raisons de sécurité.

Une fois toutes ces modifications effectuées, il est nécessaire de redémarrer le serveur PostgreSQL.

Enfin, nous devons créer le slot de réplication qui sera utilisé par pg_receivewal. La fonction pg_create_physical_replication_slot() est là pour ça. Il est à noter que la liste des slots est disponible dans le catalogue système pg_replication_slots.

Une fois le serveur PostgreSQL redémarré, on peut alors lancer pg_receivewal :

pg_receivewal -h 192.168.0.1 -U repli_user -D /data/archives -S archivage

Les journaux de transactions sont alors créés en temps réel dans le répertoire indiqué (ici, /data/archives) :

-rwx------  1 postgres postgres  16MB juil. 27 00000001000000000000000E*
-rwx------  1 postgres postgres  16MB juil. 27 00000001000000000000000F*
-rwx------  1 postgres postgres  16MB juil. 27 000000010000000000000010.partial*

En cas d'incident sur le maître, il est alors possible de partir d'une sauvegarde binaire et de rejouer les journaux de transactions disponibles (sans oublier de supprimer l'extension .partial du dernier journal).

Il ne faut pas oublier de mettre en place un script de démarrage pour que pg_receivewal soit redémarré en cas de redémarrage du serveur.

La méthode archiver est la méthode la plus simple à mettre en place. Elle se lance au lancement du serveur PostgreSQL, donc il n'est pas nécessaire de créer et installer un script de démarrage. Cependant, un journal de transactions n'est archivé que quand PostgreSQL l'ordonne, soit parce qu'il a rempli le journal en question, soit parce qu'un utilisateur a forcé un changement de journal (avec la fonction pg_switch_wal), soit parce que le délai maximum entre deux archivages a été dépassé (paramètre archive_timeout). Il est donc possible de perdre un grand nombre de transactions (même si cela reste bien inférieur à la perte qu'une restauration d'une sauvegarde logique occasionnerait).

La méthode pg_receivewal est plus complexe à mettre en place. Il faut exécuter ce démon généralement sur un autre serveur. Un script de démarrage doit être écrit et installé. Ceci étant dit, un exemple existe. Cette méthode nécessite une configuration plus importante du serveur PostgreSQL. Par contre, cette méthode a le gros avantage de ne perdre pratiquement aucune transaction. Les enregistrements de transactions sont envoyés en temps réel à pg_receivewal. Ce dernier les place dans un fichier de suffixe .partial, qui est ensuite renommé pour devenir un journal de transactions complet.

La sauvegarde a lieu en trois temps.

Tout d'abord, il faut exécuter une procédure stockée appelée pg_start_backup().

Cette procédure va réaliser entre autres choses un checkpoint. Le deuxième argument de cette fonction permet de préciser si on veut que ce checkpoint s'exécute immédiatement ou si on accepte d'attendre un certain temps (induit par la rapidité d'écriture imposé par checkpoint_completion_target).

Ensuite, la procédure va créer un fichier appelé backup_label dans le répertoire des données de PostgreSQL. Dans ce fichier, elle indique le journal de transactions et l'emplacement actuel dans le journal de transactions du checkpoint ainsi que le label précisé en premier argument de la procédure stockée. Ce label permet d'identifier l'opération de sauvegarde. Voici un exemple de ce fichier backup_label :

$ cat $PGDATA/backup_label
START WAL LOCATION: 8/DE000020 (file 0000000100000008000000DE)
CHECKPOINT LOCATION: 8/DE000020
START TIME: 2010-01-26 10:49:05 CET
LABEL: backup_full_2010_01-26

Ce fichier empêche l'exécution de deux sauvegardes PITR en parallèle. La version 9.6 supprime cette limitation en proposant de faire une sauvegarde concurrente. Pour cela, il faut indiquer un troisième argument booléen à pg_start_backup(). À true, le fichier backup_label n'est pas créé, ce qui permet l'exécution d'une autre sauvegarde PITR en parallèle. À false, le fichier est crée et le comportement est identique à celui des versions antérieures à la version 9.6. Les contraintes des sauvegardes en parallèle sont plus importantes. En effet, la session qui exécute la commande pg_start_backup() doit être la même que celle qui exécute pg_stop_backup(). Si la connexion venait à être interrompue entre-temps, alors la sauvegarde doit être considérée comme invalide. De plus il n'y a plus de fichier backup_label et c'est la commande pg_stop_backup() qui renvoie les informations qui s'y trouvaient ; elle se charge dans le même temps de créer dans pg_wal un fichier 0000000100000001000000XX.000000XX.backup contenant les informations de fin de sauvegarde. Si vous voulez implémenter cette nouvelle méthode, il vous faudra donc récupérer et conserver vous-même les informations renvoyées par la commande de fin de sauvegarde. La sauvegarde PITR devient donc plus complexe, et il est donc recommandé d'utiliser plutôt pg_basebackup ou des outils supportant ces fonctionnalités (pitrery, pg_backrest…).

L'exécution de pg_start_backup() peut se faire depuis n'importe quelle base de données de l'instance. Le choix de la base n'a aucune importance en soi, et le label n'a aucune importance pour PostgreSQL (il ne sert qu'à l' administrateur, à reconnaître le backup).

Après exécution de cette procédure, les utilisateurs peuvent continuer à travailler normalement, aucune opération ne leur est interdite.

La deuxième étape correspond à la sauvegarde des fichiers. Le choix de l'outil dépend de l'administrateur. Cela n'a aucune incidence au niveau de PostgreSQL.

La sauvegarde doit comprendre aussi les tablespaces si l'instance en dispose.

La sauvegarde se fait à chaud. Il est donc possible que certains fichiers changent pendant la sauvegarde, cela n'a pas d'importance en soi. Cependant, il faut s'assurer que l'outil de sauvegarde continue son travail malgré tout. Si vous disposez d'un outil capable de différencier les codes d'erreurs dus à « des fichiers ont bougé ou disparu lors de la sauvegarde » des autres erreurs techniques, c'est un avantage. Le tar GNU par exemple retourne 1 pour le premier cas d'erreur, et 2 quand l'erreur est critique.

Peu d'outils sont capables de copier des fichiers en cours de modification sur les plateformes Microsoft Windows. Assurez-vous d'en utiliser un possédant cette fonctionnalité. À noter l'outil tar (ainsi que d'autres issus du projet GNU) est disponible nativement à travers le projet unxutils.

Sur les fichiers et répertoires à ignorer, voici la liste exhaustive (disponible aussi dans la documentation officielle) :

• postmaster.pid
• postmaster.opts
• différents fichiers temporaires créés pendant l'opération du serveur PostgreSQL
• pg_wal, ainsi que les sous-répertoires
• pg_replslot est copiée sous la forme d'un répertoire vide
• les fichiers autres que les fichiers et les répertoires standards

La dernière étape correspond à l'exécution de la procédure stockée pg_stop_backup(). PostgreSQL va :

• marquer cette fin de backup dans le journal ;
• forcer la finalisation du journal de transactions courant et donc son archivage pour que la sauvegarde soit utilisable y compris en cas de crash immédiat ;
• archiver le fichier backup_label sous un autre nom (en cas de sauvegarde non concurrente).

En cas de sauvegarde concurrente, cette fonction nous renvoie l'équivalent du contenu du fichier backup_label. Ce contenu doit être conservé pour créer le fichier backup_label à stocker avec la sauvegarde des fichiers.

À partir du moment où cette fonction nous rend la main, il est possible de restaurer la sauvegarde obtenue et rejouer les journaux de transactions suivants en cas de besoin, sur un autre serveur ou sur ce même serveur.

Tous les journaux archivés avant celui précisé par le champ START WAL LOCATION dans le fichier backup_label ne sont plus nécessaires pour la récupération de la sauvegarde du système de fichiers et peuvent donc être supprimés. Attention, le nom des journaux ne croit pas de manière hexadécimale simple (il y a plusieurs compteurs hexadécimaux différents dans le nom du fichier journal, qui ne sont pas incrémentés de gauche à droite).

pg_basebackup permet de réaliser toute la sauvegarde de la base, à distance, via une connexion PostgreSQL. Il est donc simple à mettre en place et à utiliser, et permet d'éviter de nombreuses étapes vu précédemment. Par contre, il ne permet pas de réaliser une sauvegarde incrémentale, contrairement à une méthode de sauvegarde comme rsync.

pg_basebackup nécessite une connexion de réplication. Il faut donc configurer le serveur pour accepter la connexion de pg_basebackup. Cela se passe dans un premier temps au niveau du fichier postgresql.conf. Le paramètre max_wal_senders doit avoir une valeur assez élevée pour autoriser cette connexion :

max_wal_senders = 1

Ensuite, il faut configurer le fichier pg_hba.conf pour accepter la connexion du serveur où est exécutée pg_basebackup. Dans notre cas, il s'agit du même serveur :

host  replication  sauve  127.0.0.1/32  trust

Enfin, il faut créer l'utilisateur sauve qui sera le rôle créant la connexion :

$ psql -c "CREATE ROLE sauve LOGIN REPLICATION;" postgres

Notez qu'il serait préférable de mettre en place un mot de passe pour cet utilisateur et de forcer son utilisation avec une méthode comme md5. Nous ne le ferons pas ici.

Il ne reste plus qu'à lancer pg_basebackup :

$ pg_basebackup -Ft -x -c fast -P -h 127.0.0.1 -U sauve -D sauve_20120625
4163766/4163766 kB (100%), 1/1 tablespace
$ ll sauve_20120625
total 4163772
-rw-rw-r--. 1 guillaume guillaume 4263697408 Jun 25 15:16 base.tar

À partir de la version 9.6, il est possible d'indiquer un slot de réplication à pg_basebackup.

La fréquence dépend des besoins. Une sauvegarde par jour est le plus commun, mais il est possible d'espacer encore plus la fréquence.

Cependant, il faut conserver à l'esprit que plus la sauvegarde est ancienne, plus la restauration sera longue car un plus grand nombre de journaux seront à rejouer.

La restauration se déroule en trois voire quatre étapes suivant qu'elle est effectuée sur le même serveur ou sur un autre serveur.

Dans le cas où la restauration a lieu sur le même serveur, quelques étapes préliminaires sont à effectuer.

Il faut arrêter PostgreSQL s'il n'est pas arrêté. Cela arrivera quand la restauration a pour but de récupérer des données qui ont été supprimées par erreur par exemple.

Ensuite, il faut supprimer (ou archiver) l'ancien répertoire des données pour pouvoir y placer l'ancienne sauvegarde des fichiers. Écraser l'ancien répertoire n'est pas suffisant. Il faut réellement supprimer le répertoire actuel. Par conséquent, il faut aussi supprimer les répertoires des tablespaces au cas où l'instance en possède.

La sauvegarde des fichiers peut enfin être restaurée. Il faut bien porter attention à ce que les fichiers soient restaurés au même emplacement, tablespaces compris.

Une fois cette étape effectuée, il peut être intéressant de faire un peu de ménage. Par exemple, le fichier postmaster.pid peut poser un problème au démarrage. Conserver les journaux applicatifs n'est pas en soi un problème mais peut porter à confusion. Il est donc préférable de les supprimer. Quant aux journaux de transactions compris dans la sauvegarde, bien que ceux en provenances des archives seront utilisés même s'ils sont présents aux deux emplacements, il est préférable de les supprimer. La commande sera similaire à celle-ci :

$ rm postmaster.pid log/* pg_wal/[0-9A-F]*

Enfin, s'il est possible d'accéder au journal de transactions courant au moment de l'arrêt de l'ancienne instance, il est intéressant de le restaurer dans le répertoire pg_wal fraîchement nettoyé. Ce dernier sera pris en compte en toute fin de restauration des journaux depuis les archives et permettra donc de restaurer les toutes dernières transactions validées sur l' ancienne instance, mais pas encore archivées.

La restauration se configure dans un fichier spécifique, appelé recovery.conf.

Pour le créer, inspirez-vous de celui fourni avec PostgreSQL (sur RedHat/CentOS :
/usr/pgsql-10/share/recovery.conf.sample, et sur Debian :
/usr/share/postgresql/10/recovery.conf.sample).

Le paramètre essentiel est restore_command. Il est le pendant du paramètre archive_command pour l'archivage. Si nous poursuivons notre exemple, ce paramètre pourrait être :

restore_command = 'cp /mnt/nfs1/archivage/%f %p'

Si le but est de restaurer tous les journaux archivés, il n'est pas nécessaire d'aller plus loin dans la configuration. Dans le cas contraire, trois paramètres permettent de préciser jusqu'à quel point il est possible d'exécuter la restauration :

• jusqu'à un certain nom, grâce au paramètre recovery_target_name ;
• jusqu'à une certaine heure, grâce au paramètre recovery_target_time ;
• jusqu'à un certain identifiant de transactions, grâce au paramètre recovery_target_xid ;
• jusqu'à un certain LSN, grâce au paramètre recovery_target_lsn.

Le nom correspond à un label enregistré précédemment dans les journaux de transactions grâce à la fonction pg_create_restore_point().

Il est possible de préciser si la restauration se fait en incluant les transactions au nom, à l'heure ou à l'identifiant de transactions, ou en les excluant. Il s'agit du paramètre recovery_target_inclusive. À noter qu'il n' existe actuellement aucun outil pour récupérer facilement un numéro de transaction particulier.

Il est aussi possible de demander à la restauration de marquer une pause une fois arrivé à ce niveau. Cela permet à l'utilisateur de vérifier que le serveur est bien arrivé au point qu'il désirait. Si c'est le cas, un appel à la fonction pg_wal_replay_resume() provoquera l’arrêt de la restauration. Et si ce n'est pas le cas, l’arrêt de PostgreSQL et le changement de la cible de restauration dans le fichier recovery.conf permettra de lui demander de continuer la restauration.

La dernière étape est particulièrement simple. Il suffit de démarrer PostgreSQL.

Ceci fait, PostgreSQL va comprendre qu'il doit rejouer les journaux de transactions et s'en acquittera jusqu'à arriver à la limite fixée, jusqu'à ce qu'il ne trouve plus de journal à rejouer, ou que le bloc de journal lu soit incohérent (ce qui indique qu'on est arrivé à la fin d'un journal qui n'a pas été terminé, le journal courant au moment du crash par exemple).

Lorsque le mode recovery s'arrête, au point dans le temps demandé ou faute d 'archives disponibles, l'instance accepte les écritures. De nouvelles transactions se produisent alors sur les différentes bases de données de l' instance. Dans ce cas, l'historique des données prend un chemin différent par rapport aux archives de journaux de transactions produites avant la restauration. Par exemple, dans ce nouvel historique, il n'y a pas le DROP TABLE malencontreux qui a imposé de restaurer les données. Cependant, cette transaction existe bien dans les archives des journaux de transactions.

On a alors plusieurs historiques des transactions, avec des « bifurcations » aux moments où on a réalisé des restaurations. PostgreSQL permet de garder ces historiques grâce à la notion de timeline. Une timeline est donc l'un de ces historiques, elle se matérialise par un ensemble de journaux de transactions, identifiée par un numéro. Le numéro de la timeline est le premier nombre hexadécimal du nom des segments de journaux de transactions (le second est le numéro du journal et le troisième le numéro du segment). Lorsqu'une instance termine une restauration PITR, elle peut archiver immédiatement ces journaux de transactions au même endroit, les fichiers ne seront pas écrasés vu qu'ils seront nommés différemment. Par exemple, après une restauration PITR s'arrêtant à un point situé dans le segment 000000010000000000000009 :

$ ls -1 /backup/postgresql/archived_wal/
000000010000000000000007
000000010000000000000008
000000010000000000000009
00000001000000000000000A
00000001000000000000000B
00000001000000000000000C
00000001000000000000000D
00000001000000000000000E
00000001000000000000000F
000000010000000000000010
000000010000000000000011
000000020000000000000009
00000002000000000000000A
00000002000000000000000B
00000002000000000000000C
00000002.history

A la sortie du mode recovery, l'instance doit choisir une nouvelle timeline. Les timelines connues avec leur point de départ sont suivies grâce aux fichiers history, nommés d'après le numéro hexadécimal sur huit caractères de la timeline et le suffixe .history, et archivés avec les fichiers WAL. En partant de la timeline qu'elle quitte, l'instance restaure les fichiers history des timelines suivantes pour choisir la première disponible, et archive un nouveau fichier .history pour la nouvelle timeline sélectionnée, avec l'adresse du point de départ dans la timeline qu'elle quitte :

$ cat 00000002.history
1   0/9765A80   before 2015-10-20 16:59:30.103317+02

Après une seconde restauration, ciblant la timeline 2, l'instance choisit la timeline 3 :

$ cat 00000003.history
1   0/9765A80   before 2015-10-20 16:59:30.103317+02


2   0/105AF7D0  before 2015-10-22 10:25:56.614316+02

On peut choisir la timeline cible en configurant le paramètre recovery_target_timeline dans le fichier recovery.conf. Par défaut, la restauration se fait dans la même timeline que le base backup. Pour choisir une autre timeline, il faut donner le numéro hexadécimal de la timeline cible comme valeur du paramètre recovery_target_timeline. On peut aussi indiquer 'latest' pour que PostgreSQL détermine la timeline la plus récente en cherchant les fichiers history. Il prend alors le premier ID de timeline disponible. Attention, pour restaurer dans une timeline précise, il faut que le fichier history correspondant soit présent dans les archives, sous peine d'erreur.

En sélectionnant la timeline cible, on peut alors effectuer plusieurs restaurations successives à partir du même base backup.

Ce schéma illustre ce processus de plusieurs restaurations successives, et la création de différentes timelines qui en résulte.

On observe ici les éléments suivants avant la première restauration :

• la fin de la dernière sauvegarde se situe en haut à gauche sur l'axe des transactions, à la transaction x12 ;
• cette sauvegarde a été effectuée alors que l'instance était en activité sur la timeline 1.

On décide d'arrêter l'instance alors qu'elle est arrivée à la transaction x47, par exemple parce qu'une nouvelle livraison de l'application a introduit un bug qui provoque des pertes de données. L'objectif est de restaurer l'instance avant l'apparition du problème afin de récupérer les données dans un état cohérent, et de relancer la production à partir de cet état. Pour cela, on restaure les fichiers de l'instance à partir de la dernière sauvegarde, puis on configure le recovery.conf pour que l'instance, lors de sa phase de recovery :

• restaure les WAL archivés jusqu'à l'état de cohérence (transaction x12) ;
• restaure les WAL archivés jusqu'au point précédant immédiatement l' apparition du bug applicatif (transaction x42).

On démarre ensuite l'instance et on l'ouvre en écriture, on constate alors que celle-ci bascule sur la timeline 2, la bifurcation s'effectuant à la transaction x42. L'instance étant de nouveau ouverte en écriture, elle va générer de nouveaux WAL, qui seront associés à la nouvelle timeline : ils n'écrasent pas les fichiers WAL archivés de la timeline 1, ce qui permet de les réutiliser pour une autre restauration en cas de besoin (par exemple si la transaction x42 utilisée comme point d'arrêt était trop loin dans le passé, et que l'on désire restaurer de nouveau jusqu'à un point plus récent).

Un peu plus tard, on a de nouveau besoin d'effectuer une restauration dans le passé - par exemple, une nouvelle livraison applicative a été effectuée, mais le bug rencontré précédemment n'était toujours pas corrigé. On restaure donc de nouveau les fichiers de l'instance à partir de la même sauvegarde, puis on configure le recovery.conf pour suivre la timeline 2 ( paramètre recovery_target_timeline = 2) jusqu'à la transaction x55. Lors du recovery, l'instance va :

• restaurer les WAL archivés jusqu'à l'état de cohérence (transaction x12 ) ;
• restaurer les WAL archivés jusqu'au point de la bifurcation (transaction x42) ;
• suivre la timeline indiquée (2) et rejouer les WAL archivés jusqu'au point précédant immédiatement l'apparition du bug applicatif (transaction x55).

On démarre ensuite l'instance et on l'ouvre en écriture, on constate alors que celle-ci bascule sur la timeline 3, la bifurcation s'effectuant cette fois à la transaction x55.

Enfin, on se rend compte qu'un problème bien plus ancien et subtil a été introduit précédemment aux deux restaurations effectuées. On décide alors de restaurer l'instance jusqu'à un point dans le temps situé bien avant, jusqu'à la transaction x20. On restaure donc de nouveau les fichiers de l'instance à partir de la même sauvegarde, et on configure le recovery.conf pour restaurer jusqu'à la transaction x20. Lors du recovery, l'instance va :

• restaurer les WAL archivés jusqu'à l'état de cohérence (transaction x12 ) ;
• restaurer les WAL archivés jusqu'au point précédant immédiatement l' apparition du bug applicatif (transaction x20).

Comme la création des deux timelines précédentes est archivée dans les fichiers history, l'ouverture de l'instance en écriture va basculer sur une nouvelle timeline (4). Suite à cette restauration, toutes les modifications de données provoquées par des transactions effectuées sur la timeline 1 après la transaction x20 , ainsi que celles effectuées sur les timelines 2 et 3, ne sont donc pas présentes dans l'instance.

L'un des problème de la sauvegarde PITR est la place prise sur disque par les journaux de transactions. Avec un journal généré toutes les cinq minutes, cela représente 16 Mo toutes les 5 minutes, soit 192 Mo par heure. Autrement dit 5 Go par jour. Il n'est pas possible dans certains cas de conserver autant de journaux. La solution est la compression à la volée et il existe deux types de compression.

La méthode la plus simple et la plus sûre pour les données est une compression non destructive, comme celle proposée par les outils gzip, bzip2, lzma, etc. La compression peut ne pas être très intéressante en terme d' espace disque gagné. Néanmoins, un fichier de 16 Mo aura généralement une taille comprise entre 3 et 6 Mo. Attention par ailleurs au temps de compression des journaux, qui peut entraîner un retard conséquent de l'archivage par rapport à l'écriture des journaux.

L'utilisation de pglesslog est déconseillée. Cet outil supprime les pages complètes inutiles et tronque le fichier dans le cas de l'utilisation du paramètre archive_timeout. Bien que le gain en espace disque soit certain, les utilisateurs de cet outil ont rencontré trop de problèmes pour qu'il puisse être considéré fiable : de nombreuses instances se sont avérées impossibles à restaurer après un incident. Il est préférable de rester sur des outils de compression éprouvés.

barman est un outil créé par 2ndQuadrant. Il a pour but de faciliter la mise en place de sauvegardes PITR. Il gère à la fois la sauvegarde et la restauration.

La commande barman dispose de plusieurs actions :

• list-server, pour connaître la liste des serveurs configurés ;
• backup, pour lancer une sauvegarde de base ;
• list-backup, pour connaître la liste des sauvegardes de base ;
• show-backup, pour afficher des informations sur une sauvegarde ;
• delete, pour supprimer une sauvegarde ;
• recover, pour restaurer une sauvegarde (la restauration peut se faire à distance).

Site web de barman

pitrery a la même raison d'exister que barman, et a été créé par la société Dalibo. Il permet de réaliser facilement la sauvegarde et la restauration de la base. Cet outil s'appuie sur des fichiers de configuration, un par serveur de sauvegarde, qui permettent de définir la destination de l' archivage, la destination des sauvegardes ainsi que la politique de rétention à utiliser.

pitrery propose trois commandes :

• archive_xlog qui gère l'archivage et la compression des journaux de transactions ;
• pitrery qui gère les sauvegardes et les restaurations ;
• restore_xlog pour restaurer les journaux archivés par archive_xlog. À n' utiliser que dans le fichier recovery.conf.

L'archivage des journaux de transactions est à configurer au niveau du fichier postgresql.conf :

wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/archive_xlog %p'

Lorsque l'archivage est fonctionnel, la commande pitrery peut être utilisée pour réaliser une sauvegarde :

$ pitrery backup
INFO: preparing directories in 10.100.0.16:/opt/backups/prod
INFO: listing tablespaces
INFO: starting the backup process
INFO: backing up PGDATA with tar
INFO: archiving /home/postgres/postgresql-9.0.4/data
INFO: backup of PGDATA successful
INFO: backing up tablespace "ts2" with tar
INFO: archiving /home/postgres/postgresql-9.0.4/ts2
INFO: backup of tablespace "ts2" successful
INFO: stopping the backup process
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
INFO: copying the backup history file
INFO: copying the tablespaces list
INFO: backup directory is 10.100.0.16:/opt/backups/prod/2013.08.28-11.16.30
INFO: done

Il est possible d'obtenir la liste des sauvegardes en ligne :

$ pitrery list
List of backups on 10.100.0.16:

Directory:
  /usr/data/pitrery/backups/pitr13/2013.05.31_11.44.02
Minimum recovery target time:
  2013-05-31 11:44:02 CEST
Tablespaces:

Directory:
  /usr/data/pitrery/backups/pitr13/2013.05.31_11.49.37
Minimum recovery target time:
  2013-05-31 11:49:37 CEST
Tablespaces:
  ts1 /opt/postgres/ts1 (24576)

pitrery gère également la politique de rétention des sauvegardes. Une commande de purge permet de réaliser la purge des sauvegardes en s'appuyant sur la configuration de la rétention des sauvegardes :

$ pitrery purge
INFO: searching backups
INFO: purging /home/postgres/backups/prod/2011.08.17-11.16.30
INFO: purging WAL files older than 000000020000000000000060
INFO: 75 old WAL file(s) removed
INFO: done

Enfin, pitrery permet de restaurer une sauvegarde et de préparer la configuration de restauration (recovery.conf) :

$ pitrery -c prod restore -d '2013-06-01 13:00:00 +0200'
INFO: searching backup directory
INFO: searching for tablespaces information
INFO:
INFO: backup directory:
INFO:   /opt/postgres/pitr/prod/2013.06.01_12.15.38
INFO:
INFO: destinations directories:
INFO:   PGDATA -> /opt/postgres/data
INFO:   tablespace "ts1" -> /opt/postgres/ts1 (relocated: no)
INFO:   tablespace "ts2" -> /opt/postgres/ts2 (relocated: no)
INFO:
INFO: recovery configuration:
INFO:   target owner of the restored files: postgres
INFO:   restore_command = 'restore_xlog -L -d /opt/postgres/archives %f %p'
INFO:   recovery_target_time = '2013-06-01 13:00:00 +0200'
INFO:
INFO: checking if /opt/postgres/data is empty
INFO: checking if /opt/postgres/ts1 is empty
INFO: checking if /opt/postgres/ts2 is empty
INFO: extracting PGDATA to /opt/postgres/data
INFO: extracting tablespace "ts1" to /opt/postgres/ts1
INFO: extracting tablespace "ts2" to /opt/postgres/ts2
INFO: preparing pg_wal directory
INFO: preparing recovery.conf file
INFO: done
INFO:
INFO: please check directories and recovery.conf before starting the cluster
INFO: and do not forget to update the configuration of pitrery if needed
INFO:

Il ne restera plus qu'à redémarrer le serveur et surveiller les journaux applicatifs pour vérifier qu' aucune erreur ne se produit au cours de la restauration.

Site Web de pitrery.

Cette méthode de sauvegarde est la seule utilisable dès que les besoins de performance de sauvegarde et de restauration augmentent (Recovery Time Objective ou RTO), ou que le volume de perte de données doit être drastiquement réduit (Recovery Point Objective ou RPO).

Énoncés

Sauvegarde

• Mettre en place l'archivage des journaux de transactions dans /opt/pgsql/archives.
• Créer une table suivante comprenant deux champs, le premier de type integer (plus exactement serial), le deuxième de type text.
• Écrire un script qui insère une grande quantité de données dans cette table. Vérifier que les journaux de transactions sont bien archivés dans le répertoire prévu pour ça. Laisser le script s'exécuter pendant la durée de la sauvegarde de l'instance.
• Sauvegarder l'instance à l'aide d'un utilitaire d'archivage (tar par exemple). À la fin de la sauvegarde, relever la valeur de la colonne id du dernier élément inséré dans la table créée précédemment.
• Attendre qu'un nouveau journal de transactions soit archivé dans le répertoire d'archivage, puis arrêter l'insertion des données dans la table créée précédemment (il est aussi possible de forcer la bascule avec select pg_switch_wal()).

Restauration

• Renommer le répertoire /var/lib/pgsql/10/data en /var/lib/pgsql/10/data.old, à froid.
• Restaurer l'instance en utilisant la sauvegarde à chaud et les journaux de transactions.
• Récupérer l'id du dernier élément inséré dans la table créée précédemment. Vérifier que cet id est supérieur à l'id relevé lors de la fin de la sauvegarde.

Utilisation de pg_receivewal et des slots de réplication

• Mettre en place pg_receivewal en lui donnant un slot de réplication

Utilisation de barman (Optionnel)

• Installer barman.
• Configurer barman pour la sauvegarde du serveur local.
• Faire une sauvegarde.
• Lister les sauvegardes.
• Afficher les informations sur une sauvegarde.
• Faire une restauration.

Utilisation de pitrery (Optionnel)

• Installer pitrery.
• Configurer pitrery pour la sauvegarde du serveur local.
• Faire une sauvegarde.
• Lister les sauvegardes.
• Faire une restauration.

Solutions

Toutes les opérations de sauvegarde et restauration sont exécutées avec l'utilisateur postgres. Lorsqu'un autre utilisateur est utilisé, ceci est précisé explicitement.

Sauvegarde

Mettre en place l'archivage des journaux de transactions dans /opt/pgsql/archives.

Pour mettre en place l'archivage des journaux de transactions dans /opt/pgsql/archives, il faut positionner la variable archive_command comme suit dans le fichier de configuration postgresql.conf :

archive_command = 'rsync %p /opt/pgsql/archives/%f'

Si c'est nécessaire pour votre version, positionner aussi les paramètres wal_level à archive, et archive_mode à on.

Créer le répertoire /opt/pgsql/archives (en tant que root):

# mkdir -p /opt/pgsql/archives
# chown -R postgres /opt/pgsql

Puis faire relire la configuration à PostgreSQL (avec l'utilisateur root) :

# /etc/init.d/postgresql reload

(Un redémarrage est nécessaire si les paramètres wal_level et archive_mode ont été modifiés.)

Créer la table.

$ psql
cave=> CREATE TABLE dummy (id serial, libelle text);

Écrire un script qui insère une grande quantité de données dans cette table.

#!/bin/bash

while true
do
    cat <<_QUERY | psql
INSERT INTO dummy (libelle)
  SELECT CURRENT_TIMESTAMP - (v ||' minutes')::interval
  FROM generate_series(1,5000) AS t(v)
_QUERY
    sleep 1
done

Vérifier que les journaux de transactions sont bien générés dans le répertoire d'archivage.

$ ls -l /opt/pgsql/archives

Sauvegarder l'instance à l'aide d'un utilitaire d'archivage.

Indiquer à PostgreSQL que vous allez faire une sauvegarde de fichiers :

$ psql
postgres=> SELECT pg_start_backup('backup '||current_timestamp, true);

Sauvegarder les fichiers d'instance :

$ cd /var/lib/postgresql/10/main
$ tar -cvhz . -f /opt/pgsql/backups/backup_$(date +%F).tgz

Indiquer la fin de la sauvegarde des fichiers.

$ psql postgres
postgres=> SELECT pg_stop_backup();

À la fin de la sauvegarde, relever l'id du dernier élément inséré dans la table créée précédemment.

$ psql -U caviste cave
cave=> select MAX(id) from dummy;

Attendre qu'un nouveau journal de transactions soit généré dans le répertoire d'archivage, puis arrêter l'insertion des données dans la table créée précédemment.

Restauration

Arrêter PostgreSQL (en tant qu'utilisateur root) :

# /etc/init.d/postgresql stop

Renommer le répertoire /var/lib/postgresql/10/main en /var/lib/postgresql/10/main.old :

$ mv /var/lib/postgresql/10/main /var/lib/postgresql/10/main.old

Restaurer l'instance en utilisant la sauvegarde à chaud et les journaux de transactions.

Restaurer l'archive de la dernière sauvegarde :

$ cd /var/lib/postgresql/10
$ mkdir main
$ cd main
$ tar xzvf /opt/pgsql/backups/backup_$(date +%F).tgz

Effacer les anciens journaux et le fichier PID :

$ rm -f pg_wal/00* data/postmaster.pid

Copier éventuellement le journal de transactions courant de l'ancien répertoire :

• Déterminer le journal de transactions (c'est le dernier de la liste)

$ ls -lrt /var/lib/postgresql/10/main.old/pg_wal/

• Puis le recopier

$ cp main.old/pg_wal/le_fichier_qu_on_vient_de_trouver main/pg_wal

Créer le fichier recovery.conf dans le répertoire main contenant les informations suivantes :

restore_command = 'cp /opt/pgsql/archives/%f %p'

Ce fichier peut aussi être copié depuis un fichier d'exemple fourni avec PostgreSQL (sur RedHat/CentOS : /usr/pgsql-10/share/recovery.conf.sample)

Re-démarrer le serveur PostgreSQL (avec l'utilisateur root) :

# /etc/init.d/postgresql start

Vérifier que la restauration est terminée :

$ ls data/recovery*

On devrait trouver data/recovery.done.

Récupérer l'id du dernier élément inséré dans la table dummy. Vérifier que cet id est supérieur à l'id relevé lors de la fin de la sauvegarde.

$ psql
cave=> select MAX(id) from dummy;

Utilisation de barman (Optionnel)

Installer barman

Il est préférable de passer par les paquets de la distribution. Dans le cas contraire, les développeurs de barman proposent leur propre RPM (http://sourceforge.net/projects/pgbarman/files/).

Sur Red Hat et affiliées, la commande suivante devrait suffire :

$ sudo yum install barman

alors que sur Debian et affiliées, il faudra utiliser la commande :

$ apt-get install barman

Il est possible que des dépendances soient à installer.

Configurer barman pour la sauvegarde du serveur local

barman utilise généralement un utilisateur sans droit spécifique (pas un administrateur comme root).

La première chose à faire concerne la connexion SSH. Il faut créer les clés SSH et les installer pour permettre une connexion sans mot de passe entre les deux serveurs (dans notre TP, il s'agit du même).

Ensuite, il faut s'assurer que l'utilisateur qui exécute barman puisse se connecter sur le serveur PostgreSQL. Ça demandera au moins une modification du fichier pg_hba.conf et peut-être même du postgresql.conf.

Reste ensuite le fichier de configuration, le voici :

[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
;compression = gzip
;pre_backup_script = env | grep ^BARMAN
;post_backup_script = env | grep ^BARMAN
;pre_archive_script = env | grep ^BARMAN
;post_archive_script = env | grep ^BARMAN
configuration_files_directory = /etc/barman.d
;minimum_redundancy = 0
;retention_policy =
;bandwidth_limit = 4000
immediate_checkpoint = true
;network_compression = false

[localhost]
description =  "My own PostgreSQL Database"
ssh_command = ssh postgres@localhost
conninfo = host=localhost user=postgres

Ce fichier indique que l'utilisateur système est l'utilisateur barman. Les sauvegardes et journaux de transactions archivés seront placés dans /var/lib/barman. Le CHECKPOINT exécute par la fonction pg_start_backup() sera immédiat (i.e. on n'attend pas le CHECKPOINT planifié). Pour des raisons de facilité sur le TP, la description du seul hôte à sauvegarder se trouve dans ce fichier. Nous conseillons plutôt de faire un fichier par hôte et de les placer dans le répertoire pointé par la variable configuration_files_directory (/etc/barman.d ici). L'hôte localhost dispose de la commande SSH pour la connexion système et de la chaîne de connexion PostgreSQL.

Il faut ensuite configurer PostgreSQL pour qu'il archive au bon endroit. La commande suivante permet de savoir dans quel répertoire il faut archiver les journaux de transactions :

$ barman show-server localhost | grep incoming_wals_directory
    incoming_wals_directory: /var/lib/barman/localhost/incoming

Il faut donc modifier la configuration du fichier postgresql.conf ainsi :

archive_command = 'rsync %p /var/lib/barman/localhost/incoming/%f'

Il est préférable de tester que la configuration est bonne. Cela se fait avec cette commande :

$ barman check localhost
Server localhost:
    ssh: OK
    PostgreSQL: OK
    archive_mode: OK
    archive_command: OK
    directories: OK
    retention policy settings: OK
    compression settings: OK
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)

Si tout n'est pas à OK, c'est qu'un problème existe dans la configuration et doit être réglé avant d'aller plus loin.

Les deux problèmes habituels sont:

• ssh: FAILED. Dans ce cas, c'est que vous n'avez pas d'échange de clé entre l'utilisateur faisant fonctionner barman et l'utilisateur postgres de l'instance à sauvegarder
• PostgreSQL: FAILED. Dans ce cas, c'est que vous n'arrivez pas à vous connecter avec le protocole PostgreSQL à l'instance. Soit le pg_hba.conf n'est pas bon, soit vous n'avez pas renseigné le mot de passe dans un fichier .pgpass pour l'utilisateur barman.

Faire une sauvegarde

$ barman backup localhost
Starting backup for server localhost in
    /var/lib/barman/localhost/base/20140214T100017
Backup start at xlog location: 0/95000028 (000000010000000000000095, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/950000B8 (000000010000000000000095, 000000B8)
Backup completed

Lister les sauvegardes.

$ barman list-backup localhost
localhost 20140214T100017 - Fri Feb 14 10:00:27 2014 - Size: 672.2 MiB
                          - WAL Size: 0 B

Afficher les informations sur une sauvegarde.

$ barman show-backup localhost 20140214T100017
Backup 20140214T100017:
  Server Name       : localhost
  Status            : DONE
  PostgreSQL Version: 90302
  PGDATA directory  : /var/lib/postgresql/10/main
  Tablespaces:
    ts1: /home/guillaume/ts1 (oid: 24584)

  Base backup information:
    Disk usage      : 672.2 MiB
    Timeline        : 1
    Begin WAL       : 000000010000000000000095
    End WAL         : 000000010000000000000095
    WAL number      : 0
    Begin time      : 2014-02-14 10:00:17.276202
    End time        : 2014-02-14 10:00:27.729487
    Begin Offset    : 40
    End Offset      : 184
    Begin XLOG      : 0/95000028
    End XLOG        : 0/950000B8

  WAL information:
    No of files     : 0
    Disk usage      : 0 B
    Last available  : None

  Catalog information:
    Retention Policy: not enforced
    Previous Backup : - (this is the oldest base backup)
    Next Backup     : - (this is the latest base backup)

Faire une restauration

$ barman recover localhost 20140214T100017 /tmp/test_resto
Starting local restore for server localhost using backup 20140214T100017
Destination directory: /tmp/test_resto
Copying the base backup.
Copying required wal segments.
The archive_command was set to 'false' to prevent data losses.

Your PostgreSQL server has been successfully prepared for recovery!

Please review network and archive related settings in the PostgreSQL
configuration file before starting the just recovered instance.

Utilisation de pitrery (Optionnel)

Installer pitrery

Les développeurs de pitrery proposent leur propre RPM (https://dl.dalibo.com/public/pitrery/rpms/). Il est également possible de l'installer en compilant les sources.

Sur Red Hat et affiliées, après avoir téléchargé localement le RPM, la commande suivante devrait suffire :

$ sudo yum install pitrery-2.0-1.el7.centos.noarch.rpm

Des paquets existent également pour Debian et affiliées.

Configurer pitrery pour la sauvegarde du serveur local

Par défaut, le fichier de configuration créé est /etc/pitrery/pitr.conf. Créons en une copie vide.

$ sudo cp /etc/pitrery/pitr.conf /etc/pitrery/pitr.conf.bck
$ sudo echo > /etc/pitrery/pitr.conf

Configurons le ensuite de la manière suivante :

####################
# Backup management
####################
PGDATA="/var/lib/pgsql/10/data"
PGUSER="postgres"
BACKUP_DIR="/var/lib/pgsql/10/backups/pitr"

####################
# WAL archiving
####################
ARCHIVE_DIR="$BACKUP_DIR/archived_wal"

Ce fichier indique où se trouve notre répertoire de données PGDATA, les informations de connexion (ici uniquement PGUSER) ainsi que la configuration relative au stockage des backups et des journaux de transactions archivés.

Il convient ensuite de modifier la configuration du fichier postgresql.conf ainsi :

wal_level = replica
archive_mode = on
archive_command = '/usr/bin/archive_xlog %p'

pitrery fournit le script archive_xlog pour gérer la commande d'archivage. Par défaut, ce script utilisera le pitr.conf que nous venons de configurer.

Il faut redémarrer le service PostgreSQL si les paramètres wal_level ou archive_mode ont été modifiés, sinon un simple rechargement de la configuration suffit.

Il est préférable de tester que la configuration est bonne. Cela se fait avec cette commande :

$ pitrery check
INFO: Configuration file is: /etc/pitrery/pitr.conf
INFO: loading configuration
INFO: the configuration file contains:
PGDATA="/var/lib/pgsql/10/data"
PGUSER="postgres"
BACKUP_DIR="/var/lib/pgsql/10/backups/pitr"
ARCHIVE_DIR="$BACKUP_DIR/archived_wal"

INFO: ==> checking the configuration for inconsistencies
INFO: configuration seems correct
INFO: ==> checking backup configuration
INFO: backups are local, not checking SSH
INFO: target directory '/var/lib/pgsql/10/backups' exists
INFO: target directory '/var/lib/pgsql/10/backups' is writable
INFO: ==> checking WAL files archiving configuration
INFO: WAL archiving is local, not checking SSH
INFO: checking WAL archiving directory: /var/lib/pgsql/10/backups/pitr/archived_wal
INFO: target directory '/var/lib/pgsql/10/backups/pitr/archived_wal' exists
INFO: target directory '/var/lib/pgsql/10/backups/pitr/archived_wal' is writable
INFO: ==> checking access to PostgreSQL
INFO: psql command and connection options are: psql -X -X -U postgres
INFO: connection database is: postgres
INFO: environment variables (maybe overwritten by the configuration file):
INFO:   PGDATA=/var/lib/pgsql/10/data
INFO: PostgreSQL version is: 10.0
INFO: connection role can run backup functions
INFO: current configuration:
INFO:   wal_level = replica
INFO:   archive_mode = on
INFO:   archive_command = '/usr/bin/archive_xlog %p'
INFO: ==> checking access to PGDATA
INFO: PostgreSQL and the configuration reports the same PGDATA
INFO: permissions of PGDATA ok
INFO: owner of PGDATA is the current user
INFO: access to the contents of PGDATA ok

Faire une sauvegarde

$ pitrery backup
INFO: preparing directories in /var/lib/pgsql/10/backups/pitr
INFO: listing tablespaces
INFO: starting the backup process
INFO: performing a non-exclusive backup
INFO: backing up PGDATA with tar
INFO: archiving /var/lib/pgsql/10/data
INFO: stopping the backup process
INFO: copying the backup history file
INFO: copying the tablespaces list
INFO: copying PG_VERSION
INFO: backup directory is /var/lib/pgsql/10/backups/pitr/2017.07.31_16.50.23
INFO: done

Lister les sauvegardes.

$ pitrery list
List of local backups
/var/lib/pgsql/10/backups/pitr/2017.07.31_16.50.23  9.1M      2017-07-31 16:50:23 CEST

Faire une restauration

$ pitrery restore
INFO: searching backup directory
INFO: searching for tablespaces information
INFO: 
INFO: backup directory:
INFO:   /var/lib/pgsql/10/backups/pitr/2017.07.31_16.50.23
INFO: 
INFO: destinations directories:
INFO:   PGDATA -> /var/lib/pgsql/10/data
INFO: 
INFO: recovery configuration:
INFO:   target owner of the restored files: postgres
INFO:   restore_command = '/usr/bin/restore_xlog %f %p'
INFO: 
INFO: creating /var/lib/pgsql/10/data with permission 0700
INFO: extracting PGDATA to /var/lib/pgsql/10/data
INFO: extraction of PGDATA successful
INFO: preparing pg_xlog directory
INFO: preparing recovery.conf file
INFO: done
INFO: 
INFO: please check directories and recovery.conf before starting the cluster
INFO: and do not forget to update the configuration of pitrery if needed
INFO: 

PostgresSQL propose, comme tout bon SGBD, de nombreuses vues, accessibles en SQL, pour obtenir des informations sur son fonctionnement interne. On peut donc avoir des informations sur le fonctionnement des bases, des processus d'arrière plan, des tables, les requêtes en cours…

Cette vue donne la liste des sessions à l'instance (une ligne par session).

Au fil des versions, elle gagne de plus en plus d'informations. En version 10, elle dispose de ces colonnes :

Voici la description des différents champs :

• datid : l'OID de la base à laquelle la session est connectée ;
• datname : le nom de la base associée à cet OID ;
• pid : le numéro du processus du backend, c'est-à-dire du processus PostgreSQL chargé de discuter avec le client (cette colonne avait pour nom procpid avant la version 9.2) ;
• usesysid : l'OID de l'utilisateur connecté ;
• usename : le nom de l'utilisateur associé à cet OID ;
• application_name : un nom facultatif renseigné par l'application cliente ;
• client_addr : l'adresse IP du client connecté (ou NULL si connexion sur socket Unix) ;
• client_hostname : le nom associé à cette IP, renseigné uniquement si log_hostname est à on (attention, ce paramètre peut fortement ralentir la connexion à cause de la résolution DNS nécessaire) ;
• client_port : le numéro de port à partir duquel le client est connecté, toujours s'il s'agit d'une connexion IP ;
• backend_start : le timestamp de l'établissement de la session ;
• xact_start : le timestamp de début de la dernière transaction ;
• query_start : le timestamp de début de la dernière requête ;
• state_change : le timestamp du dernier changement d'état (utile surtout dans le cas d'une session en state idle, pour connaître l'heure de la dernière requête exécutée, et l'heure de fin de cette dernière requête) ;
• wait_event_type et wait_event : le processus est en attente d'un verrou, ces deux colonnes permettent de savoir quel type de verrou et sur quel objet (avant la version 9.6, il n'y avait qu'une colonne, nommée waiting de type booléen);
• state : l'état du processus ;
• backend_xid : l'identifiant de transaction pour ce processus ;
• backend_xmin : l'horizon xmin du processus ;
• query contient la requête en cours si state est active, et la dernière requête effectuée pour les autres valeurs de state ;
• backend_type indique le type de processus (cette colonne apparaît en version 10).

Cette vue n'est renseignée que si track_activities est à on (valeur par défaut). Certains champs ne sont renseignés que pour les superutilisateurs.

La définition de la vue est celle-ci :

• pid : le numéro du processus du backend, c'est à dire du processus PostgreSQL chargé de discuter avec le client ;
• ssl : ssl activé ou non ;
• version : version ssl utilisé, null si ssl n'est pas utilisé ;
• cipher : suite de chiffrement utilisée, null si ssl n'est pas utilisé ;
• bits : nombre de bits de la suite de chiffrement, null si ssl n'est pas utilisé ;
• compression : compression activée ou non, null si ssl n'est pas utilisé ;
• clientdn : champ Distinguished Name (DN) du certificat client, null si aucun certificat client n'est utilisé ou si ssl n'est pas utilisé ;

La définition de la vue est celle-ci :

• datid/datname : l'OID et le nom de la base de données ;
• numbackends : le nombre de sessions en cours ;
• xact_commit : le nombre de transactions ayant terminé avec commit sur cette base ;
• xact_rollback : le nombre de transactions ayant terminé avec rollback sur cette base ;
• blks_read : le nombre de blocs demandés au système d'exploitation ;
• blks_hit : le nombre de blocs trouvés dans la cache de PostgreSQL ;
• tup_returned : le nombre d'enregistrements réellement retournés par les accès aux tables ;
• tup_fetched : le nombre d'enregistrements interrogés par les accès aux tables (ces deux compteurs seront explicités dans la vue sur les index) ;
• tup_inserted : le nombre d'enregistrements insérés en base ;
• tup_updated : le nombre d'enregistrements mis à jour en base ;
• tup_deleted : le nombre d'enregistrements supprimés en base ;
• conflicts : le nombre de conflits de réplication (sur un esclave) ;
• temp_files : le nombre de fichiers temporaires (utilisés pour le tri) créés par cette base depuis son démarrage ;
• temp_bytes : le nombre d'octets correspondant à ces fichiers temporaires. Cela permet de trouver les bases effectuant beaucoup de tris sur disque ;
• deadlocks : le nombre de deadlocks (interblocages) ;
• blk_read_time et blk_write_time : le temps passé à faire des lectures et des écritures vers le disque (il faut que track_io_timing soit à on, ce qui n'est pas la valeur par défaut) ;
• stats_reset : la date de dernière remise à zéro des compteurs de cette vue.