Pour aller plus loin

Les éléments précédemment présentés vous donnent les principaux aspects à connaitre et à maîtriser afin d'appréhender convenablement une mise à jour ou une restauration de base de données. Il est possible d'aller encore plus loin dans l'analyse afin d'identifier plus précisément les composants les plus volumineux d'un environnement.

1. Répertoire de stockage des bases de données

PostgreSQL stocke ses bases de données sur un répertoire que l’on peut explorer/quantifier. Cette information peut être retrouvée sur la clé « data_directory » présente dans le fichier postgresql.conf et peut être trouvé via un simple grep (ou via SQL, voir plus bas). Exécuter la commande : grep data_directory /etc/postgresql/10/main/postgresql.conf 

En effet, dans le répertoire /var/lib/postgresql/10/main/base ("10" représentant ici la version de l’instance PostgreSQL et "main" son nom), l’utilisateur pourra trouver ses bases de données.

Cette documentation étant réalisée avec une version 18 d’Ubuntu, il faut l’adapter pour les autre versions (PostgreSQL 12 pour Ubuntu 20, PostgreSQL14 pour Ubuntu 22, etc…) ou pour les autres nom d’instance (main) ! 

Considérer que dans la plupart des cas, vos fichiers de configuration PostgreSQL seront sous :
/etc/postgresql/<version_instance>/<nom_instance>

Et vos fichiers data sous : 
/var/lib/postgresql/<version_instance>/<nom_instance>

Une commande SQL vous donne la localisation des fichiers de votre instance : 
SHOW data_directory;

Une commande SQL vous permet de déterminer le répertoire de stockage de votre base :
SELECT oid from pg_database where datname = 'BDD';

Cet espace est protégé et seul l’utilisateur « root » peut l’explorer…

Il faut donc s'authentifier comme l'utilisateur root comme ci-dessous :

Détail des commandes lancées :
$ sudo su root --> permet de passer sur l’utilisateur root.
# cd main --> change de répertoire et va dans le répertoire « main ».
# cd base --> Change de réperoire et va dans le répertoire « base ».
# ls -l -->Affiche le contenu du répertoire en mode liste.

Contrairement à SQL Server, la base de données sous PostgreSQL n’est pas composée d’un seul fichier (.dat) mais d’un ensemble de fichier, chaque fichier constituant une table.

Comme nous l’avons vu plus haut, le fait de regarder la taille de ce répertoire nous indique la taille de la base de données. Bien que le moteur de base de données exécute ses propres « plans de maintenance » automatiquement, ces derniers ne seront réalisés que si certains seuils sont atteints par les tables. Il peut être souhaitable de déclencher manuellement certaines opérations de maintenance.

Ce sont des opérations d’administration importantes et une sauvegarde est impérative avant le déclenchement de ces traitements.

Via pgadmin, sélectionner la base (clique-droit) puis « Maintenance ».

                   

Mais aussi sur les fichiers eux-mêmes :

2. Analyse de la volumétrie individuelle des fichiers

Autre point, il est possible de déterminer l’espace qu’occupe chaque fichier individuellement. Pour ce faire, il faut se connecter sur la base souhaitée et exécuter la requête suivante :

SELECT table_name, 
pg_relation_size(table_schema || '.' || table_name) As Taille_donnees,
pg_total_relation_size(table_schema || '.' || table_name) As Taille_totale
FROM information_schema.tables
ORDER BY Taille_totale DESC;

Vous obtiendrez ainsi la taille des fichiers les plus volumineux de votre base de données :

Enfin, on peut aussi compter le nombre d’enregistrements présents dans un fichier à l'aide de la requête suivante :
SELECT count(id) from mrp_rl_cost_history;