DEV Community: francisadp

PostgreSQL 14 et Authentification SCRAM. Dois-je migrer vers SCRAM?

francisadp — Wed, 16 Mar 2022 10:38:46 +0000

Notre blog lesamisdepercona.fr est dedié à la base de donnée open source MySQL, PostgreSQL, MongoDB et les Produits Percona

Récemment, quelques utilisateurs de PostgreSQL ont signalé avoir eu des échecs de connexion après avoir passés à PostgreSQL 14.

“ Pourquoi est-ce que j'obtiens l'erreur **FATAL : l'authentification du mot de passe a échoué pour un utilisateur* dans le nouveau serveur ?* » est devenue l'une des questions les plus intrigantes.

Au moins dans un cas, c'était un peu surprenant que le message d'application soit le suivant :

FATAL : La connexion à la base de données a échoué : la connexion au serveur sur «localhost» (::1 ), le port 5432 a échoué : fe_sendauth : aucun mot de passe fourni

La raison de ces erreurs est que les valeurs par défaut pour le chiffrement du mot de passe sont modifiées dans les nouvelles versions de PostgreSQL vers l'authentification SCRAM. Même si le dernier ne semble rien directement lié à SCRAM, oh oui, un script de post-installation a échoué qui cherchait "md5".

L'authentification SCRAM n'est pas quelque chose de nouveau dans PostgreSQL . Il était là depuis PostgreSQL 10 mais n'a jamais affecté DBA en général car cela n'a jamais été la valeur par défaut. Il s'agissait d'une fonctionnalité opt-in en modifiant explicitement les paramètres par défaut. Ceux qui font un opt-in comprennent généralement et le font intentionnellement, et cela n'a jamais causé de problème. La communauté PostgreSQL était réticente à en faire une méthode de choix pendant des années car de nombreuses bibliothèques client/application n'étaient pas prêtes pour l'authentification SCRAM.

Mais cela change dans PostgreSQL 14. Avec PostgreSQL 9.6 qui n'est plus pris en charge, le paysage change. Maintenant, nous nous attendons à ce que toutes les anciennes bibliothèques clientes soient mises à niveau et l'authentification SCRAM devient la principale méthode d'authentification par mot de passe. Mais, ceux qui l'ignorent complètement vont être accueillis par une surprise un jour ou l'autre. Le but de cet article est de créer une prise de conscience rapide pour ceux qui ne le sont pas encore et de répondre à certaines des questions fréquemment posées.

Qu'est-ce que l'authentification SCRAM ?

En termes simples, la base de données le client et le serveur se prouvent et se convainquent qu'ils connaissent le mot de passe sans échanger le mot de passe ou le hashage du mot de passe . Oui, c'est possible en faisant un défi et des réponses salés, SCRAM-SHA-256, comme spécifié par RFC 7677 . Cette façon de stocker, de communiquer et de vérifier les mots de passe rend très difficile la rupture d'un mot de passe.

Cette méthode est plus résistante à :

dictionnaire attaques
Rejouer attaques
Hashes volés

Dans l'ensemble, il devient très difficile de casser une authentification par mot de passe.

Qu'est-ce qui a changé avec le temps ?

Liaison de canal

L'authentification n'est qu'une partie de la communication sécurisée. Après l'authentification, un serveur malveillant au milieu peut potentiellement prendre le relais et tromper la connexion client. PostgreSQL 11 a introduit SCRAM-SHA-256-PLUS qui prend en charge la liaison de canal. C'est pour s'assurer qu'il n'y a pas de serveur malveillant agissant comme un vrai serveur OU faisant une attaque de l'homme du milieu.

À partir de PostgreSQL 13, un client peut demander et même insister sur la liaison de canal.

Par exemple :

psql -U postgres -h c76pri channel_binding=prefer
or
psql -U postgres -h c76pri channel_binding=require

La liaison de canal fonctionne sur SSL/TLS, la configuration SSL/TLS est donc obligatoire pour que la liaison de canal fonctionne.

Définition du mot de passe de cryptage

Le md5 était la seule option disponible pour le chiffrement de mot de passe avant PostgreSQL 10, donc PostgreSQL permet aux paramètres d'indiquer que "le chiffrement de mot de passe est requis" qui est par défaut à md5.

–Upto PG 13
postgres=# set password_encryption TO ON;
SET

