Le checkpointer et le bgwriter son désormais lancés pendant la phase de crash recovery de la même manière qu’on le fait pour la réplication. L’objectif est de limiter la duplication de code en supprimant ce cas particulier. Il est possible que, dans certains cas, cela améliore les performances. Par exemple quand la quantité de données à mettre en cache pour la recovery dépasse la taille des shared buffers.

Le comportement de l’instance reste inchangé en mode single user (option --single du postmaster).

Précédemment, lors de la création d’une base de données, PostgreSQL devait réaliser un checkpoint, copier les fichiers de la base de référence, puis faire un nouveau checkpoint.

Le premier checkpoint permet de s’assurer que les données des buffers sales sont sur écrits sur disque, y compris ceux des tables UNLOGGED. Il permet aussi de s’assurer que les commandes de suppressions de fichiers ont été traitées, ce qui évite la disparition d’un fichier pendant sa copie.

La copie des fichiers de la base de référence est tracée dans les WAL sous forme d’un enregistrement unique pour chaque TABLESPACE utilisé par la base. Chacun de ces enregistrements représente l’écriture du répertoire associé.

Le second checkpoint permet de s’assurer qu’on ne rejouera pas les enregistrements de WAL du CREATE DATABASE en cas de crash recovery. La copie des fichiers pourrait en effet produire un résultat différent car des modifications ont été faites après la copie mais avant la fin des WAL. Cela causerait des erreurs dans le rejeu des enregistrements de WAL suivants.

Un nouveau mécanisme a été mis en place pour permettre de réaliser le CREATE DATABASE sans checkpoint.

Ce changement a plusieurs avantages :

• éviter deux checkpoints qui peuvent être très coûteux en performance à la fois pendant le checkpoint et après si full_page_writes est configuré à on (ce qui est la valeur par défaut). Ce problème peut arriver sur des systèmes avec une grosse activité. La nouvelle méthode permet également d’améliorer les performances de la commande lorsque la base de référence est petite ;
• sécuriser la copie en listant les fichiers copiés à partir des informations présentes dans le catalogue au lieu de se baser sur le contenu du système de fichier. Cela permet d’éviter de copier des fichiers qui ne devraient pas être là ;
• sécuriser le rejeu des WAL en rendant l’opération plus robuste. Les données copiées sont toutes tracées dans les WAL au lieu de n’enregistrer qu’un marqueur qui signale qu’il faut copier le répertoire de la base modèle ;
• permettre plus de flexibilité pour développer d’autres fonctionnalités, par exemple TDE (Transparent Data Encryption).

Ce changement augmente cependant la volumétrie de WAL écrits, cela peut être un problème dans certains cas. De plus, si la base est grosse, la copie de fichier est plus performante.

Le changement n’étant pas sans pénalité, le choix de la stratégie de création est laissé à l’utilisateur. CREATE DATABASE se voit donc ajouter un paramètre supplémentaire, appelé STRATEGY, qui peut prendre la valeur WAL_LOG (valeur par défaut) ou FILE_COPY.

La différence de volume de WAL généré par chaque commande est facilement observable dans la vue pg_stat_wal.

Cas d’une création entièrement tracée dans les WAL :

SELECT pg_stat_reset_shared('wal');
CREATE DATABASE db_wal_log STRATEGY WAL_LOG;
SELECT wal_records, wal_bytes FROM pg_stat_wal;

 wal_records | wal_bytes 
-------------+-----------
        1254 |   4519307
(1 row)

Cas d’une création en mode copie de fichier :

SELECT pg_stat_reset_shared('wal');
CREATE DATABASE db_file_copy STRATEGY FILE_COPY;
SELECT wal_records, wal_bytes FROM pg_stat_wal ;

 wal_records | wal_bytes 
-------------+-----------
          11 |       849
(1 row)

La commande ALTER DATABASE .. SET TABLESPACE repose sur les mêmes mécanismes que CREATE DATABASE .. STRATEGY FILE_COPY. L’opération était initialement couverte par cette évolution mais la commande est plus complexe à modifier et le travail n’a pas pu être fait dans les temps pour la sortie de la version 15.

Dans les versions précédentes, le processus stats collector recevait des mises à jour des statistiques d’activité collectées par les autres processus via UDP. Il partageait ces statistiques en les écrivant à intervalle régulier dans des fichiers temporaires situés dans le répertoire pointé par stats_temp_directory. Ces fichiers pouvaient atteindre quelques dizaines de mégaoctets et être écrits jusqu’à deux fois par seconde, ce goulet d’étranglement a longtemps été un frein à l’ajout de statistiques pourtant utiles.

Désormais, les statistiques sont stockées en mémoire partagée, soit directement si le nombre de ces statistiques est fixe (pg_stat_database), soit dans une table de hachage dans le cas où leur nombre est variable (pg_stat_users_tables).

Une zone dédiée est désormais visible dans la mémoire partagée :

SELECT *
  FROM pg_shmem_allocations
 WHERE name = 'Shared Memory Stats';

        name         |    off    |  size  | allocated_size
---------------------+-----------+--------+----------------
 Shared Memory Stats | 147290112 | 263312 |         263424
(1 row)

Ce changement d’architecture se traduit par la disparition du processus stats collector et du paramètre stats_temp_directory. Le répertoire pg_stat_tmp existe toujours dans le répertoire de données de l’instance car certaines extensions dont pg_stat_statements l’utilisent toujours.

Les statistiques sont chargées depuis des fichiers situés dans le répertoire pg_stat lors du démarrage. Elles sont écrites lors de l’arrêt de l’instance par le processus checkpointer. Il y a deux exceptions à cela : en cas de crash les statistiques sont remises à zéro et en cas d’arrêt avec l’option immediate les données ne sont pas sauvées.

Les données sont la plupart du temps accumulées localement par les processus avant d’être écrites suite à un commit ou lors d’un timeout causé par l’inactivité de la session.

Un nouveau paramètre a été introduit : stats_fetch_consistency. Il permet de déterminer le comportement lorsque les statistiques sont accédées dans une transaction. Il peut être changé dans la session et a trois valeurs possibles :

• none : chaque accès récupère les données depuis la mémoire partagée. Les valeurs ramenées sont donc différentes à chaque fois. C’est le mode le moins coûteux. Il est adapté pour les systèmes de supervision qui accèdent aux données une seule fois ;
• cache : le premier accès à une statistique la met en cache pour le restant de la transaction à moins que pg_stat_clear_snapshot() ne soit appelée. C’est utile pour des requêtes qui font des auto-jointures. C’est la valeur par défaut ;
• snapshot : le premier accès aux statistiques met en cache toutes les statistiques accessibles pour la base de données en cours. Elles seront conservées jusqu’à la fin de la transaction à moins que pg_stat_clear_snapshot() ne soit appelée. C’est le mode le plus coûteux.

On observe quatre contextes mémoires relatifs aux statistiques d’activité pour ces trois modes de fonctionnement :

SELECT name, parent, level, total_bytes, free_bytes, used_bytes
  FROM pg_backend_memory_contexts
 WHERE name LIKE ANY(ARRAY['PgStat%','CacheMemoryContext']);

          name          |       parent       | level | total_bytes | free_bytes | used_bytes
------------------------+--------------------+-------+-------------+------------+------------
 CacheMemoryContext     | TopMemoryContext   |     1 |     1048576 |     501176 |     547400
 PgStat Shared Ref Hash | CacheMemoryContext |     2 |        7224 |        680 |       6544
 PgStat Shared Ref      | CacheMemoryContext |     2 |        8192 |       3568 |       4624
 PgStat Pending         | CacheMemoryContext |     2 |        8192 |       6944 |       1248
(4 rows)

On peut noter la présence de la zone mémoire PgStat Pending qui est celle utilisée pour les statistiques en cours de mise à jour dans la session.

Avec les modes cache et snapshot, on voit l’apparition d’une autre zone. Sa dimension est importante dans le cas du mode snapshot.

  mode   |         name           |       parent       | level | total_bytes | free_bytes | used_bytes
---------+------------------------+--------------------+-------+-------------+------------+------------
snapshot | PgStat Snapshot        | TopMemoryContext   |     1 |       57400 |       4488 |      52912
cache    | PgStat Snapshot        | TopMemoryContext   |     1 |       25656 |        680 |      24976

