Espace disque et BDD Postgres
La manipulation des Backups (.dump) de PostgreSQL peut entrainer une saturation d’espace disque et il convient lors de la duplication (ou de la restauration) de bases d’attacher une vigilance particulière sur la volumétrie du disque disponible sur le serveur.
Ce document a pour but de vous « éclairer » sur cet aspect.
- Prérequis
- Manipulation des bases de données
- Présentation
- Restauration d'une base de test
- Impact d'une montée de version (sql-update)
- Déterminer la volumétrie d’une base de données
- Echec de restauration de Base de données
- Pour aller plus loin
- Procédures
Prérequis
Les principales commandes Ubuntu utilisées dans ce document seront :
dfqui nous donne le taux d’occupation disque,duqui nous donne la taille d’un répertoire,grepqui nous permet d’effectuer une recherche sur un fichier « txt »,psql,pg_dump&pg_restoresont propres à PostgreSQL.
Pour avoir une information complémentaire sur l’usage de ces commandes, taper en ligne de commande man <cmd> pour avoir un complément d’information sur leur usage respectif sur votre serveur. Par exemple :
La plupart des commandes fournies dans cette documentation le sont à titre indicatif : elles fonctionnent parfaitement sur un serveur PostgreSQL 10 (Ubuntu 18) mais peuvent être à adapter en fonction de votre système d’exploitation ou votre SGBD.
Manipulation des bases de données
Présentation
Sous PostgreSQL, la sauvegarde et la restauration de bases s’effectuent par deux commandes distinctes : pg_dump et pg_restore. Chacune de ces deux commandes exploitent des fichiers « dump », l’équivalent sous SQL Server du « .bak ».
Si on regarde un peu plus en détail le contenu d’un fichier .dump, on constate que le .dump est une succession de commande SQL, que le moteur va « rejouer », récréant ainsi la structure de la base de données (ses extensions, ses fonctions, ses tables, etc..) et les enregistrements contenus dans chacun des fichiers au moment où la sauvegarde a été produite.
Sur Open-Prod, il existe deux type des sauvegardes : celle en .dump, et celle avec le filestore inclus.
Leur contenu est le suivant :
1. Si on produit une sauvegarde avec pg_dump, on obtiendra un fichier directement exploitable dans une version compatible pour tout moteur PostgreSQL de la même version :
2. Si on produit une sauvegarde avec le filestore inclus, le fichier sera produit au format zip et contiendra les fichiers suivants :
Le zip sera porteur du nom de la base sur le serveur d’origine et le .dump sera intégré dans ce dernier.
Attention ! la volumétrie du .dump n’a rien a voir avec l’espace disque qu’utilisera la base de données une fois restaurée. En effet, le .dump est un fichier « txt » qui ne contient que les commandes permettant de réalimenter la base. Avec des champs « vides » par exemple occuperont nécessairement un espace dans un SGBD mais pas dans le dump.
Restauration d'une base de test
La première étape est de vérifier l'espace disque sur le serveur :
Nous constatons ici que le disque dur a une taille de 67 Go utiles et que nous avons une disponibilité de 51 Go.
Nous allons donc restaurer notre fichier dump cité plus haut sur ce disque dur au travers de l'interface d'Open-Prod.
Avec la commande tail -f /var/log/openprod/openprod-server.log, il est possible de "suivre" les traitements réalisés par Open-Prod sur le moment. Nous trouvons l’information suivante :
Le serveur exécute la commande pg_restore sur le fichier « /tmp/tmp5SX1l3 », préalablement téléchargé via le navigateur. Une fois restauré, on contrôle à nouveau l'espace disque :
Nous constatons que le fichier dump de 1.13 Go, une fois restauré occupe +/- 12 Go d’espace disque.
Impact d'une montée de version (sql-update)
De nouveau, une montée de version d’Open-Prod peut fortement impacter l'espace disque de l'environnement. Par exemple, si de nouveaux champs fonctionnels sont mis en place par l’éditeur (ou vos modules) dans un grand nombre d’enregistrements, l’occupation définitive du disque peut arriver à saturation.
Naturellement, PostgreSQL va créer des fichiers d’échange temporaire sur disque et il convient que de l’espace disque soit disponible en quantité suffisante lors de l’opération.
Si nous poursuivons sur l'exemple précédent, voici l'espace disque suite à un sql-update sur notre base de donnée :
Après la montée de version de notre base dans un format ancien, notre espace disque disponible est tombé à 7.5 Go, la taille de la base a donc augmenté de 32 Go ! Prenez soin d’avoir toujours le maximum d’espace disque disponible lors d’une montée de version et de tester la taille définitive de la future base sur un serveur de test.
Déterminer la volumétrie d’une base de données
Il existe de nombreux moyen pour déterminer la taille d’une base de données spécifique.
Via la commande sudo -u postgres psql puis \l+ :
Via la commande du si on connait le lieu de stockage de nos bases. Ici la base « BDD » correspond au répertoire 94118, soit 42477984 Mo :
Depuis l'interface de pgadmin4 et via la requête SELECT pg_size_pretty( pg_database_size('BDD') )
Echec de restauration de Base de données
Saturer l’espace disque d’un serveur de production va fatalement interrompre/impacter un grand nombre de services. Veiller toujours à avoir de l’espace en adéquation avec la manipulation à réaliser. Par exemple, si on veut dupliquer une base par la production d’un fichier zip, le serveur va occuper l’espace de la base de données initiale plus la base de données compressée (dans le .zip uploadé) et la taille de la base de données de destination.
Voici un exemple d'erreur qui peut être rencontrée :
Et le log joint :
Si vous obtenez ce type de message lors d’une restauration, contrôler rapidement l’espace à disposition sur le serveur et effectuer le correctif nécessaire.
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;