Pour la même raison, la déclaration ci-dessus était effectivement la même que :

postgres=# set password_encryption TO MD5;
SET

Nous pourrions même utiliser "vrai", "1", "oui" au lieu de "on" comme valeur équivalente.

Mais maintenant, nous avons plusieurs méthodes de cryptage et "ON" ne transmet pas vraiment ce que nous voulons vraiment. Ainsi, à partir de PostgreSQL 14, le système s'attend à ce que nous spécifions la méthode de chiffrement.

postgres=# set password_encryption TO 'scram-sha-256';
SET

postgres=# set password_encryption TO 'md5';
SET

Toute tentative d'utiliser "on"/"true", "yes" sera rejetée avec une erreur.

–-From PG 14
postgres=# set password_encryption TO 'on';
ERROR:  invalid value for parameter "password_encryption": "on"
HINT:  Available values: md5, scram-sha-256.

Veuillez donc vérifier vos scripts et vous assurer qu'ils n'ont pas l'ancienne méthode "d'activation" du chiffrement.

Quelque Question Fréquemment posées

Ma sauvegarde et ma restauration logiques sont-elles affectées ? Sauvegarde et restauration logique de PostgreSQL globals ( pg_dumpall ) n'affectera pas l'authentification SCRAM, le même mot de passe devrait fonctionner après la restauration. En fait, il sera intéressant de rappeler que l'authentification SCRAM est plus résistante aux changements. Par exemple, si nous renommons un USER, l'ancien mot de passe MD5 ne fonctionnera plus, car la façon dont PostgreSQL génère le MD5 utilise également le nom d'utilisateur.

postgres =# ALTER USER jobin1 RENOMMER EN jobin ; AVIS : Le mot de passe MD5 a été effacé en raison du rôle renameALTER ROLE

Comme le NOTICE l'indique, le hashage du mot de passe dans pg_authid sera effacé car l'ancien n'est plus valide. Mais ce ne sera pas le cas avec l'authentification SCRAM, car nous pouvons renommer les utilisateurs sans affecter le mot de passe.

postgres =# ALTER USER jobin RENOMMER EN jobin1 ; MODIFIER LE RÔLE

La méthode de cryptage existante/ancienne (md5) était une grande vulnérabilité. Y avait-il un gros risque ?
Cette inquiétude vient principalement du nom "MD5" qui est bien trop idiot pour le matériel moderne. La façon dont PostgreSQL utilise md5 est différente, ce n'est pas seulement le hashage du mot de passe, mais il considère également le nom d'utilisateur. De plus, il est communiqué sur le réseau après avoir préparé un hashage avec un salt aléatoire fourni par le serveur. Effectivement ce qui est communiqué sera différent du hashage du mot de passe, il n'est donc pas trop vulnérable. Mais sujet aux attaques par dictionnaire et aux fuites de problèmes de hashage du mot de passe du nom d'utilisateur.
Les nouvelles annonces d'authentification scram sont-elles complexes à authentifier ? Est-ce que ma demande de connexion va prendre plus de temps ?
Le protocole filaire SCRAM est très efficace et n'est pas connu pour provoquer de dégradation du temps de connexion. De plus, par rapport aux autres surcoûts de la gestion des connexions côté serveur, le surcoût créé par SCRAM devient très négligeable
Est-il obligatoire d'utiliser l'authentification SCRAM depuis PostgreSQL 14 et de forcer tous mes comptes utilisateurs à y basculer ?
Absolument pas, seules les valeurs par défaut sont modifiées. L'ancienne méthode md5 est toujours une méthode valide qui fonctionne très bien, et si l'accès à l'environnement PostgreSQL est restreint par des règles de pare-feu/ hba , il y a déjà moins de risques à utiliser md5.
Pourquoi est-ce que j'obtiens l'erreur « : FATAL : l'authentification du mot de passe a échoué pour l'utilisateur » lorsque je suis passé à PostgreSQL 14 ?
La raison la plus probable est les entrées pg_hba.conf . Si nous spécifions "md5" comme méthode d'authentification, PostgreSQL autorisera également l'authentification SCRAM. Mais l'inverse ne fonctionnera pas. Lorsque vous avez créé l'environnement PostgreSQL 14, il peut très probablement avoir "scram-sha-256" comme méthode d'authentification. Dans certains des packages PostgreSQL, le script d'installation le fait automatiquement pour vous. Dans le cas où l'authentification fonctionne à partir des outils client PostgreSQL et non à partir de l'application, veuillez vérifier la version du pilote et vérifier la portée de la mise à niveau
Pourquoi ai-je d'autres types d'erreurs d'authentification ?
Les raisons les plus probables sont les scripts de post-installation. Il est courant dans de nombreuses organisations d'utiliser des outils DevOps ( Ansible /Chef) ou même des scripts shell pour effectuer les personnalisations post-installation. Beaucoup d'entre eux feront une gamme de choses qui impliquent des étapes telles que set password_encryption TO ON; ou même la modification de pg_hba.conf en utilisant sed , qui devrait échouer s'il essaie de modifier une entrée qui n'y est plus.