Les vues pg_backend_memory_contexts, pg_shmem_allocations sont normalement accessibles uniquement aux utilisateurs dotés de l’attribut super utilisateur. En version 15, les membres du groupe pg_read_all_stats peuvent aussi y accéder.

pg_upgrade préserve désormais les relfilenodes, tablespace oid et database oid.

Le relfilenode est le nom utilisé par le fichier qui contient les données d’une relation. Les différents segments et forks de la relation ajoutent un suffixe au relfilenode (ex: _vm pour la visibility map). Il peut être différent de l’oid de l’objet (identifiant unique d’un objet) car certaines opérations peuvent conduire à la recréation des fichiers de la relation comme un VACUUM FULL.

Le tablespace oid est l’identifiant unique d’un tablespace. Il est utilisé pour le lien symbolique placé dans le répertoire pg_tblspc et qui pointe vers le répertoire qui contient les données du tablespace.

Le database oid est l’identifiant unique d’une base de données. Il est utilisé pour nommer le répertoire qui regroupe toutes les données d’une base de données dans un tablespace.

Ce changement permet donc limiter les changements de noms de fichiers, répertoires et lien symboliques suite à une montée de version avec pg_upgrade. Les bénéfices sont multiples :

• faciliter l’analyse en cas de problème lors de la mise à jour ;
• économiser de la bande passante quand on utilise rsync pour faire une mise à jour différentielle des fichiers d’une instance après une mise à jour ;
• faciliter l’implémentation de futures fonctionnalités comme le chiffrement des blocs pour lesquels le sel pourrait se baser sur le refilenode.

Pour permettre cette modification, il a été décidé que l’oid des bases système serait fixé et que les bases de données utilisateurs auront un oid supérieur ou égale à 16384.

SELECT datname, oid FROM pg_database

   datname    |  oid
--------------+-------
 formation    | 16384
 postgres     |     5
 template1    |     1
 template0    |     4
(4 rows)

La commande CREATE DATABASE se voit ajouter une nouvelle clause OID qui permet de spécifier manuellement un oid. Cet ajout est principalement destiné à l’usage de pg_upgrade qui est par ailleurs le seul à pouvoir assigner des oid inférieurs à 16384.

L’utilisation de plus larges segments de données par la commande psql \copy from permet d’effectuer plus rapidement l’import de données dans des tables.

Le gain observé approche les 10% sur un fichier de données contenant 20 millions d’entrées.

PostgreSQL 14 :

postgres=# \copy t1 from '~/data.txt';
COPY 20000000
Time: 9755.384 ms (00:09.755)

PostgreSQL 15 :

postgres=# \copy t1 from '~/data.txt';
COPY 20000000
Time: 8920.834 ms (00:08.921)
postgres=#

Si ce transfert passe par une connexion distante, la quantité de trafic réseau est également réduite.

Commande \dconfig

La commande \dconfig permet d’afficher les paramètres de configuration de l’instance.

Son appel sans argument permet d’afficher les paramètres dont les valeurs ne sont pas celles par défaut :

postgres=# \dconfig
     List of non-default configuration parameters
         Parameter          |          Value           
----------------------------+--------------------------
 application_name           | psql
 client_encoding            | UTF8
 config_file                | /data/15/postgresql.conf
 data_directory             | /data/15
 default_text_search_config | pg_catalog.english
 hba_file                   | /data/15/pg_hba.conf
 ident_file                 | /data/15/pg_ident.conf
 lc_messages                | en_US.UTF-8
 lc_monetary                | en_US.UTF-8
 lc_numeric                 | en_US.UTF-8
 lc_time                    | en_US.UTF-8
 log_filename               | postgresql-%a.log
 logging_collector          | on
 log_rotation_size          | 0
 log_timezone               | UTC
 log_truncate_on_rotation   | on
 TimeZone                   | UTC

L’ajout d’un + à la commande permet d’obtenir plus d’informations :

postgres=# \dconfig+
                           List of non-default configuration parameters
         Parameter          |          Value           |  Type   |  Context   | Access privileges 
----------------------------+--------------------------+---------+------------+-------------------
 application_name           | psql                     | string  | user       | 
 client_encoding            | UTF8                     | string  | user       | 
 config_file                | /data/15/postgresql.conf | string  | postmaster | 
 data_directory             | /data/15                 | string  | postmaster | 
 default_text_search_config | pg_catalog.english       | string  | user       | 
 hba_file                   | /data/15/pg_hba.conf     | string  | postmaster | 
 ident_file                 | /data/15/pg_ident.conf   | string  | postmaster | 
 lc_messages                | en_US.UTF-8              | string  | superuser  | 
 lc_monetary                | en_US.UTF-8              | string  | user       | 
 lc_numeric                 | en_US.UTF-8              | string  | user       | 
 lc_time                    | en_US.UTF-8              | string  | user       | 
 log_filename               | postgresql-%a.log        | string  | sighup     | 
 logging_collector          | on                       | bool    | postmaster | 
 log_rotation_size          | 0                        | integer | sighup     | 
 log_timezone               | UTC                      | string  | sighup     | 
 log_truncate_on_rotation   | on                       | bool    | sighup     | 
 TimeZone                   | UTC                      | string  | user       | 

La commande accepte également l’utilisation de wild card :

postgres=# \dconfig *work_mem*
 List of configuration parameters
         Parameter         | Value 
---------------------------+-------
 autovacuum_work_mem       | -1
 logical_decoding_work_mem | 64MB
 maintenance_work_mem      | 64MB
 work_mem                  | 4MB

l’appel \dconfig * permet ainsi de lister l’ensemble des paramètres de l’instance.

Commande \getenv

La commande \getenv permet d’enregistrer la valeur d’une variable d’environnement dans une variable sql.

[postgres@pg15 ~]$ export ENV_VAR='foo'
[postgres@pg15 ~]$ psql
psql (15beta2)
Type "help" for help.

postgres=# \getenv sql_var ENV_VAR

postgres=# \echo :sql_var
foo

L’auto-complétion dans psql a été améliorée à différents niveaux.

Recherche insensible à la casse

L’auto-complétion est désormais capable de suggérer ou compléter une commande même si la casse n’est pas respectée.

La complétion par une double tabulation de la saisie suivante permet d’afficher la liste des paramètres des traces, alors que les versions précédentes ne renvoyait rien :

postgres=# set LOG_

La saisie est automatiquement transformée en minuscule, et les différentes suggestions apparaissent :

postgres=# set log_
log_duration                       log_lock_waits                     log_min_messages                   log_planner_stats                  log_statement_stats                
log_error_verbosity                log_min_duration_sample            log_parameter_max_length           log_replication_commands           log_temp_files                     
log_executor_stats                 log_min_duration_statement         log_parameter_max_length_on_error  log_statement                      log_transaction_sample_rate        
logical_decoding_work_mem          log_min_error_statement            log_parser_stats                   log_statement_sample_rate   

Noms de paramètres

La complétion d’un \ via une double tabulation permet de lister les commandes disponibles. Cette liste affiche désormais le nom complet de chaque commande, alors que certaines commandes apparaissaient sous la forme d’abréviations. La commande \l devient ainsi \list, \o devient \out, \e devient \echo, etc.

postgres=# \
Display all 106 possibilities? (y or n)
\!                 \dAp               \dFp               \dRs               \errverbose        \lo_export         \sv
\?                 \db                \dFt               \ds                \ev                \lo_import         \t
\a                 \dc                \dg                \dt                \f                 \lo_list           \T
\C                 \dC                \di                \dT                \g                 \lo_unlink         \timing
\cd                \dconfig           \dl                \du                \gdesc             \out               \unset
\connect           \dd                \dL                \dv                \getenv            \password          \warn
\conninfo          \dD                \dm                \dx                \gexec             \print             \watch
\copy              \ddp               \dn                \dX                \gset              \prompt            \write
\copyright         \dE                \do                \dy                \gx                \pset              \x
\crosstabview      \des               \dO                \echo              \help              \qecho             \z
\d                 \det               \dp                \edit              \html              \quit              
\da                \deu               \dP                \ef                \if                \reset             
\dA                \dew               \dPi               \elif              \include           \s                 
\dAc               \df                \dPt               \else              \include_relative  \set               
\dAf               \dF                \drds              \encoding          \ir                \setenv            
\dAo               \dFd               \dRp               \endif             \list              \sf  

