Tout ce qui suit suppose :
une installation sous Rocky Linux avec les paquets RPM de yum.postgresql.org ;
une instance installée avec les options par défaut ;
un administrateur dont le compte habituel sur la machine, non
privilégié, est nommé dalibo .
Traces maximales
But : suivre toutes les requêtes dans les traces
À titre pédagogique et pour alimenter un rapport pgBadger plus tard,
toutes les requêtes vont être tracées.
Dans postgresql.conf, positionner ceci :
log_min_duration_statement = 0 log_temp_files = 0 log_autovacuum_min_duration = 0 lc_messages = 'C' log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '
Éviter de faire cela en production, surtout
log_min_duration_statement = 0 ! Sur une base très active,
les traces peuvent rapidement monter à plusieurs dizaines de
gigaoctets !
Dans le présent TP, il faut surveiller l’espace disque pour cette
raison.
Puis passer à on les paramètres suivants s’ils ne le
sont pas déjà :
log_checkpoints log_connections log_disconnections log_lock_waits Recharger la configuration.
Laisser une fenêtre ouverte pour voir défiler le contenu des
traces.
Dans postgresql.conf :
log_min_duration_statement = 0 log_temp_files = 0 log_autovacuum_min_duration = 0 lc_messages = 'C' log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h ' log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on SELECT pg_reload_conf() ;Laisser une fenêtre ouverte avec le fichier, par exemple avec :
tail -f /var/lib/pgsql/15/data/log/postgresql-Wed.logLa moindre requête doit y apparaître, y compris quand l’utilisateur
effectue un simple \d.
Méthode d’authentification
But : Gérer les rôles et les permissions
Activer la méthode d’authentification scram-sha-256 dans
postgresql.conf si elle n’est pas déjà en place.
Cette méthode existe depuis PostgreSQL 10 mais n’est le défaut que
depuis PostgreSQL 14.
Dans postgresql.conf :
password_encryption = scram-sha-256 Ne pas oublier de recharger la configuration. Depuis psql en tant que
postgres :
SELECT pg_reload_conf() ;Alternative depuis le shell :
# systemctl reload postgresql-15
Consulter les droits définis dans pg_hba.conf au travers
de la vue système pg_hba_file_rules.
La vue permet de voir le contenu de pg_hba.conf
avant de le recharger.
SELECT * FROM pg_hba_file_rules ;
Dans pg_hba.conf, supprimer les accès avec la méthode
trust pouvant rester après les précédents exercices.
Vérifier dans la vue avant de recharger la configuration.
Dans le fichier pg_hba.conf, supprimer les lignes avec
la méthode trust. La vue se met à jour immédiatement. En
cas d’erreur de syntaxe dans le fichier, elle doit aussi indiquer une
erreur.
Puis on recharge :
SELECT pg_reload_conf() ;Création des bases
But : Créer des bases appartenant à un utilisateur
non privilégié
Créer un utilisateur nommé testperf avec attribut
LOGIN.
Au choix, on peut utiliser les commandes shell ou les commandes SQL
qu’elles affichent :
$ sudo -iu postgres$ createuser --login --echo testperfSELECT pg_catalog.set_config( 'search_path' , '' , false); CREATE ROLE testperf NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
Créer une base pgbench lui appartenant.
$ createdb --echo pgbench --owner testperfSELECT pg_catalog.set_config( 'search_path' , '' , false); CREATE DATABASE pgbench OWNER testperf;
Vérifier que la connexion ne marche pas encore depuis votre compte
habituel, y compris avec -h localhost.
La connexion ne peut se faire car dans pg_hba.conf ne
figure, en local, que l’accès peer pour un
utilisateur nommé postgres (nom système comme nom du
rôle.) Elle ne fonctionnera pas pour des utilisateurs nommés
différemment.
Corriger cela impliquerait de modifier
pg_ident.conf.
Si l’on ajoute -h localhost, l’accès est contrôlé via
une ligne host, et exige un mot de passe qui n’existe pas
encore :
$ psql -U testerf pgbench -h localhostPassword for user testerf: Nous créerons ces mots de passe plus bas.
Créer un rôle patron avec attribut
LOGIN, et une base entreprise lui
appartenant.
$ createuser --login patron$ createdb --owner patron entrepriseCe qui est équivalent à :
CREATE ROLE patron LOGIN;CREATE DATABASE entreprise OWNER patron;Noter que, là encore, c’est le superutilisateur
postgres qui crée la base et affecte les droits à
patron . Celui-ci n’a pas le droit de créer des
bases.
Mots de passe
But : Mise en place de l’authentification par mot de
passe
Créer des mots de passe pour les rôles patron et
testperf .
Déclarer le mot de passe se fait facilement depuis psql
(en tant que superutilisateur postgres ) :
= # \password testperf= # \password patronUn outil courant pour générer des mots de passe aléatoires longs est
pwgen :
$ pwgen 20 1jahT0eeRov2aiQuae1iM Si pwgen n’est pas présent sur le système, un bon mot de
passe peut être généré ainsi :
$ echo "faitespasserunchatsurleclavier" | md5sum b0cdc36ff6c986b3930bfc269f37f3f2 Pour l’exercice, il est possible de donner le même mot de passe à
tous les utilisateurs (ce que personne ne fait en production, bien
sûr).
Consulter la table pg_authid pour voir la version
chiffrée.
Noter que, même identiques, les mots de passe n’ont pas la même
signature.
SELECT * FROM pg_authid WHERE rolname IN ('testperf' ,'patron' ) \gx
Pour ouvrir les accès :
ajuster pg_hba.conf pour permettre l’accès à la base
pgbench uniquement à l’utilisateur
testperf , permettre l’accès à la base
entreprise à tout utilisateur, en local avec son mot de
passe, en authentification scram-sha-256 ;
vérifier avec la vue pg_hba_file_rules ;
recharger la configuration ;
tester la connexion.
Ajouter ceci dans pg_hba.conf, en tête
(nous verrons que c’est une erreur) :
# TYPE DATABASE USER ADDRESS METHOD
local pgbench testperf scram-sha-256
local entreprise all scram-sha-256Recharger la configuration et tenter une connexion depuis un compte
utilisateur normal : cela doit fonctionner en entrant le mot de
passe.
$ sudo -iu postgres psql -c 'SELECT pg_reload_conf()' ; $ psql -U testperf -d pgbenchMot de passe pour l'utilisateur testperf : psql (15.1) Type "help" for help. pgbench=> patron doit aussi pouvoir se connecter :
$ psql -U patron -d entreprise
Créer un fichier .pgpass dans votre répertoire
utilisateur (/home/dalibo) ou dans celui de l’utilisateur
postgres (/var/lib/pgsql) pour qu’il puisse se
connecter aux bases entreprise et
pgbench sans entrer le mot de passe.
Le fichier doit contenir le mot de passe en clair sous cette
forme :
localhost:5432:pgbench:testperf:b0cdc36ff6c986b3930bfc269f37f3f2
localhost:5432:entreprise:patron:b0cdc36ff6c986b3930bfc269f37f3f2NB : la mention localhost dans ce fichier couvre aussi
bien les accès via :::1 ou 127.0.0.1 (lignes
host de pg_hba.conf) que les accès via la
socket unix (lignes local).
Si le mot de passe est le même pour tous les utilisateurs créés par
la suite, le nom d’utilisateur patron peut même être
remplacé par *.
Si la connexion échoue, vérifier que le fichier est en mode 600 :
$ chmod u=rw,go= ~/.pgpassLa connexion doit à présent se faire sans mot de passe.
Le superutilisateur PostgreSQL postgres peut-il se
connecter aux bases entreprise et
pgbench ? Si non, comment lui permettre ?
Même depuis l’utilisatur système postgres , la
connexion aux deux bases que nous venons de créer échoue :
$ sudo -iu postgres psql -d entreprise -U postgresMot de passe pour l'utilisateur postgres : psql: fe_sendauth: no password supplied La cause est dans les traces :
L’échec est donc normal : la ligne de pg_hba.conf qui
permet un accès inconditionnel à toute base depuis le compte système
postgres est à présent la troisième. Pour corriger cela
sans créer de mot de passe, la remplacer par cette toute
première ligne de pg_hba.conf :
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peerEt recharger la configuration.
Depuis l’utilisateur système postgres, il ne doit plus y
avoir de mot de passe demandé à la connexion :
Remplir la base pgbench et générer un peu
d’activité :
/usr/pgsql-15/bin/pgbench -i -s1 --foreign-keys pgbench -U testperf/usr/pgsql-15/bin/pgbench -P1 -T3 pgbench -U testperfAucun mot de passe ne doit être demandé, avec ou sans
-h localhost.
$ sudo -iu postgres$ createuser --login --echo testperfSELECT pg_catalog.set_config( 'search_path' , '' , false); CREATE ROLE testperf NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN; $ createdb --echo pgbenchSELECT pg_catalog.set_config( 'search_path' , '' , false); CREATE DATABASE pgbench OWNER testperf; Initialisation de la base (23 Mo) :
$ sudo -iu postgres$ /usr/pgsql-15/bin/pgbench -i --foreign-keys -d pgbench -U testperf -h localhostGénération de données :
/usr/pgsql-15/bin/pgbench -P1 -T3 pgbench -U testperf -h localhostLe pg_hba.conf doit ressembler à celui-ci :
# TYPE DATABASE USER ADDRESS METHOD local all postgres peer local pgbench testperf scram-sha-256 local entreprise all scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 L’accès sans -h localhost fonctionne grâce aux lignes
local. L’accès via -h localhost fonctionne
grâce à l’une des lignes host. On avait vu qu’il ne
manquait que le mot de passe. Évidemment cela ne marche pas depuis une
adresse extérieure.
Les 3 dernières lignes sont réservées à des connexions de
réplication.
Compléter pg_ident.conf pour pouvoir se connecter au
rôle postgres depuis le compte système habituel
(dalibo ), en local.
Remplacer la première ligne de pg_hba.conf par :
local all postgres peer map = admins Dans pg_ident.conf, ajouter :
# MAPNAME SYSTEM-USERNAME PG-USERNAME admins postgres postgres admins dalibo postgres On recharge :
$ sudo systemctl reload postgresql-15
Toute erreur peut empêcher la connexion au rôle
postgres !
Tester la connexion depuis postgres au rôle
postgres , de dalibo à
postgres .
Ces connexions doivent fonctionner sans mot de passe :
postgres$ psqlpostgres$ psql -d entreprisedalibo$ psql -U postgresdalibo$ psql -U postgres -d entrepriseRôles et permissions
But : Gérer les rôles et les permissions sur les
tables
Sous les utilisateurs dalibo comme
postgres , créer un nouveau fichier
~/.psqlrc contenant \set PROMPT1 '%n@%/%R%# '
pour que l’invite indique quels sont les utilisateur et base en
cours.
Noter que l’affichage de l’invite est légèrement différente selon que
le type d’utilisateur : superutilisateur ou un utilisateur
« normal ».
Ajouter à la base de données entreprise la table
facture (id int, objet text, creationts timestamp). Elle
appartiendra à patron , administrateur de la base.
Se connecter avec l’utilisateur patron
(administrateur de la base entreprise ) :
$ psql -U patron entrepriseCréer la table facture :
=> CREATE TABLE facture (id int , objet text, creationts timestamp );Noter que la table appartient à celui qui la crée :
Création d’un utilisateur et d’un groupe
Créer un rôle secretariat sans droit de connexion,
mais qui peut visualiser, ajouter, mettre à jour et supprimer des
éléments de la table facture.
Il faut le faire avec le rôle postgres , car
patron n’a pas les droits :
= # \c - postgresto database "entreprise" as user "postgres" .= # CREATE ROLE secretariat;= # GRANT SELECT , INSERT , UPDATE , DELETE ON facture TO secretariat;
Créer un rôle boulier qui peut se connecter et
appartient au rôle secretariat , avec un mot de passe (à
ajouter au .pgpass).
= # CREATE ROLE boulier LOGIN IN ROLE SECRETARIAT;= # \password boulier
Vérifier la création des deux rôles.
En tant que boulier , créer une table
brouillon identique à facture. Dans quel
schéma cela se passe-t-il ? Avec PostgreSQL 15 ou supérieur, il y a une
étape supplémentaire.
La connexion doit se faire sans problème avec le mot de passe.
Une astuce à connaître pour créer une table vide de même structure
qu’une autre est :
=> CREATE TABLE brouillon (LIKE facture INCLUDING ALL ) ;Cet ordre fonctionnera directement jusque PostgreSQL 14 compris, car
la table est créée implicitement dans le schéma public .
À partir de PostgreSQL 15, par défaut, seul le propriétaire de la base
(patron ) peut écrire dans le schéma
public :
Il doit donc donner ce droit à boulier :
=> GRANT CREATE ON schema public TO boulier ;=> \dn+ public => CREATE TABLE brouillon (LIKE facture INCLUDING ALL ) ;
Vérifier les tables présentes et les droits \dp. Comment
les lire ?
La nouvelle table appartient bien à boulier :
Sans affectation explicite de droits, les droits par défauts ne
figurent pas : par exemple, brouillon pourra être lu et
modifié par son propriétaire, boulier .
patron a tous les droits sur la table
facture (il possède la table).
On retrouve les droits donnés plus haut au rôle
secretariat : insertion (a pour
append ), lecture (r pour read ),
modification (w pour write ) et suppression
(d pour delete ).
On ne voit pas explicitement les droits de boulier
(membre de secretariat ) sur facture.
À l’aide du rôle boulier : insérer 2 factures ayant
pour objet « Vin de Bordeaux » et « Vin de Bourgogne » avec la date et
l’heure courante.
=> INSERT INTO facture VALUES 1 , 'Vin de Bordeaux' , now()),2 , 'Vin de Bourgogne' , now());
Afficher le contenu de la table facture.
=> SELECT * FROM facture;
Mettre à jour la deuxième facture avec la date et l’heure
courante.
=> UPDATE facture SET creationts = now() WHERE id = 2 ;
Supprimer la première facture.
=> DELETE FROM facture WHERE id = 1 ;Modification des permissions
Retirer les droits DELETE sur la table
facture au rôle secretariat .
=> \c - patron=> REVOKE DELETE ON facture FROM secretariat;
Vérifier qu’il n’est plus possible de supprimer la deuxième facture
avec le rôle boulier .
=> \c - boulier=> DELETE FROM facture WHERE id = 2 ;
En tant que patron , créer une table
produit contenant une colonne texte nommée
appellation et la remplir avec des noms de boissons.
=> \c - patron=> CREATE TABLE produit (appellation text) ;=> INSERT INTO produit VALUES 'Gewurtzraminer vendanges tardives' ), ('Cognac' ), ('Eau plate' ),'Eau gazeuse' ), ('Jus de groseille' ) ;
Afficher les droits sur cette table avec \dt et
\dp. Vérifier que le rôle boulier
appartenant au rôle secretariat ne peut pas
sélectionner les produits contenus dans la table
produit.
On voit bien que produit appartient à
patron et que secretariat n’a à priori
aucun droit dessus.
En conséquence, boulier ne peut lire la table :
=> \c - boulier'utilisateur « boulier ». boulier@entreprise=> SELECT * FROM produit; ERROR: permission denied for table produit
Retirer tous les droits pour le groupe secretariat
sur la table produit.
=> REVOKE ALL ON produit from secretariat;
Que deviennent les droits affichés ? boulier peut-il
lire la table ?
secretariat n’avait pourtant aucun droit, mais
l’affichage a changé et énumère à présent explicitement les droits
présents :
Autoriser l’utilisateur boulier à accéder à la table
produit en lecture.
=> GRANT SELECT ON produit TO boulier ;
Vérifier que boulier peut désormais accéder à la
table produit.
=> SELECT * FROM produit ;Héritage des droits au login
Créer un rôle tina appartenant au rôle
secretariat , avec l’attribut LOGIN, mais
n’héritant pas des droits à la connexion. Vérifier les droits avec
\du. Lui donner un mot de passe.
La clause NOINHERIT évite qu’un rôle hérite
immédiatement des droits des autres rôles :
=> CREATE ROLE tina LOGIN NOINHERIT ;=> GRANT secretariat TO tina;= # \du= # \password tinaTester la connexion en tant que tina .
Vérifier que tina ne peut pas accéder à la table
facture.
=> SELECT * FROM facture;
En tant que tina , activer le rôle
secretariat (SET ROLE).
=> SET ROLE secretariat;
Vérifier que tina possède maintenant les droits du
rôle secretariat . Sélectionner les données de la table
facture.
L’utilisateur tina possède maintenant les droits du
rôle secretariat :
=> SELECT * FROM facture;Autorisation d’accès distant
But : Mettre en place les accès dans
pg_hba.conf.
Autoriser tous les membres du réseau local à se connecter avec un mot
de passe (autorisation en IP sans SSL) avec les utilisateurs
boulier ou tina . Tester avec l’IP du
serveur avant de demander aux voisins de tester.
Pour tester, repérer l’adresse IP du serveur avec ip a,
par exemple 192.168.28.1, avec un réseau local en
192.168.28.*.
Ensuite, lors des appels à psql, utiliser
-h 192.168.28.1 au lieu de la connexion locale ou de
localhost :
$ psql -h 192.168.123.180 -d entreprise -U tinaAjouter les lignes suivantes dans le fichier
pg_hba.conf :
host entreprise tina,boulier 192.168.28.0/24 scram-sha-256Il ne faut pas oublier d’ouvrir PostgreSQL aux connexions extérieures
dans postgresql.conf :
Cette modification néces¨site un rédémarrage
Plus prudemment, on peut juste ajouter l’adresse publique de
l’instance PostgreSQL :
listen_addresses = 'localhost,192.168.28.1' Il y a peut-être un firewall à désactiver :
$ sudo systemctl status firewalld$ sudo systemctl stop firewalldVACUUM, VACUUM FULL,
DELETE, TRUNCATE
But : Effacer des données, distinguer
VACUUM et VACUUM FULL.
Pré-requis
Désactiver le démon autovacuum de l’instance.
Dans le fichier postgresql.conf, désactiver le démon
autovacuum en modifiant le paramètre suivant :
Ne jamais faire cela en production !
On recharge la configuration :
$ psql -c 'SELECT pg_reload_conf()' On vérifie que le paramètre a bien été modifié :
= # show autovacuum ;Nettoyage avec VACUUM
Se connecter à la base pgbench en tant que
testperf .
$ psql -U testperf -d pgbench
Grâce aux fonctions pg_relation_size et
pg_size_pretty, afficher la taille de la table
pgbench_accounts.
\d+ affiche les tailles des tables, mais il existe des
fonctions plus ciblées.
Pour visualiser la taille de la table, il suffit d’utiliser la
fonction pg_relation_size. Comme l’affichage a parfois trop
de chiffres pour être facilement lisible, on utilise
pg_size_pretty.
Il est facile de retrouver facilement une fonction en effectuant une
recherche par mot clé dans psql, notamment pour retrouver
ses paramètres. Exemple :
Cela donne au final :
=> SELECT pg_relation_size('pgbench_accounts' );=> SELECT pg_size_pretty(pg_relation_size('pgbench_accounts' ));
Copier le contenu de la table dans une nouvelle table
(pba_copie).
=> CREATE table pba_copie AS SELECT * FROM pgbench_accounts;
Supprimer le contenu de la table pba_copie, à
l’exception de la dernière ligne (aid=100000), avec un
ordre DELETE. Quel est alors l’espace disque utilisé par
cette table ?
=> DELETE FROM pba_copie WHERE aid < 100000 ;Il ne reste qu’une ligne, mais l’espace disque est toujours
utilisé :
=> SELECT pg_size_pretty(pg_relation_size('pba_copie' ));Noter que même si l’autovacuum n’était pas désactivé, il n’aurait pas
réduit l’espace occupé par la table car il reste la ligne à la fin de
celle-ci. De plus, il n’aurait pas eu forcément le temps de passer sur
la table entre les deux ordres précédents.
Insérer le contenu de la table pgbench_accounts dans la
table pba_copie. Quel est alors l’espace disque utilisé par
la table ?
=> INSERT into pba_copie SELECT * FROM pgbench_accounts;L’espace disque utilisé a doublé :
=> SELECT pg_size_pretty(pg_relation_size('pba_copie' ));Les nouvelles données se sont ajoutées à la fin de la table. Elles
n’ont pas pris la place des données effacées précédemment.
Effectuer un VACUUM simple sur pba_copie. Vérifier la
taille de la base.
La commande vacuum « nettoie » mais ne libère pas d’espace
disque :
=> VACUUM pba_copie;=> SELECT pg_size_pretty(pg_relation_size('pba_copie' ));
Vider à nouveau la table pba_copie des lignes
d’aid inférieur à 100 000. Insérer à nouveau le contenu de
la table pgbench_accounts. L’espace mis à disposition
a-t-il été utilisé ?
=> DELETE FROM pba_copie WHERE aid < 100000 ;=> SELECT pg_size_pretty(pg_relation_size('pba_copie' ));Cette fois, la table n’a pas augmenté de taille. PostgreSQL a pu
réutiliser la place des lignes effacées que VACUUM a marqué
comme disponibles.
Voir la taille de la base. Supprimer la table pba_copie.
Voir l’impact sur la taille de la base.
Nous verrons plus tard comment récupérer de l’espace. Pour le moment,
on se contente de supprimer la table.
= # SELECT pg_size_pretty(pg_database_size ('pgbench' )) ;= # DROP TABLE pba_copie ;= # SELECT pg_size_pretty(pg_database_size ('pgbench' )) ;Supprimer une table rend immédiatement l’espace disque au
système.
VACUUM avec les requêtes de pgbench
Tout d’abord, repérer les tailles des différentes tables et le nombre
de lignes de chacune.
=> SELECT count (* ) FROM pgbench_accounts;=> SELECT count (* ) FROM pgbench_tellers;=> SELECT count (* ) FROM pgbench_branches;=> SELECT count (* ) FROM pgbench_history;(Le contenu de cette dernière table dépend de l’historique de la
base.)
Pour amplifier le phénomène à observer, on peut créer une session de
très longue durée, laissée ouverte sans COMMIT ni
ROLLBACK. Il faut qu’elle ait consulté une des tables pour
que l’effet soit visible :
=> BEGIN ;=> SELECT count (* ) FROM pgbench_accounts ;
Depuis un autre terminal, générer de l’activité sur la table, ici
avec 10 000 transactions sur 20 clients :
PGOPTIONS = '-c synchronous_commit=off' \ /usr/pgsql-15/bin/pgbench -U testperf -d pgbench \ --jobs = 2 -t 10000 --no-vacuum (NB : La variable d’environnement PGOPTIONS restreint
l’utilisation des journaux de transaction pour accélérer les écritures
(données NON critiques ici). Le --no-vacuum est destiné à
éviter que l’outil demande lui-même un VACUUM. Le test dure
quelques minutes. Le relancer au besoin.)
Après quelques minutes, pgbench affichera le nombre de
transactions par seconde, bien sûr très dépendant de la machine :
(Optionnel) C’est l’occasion d’installer l’outil
pg_activity depuis les dépôts du PGDG (il peut y avoir
besoin du dépôt EPEL) et de le lancer en tant que
postgres pour voir ce qui se passe dans la base.
Pour pg_activity :
$ sudo yum install epel-release$ sudo yum install pg_activityIl se lance ainsi :
$ sudo -iu postgres pg_activityLe premier écran affiche les sessions en cours, le deuxième celles en
attente de libération d’un verrou, le troisième celles qui en bloquent
d’autres.
Noter que la session restée délibérément ouverte n’est pas
bloquante.
Comparer les nouvelles tailles des tables (taille sur le disque et
nombre de lignes). La table pg_stat_user_tables contient
l’activité sur chaque table. Comment s’expliquent les évolutions ?
La volumétrie des tables a explosé :
On constate que le nombre de lignes reste le même malgré l’activité,
sauf pour la table d’historique :
=> SELECT count (* ) FROM pgbench_accounts;=> SELECT count (* ) FROM pgbench_tellers;=> SELECT count (* ) FROM pgbench_branches;=> SELECT count (* ) FROM pgbench_history;Ce dernier chiffre dépend de l’activité réelle et du nombre de
requêtes.
Les statistiques d’activité de la table sont dans
pg_stat_user_tables. Pour pgbench_accounts, la
plus grosse table, on y trouve ceci :
=> SELECT * FROM pg_stat_user_tables ORDER BY relname \gxLe champ n_tup_upd montre qu’il y a eu 200 000 mises à
jour après l’insertion initiale de 100 000 lignes (champ
n_tup_ins). Il y a toujours 100 000 lignes visibles
(n_live_tup).
Le VACUUM a été demandé explicitement à la création
(last_vacuum) mais n’est pas passé depuis.
La VACUUM étant inhibé, il est normal que les lignes
mortes se soient accumulées (n_dead_tup) : il y en a
200 000, ce sont les anciennes versions des lignes modifiées.
Pour la table pgbench_history :
La table pgbench_history a subi 200 000 insertions et
contient à présent 200 000 lignes : il est normal qu’elle ait grossi de
0 à 10 Mo.
Pour la table pgbench_tellers :
Elle ne contient toujours que 10 lignes visibles
(n_live_up), mais 199 979 lignes « mortes »
(n_dead_tup).
Même s’il n’a gêné aucune opération du point de vue de l’utilisateur,
le verrou posé par la session en attente est visible dans la table des
verrous pg_locks :
= # SELECT * FROM pg_locksWHERE relation = (SELECT relid FROM pg_stat_user_tablesWHERE relname = 'pgbench_accounts' ) ;Nettoyage avec VACUUM FULL
Exécuter un VACUUM FULL VERBOSE sur
pgbench_tellers.
= # VACUUM FULL VERBOSE pgbench_tellers ;Un \d+ indique que la taille de la table est bien
retombée à 8 ko (1 bloc), ce qui suffit pour 10 lignes.
Exécuter un VACUUM FULL VERBOSE sur
pgbench_accounts.
Si celui-ci reste bloqué, il faudra sans doute arrêter la transaction
restée ouverte plus haut.
= # VACUUM FULL VERBOSE pgbench_accounts ;Soit : 100 000 lignes conservées, 200 000 mortes supprimées dans 4925
blocs (39 Mo).
Effectuer un VACUUM FULL VERBOSE. Quel est l’impact sur
la taille de la base ?
Même les tables système seront nettoyées :
=> VACUUM FULL VERBOSE ;Seule pgbench_branches était encore à nettoyer (1 ligne
conservée).
La taille de la base retombe à 32 Mo selon \l+. Elle
faisait au départ 22 Mo, et 10 Mo ont été ajoutés dans
pgbench_history.
Truncate ou Delete ?
Créer copie1 et copie2 comme des copies de
pgbench_accounts, données incluses.
= # CREATE TABLE copie1 AS SELECT * FROM pgbench_accounts ;= # CREATE TABLE copie2 AS SELECT * FROM pgbench_accounts ;
Effacer le contenu de copie1 avec
DELETE.
= # DELETE FROM copie1 ;
Effacer le contenu de copie2 avec
TRUNCATE.
= # TRUNCATE copie2 ;
Quelles sont les tailles de ces deux tables après ces
opérations ?
Pour une purge complète, TRUNCATE est à préférer : il
vide la table et rend l’espace au système. DELETE efface
les lignes mais l’espace n’est pas encore rendu.
Réactivation de l’autovacuum
Réactiver l’autovacuum de l’instance.
Dans postgresql.conf :
= # SELECT pg_reload_conf() ;= # SHOW autovacuum;
Attendre quelques secondes et voir si copie1 change de
taille.
Après quelques instants, la taille de copie1 (qui avait
été vidée plus tôt avec DELETE) va redescendre à quelques
kilooctets.
Le passage de l’autovacuum en arrière-plan est tracé dans
last_autovacuum :
= # SELECT * FROM pg_stat_user_tables WHERE relname = 'copie1' \gxStatistiques des données
But : Savoir trouver les statistiques des données et
les mettre à jour
Créer une table copie3, copie de
pgbench_accounts.
CREATE TABLE copie3 AS SELECT * FROM pgbench_accounts ;
Dans la vue système pg_stats, afficher les statistiques
collectées pour la table copie3.
= # SELECT * FROM pg_stats WHERE tablename = 'copie3' ;L’autovacuum n’est pas passé, les statistiques ne sont pas encore
présentes. Noter que, même activé, il n’aurait pas forcément eu le temps
de passer entre les deux ordres précédents.
Lancer la collecte des statistiques pour cette table uniquement.
La collecte se déclenche avec la commande ANALYZE :
= # ANALYZE VERBOSE copie3 ;30 000 lignes font partie de l’échantillonnage.
Afficher de nouveau les statistiques.
SELECT * FROM pg_stats WHERE tablename = 'copie3' ;Cette fois, la vue pg_stats renvoie des informations,
colonne par colonne.
Le champ aid est la clé primaire, ses valeurs sont
toutes différentes. L’histogramme des valeurs compte 100 valeurs qui
délimite autant de buckets . Ils sont là régulièrement répartis,
ce qui indique une répartition homogène des valeurs.
SELECT * FROM pg_stats WHERE tablename = 'copie3' ;Autre exemple, le champ bid : on voit qu’il ne possède
qu’une seule valeur.
De même, on pourra vérifier que le champ filler a une
taille moyenne de 85 octets, ou voir la répartition des valeurs du champ
abalance.
Réindexation
Recréer les index de la table pgbench_accounts.
La réindexation d’une table se fait de la manière suivante :
= # REINDEX (VERBOSE) TABLE pgbench_accounts ;
Comment recréer tous les index de la base
pgbench ?
= # REINDEX (VERBOSE) DATABASE pgbench ;
Comment recréer uniquement les index des tables systèmes ?
Pour réindexer uniquement les tables systèmes :
= # REINDEX SYSTEM pgbench ;
Quelle est la différence entre la commande REINDEX et la
séquence DROP INDEX + CREATE INDEX ?
REINDEX est similaire à une suppression et à une
nouvelle création de l’index. Cependant, les conditions de verrouillage
sont différentes :
REINDEX verrouille les écritures mais pas les
lectures de la table mère de l’index. Il prend aussi un verrou exclusif
sur l’index en cours de traitement, ce qui bloque les lectures qui
tentent d’utiliser l’index.
Au contraire, DROP INDEX crée temporairement un
verrou exclusif sur la table parent, bloquant ainsi écritures et
lectures. Le CREATE INDEX qui suit verrouille les écritures
mais pas les lectures ; comme l’index n’existe pas, aucune lecture ne
peut être tentée, signifiant qu’il n’y a aucun blocage et que les
lectures sont probablement forcées de réaliser des parcours séquentiels
complets.
Traces
But : Gérer les fichiers de traces
Quelle est la méthode de gestion des traces utilisée par défaut ?
Par défaut, le mode de journalisation est
stderr :
= # SHOW log_destination ;
Paramétrer le programme interne de rotation des journaux :
modifier le fichier postgresql.conf pour utiliser le
logging collector ;
les traces doivent désormais être sauvegardés dans le répertoire
/var/lib/pgsql/traces ;
la rotation des journaux doit être automatisée pour générer un
nouveau fichier de logs toutes les 30 minutes, quelle que soit la
quantité de logs archivés ; le nom du fichier devra donc comporter les
minutes.
Tester en forçant des rotations avec la fonction
pg_rotate_logfile.
Augmenter la trace (niveau info).
Sur Red Hat/CentOS/Rocky Linux, le collecteur des traces (logging
collector ) est activé par défaut dans postgresql.conf
(mais ce ne sera pas le cas sur un environnement Debian ou avec une
installation compilée, et il faudra redémarrer pour l’activer) :
On crée le répertoire, où postgres doit pouvoir
écrire :
$ sudo mkdir -m700 /var/lib/pgsql/traces$ sudo chown postgres: /var/lib/pgsql/tracesPuis paramétrer le comportement du récupérateur :
log_directory = '/var/lib/pgsql/traces' log_filename = 'postgresql-%Y-%m-%d_%H-%M.log' log_rotation_age = 30min log_rotation_size = 0 log_min_messages = info Recharger la configuration et voir ce qui se passe dans
/var/lib/pgsql/traces :
$ sudo systemctl reload postgresql-12$ sudo watch -n 5 ls -lh /var/lib/pgsql/tracesDans une autre fenêtre, générer un peu d’activité, avec
pgbench ou tout simplement avec :
= # SELECT 1 ;= # \watch 1 Les fichiers générés doivent porter un nom ressemblant à
postgresql-2019-08-02_16-55.log.
Pour forcer le passage à un nouveau fichier de traces :
= # SELECT pg_rotate_logfile() ;
Comment éviter l’accumulation des fichiers ?
La première méthode consiste à avoir un log_filename
cyclique. C’est le cas par défaut sur Red Hat/CentOS/Rocky Linux avec
postgresql-%a, qui reprend les jours de la semaine. Noter
qu’il n’est pas forcément garanti qu’un
postgresql-%H-%M.log planifié toutes les 5 minutes écrase
les fichiers de la journée précédente. En pratique, on descend rarement
en-dessous de l’heure.
Utiliser l’utilitaire logrotate, fourni avec toute
distribution Linux, dont le travail est de gérer rotation, compression
et purge. Il est activé par défaut sur Debian.
Enfin, on peut rediriger les traces vers un système
externe.