Pourquoi devrais-je m'en soucier et que faire

Tout ce qui commence par les scripts d'automatisation/déploiement, les outils, les connexions d'application et les poolers de connexion peut potentiellement se casser. L'un des principaux arguments pour retarder ce changement jusqu'à PostgreSQL 14 est que la version la plus ancienne prise en charge (9.6) ne sera bientôt plus prise en charge. C'est donc le bon moment pour inspecter vos environnements pour voir si l'un de ces environnements possède d'anciennes bibliothèques PostgreSQL (9.6 ou antérieures) et avoir un plan pour la mise à niveau, car les bibliothèques PostgreSQL de l'ancienne version ne peuvent pas gérer les négociations SCRAM.

En résumé, avoir un bon plan pour migrer aidera, même si ce n'est pas urgent.

Inspectez les environnements et les pilotes d'application pour voir si l'un d'entre eux utilise encore d'anciennes versions des bibliothèques client PostgreSQL et mettez-les à niveau si nécessaire. Veuillez vous référer à : https://wiki.postgresql.org/wiki/List_of_drivers Encourager / Piloter la mise à niveau des bibliothèques clientes avec un calendrier
Si l'environnement existant utilise md5, encouragez les utilisateurs à passer à l'authentification SCRAM . N'oubliez pas que la méthode d'authentification mentionnée comme "md5" dans pg_hba.conf continuera à fonctionner pour l'authentification SCRAM et MD5 dans PostgreSQL 14 également.
Profitez de chaque occasion pour tester et migrer l'automatisation, les pooleurs de connexions et d'autres infrastructures vers l'authentification SCRAM.

En modifiant l'authentification par défaut, la communauté PostgreSQL montre une direction claire pour l'avenir.

Percona Distribution for PostgreSQL fournit les meilleurs composants entreprise de la communauté open source, dans une distribution unique, conçue et testée pour fonctionner ensemble.

Téléchargez Percona Distribution pour PostgreSQL

Source en anglais: Percona

Comparaison entre mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

francisadp — Wed, 09 Mar 2022 09:58:48 +0000

Dans cet article de blog, nous comparerons les performances d’une sauvegarde à partir d’une base de données MySQL à l’aide de mysqldump , de la fonctionnalité MySQL Shell appelée Instance Dump , mysqlpump , mydumper et Percona XtraBackup . Toutes ces options disponibles sont open source et gratuites pour toute la communauté.

Pour commencer, voyons les résultats du test.

Résultats de Benchmark

Le benchmark a été exécuté sur une instance m5dn.8xlarge , avec 128 Go de RAM, 32 vCPU et 2 disques NVMe de 600 Go (un pour la sauvegarde et l’autre pour les données MySQL ). La version MySQL était 8.0.26 et configurée avec 89 Go de pool de mémoire tampon, 20 Go de journal de rétablissement et une base de données exemple de 177 Go (plus de détails ci-dessous).

Nous pouvons observer les résultats dans le tableau ci-dessous :

Et si nous analysons le graphique uniquement pour les options multi-thread:

Comme nous pouvons le voir, pour chaque logiciel, j'ai exécuté chaque commande trois fois afin d'expérimenter en utilisant 16, 32 et 64 threads. L'exception à cela est mysqldump, qui n'a pas d'option parallèle et ne s'exécute qu'en mode monothread.

Nous pouvons observer des résultats intéressants :