EXPLAIN EXECUTE

La complétion de la commande EXPLAIN ajoute l’option EXECUTE.

postgres=# EXPLAIN 
ANALYZE      DECLARE      DELETE FROM  EXECUTE      INSERT INTO  MERGE        SELECT       UPDATE       VERBOSE 

LOCK TABLE

La commande LOCK TABLE permet désormais la complétion de l’option ONLY avant le nom de la table :

postgres=# LOCK TABLE 
information_schema.  ONLY                 public.              t1

Idem pour l’option NOWAIT, à préciser après le nom de la table :

postgres=# LOCK TABLE t1 
IN      NOWAIT

CREATE, ALTER, DROP

Enfin, diverses améliorations ont été apportées aux options de complétion de plusieurs commandes CREATE, ALTER et DROP :

• CREATE CONVERSION, CREATE DOMAIN, CREATE LANGUAGE, CREATE SCHEMA, CREATE SEQUENCE, CREATE TRANSFORM
• ALTER DEFAULT PRIVILEGES,ALTER FOREIGN DATA WRAPPER, ALTER SEQUENCE, ALTER VIEW
• DROP MATERIALIZED VIEW, DROP OWNED BY, DROP POLICY, DROP TRANSFORM

Le mode backup_exclusive pose problème car il crée un fichier backup_label dans le répertoire de données durant l’execution d’une sauvegarde. Avec ce mode, il n’y a aucun moyen de distinguer le répertoire de données d’un serveur en mode sauvegarde de celui d’un serveur interrompu pendant la sauvegarde. En cas de crash, l’instance cherche alors à se restaurer au lieu de poursuivre la sauvegarde inachevée.

En essayant de se restaurer sans restore_command, PostgreSQL cherche à rejouer les journaux disponibles dans pg_wal et peut échouer si une activité importante a entraîné la rotation desdits journaux. Dans certains cas, si le checkpoint écrit dans backup_label n’est pas bon, ou si le fichier lui-même n’est pas bon, l’instance tente de revenir à un état différent de celui précédent le backup, entraînant un risque de corruption des données.

Avec le mode de sauvegarde non exclusif, le fichier backup_label est renvoyé par la fonction pg_backup_stop au lieu d’être écrit dans le répertoire de données, protégeant ainsi le serveur en cas de crash pendant une sauvegarde. Une connexion avec le client de sauvegarde est nécessaire pendant toute la durée de celle-ci. En cas d’interruption, l’opération est abandonnée, sans risque pour l’intégrité des données. Ce mode de sauvegarde a été introduit avec PostgreSQL 9.6 et a supplanté le mode exclusif qui a été déprécié dans la foulée. Cependant, les sauvegardes exclusives ont continué à être présentes et utilisables jusqu’à la version 14.

PostgreSQL 15 supprime cette possibilité, et pour éviter toute confusion, les fonctions pg_start_backup() et pg_stop_backup()ont été renommées pg_backup_start() et pg_backup_stop(). Il est donc impératif de contrôler ses procédures de sauvegardes pour, d’une part, vérifier qu’elles n’utilisent plus les sauvegardes exclusives, et d’autre part, renommer les fonctions d’appel.

Il est désormais possible d’utiliser des modules pour l’archivage plutôt que l’archive_command. Cela simplifie la mise en place de l’archivage, permet de rendre cette opération plus sécurisée et robuste, et aussi plus performante. Ces modules peuvent accéder à des fonctionnalités avancées de PostgreSQL comme la création des paramètres configuration ou de background workers.

On peut s’attendre à un gain de performance dû au fait que, plutôt de créer un processus pour l’exécution de chaque archive_command, PostgreSQL va utiliser le module qui a été chargé en mémoire une fois pour toutes.

On peut imaginer que des gains similaires pourront être fait pour l’établissement d’une connexion à un serveur distant. Il pourrait également être possible d’invoquer des background workers en réaction à une accumulation de WAL en attente d’archivage.

L’écriture d’un module d’archivage est décrite dans la documentation. Il faut pour cela écrire un programme en C, en plus de requérir des compétences particulières, les chances de planter le serveur sont grandes en cas de bug. Il semble donc plus raisonnable de s’appuyer et participer à des projets communautaires. Les outils de sauvegardes comme pgBackRest ou Barman vont sans doute également s’emparer du sujet.

Un nouveau paramètre archive_library a été ajouté à la configuration et permet de charger le module. Comme pour l’archive_command, le serveur n’effectuera la suppression ou le recyclage des WAL que lorsque le module indique que les WAL ont été archivés. Ce nouveau paramètre peut être rechargé à chaud.

=# \dconfig+ archive_library
                List of configuration parameters
    Parameter    | Value |  Type  | Context | Access privileges
-----------------+-------+--------+---------+-------------------
 archive_library |       | string | sighup  |
(1 row)

Si l’archive_library n’est pas remplie, PostgreSQL utilisera l’archive_command. En version 15, si les deux paramètres sont remplis, PostgreSQL favorisera l’archive_library. Ce comportement va changer en v16 ou les deux paramètres ne pourront pas être définis en même temps.

Le module d’exemple basic_archive a également été mis à disposition pour tester la fonctionnalité et donner un exemple d’implémentation pour ce genre de module. Pour l’utiliser, il suffit d’activer l’archivage, d’ajouter le module à l’archive_library et de configurer le répertoire cible pour l’archivage. Il faut ensuite redémarrer l’instance.

Afin d’observer le fonctionnement de ce module, nous allons créer une instance neuve :

ARCHIVE=$HOME/archives
PGDATA=$HOME/data
PGPORT=5656
PGUSER=$USER

mkdir -p $ARCHIVE $PGDATA

initdb --data-checksum $PGDATA

cat << __EOF__ >> $PGDATA/postgresql.conf
port = $PGPORT
listen_addresses = '*'
cluster_name = 'test_archiver'
archive_mode = on
archive_library = 'basic_archive'
basic_archive.archive_directory = '$ARCHIVE'
__EOF__

pg_ctl start -D $PGDATA

Si on force un archivage, on voit que PostgreSQL a bien archivé dans la vue pg_stat_archiver :

psql -c "SELECT pg_create_restore_point('Forcer une ecriture dans les WAL.')"
psql -c "SELECT pg_switch_wal()"
psql -xc "SELECT * FROM pg_stat_archiver";

-[ RECORD 1 ]------+------------------------------
archived_count     | 1
last_archived_wal  | 000000010000000000000001
last_archived_time | 2022-07-06 17:39:33.632029+02
failed_count       | 0
last_failed_wal    | ¤
last_failed_time   | ¤
stats_reset        | 2022-07-06 17:39:23.287479+02

On peut vérifier la présence du fichier dans le répertoire :

SELECT file.name, stats.*
  FROM current_setting('basic_archive.archive_directory') AS archive(directory)
     , LATERAL pg_ls_dir(archive.directory) AS file(name)
     , LATERAL pg_stat_file(archive.directory || '/' || file.name) AS stats

-[ RECORD 1 ]+-------------------------
name         | 000000010000000000000001
size         | 16777216
access       | 2022-07-06 17:39:33+02
modification | 2022-07-06 17:39:33+02
change       | 2022-07-06 17:39:33+02
creation     | ¤
isdir        | f

Le module basic_archive copie le WAL à archiver vers un fichier temporaire, le synchronise sur disque, puis le renomme. Si le fichier archivé existe déjà dans le répertoire cible et est identique, l’archivage est considéré comme un succès. Si le serveur plante, il est possible que des fichiers temporaires qui commencent par archtemp soient présents. Il est conseillé de les supprimer avant de démarrer PostgreSQL. Il est possible de les supprimer à chaud mais il faut s’assurer qu’il ne s’agisse pas d’un fichier en cours d’utilisation.

En cas d’échec de l’archivage, il est possible que l’erreur ne soit pas visible dans le titre du processus ou la vue pg_stat_archiver. C’est par exemple le cas si une erreur de configuration empêche le chargement du module. Les traces de PostgreSQL contiennent alors les informations nécessaires pour résoudre le problème.

