Maintenance de la base de données PostgreSQL

Introduction

Dans ce laboratoire, nous explorerons les tâches essentielles de maintenance de la 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 et résoudre les problèmes potentiels.

Nous commencerons par interroger la taille des tables et des index à l'aide des fonctions pg_size_pretty et pg_relation_size. Ensuite, nous apprendrons à exécuter ANALYZE et VACUUM pour optimiser les performances des requêtes et récupérer de l'espace de stockage. Enfin, nous verrons comment lister les connexions client actives et vérifier les journaux du serveur (server logs) pour détecter les erreurs, diagnostiquer et résoudre les problèmes de base de données.

Skills Graph

Connexion à PostgreSQL et requête de la taille d'une table

Dans cette étape, nous allons nous connecter à la base de données PostgreSQL et interroger la taille d'une table. Comprendre la taille de vos objets de base de données est essentiel pour l'optimisation des performances (performance tuning) et la planification de la capacité (capacity planning).

Tout d'abord, ouvrez un terminal dans votre répertoire ~/project.

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 une invite de commande comme mydatabase=#. Cela indique que vous êtes connecté à la base de données mydatabase.

Si vous n'avez pas la base de données mydatabase et la table mytable, créez-les à l'aide des commandes SQL suivantes :

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    data TEXT
);

INSERT INTO mytable (name, data) SELECT 'Name ' || i, 'Data ' || i FROM generate_series(1, 1000) AS i;

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

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 :

 pg_size_pretty
------------------
 128 kB
(1 row)

Cela indique que la table mytable occupe actuellement 128 KB d'espace disque.

Vérification de la taille d'un index

Dans cette étape, nous allons créer un index, puis interroger sa taille. Les index sont utilisés pour accélérer les performances des requêtes, mais ils consomment également de l'espace disque. Nous vérifierons également la taille totale de la table, y compris les index.

Tout d'abord, créons un index sur la colonne name de mytable :

CREATE INDEX idx_mytable_name ON mytable (name);

Maintenant, vérifions la taille de l'index. Nous pouvons utiliser les mêmes fonctions, mais nous devons spécifier le nom de l'index.

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

La sortie pourrait ressembler à ceci :

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

Cela montre que l'index idx_mytable_name occupe 48 KB d'espace disque.

Enfin, obtenons la taille totale de la table, y compris les index. Nous pouvons utiliser la fonction pg_total_relation_size pour cela :

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

La sortie pourrait ressembler à ceci :

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

Cela montre que la taille totale de la table mytable, y compris tous les index, est de 176 KB.

Exécution de ANALYZE et VACUUM pour la maintenance

Dans cette étape, nous allons découvrir les commandes ANALYZE et VACUUM dans PostgreSQL, qui sont essentielles pour maintenir les performances de la base de données.

ANALYZE met à jour les statistiques de la base de données utilisées par le planificateur de requêtes (query planner) pour choisir les plans d'exécution les plus efficaces. Ces statistiques décrivent le contenu des tables dans la base de données. Sans statistiques précises, le planificateur de requêtes pourrait faire de mauvais choix, ce qui entraînerait des performances de requête lentes.

VACUUM récupère l'espace de stockage occupé par les tuples morts (dead tuples). Dans PostgreSQL, lorsqu'une ligne est mise à jour ou supprimée, l'ancienne version de la ligne n'est pas immédiatement supprimée. Au lieu de cela, elle est marquée comme morte. VACUUM récupère l'espace occupé par ces tuples morts, le rendant disponible pour être réutilisé. Il met également à jour la carte de visibilité (visibility map), qui aide le planificateur de requêtes à déterminer quelles lignes sont visibles pour les transactions.

Exécutons ANALYZE sur la table mytable :

ANALYZE mytable;

Cette commande analyse la table mytable et met à jour les statistiques. Vous ne verrez aucune sortie, mais les statistiques seront mises à jour en arrière-plan.

Ensuite, exécutons VACUUM sur la table mytable :

VACUUM mytable;

Cette commande récupère l'espace de stockage occupé par les tuples morts dans la table mytable. Encore une fois, vous ne verrez aucune sortie, mais le processus de nettoyage (vacuuming process) s'exécutera en arrière-plan.

Pour un nettoyage plus agressif, vous pouvez utiliser VACUUM FULL. Cependant, VACUUM FULL verrouille la table de manière exclusive, empêchant d'autres opérations d'être effectuées sur la table pendant le processus de nettoyage. Il est généralement recommandé d'utiliser VACUUM au lieu de VACUUM FULL, sauf si vous avez une raison spécifique d'utiliser VACUUM FULL.

-- VACUUM FULL mytable; -- Uncomment this line to run VACUUM FULL (use with caution)

Enfin, vous pouvez combiner ANALYZE et VACUUM en une seule commande :

VACUUM ANALYZE mytable;

Cette commande récupère d'abord l'espace de stockage occupé par les tuples morts, puis met à jour les statistiques. C'est souvent le moyen le plus efficace de maintenir les performances de la base de données.

Lister les connexions client actives et vérifier les journaux du serveur

Dans cette étape, nous allons explorer comment lister les connexions client actives et vérifier les journaux du serveur pour détecter les erreurs.

Pour lister les connexions client actives, vous pouvez interroger la vue pg_stat_activity. Cette vue fournit des informations sur chaque processus serveur, y compris l'utilisateur, la base de données et la requête en cours d'exécution.

Exécutez la requête SQL suivante :

SELECT datname, usename, client_addr, state, query FROM pg_stat_activity WHERE state != 'idle';

Cette requête vous montrera toutes les connexions actives qui ne sont pas inactives (idle). La sortie inclura le nom de la base de données (datname), le nom d'utilisateur (usename), l'adresse du client (client_addr), l'état actuel (state) et la requête en cours d'exécution (query).

Ensuite, vérifions les journaux du serveur pour détecter les erreurs. Les journaux PostgreSQL sont généralement situés dans le répertoire /var/log/postgresql. Les noms de fichiers journaux suivent généralement le modèle postgresql-VERSION-main.log, où VERSION est le numéro de version de PostgreSQL.

Pour vérifier les journaux pour les erreurs, vous pouvez utiliser la commande grep. Par exemple, pour rechercher des erreurs dans le fichier journal, exécutez la commande suivante dans un nouveau terminal (en dehors de psql) :

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

Note : Remplacez postgresql-14-main.log par le nom réel de votre fichier journal PostgreSQL. Vous pouvez lister les fichiers dans le répertoire /var/log/postgresql pour trouver le nom de fichier journal correct.

Cette commande affichera toutes les lignes du fichier journal qui contiennent le mot "ERROR". L'examen des journaux du serveur est crucial pour diagnostiquer et résoudre les problèmes de base de données.

Résumé

Dans ce labo, vous avez appris à effectuer des tâches essentielles de maintenance de base de données PostgreSQL. Vous comprenez maintenant comment surveiller la taille des objets de la base de données, exécuter ANALYZE et VACUUM pour optimiser les performances des requêtes et récupérer de l'espace de stockage, lister les connexions client actives et vérifier les journaux du serveur pour détecter les erreurs. Ces compétences sont cruciales pour assurer la santé et les performances de votre base de données PostgreSQL.