Lors de l'utilisation de la compression zstd , mydumper brille vraiment en termes de performances. Cette option était ajouté il n'y a pas longtemps ( MyDumper 0.11.3 ).
Lorsque mydumper utilise gzip , MySQL Shell est l'option de sauvegarde la plus rapide.
En 3ème nous avons Percona XtraBackup .
mysqlpump est le 4ème plus rapide suivi de plus près par mydumper lors de l'utilisation de gzip .
mysqldump est le style classique de la vieille école pour effectuer des vidages et est le plus lent des quatre outils.
Dans un serveur avec plus de processeurs, le parallélisme potentiel augmente, donnant encore plus d'avantages aux outils qui peuvent bénéficier de plusieurs threads.

Spécifications matérielles et logicielles

Voici les spécifications du benchmark :

32 CPUs
128 Go de mémoire
2x NVMe disques 600 Go
Centos 7.9
MySQL 8.0.26
MySQL Shell 8.0.26
mydumper 0.11.5 – gzip
mydumper 0.11.5 – zstd
Xtrabackup 8.0.26

La configuration my.cnf :

[mysqld]
innodb_buffer_pool_size = 89G
innodb_log_file_size = 10G

Test de performance

Pour le test, j'ai utilisé sysbench pour remplir MySQL. Pour charger les données, je choisis la méthode tpcc :

$ ./tpcc.lua  --mysql-user=sysbench --mysql-password='sysbench' --mysql-db=percona --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepare

Avant de commencer la comparaison, j'ai exécuté mysqldump une fois et j'ai ignoré les résultats pour réchauffer le cache, sinon notre test serait biaisé car la première sauvegarde devrait récupérer les données du disque et non du cache.

Avec tout défini, j'ai démarré le mysqldump avec les options suivantes :

$ time mysqldump --all-databases --max-allowed-packet=4294967295 --single-transaction -R --master-data=2 --flush-logs | gzip > /backup/dump.dmp.gz

Pour l'utilitaire Shell:

$ mysqlsh
MySQL JS > shell.connect('root@localhost:3306');
MySQL localhost:3306 ssl test JS > util.dumpInstance("/backup", {ocimds: true, compatibility: ["strip_restricted_grants","ignore_missing_pks"],threads: 16})

Pour mydumper :

$ time mydumper  --threads=16  --trx-consistency-only --events --routines --triggers --compress --outputdir /backup/ --logfile /backup/log.out --verbose=2

PS : Pour utiliser zstd , il n'y a aucun changement dans la ligne de commande, mais vous devez télécharger les zstd binaires .

Pour mysqlpump :

$ time mysqlpump --default-parallelism=16 --all-databases > backup.out

Pour xtrabackup:

$ time xtrabackup --backup --parallel=16 --compress --compress-threads=16 --datadir=/mysql_data/ --target-dir=/backup/

Analyse des résultats

Et que nous disent les résultats ?

Les méthodes parallèles ont un débit de performances similaire. L' outil mydumper a réduit le temps d'exécution de 50 % lors de l'utilisation de zstd au lieu de gzip , de sorte que la méthode de compression fait une grande différence lors de l'utilisation de mydumper .

Pour l’utilitaire util.dumpInstance, l'un des avantages est que l'outil stocke les données au format binaire et texte et utilise la compression zstd par défaut. Comme mydumper, il utilise plusieurs fichiers pour stocker les données et a un bon taux de compression.

XtraBackup a obtenu la troisième place avec quelques secondes de différence par rapport au MySQL shell. Le principal avantage de XtraBackup est sa flexibilité, fournissant le PITR et le chiffrement par exemple.

Ensuite, mysqlpump est plus efficace que mydumper avec gzip , mais seulement par une petite marge. Les deux sont des méthodes de sauvegarde logiques et fonctionnent de la même manière. J'ai testé mysqlpump avec la compression zstd, mais les résultats étaient les mêmes, d'où la raison pour laquelle je ne l'ai pas ajouté au tableau. Une possibilité est que mysqlpump diffuse les données dans un seul fichier.

Enfin, pour mysqldump, nous pouvons dire qu'il a le comportement le plus prévisible et a des temps d'exécution similaires avec des exécutions différentes. Le manque de parallélisme et de compression est un inconvénient pour mysqldump ; cependant, comme il était présent dans les premières versions de MySQL, basé sur les cas Percona, il est toujours utilisé comme méthode de sauvegarde logique.

Veuillez laisser dans les commentaires ci-dessous ce que vous avez pensé de cet article de blog, si j'ai raté quelque chose ou si cela vous a aidé. Je serai ravi d'en discuter !