Au moment de l’écriture de cet article, les paramètres des modules d’archivage ne sont pas visibles depuis pg_settings ou depuis la nouvelle méta-commande \dconfig+ qui utilise cette vue.

Ce comportement s’explique par cette ligne de la documentation de la vue pg_settings :

This view does not display customized options until the extension module that defines them has been loaded.

C’est le processus d’archivage qui charge le module d’archivage, les paramètres qui y sont définis ne sont donc pas visibles des autres processus.

Plusieurs alternatives sont possibles pour consulter leurs valeurs :

• charger la librairie dans la session :

  LOAD 'basic_archive';
  SELECT name, setting FROM pg_settings WHERE name = 'basic_archive.archive_directory';

                name               |                      setting
  ---------------------------------+---------------------------------------------------
   basic_archive.archive_directory | /home/benoit/var/lib/postgres/archives/pgsql-15b3
  (1 row)

• consulter les valeurs des paramètres directement avec la commande SHOW de psql :

  SHOW basic_archive.archive_directory;

   basic_archive.archive_directory 
  ---------------------------------
   /home/benoit/archives

• voir les paramètres renseignés dans le fichier de configuration dans la vue pg_file_settings :

  SELECT *
    FROM pg_file_settings
   WHERE name = 'basic_archive.archive_directory' \gx

  -[ RECORD 1 ]-----------------------------------------
  sourcefile | /home/benoit/data/postgresql.conf
  sourceline | 820
  seqno      | 27
  name       | basic_archive.archive_directory
  setting    | /home/benoit/archives
  applied    | t
  error      | ¤

Le nouveau paramètre recovery_prefetch permet d’activer le prefetch lors du rejeu des WAL. Il permet de lire à l’avance les WAL et d’initier la lecture asynchrone des blocs de données qui ne sont pas dans le cache de l’instance. Tous les OS ne permettent pas d’implémenter cette fonctionnalité, le paramètre a donc trois valeurs possibles try, on et off. La valeur par défaut est try.

wal_decode_buffer_size permet de limiter la distance à laquelle on peut lire les WAL en avance de phase. Sa valeur par défaut est de 512 ko.

Le GUC maintenance_io_concurrency est également utilisé pour limiter le nombre d’I/O concurrentes autorisées, ainsi que le nombre de blocs à lire en avance. Le calcul utilisé est le suivant : maintenance_io_concurrency * 4 blocs.

Cette nouvelle fonctionnalité devrait accélérer grandement la recovery suite à un crash, une restauration ou lorsque la réplication utilise le log shipping.

Précédemment, pour réaliser ce genre d’optimisation, il fallait passer des outils externes comme pg_prefaulter qui a servi d’inspiration à cette fonctionnalité.

Création d’un environnement de test :

PGDATA=/home/benoit/var/lib/postgres/testpg15
PGDATASAV=/home/benoit/var/lib/postgres/testpg15-save
PGUSER=postgres
PGPORT=5432

initdb --username "$PGUSER" "$PGDATA"

Démarrer PostgreSQL en forçant des checkpoints très éloignés les un des autres. Pour cela on augmente le timeout et la quantité maximale de WAL avant le déclenchement du checkpoint. On désactive aussi les full page writes pour éviter que les pages complètes soient dans les WAL. En effet dans ce cas, le préfetch est inutile.

pg_ctl -D "$PGDATA" \
       -o "-c checkpoint_timeout=60min -c max_wal_size=10GB -c full_page_writes=off" \
       -W \
       start

Ajouter des données avec pgbench pour générer des WAL.

pgbench -i -s300 postgres
psql postgres -c checkpoint
pgbench -T300 -Mprepared -c4 -j4 postgres

Tuer PostgreSQL pour forcer une restauration au redémarrage de PostgreSQL.

killall -9 postgres

Sauvegarder le répertoire de données.

cp -R "$PGDATA" "$PGDATASAV"

Démarrer PostgreSQL avec le prefetch désactivé :

pg_ctl -D "$PGDATA" \
       -o "-c recovery_prefetch=off" \
       -W \
       start

Voici les traces du démarrage :

LOG:  starting PostgreSQL 15.1 on x86_64-pc-linux-gnu,
      compiled by gcc (GCC) 12.2.1 20220819 (Red Hat 12.2.1-2), 64-bit
LOG:  listening on IPv6 address "::1", port 5432
LOG:  listening on IPv4 address "127.0.0.1", port 5432
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
LOG:  database system was interrupted; last known up at 2023-02-10 23:17:43 CET
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/E7A522B8
LOG:  redo in progress, elapsed time: 10.00 s, current LSN: 0/E82D8528
LOG:  redo in progress, elapsed time: 20.00 s, current LSN: 0/E8B3A690
LOG:  redo in progress, elapsed time: 30.00 s, current LSN: 0/E93F3D98
LOG:  redo in progress, elapsed time: 40.00 s, current LSN: 0/E9C57E60
LOG:  redo in progress, elapsed time: 50.00 s, current LSN: 0/EA4EB5A8
FATAL:  the database system is not yet accepting connections
DETAIL:  Consistent recovery state has not been yet reached.
LOG:  redo in progress, elapsed time: 60.00 s, current LSN: 0/EAD8F530
FATAL:  the database system is not yet accepting connections
DETAIL:  Consistent recovery state has not been yet reached.
LOG:  invalid record length at 0/EB4CAF48: wanted 24, got 0
LOG:  redo done at 0/EB4CAF10 system usage: CPU: user: 6.75 s, system: 15.58 s, elapsed: 67.99 s
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  checkpoint complete:
      wrote 10366 buffers (63.3%);
      0 WAL file(s) added, 4 removed, 0 recycled;
      write=0.325 s, sync=0.010 s, total=0.367 s;
      sync files=23, longest=0.005 s, average=0.001 s;
      distance=59875 kB, estimate=59875 kB
LOG:  database system is ready to accept connections

Arrêter PostgreSQL, copier de la sauvegarde du répertoire de données et démarrer PostgreSQL avec le prefetch activé :

pg_ctl -D "$PGDATA" \
       -m fast \
       stop

rm -fr "$PGDATA"
cp -r "$PGDATASAV" "$PGDATA"

pg_ctl -D "$PGDATA" \
       -o "-c recovery_prefetch=try" \
       -W \
       start

LOG:  starting PostgreSQL 15.1 on x86_64-pc-linux-gnu,
      compiled by gcc (GCC) 12.2.1 20220819 (Red Hat 12.2.1-2), 64-bit
LOG:  listening on IPv6 address "::1", port 5432
LOG:  listening on IPv4 address "127.0.0.1", port 5432
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
LOG:  database system was interrupted; last known up at 2023-02-10 23:17:43 CET
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/E7A522B8
LOG:  redo in progress, elapsed time: 10.00 s, current LSN: 0/EAD67BC0
LOG:  invalid record length at 0/EB4CAF48: wanted 24, got 0
LOG:  redo done at 0/EB4CAF10 system usage: CPU: user: 3.57 s, system: 7.26 s, elapsed: 11.46 s
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  checkpoint complete:
      wrote 10179 buffers (62.1%);
      0 WAL file(s) added, 4 removed, 0 recycled;
      write=0.322 s, sync=0.039 s, total=0.429 s;
      sync files=23, longest=0.017 s, average=0.002 s;
      distance=59875 kB, estimate=59875 kB
LOG:  database system is ready to accept connections

On voit que le redo a duré 11.46s au lieu de 1min 8s du test lors du précédent.

Des statistiques peuvent être lues dans la nouvelle vue pg_stat_recovery_prefetch :

SELECT * FROM pg_stat_recovery_prefetch \gx

stats_reset    | 2023-02-10 23:35:55.873179+01
prefetch       | 200524
hit            | 416757
skip_init      | 2308
skip_new       | 0
skip_fpw       | 0
skip_rep       | 140012
wal_distance   | 0
block_distance | 0
io_depth       | 0

La signification des colonnes est la suivante :

