Maintenance de base de données PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez les tâches essentielles de maintenance d'une base de données PostgreSQL. L'objectif est de comprendre comment surveiller la taille des objets de la base de données, effectuer une maintenance de routine pour optimiser les performances et dépanner les problèmes potentiels en vérifiant les connexions et les journaux.

Vous commencerez par vous connecter à une base de données PostgreSQL et interroger la taille des tables et des index. Ensuite, vous apprendrez à exécuter ANALYZE pour mettre à jour les statistiques de la base de données et VACUUM pour récupérer de l'espace de stockage. Enfin, vous verrez comment lister les connexions client actives et vérifier les journaux du serveur pour détecter les erreurs afin de diagnostiquer et de résoudre les problèmes de base de données.

Interrogation des tailles d'objets de base de données

Dans cette étape, vous allez vous connecter à la base de données PostgreSQL et interroger la taille d'une table et de son index. Comprendre la taille de vos objets de base de données est crucial pour l'optimisation des performances et la planification de la capacité.

Tout d'abord, ouvrez un terminal. Pour vous connecter au serveur PostgreSQL en tant qu'utilisateur postgres et accéder à la base de données mydatabase, exécutez la commande suivante :

sudo -u postgres psql mydatabase

Vous devriez voir l'invite psql (mydatabase=#), indiquant que vous êtes connecté à la base de données mydatabase. Toutes les commandes SQL ultérieures dans ce laboratoire doivent être exécutées dans ce shell psql, sauf indication contraire.

Maintenant, déterminons la taille de mytable. Nous utiliserons les fonctions pg_size_pretty et pg_relation_size. pg_relation_size renvoie la taille d'une table en octets, et pg_size_pretty la formate dans un format lisible par l'homme (par exemple, KB, MB).

Exécutez la requête SQL suivante pour obtenir la taille de mytable :

SELECT pg_size_pretty(pg_relation_size('mytable'));

Vous devriez voir une sortie similaire à celle-ci, montrant l'espace disque utilisé par les données de la table :

 pg_size_pretty
----------------
 56 kB
(1 row)

Ensuite, vérifiez la taille de l'index idx_mytable_name qui a été créé sur la colonne name :

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

La sortie montrera l'espace consommé par l'index :

 pg_size_pretty
----------------
 48 kB
(1 row)

Enfin, pour obtenir la taille totale de la table, y compris tous ses index, utilisez la fonction pg_total_relation_size :

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Cette sortie montre la taille combinée de la table et de son index :

 pg_size_pretty
----------------
 176 kB
(1 row)

Optimisation avec ANALYZE

Dans cette étape, vous découvrirez la commande ANALYZE, essentielle pour maintenir de bonnes performances de requête.

Comprendre ANALYZE

La commande ANALYZE collecte des statistiques sur le contenu des tables de la base de données. Le planificateur de requêtes PostgreSQL utilise ces statistiques pour choisir les plans d'exécution les plus efficaces pour les requêtes. Sans statistiques précises, le planificateur peut faire de mauvais choix, entraînant des performances de requête lentes. Il est de bonne pratique d'exécuter ANALYZE périodiquement, surtout après des modifications importantes des données de la table.

Toujours dans le shell psql, exécutez ANALYZE sur la table mytable :

ANALYZE mytable;

Cette commande analyse mytable et met à jour ses statistiques. Vous verrez la sortie suivante, confirmant que la commande a réussi :

ANALYZE

Bien que la commande renvoie simplement ANALYZE, elle a mis à jour les statistiques internes de mytable en arrière-plan.

Récupération d'espace avec VACUUM

Dans cette étape, vous utiliserez la commande VACUUM pour effectuer une autre tâche de maintenance critique : la récupération d'espace.

Comprendre VACUUM

Dans PostgreSQL, lorsqu'une ligne est mise à jour ou supprimée, l'ancienne version de la ligne (un "tuple mort") n'est pas immédiatement supprimée du disque. VACUUM récupère l'espace occupé par ces tuples morts, rendant l'espace disponible pour réutilisation. Il met également à jour les informations de visibilité des données, ce qui contribue à améliorer les performances des requêtes.

Exécutons VACUUM sur la table mytable. Dans le shell psql, exécutez :

VACUUM mytable;

Cette commande traitera la table et renverra une confirmation :

VACUUM

Vous pouvez également combiner VACUUM et ANALYZE en une seule commande efficace. C'est une pratique de maintenance courante.

VACUUM ANALYZE mytable;

Cette commande récupère d'abord l'espace, puis met à jour les statistiques de la table, renvoyant VACUUM à l'achèvement.

Note : Il existe également la commande VACUUM FULL, qui récupère l'espace de manière plus agressive et réduit la taille du fichier sur disque. Cependant, elle verrouille la table entière, empêchant toute lecture ou écriture pendant son exécution, elle doit donc être utilisée avec prudence et uniquement lorsque nécessaire.

Surveillance des Connexions et des Journaux

Dans cette dernière étape, vous apprendrez à surveiller les connexions actives à la base de données et à vérifier les journaux du serveur pour les erreurs, ce qui sont des compétences clés pour le dépannage.

Tout d'abord, toujours dans le shell psql, vous pouvez interroger la vue pg_stat_activity pour voir toutes les connexions actives au serveur.

Exécutez la requête SQL suivante :

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Cette requête affiche le nom de la base de données (datname), le nom de l'utilisateur (usename), l'adresse IP du client (client_addr) et l'état actuel de la connexion (state). Vous verrez votre propre connexion listée dans la sortie, similaire à ceci :

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

Ensuite, vous vérifierez les journaux du serveur. Pour ce faire, vous devez d'abord quitter le shell psql. Tapez \q et appuyez sur Entrée :

\q

Vous êtes maintenant de retour à l'invite du terminal Linux standard. Les journaux PostgreSQL sont situés dans le répertoire /var/log/postgresql/. Sur ce système, le fichier journal est postgresql-14-main.log.

Utilisez la commande grep pour rechercher toutes les lignes contenant "ERROR" dans le fichier journal :

grep ERROR /var/log/postgresql/postgresql-14-main.log

S'il n'y a pas d'erreurs, cette commande ne produira aucune sortie. C'est le résultat attendu pour ce laboratoire, car nous n'avons effectué aucune action susceptible de provoquer une erreur. L'examen des journaux du serveur est une étape fondamentale pour diagnostiquer les problèmes de base de données.

Résumé

Dans ce laboratoire, vous avez appris à effectuer des tâches de maintenance essentielles pour les bases de données PostgreSQL. Vous comprenez désormais comment surveiller la taille des tables et des index, exécuter ANALYZE pour optimiser les performances des requêtes et utiliser VACUUM pour récupérer de l'espace de stockage. Vous vous êtes également exercé à lister les connexions clients actives et à vérifier les journaux du serveur pour les erreurs. Ces compétences sont cruciales pour garantir la santé, les performances et la fiabilité de votre base de données PostgreSQL.