Ressources utiles

Enfin, vous pouvez nous joindre via les réseaux sociaux, notre forum, ou accéder à notre matériel en utilisant les liens présentés ci-dessous :

Dois-je créer un index sur les clés étrangères dans PostgreSQL?

francisadp — Thu, 24 Feb 2022 20:52:32 +0000

Bienvenue sur un blog hebdomadaire où je peux répondre (comme, vraiment répondre) à certaines des questions que j'ai vues dans les webinaires que j'ai présentés récemment. Si vous avez manqué le dernier, PostgreSQL Performance Tuning Secrets , il peut être utile d'en écouter une partie avant ou après avoir lu cet article. Chaque semaine, je vais plonger profondément dans une question. Faites-moi savoir ce que vous pensez dans les commentaires.

Nous entendons constamment dire que les index améliorent les performances de lecture et c'est généralement vrai, mais nous savons également que cela aura toujours un impact sur les écritures. Ce dont nous n'entendons pas parler trop souvent, c'est que dans certains cas, cela peut ne donner aucune amélioration des performances. Cela se produit plus que nous ne le souhaitons et peut se produire plus que nous ne le remarquons, et les clés étrangères (FK) en sont un excellent exemple. Je ne dis pas que tous les index de FK sont mauvais, mais la plupart de ceux que j'ai vus sont tout simplement inutiles, ne faisant qu'ajouter de la charge au système.

Par exemple, la relation ci-dessous où nous avons une relation 1 :N entre la table « Supplier» et la table « Product» :

Si nous prêtons une attention particulière aux FK dans cet exemple, il n'y aura pas un nombre élevé de recherches sur la table enfant en utilisant la colonne FK, " SupplierID " dans cet exemple, si nous comparons avec le nombre de recherches en utilisant " ProductID " et probablement " ProductName ". L'utilisation principale sera de maintenir la cohérence de la relation et de rechercher dans l'autre sens, en trouvant le fournisseur d'un certain produit. Dans ce cas, l'ajout d'un index à l'enfant FK sans s'assurer que le modèle d'accès l'exige ajoutera le coût supplémentaire de la mise à jour de l'index chaque fois que nous mettons à jour la table « Product ».

Un autre point auquel nous devons prêter attention est la cardinalité de l'index

Si la cardinalité de l'index est trop faible, Postgres ne l'utilisera pas et l'index sera simplement ignoré. On peut se demander pourquoi cela se produit et si cela ne serait pas encore moins cher pour la base de données de parcourir, par exemple, la moitié des index au lieu de faire une analyse complète de la table? La réponse est non, en particulier pour les bases de données qui utilisent des tables de tas comme Postgres. L'accès à la table dans Postgres est principalement séquentiel, ce qui est plus rapide que l'accès aléatoire dans les disques durs en rotation et encore un peu plus rapide sur les disques SSD, tandis que l'accès à l'index b+-tree est aléatoire par nature.

Lorsque Postgres utilise un index, il doit ouvrir le fichier d'index, trouver les enregistrements dont il a besoin, puis ouvrir le fichier de table, effectuer une recherche à l'aide des adresses de page obtenues à partir des index, en modifiant le modèle d'accès de séquentiel à aléatoire et en fonction des données distribution, il accédera probablement à la majorité des pages de la table, se terminant par une analyse complète de la table mais utilisant maintenant un accès aléatoire, ce qui est beaucoup plus coûteux. Si nous avons des colonnes avec une faible cardinalité et que nous avons vraiment besoin de les indexer, nous devons utiliser une alternative aux index b-tree, par exemple, un index GIN, mais c'est un sujet pour une autre discussion.

Avec tout cela, on peut penser que les index FK sont toujours mauvais et ne jamais les utiliser sur une table enfant, n'est-ce pas?

Eh bien, ce n'est pas vrai non plus et il y a de nombreuses circonstances où ils sont utiles et nécessaires, par exemple, l'image ci-dessous a deux autres tableaux, " Customer" et " Order" :

Il peut être pratique d'avoir un index sur la table enfant " Order-> CustomerId " car il est courant d'afficher toutes les commandes d'un certain utilisateur et la colonne "CustomerId " de la table "Order" sera utilisée assez fréquemment comme clé de recherche .