• prefetch : Nombre de blocs récupérés avec le prefetch parce que le les blocs ne sont pas le buffer pool ;
• hit : Nombre de blocs qui n’ont pas été récupérés avec le prefetch car ils étaient déjà dans le buffer pool ;
• skip_init : Nombre de blocs qui n’ont pas été récupérés avec le prefetch car ils auraient été initialisé à zéro ;
• skip_init : Nombre de blocs qui n’ont pas été récupérés avec le prefetch car ils n’existaient pas encore ;
• skip_fpw : Nombre de blocs qui n’ont pas été récupérés avec le prefetch car une lecture de page complête était incluse dans le WAL ;
• skip_rep : Nombre de blocs qui n’ont pas été récupérés avec le prefetch car elles ont déjà été préfetchées récemment ;
• wal_distance : De combien de bytes le prefetcher est entrain de lire en avance ; block_distance : De combien de blocs le prefetcher est en train de lire en avance ;
• io_depth : Combien de prefetch ont été initialisés mais ne sont pas encore terminés.

Cette version introduit la notion de cible pour les sauvegardes effectuées avec pg_basebackup. Le nouveau paramètre -t/--target a été introduit à cet effet. Il peut prendre les valeurs :

• client : la sauvegarde est faite en local (c’est la valeur par défaut) ;
• server : la sauvegarde est faite sur le serveur de base de données ;
• blackhole : aucun fichier n’est créé, c’est utile pour les tests ;

Pour les modes client et server, il faut spécifier un chemin :

• pour client, il faut utiliser l’option -D/--pgdata ;
• pour server, il faut affixer : suivit d’un chemin à la cible.

Sauvegarder sur le serveur de base de données est une tâche plus sensible qu’effectuer une sauvegarde en local. C’est pour cette raison qu’il faut faire partie du groupe pg_write_server_files pour pouvoir utiliser cette cible.

Lorsque la cible est différente de client, l’option -X/--wal-method est requise et doit prendre la valeur none ou fetch. Si l’on choisit la méthode fetch, il peut donc être nécessaire de configurer wal_keep_size pour s’assurer que les WAL nécessaires pour rendre la sauvegarde cohérente soient conservés jusqu’à la fin de l’opération. Si l’archivage est déjà configuré, l’option none peut être utilisée.

Voici quelques exemples de syntaxe pour la commande :

# Sauvegarde sur le serveur du client
pg_basebackup --checkpoint fast --progress \
              --target client --pgdata .

# Sauvegarde "à blanc"
pg_basebackup --checkpoint fast --progress \
              --target blackhole --wal-method fetch

# Sauvegarde sur le serveur de base de données
pg_basebackup --checkpoint fast --progress \
              --target server:/backup/15/main --wal-method fetch

Pour toutes les cibles différentes de client (la valeur par défaut), le format de la sauvegarde est obligatoirement tar. Par exemple, si --format p et --target=server sont spécifiés, l’erreur suivante est affichée.

pg_basebackup: error: cannot specify both format and backup target

Il est prévu de pouvoir étendre le fonctionnement pg_basebackup en ajoutant de nouveaux types de cibles. Le module de test basebackup_to_shell est fourni à titre d’exemple. Il permet d’exécuter une commande qui prend en entrée standard un fichier généré par la sauvegarde.

Le module ajoute à pg_basebackup la cible shell, pour laquelle il est possible d’affixer : et une chaîne de caractère. Cette chaîne de caractère ne peut contenir que des caractères alphanumériques.

Pour l’utiliser, il faut ajouter le module à shared_preload_libraries ou local_preload_libraries, et configurer les paramètres :

• basebackup_to_shell.command : une commande que le serveur va utiliser pour chaque fichier généré par pg_basebackup. Si %f est spécifié dans la commande, il sera remplacé par le nom de fichier. Si %d est spécifié dans la commande, il sera remplacé par la chaîne spécifiée après la cible ;

• basebackup_to_shell.required_role : le rôle requis pour pouvoir utiliser la cible shell. Il faut que l’utilisateur dispose de l’attribut REPLICATION.

Le module est fourni à titre d’exemple pour démontrer la création de ce genre de module et son utilisation. Son utilité est limitée. Nous allons ici l’utiliser pour chiffrer la sauvegarde :

BACKUP=$HOME/backup
PGDATA=$HOME/data
PGPORT=5656
PGUSER=$USER

mkdir -p $PGDATA $BACKUP

initdb --data-checksum $PGDATA

cat << __EOF__ >> $PGDATA/postgresql.conf
port = $PGPORT
listen_addresses = '*'
cluster_name = 'test_shell_module'
shared_preload_libraries = 'basebackup_to_shell'
basebackup_to_shell.command = 'gpg --encrypt --recipient %d --output $BACKUP/%f'
basebackup_to_shell.required_role = 'gpg'
__EOF__

pg_ctl start -D $PGDATA

psql -c "CREATE ROLE gpg WITH LOGIN PASSWORD 'secret'"
psql -c "CREATE ROLE non_autorise WITH LOGIN PASSWORD 'secret'"
echo "localhost:5656:postgres:gpg:secret" >> $HOME/.pgpass
echo "localhost:5656:postgres:non_autorise:secret" >> $HOME/.pgpass
chown $USER $HOME/.pgpass
chmod 600 $HOME/.pgpass

Effectuer une sauvegarde avec le module shell et l’utilisateur gpg :

pg_basebackup --checkpoint fast --progress --user gpg \
              --target shell:$PGUSER --wal-method fetch

On peut contrôler que le backup_manifest est bien chiffré en l’éditant. Pour l’afficher en clair :

gpg -d $BACKUP/backup_manifest

Avec l’utilisateur non_autorise, la sauvegarde échoue :

pg_basebackup --checkpoint fast --progress --user non_autorise \
              --target shell:$PGUSER --wal-method fetch

pg_basebackup: error: could not initiate base backup:
+++ ERROR:  permission denied to use basebackup_to_shell

PostgreSQL permet désormais d’utiliser les algorithmes de compressions LZ4, Zstandard en plus de gzip pour la compression des sauvegardes, des WAL et des écritures de page complètes.

Avantages attendus par type de compression

gzip est la méthode de compression historique de PostgreSQL, elle est utilisée par défaut.

lz4 est plus rapide que gzip mais a généralement un taux de compression inférieur.

zstd présente l’avantage de permettre la parallélisation de la compression, ce qui permet plus de performances.

Type de compression et compilation

L’utilisation de l’algorithme lz4 nécessite l’utilisation du paramètre --with-lz4 lors de la compilation. Ce paramètre avait été ajouté en version 14 pour permettre l’utilisation de lz4 afin de compresser les TOAST.

Le paramètre --with-zstd a été ajouté en version 15 pour permettre l’utilisation de l’algorithme zstd. Ces paramètres sont activés par défaut sur les distributions de type RockyLinux et Debian.

Écriture de pages complètes

Le paramètre wal_compression acceptait précédemment deux valeurs on et off. Il permettait d’activer ou non la compression des images de page complètes (FPI: Full Page Image) écrites dans les WAL lorsque le paramètre full_page_writes était activé ou pendant une sauvegarde physique.

En version 15, trois nouveaux paramètres sont ajoutés et permettent de contrôler le type d’algorithme de compression utilisé parmi : pglz, lz4 et zstd. Le mode de compression par défaut est pglz, c’est l’algorithme choisi si l’on valorise wal_compression à on.

pg_basebackup

Il est désormais également possible de spécifier l’algorithme de compression utilisé par pg_basebackup avec l’option --compression / -Z dont la nouvelle spécification est :

-Z level
-Z [{client|server}-]method[:detail]
--compress=level
--compress=[{client|server}-]method[:detail]

Les valeurs possibles pour la méthode de compression sont none, gzip, lz4 et zstd. Lorsqu’un algorithme de compression est spécifié, il est possible d’ajouter des options de compression en ajoutant une série de paramètre précédé de deux point et séparé par des virgules, sous la forme d’un mot clé ou d’un mot clé=valeur. Pour le moment, les mots clé suivants sont supportés :

• [level=]entier permet de spécifier le niveau de compression ;
• workers=entier permet de spécifier le nombre de processus pour la parallélisation de la compression.

Exemples :

$ pg_basebackup --format t \
                --compress server-lz4:1 \
                --pgdata splz41

$ pg_basebackup --format t \
                --compress server-lz4:level=1 \
                --pgdata splz4l1

$ pg_basebackup --format t \
                --compress server-zstd:level=9,workers=2 \
                --pgdata spzstdl9w2