Un autre bon exemple est de fournir une méthode plus rapide pour valider l'intégrité référentielle. Si l'on a besoin de changer la table parent (mettre à jour ou supprimer une clé parent), les enfants doivent être vérifiés pour s'assurer que la relation n'est pas rompue. Dans ce cas, avoir un index du côté de l'enfant aiderait à améliorer les performances. Dans ce cas, l’index est utile mais "dépendant de la charge". Si la clé parent a de nombreuses suppressions, cela peut être envisageable, cependant, s'il s'agit d'une table principalement statique ou ayant principalement des insertions ou des mises à jour dans les autres colonnes autres que la colonne de clé parent, ce n'est pas un bon candidat pour avoir un index sur les tables enfants.

Il existe de nombreux autres exemples qui peuvent être donnés pour expliquer pourquoi un index sur la table enfant peut être utile et vaut le coût/la pénalité d'écriture supplémentaire.

Conclusion

Le point à retenir ici est que nous ne devons pas créer d'index sans distinction sur tous les FK, car beaucoup d'entre eux ne seront tout simplement pas utilisés ou si rarement utilisés qu'ils n'en valent pas la peine. Il est préférable de concevoir initialement la base de données avec les FK mais pas les index et de les ajouter pendant que la base de données se développe et que nous comprenons la charge de travail. Il est possible qu'à un moment donné, nous trouvions que nous ayons besoin d'un index sur « Product-> SupplierId » en raison de notre charge de travail et que l'index sur « Order-> CustomerId » ne soit plus nécessaire. Les charges changent et la distribution des données également, l'index doit les suivre et ne pas être traité comme des entités immuables.

Source : Percona

Base de données PostgreSQL 14 : Surveillance et Enregistrement

francisadp — Wed, 23 Feb 2022 13:03:19 +0000

Veuillez vous rendre sur notre blog pour d'autres articles sur les bases de données opensource et en particulier MySQL, PostgreSQL, MongoDB

PostgreSQL-14 a été publié en septembre 2021 et contenait de nombreuses améliorations des performances et des fonctionnalités, y compris certaines fonctionnalités du point de vue surveillance. Comme nous le savons, la surveillance est l'élément clé de tout système de gestion de base de données, et PostgreSQL continue de mettre à jour et d'améliorer les capacités de surveillance. Voici quelques clés dans PostgreSQL-14.

Identificateur de requête

L'identificateur de requête est utilisé pour identifier la requête, qui peut être référencée entre les extensions. Avant PostgreSQL-14, les extensions utilisaient un algorithme pour calculer le query_id . Habituellement, le même algorithme est utilisé pour calculer le query_id , mais toute extension peut utiliser son propre algorithme. Désormais, PostgreSQL-14 fournit éventuellement un query_id à calculer dans le noyau. Maintenant, les extensions de surveillance et les utilitaires de PostgreSQL-14 comme pg_stat_activity , explain, et pg_stat_statments utilisent ce query_id au lieu de calculer le sien. Ce query_id peut être vu dans csvlog , après avoir spécifié dans le log_line_prefix . Du point de vue de l'utilisateur, cette fonctionnalité présente deux avantages:

Tous les utilitaires/extensions utiliseront le même query_id calculé par core, ce qui permet de référencer facilement ce query_id . Auparavant, tous les utilitaires/extensions devaient utiliser le même algorithme dans leur code pour obtenir cette capacité.
Le deuxième avantage est que l'extension/les utilitaires peuvent utiliser le query_id calculé et n'ont plus besoin de le faire, ce qui est un avantage en termes de performances.

PostgreSQL introduit un nouveau paramètre de configuration GUC compute_query_id pour activer/désactiver cette fonctionnalité. La valeur par défaut est automatique; cela peut être activé/désactivé dans le fichier postgresql.conf ou à l'aide de la commande SET.

pg_stat_activity

SET compute_query_id = OFF;

SELECT datname, query, query_id FROM pg_stat_activity;
 datname  |                                 query                                 | query_id 
----------+-----------------------------------------------------------------------+----------
 postgres | select datname, query, query_id from pg_stat_activity;                |         
 postgres | UPDATE pgbench_branches SET bbalance = bbalance + 2361 WHERE bid = 1; |

SET compute_query_id = on;

SELECT datname, query, query_id FROM pg_stat_activity;
 datname  |                                 query                                 |      query_id       