Seule la compression zstd accepte le paramètre workers :

$ pg_basebackup -Ft \
                --compress=server-zstd:level=9,workers=2 \
                --pgdata stzstl9w2

$ pg_basebackup -Ft \
                --compress server-lz4:level=9,workers=2 \
                --pgdata stlzl9w2

pg_basebackup: error: could not initiate base backup:
+++ERROR:  invalid compression specification: compression algorithm "lz4" does not accept a worker count
pg_basebackup: removing data directory "stlzl9w2"

Si aucun algorithme de compression n’est spécifié et que le niveau de compression est de 0, aucune compression n’est mise en place. Si le niveau de compression est supérieur à zéro, la compression gzip est utilisée avec le niveau spécifié.

Il est possible de spécifier le lieu où sera effectuée la compression en précédant le nom de l’algorithme de compression par client- ou server-. Activer la compression côté serveur permet de réduire le coût en bande passante au prix de l’augmentation de la consommation CPU. La valeur par défaut est client à moins que l’option --target=server... ne soit spécifiée, dans ce cas, la sauvegarde est réalisée sur le serveur de base de données, la compression sera donc réalisée également sur le serveur. La notion de cible est abordée dans un chapitre séparé.

Exemple d’une sauvegarde réalisée côté serveur :

$ pg_basebackup --wal-method fetch \
                --target server:/var/lib/postgres/sauvegarde/sstlz4 \
                --compress server-lz4
$ ls ./sstlz4/

backup_manifest base.tar.lz4

On peut voir que si le format ne peut être spécifié avec une sauvegarde côté serveur, il est forcé à tar.

$ pg_basebackup --wal-method fetch \
                --format p \
                --target server:/var/lib/postgres/sauvegarde/sstlz4 \
                --compress server-lz4

pg_basebackup: error: cannot specify both format and backup target
pg_basebackup: hint: Try "pg_basebackup --help" for more information

La compression des WAL côté serveur n’est pas possible quand -Xstream (ou --wal-method stream) est utilisé. Pour cela, il faut utiliser -Xfetch.

L’exemple ci-dessous montre qu’avec la compression côté serveur et l’option -Xstream, les WAL sont dans un fichier tar non compressé : pg_wal.tar.

$ pg_basebackup -Xstream \
                --format t \
                --compress server-gzip \
                --pgdata ./sctgzs
$ ls ./sctgzs/

backup_manifest base.tar.gz pg_wal.tar

Avec l’option -Xfetch, les WAL sont placés dans le répertoire pg_wal et compressés avec le reste de la sauvegarde.

$ pg_basebackup -Xfetch \
                --format t \
                --compress server-gzip \
                --pgdata ./sctgzf
$ ls ./sctgzf

backup_manifest base.tar.gz

Si la compression est réalisée côté client et que l’option -Xstream est choisie, l’algorithme de compression sélectionné doit être gzip. Dans ce cas, le fichier pg_wal.tar sera compressé en gzip. Si un autre algorithme est choisi, le fichier ne sera pas compressé.

Si le format tar est spécifié (--format=t / Ft) avec gzip, lz4 et zstd, l’extension du fichier de sauvegarde sera respectivement .gz, .lz4 ou .zst.

Dans cet exemple d’une compression avec gzip, on voit que pg_wal.tar est compressé et que l’extension des fichiers compressé est .gz.

$ pg_basebackup -Ft --compress=gzip --pgdata tgzip
$ ls ./tgzip/

backup_manifest base.tar.gz pg_wal.tar.gz

Exemple d’une compression avec lz4, on voit que pg_wal.tar n’est pas compressé et que l’extension des fichiers compressé est .lz4.

$ pg_basebackup -Ft --compress=lz4 --pgdata tlz4 --progress
$ ls ./tlz4/

backup_manifest base.tar.lz4 pg_wal.tar

Exemple d’une compression avec zstd, on voit que pg_wal.tar n’est pas compressé et que l’extension des fichiers compressé est .zst.

$ pg_basebackup -Ft --compress=zstd --pgdata tzstd --progress
$ ls ./tzstd/

backup_manifest base.tar.zst pg_wal.tar

Si le format plain est utilisé (--format=p / -Fp), la compression côté client ne peut pas être choisie. Elle peut en revanche être spécifiée côté serveur. Dans ce cas, le serveur va compresser les données pour le transfert et le client les décompressera ensuite.

$ pg_basebackup -Fp --compress=lz4 --pgdata plz4

pg_basebackup: error: only tar mode backups can be compressed

Dans cet exemple, on voit que la sauvegarde est compressée côté serveur et décompressée sur le client :

$ pg_basebackup -Fp --compress=server-lz4 --pgdata scplz4
$ ls -al scplz4/

total 372
drwx------. 20 postgres postgres   4096 Dec 12 17:49 .
drwxrwxr-x.  6 postgres postgres   4096 Dec 12 17:49 ..
-rw-------.  1 postgres postgres    227 Dec 12 17:49 backup_label
-rw-------.  1 postgres postgres 235587 Dec 12 17:49 backup_manifest
drwx------.  7 postgres postgres   4096 Dec 12 17:49 base
-rw-------.  1 postgres postgres     30 Dec 12 17:49 current_logfiles
drwx------.  2 postgres postgres   4096 Dec 12 17:49 global
drwx------.  2 postgres postgres   4096 Dec 12 17:49 log
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_commit_ts
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_dynshmem
-rw-------.  1 postgres postgres   4789 Dec 12 17:49 pg_hba.conf
-rw-------.  1 postgres postgres   1636 Dec 12 17:49 pg_ident.conf
drwx------.  4 postgres postgres   4096 Dec 12 17:49 pg_logical
drwx------.  4 postgres postgres   4096 Dec 12 17:49 pg_multixact
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_notify
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_replslot
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_serial
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_snapshots
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_stat
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_stat_tmp
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_subtrans
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_tblspc
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_twophase
-rw-------.  1 postgres postgres      3 Dec 12 17:49 PG_VERSION
drwx------.  3 postgres postgres   4096 Dec 12 17:49 pg_wal
drwx------.  2 postgres postgres   4096 Dec 12 17:49 pg_xact
-rw-------.  1 postgres postgres     88 Dec 12 17:49 postgresql.auto.conf
-rw-------.  1 postgres postgres  29665 Dec 12 17:49 postgresql.conf

Le test suivant consiste à sauvegarder une base de 630Mo contenant principalement du jsonb avec les trois algorithmes de compression. Le test est réalisé sur un portable avec 8 CPU, 8 Go de RAM et un disque SSD.

Le tableau suivant montre le volume des sauvegardes (hors WAL -Xnone) par niveau de compression. On peut voir que l’algorithme le plus performant est zstd.

Le tableau suivant montre les temps de sauvegarde par niveau de compression. Pour le mode de compression zstd, le chiffre qui suit correspond au nombre de processus utilisés pour la compression. On voit ici que l’algorithme le plus rapide est lz4. zstd permet d’obtenir de meilleures performances si on augmente le nombre de processus dédiés à la compression.

pg_receivewal

Le dernier outil qui bénéficie des nouveaux algorithmes de compression supportés par PostgreSQL est pg_receivewal. Là aussi, l’option --compression / -Z est utilisée et sa nouvelle spécification est :

-Z level
-Z method[:detail]
--compress=level
--compress=method[:detail]

Le principe est le même que pour pg_basebackup à quelques différences près :

• pg_receivewal compresse forcément les WAL côté client ;
• les algorithmes de compression disponible sont gzip et lz4. Cette évolution permettra donc d’avoir le choix entre taux de compression (gzip) et vitesse de compression (lz4).

La compression par défaut est gzip, les fichiers produits se terminent donc pas l’extension .gz. Le niveau de compression peut être ajouté après la méthode de compression sous forme d’un entier ou avec l’ensemble clé valeur level=nombre entier.

$ pg_receivewal --compress 2
$ pg_receivewal --compress gzip:2
$ pg_receivewal --compress gzip:level=2

Avec les commandes précédentes, on obtient :

0000000100000000000000E6.gz 0000000100000000000000E7.gz.partial

Les fichiers compressés avec lz4 se terminent par .lz4.

$ pg_receivewal --compress lz4:level=1

Avec la commande précédente, on obtient :

total 80
0000000100000000000000E7.lz4 0000000100000000000000E8.lz4.partial

Diverses optimisations ont été apportées pour améliorer les performances de l’outil pg_dump lorsque l’on souhaite exporter un grand nombre d’objets. Avant la version 15, pg_dump lançait une requête pour chaque objet dont il devait exporter les données. Désormais, il ne lance plus qu’une seule requête et c’est une clause WHERE qui permet de se limiter aux seuls objets voulus dans l’export.

Lorsque l’on exporte beaucoup d’objets similaires, il est probable qu’une même requête soit répétée de nombreuses fois, en changeant seulement la valeur des paramètres. C’est pourquoi pg_dump utilise désormais les clauses PREPARE et EXECUTE, afin de ne calculer qu’une seule fois le plan d’execution.

Afin d’éviter l’utilisation d’une sous-requête, qui peut pénaliser les performances lorsqu’un grand nombre d’objets sont exportés, pg_dump récupère désormais les noms de rôles via leurs OIDs. Une autre sous-requête vérifiant le lien de dépendance (pg_depend) entre relations et séquences a été supprimée car cette vérification était redondante avec une autre déjà en place.

L’export parallélisé des tables TOAST bénéficie d’une amélioration de ses performances car l’estimation du volume des tables a été corrigée. Cette estimation ne prenait pas en compte les champs stockés dans les tables TOAST dans le calcul du volume des tables à exporter, ceci pouvait déséquilibrer la répartition de charge des processus lancés en parallèle.

De façon similaire à la vue pg_hba_file_rules, la nouvelle vue système pg_ident_file_mappings donne un résumé du fichier de configuration pg_ident.conf. En plus des informations contenues dans le fichier pg_ident.conf, elle va fournir une colonne error qui va permettre de vérifier le fonctionnement de la configuration avant application ou de diagnostiquer un éventuel problème.

Cette vue n’intervient que sur le contenu actuel du fichier, et non pas sur ce qui a pu être chargé par le serveur. Par défaut elle n’est accessible que pour les super-utilisateurs.

Voici un exemple de ce que peut retourner la vue pg_ident_file_mappings :

postgres=# select * from pg_ident_file_mappings;
 line_number | map_name |         sys_name         | pg_username |            error             
-------------+----------+--------------------------+-------------+------------------------------
          43 | workshop | dalibo                   | test        | 
          44 | mymap    | /^(.*)@mydomain\.com$    | \1          | 
          45 | mymap    | /^(.*)@otherdomain\.com$ | guest       | 
          46 |          |                          |             | missing entry at end of line

On peut remarquer ci-dessus, qu’une erreur est retournée par la vue pg_ident_file_mappings à la ligne 46 du fichier pg_ident.conf : missing entry at end of line.

La vue système pg_stat_subscription_stats permet de récupérer des informations sur les erreurs qui se sont produisent au niveau des souscriptions avec la réplication logique. Ces données sont stockées sous forme de compteur et concernent les erreurs rencontrées lors de l’application des changements ou lors de la synchronisation initiale. Elle contient une ligne par souscription.

Voici la description des colonnes de cette vue :

• subid : OID de la souscription ;
• subname : nom de la souscription ;
• apply_error_count : nombre d’erreurs rencontrées lors de l’application des changements ;
• sync_error_count : nombre d’erreurs rencontrées lors de la synchronisation initiale des tables ;
• stats_reset : date de réinitialisation des statistiques.

La fonction pg_stat_reset_subscription_stats permet de réinitialiser les statistiques de la vue pg_stat_subscription_stats. Elle prend en paramètre soit l’OID d’une souscription pour ne réinitialiser que les statistiques de cette dernière, soit NULL pour appliquer la réinitialisation à toutes les souscriptions.

La variable shared_memory_size renvoie la taille de la mémoire partagée de PostgreSQL. Le résultat est calculé après le chargement des modules complémentaires (shared_preload_libraries). Il tient donc compte des éventuels modules et extensions qui pourraient consommer de la mémoire partagée supplémentaire.

# show shared_memory_size;
 shared_memory_size 
--------------------
 143MB

On obtient quelque chose de similaire en faisant la somme des zones de mémoire partagée allouées avec la vue pg_shmem_allocations :

# select pg_size_pretty(sum(allocated_size)) from pg_shmem_allocations;
 pg_size_pretty 
----------------
 143 MB

La variable shared_memory_size_in_huge_pages va quant à elle indiquer le nombre de Huge Pages nécessaires pour stocker la mémoire partagée de PostgreSQL. Elle est basée sur la valeur de
shared_memory_size vue précédemment et sur la taille des Huge Pages du système. Pour récupérer cette taille, PostgreSQL va en premier lieu regarder si le paramètre huge_page_size apparu en version 14 est défini. Si c’est le cas, il sera utilisé pour le calcul sinon, c’est le paramétrage du système qui sera utilisé (/proc/meminfo).

# show shared_memory_size_in_huge_pages;
 shared_memory_size_in_huge_pages
----------------------------------
 72

Il faut également que PostgreSQL puisse utiliser les Huge Pages. Le paramètre huge_pages doit
donc être défini à on ou try. Si elles ne sont pas utilisables ou si l’on se trouve sur un autre système que linux, shared_memory_size_in_huge_pages retournera -1.

Autre particularité avec ces deux variables, ce sont des variables calculées durant l’exécution (runtime-computed GUC). Dans les versions antérieures, la consultation de ce type de paramètre avec la commande postgres -C renvoyait des valeurs erronées car elle nécessitait le chargement d’éléments complémentaires (ce que ne faisait pas l’ancienne implémentation). La version 15 vient corriger ce problème et permet d’obtenir des valeurs correctes pour ces paramètres. Seule restriction, les paramètres runtime-computed GUC ne sont consultables avec postgres -C que lorsque l’instance est arrêtée.

postgres -C shared_memory_size -D $PGDATA
postgres -C shared_memory_size_in_huge_pages -D $PGDATA

On peut donc dorénavant savoir combien de mémoire partagée et de Huge Pages le système à besoin avant de démarrer une instance PostgreSQL.

Lorsqu’un UPDATE sur une table partitionnée référencée par une contrainte de clé étrangère provoque la migration d’une ligne vers une autre partition, l’opération est implémentée sous la forme d’un DELETE sur la partition source, suivi d’un INSERT sur la partition cible.

Sur les versions précédentes, cela pose un souci lorsque la contrainte de clé étrangère implémente la clause ON DELETE. En effet, dans ce cas, le changement de partition provoque le déclenchement de l’action associée à la commande DELETE, par exemple : une suppression. C’est une erreur puisqu’en réalité la ligne est juste déplacée vers une autre partition.

En version 15, le trigger posé par la contrainte de clé étrangère ne se déclenche plus sur le DELETE exécuté sur la partition, mais sur un UPDATE exécuté sur la table mère. Cela permet d’obtenir le comportement attendu.

L’implémentation choisie à une limitation : elle ne fonctionne que si la contrainte de clé étrangère concerne la table partitionnée. Cela ne devrait pas être un facteur limitant, en effet, il est rare d’avoir des clés étrangères différentes qui pointent vers les différentes partitions. On trouve généralement plutôt une clé étrangère qui pointe vers une ou plusieurs colonnes de la table partitionnée dans son ensemble.

Voici un exemple du comportement en version 14 puis 15.

Mise en place :

CREATE TABLE tpart (i int PRIMARY KEY, t text) PARTITION BY RANGE ( i );
CREATE TABLE tpart_1_10 PARTITION OF tpart FOR VALUES FROM (1) TO (10);
CREATE TABLE tpart_11_20 PARTITION OF tpart FOR VALUES FROM (11) TO (20);
CREATE TABLE foreignk(j int PRIMARY KEY, i int CONSTRAINT fk_tpart_i REFERENCES tpart(i) ON DELETE CASCADE, t text );
INSERT INTO tpart VALUES (1, 'value 1');
INSERT INTO foreignk VALUES (1, 1, 'fk 1');

Voici les données présentes dans les tables :

SELECT *, tableoid::regclass FROM tpart;

 i |    t    |  tableoid  
---+---------+------------
 1 | value 1 | tpart_1_10
(1 row)

SELECT * FROM foreignk ;

 j | i |  t   