----------+-----------------------------------------------------------------------+---------------------
 postgres | select datname, query, query_id from pg_stat_activity;                |  846165942585941982
 postgres | UPDATE pgbench_tellers SET tbalance = tbalance + 3001 WHERE tid = 44; | 3354982309855590749

Log

Dans les versions précédentes, il n'y avait aucun mécanisme pour calculer le query_id dans le noyau du serveur. Le query_id est particulièrement utile dans les fichiers journaux. Pour activer cela, nous devons configurer le paramètre de configuration log_line_prefix . L'option "%Q" est ajoutée pour afficher le query_id; voici un exemple.

log_line_prefix = 'query_id = [%Q] -> '

query_id = [0] -> LOG:  statement: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
query_id = [-6788509697256188685] -> ERROR:  return type mismatch in function declared to return record
query_id = [-6788509697256188685] -> DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
query_id = [-6788509697256188685] -> CONTEXT:  SQL function "ptestx"
query_id = [-6788509697256188685] -> STATEMENT:  CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;

Explain

EXPLAIN VERBOSE affichera le query_id si compute_query_id est vrai.

SET compute_query_id = OFF;

EXPLAIN VERBOSE SELECT * FROM foo;
                          QUERY PLAN                          
--------------------------------------------------------------

 Seq Scan on public.foo  (cost=0.00..15.01 rows=1001 width=4)
   Output: a
(2 rows)

SET compute_query_id = on;

EXPLAIN VERBOSE SELECT * FROM foo;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on public.foo  (cost=0.00..15.01 rows=1001 width=4)
   Output: a
 Query Identifier: 3480779799680626233
(3 rows)

Améliorations de l’Autovacuum et enregistrement auto-analyse

PostgreSQL-14 améliore l’enregistrement de l’auto-vacuum et auto-analyze. Nous pouvons maintenant voir les délais I/O dans le journal, indiquant la lecture et écriture.

automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 1
pages: 0 removed, 67 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 89 removed, 8873 remain, 0 are dead but not yet removable, oldest xmin: 210871
index scan needed: 2 pages from table (2.99% of total) had 341 dead item identifiers removed
index "pg_depend_depender_index": pages: 39 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "pg_depend_reference_index": pages: 41 in total, 0 newly deleted, 0 currently deleted, 0 reusable

I/O timings: read: 44.254 ms, write: 0.531 ms

avg read rate: 13.191 MB/s, avg write rate: 8.794 MB/s
buffer usage: 167 hits, 126 misses, 84 dirtied
WAL usage: 85 records, 15 full page images, 78064 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s

Ces journaux ne sont disponibles que si track_io_timing est activé.

Connecter le journal

PostgreSQL enregistre déjà la connexion/déconnexion si log_connections / log_disconnections est activé. Par conséquent, PostgreSQL-14 enregistre désormais également le nom d'utilisateur réel fourni par l'utilisateur. Dans le cas où une authentification externe est utilisée et que le mappage est défini dans pg_ident.conf, il deviendra difficile d'identifier le nom d'utilisateur réel. Avant PostgreSQL-14, vous ne voyez que l'utilisateur mappé au lieu de l'utilisateur réel.

pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

pg              vagrant                 postgres

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer map=pg

Avant PostgreSQL-14

LOG:  database system was shut down at 2021-11-19 11:24:30 UTC
LOG:  database system is ready to accept connections
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres application_name=psql

PostgreSQL -14

LOG:  database system is ready to accept connections
LOG:  connection received: host=[local]
LOG:  connection authenticated: identity="vagrant" method=peer (/usr/local/pgsql.14/bin/data/pg_hba.conf:89)
LOG:  connection authorized: user=postgres database=postgres application_name=psql

Conclusion

Chaque version majeure de PostgreSQL comporte des améliorations significatives, et PostgreSQL-14 n'était pas différent.

La surveillance est une fonctionnalité clé de tout système de SGBD, et PostgreSQL continue de mettre à niveau ses capacités pour améliorer ses capacités de journalisation et de surveillance. Avec ces fonctionnalités nouvellement ajoutées, vous avez plus d'informations sur les connexions ; on peut facilement suivre les requêtes et observer les performances, et identifier le temps passé par le processus de vide dans les opérations de lecture/écriture. Cela peut vous être très utile pour mieux configurer les paramètres de vide.

Source: Percona