---+---+------
 1 | 1 | fk 1
(1 row)

Mise à jour et nouveaux contrôles en version 14 :

UPDATE tpart SET i = 11 WHERE i = 1;
SELECT *, tableoid::regclass FROM tpart;

 i  |    t    |  tableoid
----+---------+-------------
 11 | value 1 | tpart_11_20
(1 row)

SELECT * FROM foreignk ;

j | i | t
---+---+---
(0 rows)

La ligne a bien changé de partition, en revanche elle a été supprimée de la table qui référence la table partitionnée.

Avec PostgreSQL 15, on obtient désormais l’erreur suivante :

ERROR:  update or delete on table "tpart" violates foreign key constraint "fk_tpart_i" on table "foreignk"
DETAIL:  Key (i)=(1) is still referenced from table "foreignk".

log_checkpoints

Le paramètre log_checkpoints est désormais à on par défaut, chaque CHECKPOINT sera par conséquent journalisé dans les traces de l’instance.

Les traces générées par ce paramètre contiennent des informations sur la durée des CHECKPOINT et sur les écritures effectuées :

2022-07-15 09:40:01.393 UTC [4198] LOG:  checkpoint starting: wal
2022-07-15 09:42:16.273 UTC [4198] LOG:  checkpoint complete: wrote 67 buffers (0.4%); 
   0 WAL file(s) added, 0 removed, 134 recycled; 
   write=134.352 s, sync=0.001 s, total=134.880 s; sync files=9, longest=0.001 s, average=0.001 s; 
   distance=2192214 kB, estimate=2193764 kB
2022-07-15 09:42:29.121 UTC [4198] LOG:  checkpoint starting: wal
2022-07-15 09:43:56.646 UTC [4198] LOG:  checkpoint complete: wrote 81 buffers (0.5%); 
   0 WAL file(s) added, 0 removed, 134 recycled; 
   write=86.438 s, sync=0.026 s, total=87.525 s; sync files=8, longest=0.012 s, average=0.004 s;
   distance=2198655 kB, estimate=2198655 kB
2022-07-15 09:43:58.331 UTC [4198] LOG:  checkpoint starting: wal
2022-07-15 09:45:34.024 UTC [4198] LOG:  checkpoint complete: wrote 29 buffers (0.2%); 
   0 WAL file(s) added, 0 removed, 134 recycled;
   write=94.874 s, sync=0.028 s, total=95.693 s; sync files=9, longest=0.016 s, average=0.004 s; 
   distance=2192128 kB, estimate=2198003 kB

log_autovacuum_min_duration

Le paramètre log_autovacuum_min_duration est désormais configuré à 10 minutes. Cela signifie que chaque opération d’autovacuum qui dépasse ce délai sera tracée.

Les traces générées par ce paramètre permettent d’obtenir un rapport détaillé sur les opérations de VACUUM et ANALYZE exécutées par l’autovacuum :

2022-07-15 09:53:05.049 UTC [6563] LOG:  automatic vacuum of table "postgres.public.db_activity": index scans: 0
    pages: 0 removed, 108334 remain, 75001 scanned (69.23% of total)
    tuples: 0 removed, 9926694 remain, 2591536 are dead but not yet removable
    removable cutoff: 1024, which was 2 XIDs old when operation ended
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 62.039 MB/s, avg write rate: 16.120 MB/s
    buffer usage: 91282 hits, 58777 misses, 15272 dirtied
    WAL usage: 1 records, 1 full page images, 2693 bytes
    system usage: CPU: user: 1.34 s, system: 0.22 s, elapsed: 7.40 s
2022-07-15 09:53:08.658 UTC [6563] LOG:  automatic analyze of table "postgres.public.db_activity"
    avg read rate: 55.129 MB/s, avg write rate: 27.167 MB/s
    buffer usage: 4746 hits, 25467 misses, 12550 dirtied
    system usage: CPU: user: 0.37 s, system: 0.15 s, elapsed: 3.60 s

Le paramètre log_destination se voit enrichi d’une nouvelle option jsonlog qui permet d’obtenir une journalisation au format JSON.

postgres=# show log_destination ;
 log_destination 
-----------------
 jsonlog

Le fichier de log produit aura alors l’extension .json :

postgres=# SELECT pg_current_logfile();
   pg_current_logfile    
-------------------------
 log/postgresql-Fri.json
(1 row)

Voici un exemple d’une ligne de trace, la première générée au démarrage de l’instance :

{
  "timestamp": "2022-07-26 10:26:36.370 UTC",
  "pid": 3330,
  "session_id": "62dfc15c.d02",
  "line_num": 2,
  "session_start": "2022-07-26 10:26:36 UTC",
  "txid": 0,
  "error_severity": "LOG",
  "message": "starting PostgreSQL 15beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit",
  "backend_type": "postmaster",
  "query_id": 0
}

Le format JSON peut s’avérer utile pour alimenter les traces de l’instance dans un autre programme. pgBadger supporte déjà l’analyse de traces dans ce format, car il supportait auparavant l’extension jsonlog qui ajoutait cette fonctionnalité avant qu’elle soit intégrée en standard dans PostgreSQL.

Par ailleurs, l’utilisation de l’outil jq permet de rechercher des clés spécifiques dans les traces, par exemple pour n’afficher que les erreurs :

[postgres@pg1 log]$ jq 'select(.error_severity == "ERROR" )' postgresql-Tue.json 
{
  "timestamp": "2022-07-26 10:45:16.563 UTC",
  "user": "postgres",
  "dbname": "postgres",
  "pid": 3361,
  "remote_host": "[local]",
  "session_id": "62dfc250.d21",
  "line_num": 1,
  "ps": "INSERT",
  "session_start": "2022-07-26 10:30:40 UTC",
  "vxid": "3/20",
  "txid": 0,
  "error_severity": "ERROR",
  "state_code": "42P01",
  "message": "relation \"t2\" does not exist",
  "statement": "insert into t2 values ('missing_table_test');",
  "cursor_position": 13,
  "application_name": "psql",
  "backend_type": "client backend",
  "query_id": 0
}

Les données peuvent également être chargées dans une table. Il n’est pas possible d’utiliser COPY directement pour cela car les caractère d’échappement disparaissent.

postgres=# CREATE TABLE pglog( data jsonb);
CREATE TABLE
postgres=# COPY pglog FROM PROGRAM 'sed ''s/\\/\\\\/g'' log/postgresql-Fri.json';
COPY 52
postgres=# SELECT data->>'timestamp' AS starttime FROM pglog WHERE data ->> 'message' LIKE 'starting%';
          starttime           
------------------------------
 2022-08-19 16:47:48.412 CEST
(1 row)

Le code de la commande VACUUM a été simplifié et optimisé. La nouvelle version permet de collecter plus d’informations sur l’exécution de l’opération de maintenance. Par conséquent, la sortie de la commande VACUUM VERBOSE est encore plus verbeuse dans PostgreSQL 15. En voici un exemple :

postgres=# VACUUM VERBOSE T1;
INFO:  vacuuming "postgres.public.t1"
INFO:  table "t1": truncated 1 to 0 pages
INFO:  finished vacuuming "postgres.public.t1": index scans: 0
pages: 1 removed, 0 remain, 1 scanned (100.00% of total)
tuples: 5 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 1 XIDs old when operation ended
new relfrozenxid: 747, which is 6 XIDs ahead of previous value
index scan not needed: 1 pages from table (100.00% of total) had 5 dead item identifiers removed
avg read rate: 2.637 MB/s, avg write rate: 4.394 MB/s
buffer usage: 7 hits, 3 misses, 5 dirtied
WAL usage: 6 records, 2 full page images, 9339 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

La commande affiche un rapport détaillé de l’exécution, on y voit apparaître :

• le nouveau relfrozenxid après l’opération
• des informations sur l’utilisation des buffers
• des informations sur le nettoyage effectué sur les index de la table
• des métriques sur les performances du VACUUM : avg read rate et avg write rate.

La même commande en version 14 affichait un rapport moins complet:

postgres=# VACUUM VERBOSE T1;
INFO:  vacuuming "public.t1"
INFO:  table "t1": removed 5 dead item identifiers in 1 pages
INFO:  table "t1": found 5 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 745
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  table "t1": truncated 1 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM