PostgreSQL pg_dump et pg_restore : Guide complet pour sysadmins

PostgreSQL est un système de gestion de bases de données relationnelles (SGBD-R) open source reconnu pour ses performances élevées, sa flexibilité et sa conformité stricte aux standards SQL. Sa capacité à gérer de grandes quantités de données en fait un choix privilégié pour les entreprises et les développeurs souhaitant une solution robuste et évolutive.

Que vous soyez administrateur système ou développeur, il est essentiel de connaître les bonnes pratiques pour assurer la sécurité et l'intégrité de vos bases de données PostgreSQL. Ce guide vous aidera à :

• sauvegarder efficacement vos bases de données,
• sécuriser vos données contre les pertes potentielles,
• restaurer vos bases en cas de besoin.

Sauvegarder vos bases de données PostgreSQL

Sauvegarder une seule base de données

Pour sauvegarder une base de données PostgreSQL dans un fichier dump SQL, utilisez la commande suivante :

pg_dump -h localhost -p 5432 -U <username> -d <nom_base_de_donnees> -f dump.sql

Explications:

• -h localhost: Spécifie l'hôte PostgreSQL
• -p 5432: Spécifie le port de connexion à PostgreSQL
• -U <username> : Spécifie l'utilisateur PostgreSQL.
• -d <nom_base_de_donnees> : Nom de la base de données à sauvegarder.
• -f dump.sql : Nom du fichier de sauvegarde.

Dans certains cas, vous pourriez vouloir générer un dump dans un format custom. Cela est possible en ajoutant l'option -F c à la commande :

pg_dump -U <username> -d <nom_base_de_donnees> -F c -f sauvegarde.dump

Configuration du fichier .pgpass (optionnel)

Le fichier .pgpass permet de stocker les informations d'identification de manière sécurisée et d'automatiser l'accès à PostgreSQL sans avoir à entrer un mot de passe à chaque exécution de commande.

Configuration du fichier .pgpass :

Créez un fichier .pgpass dans le répertoire personnel de l'utilisateur PostgreSQL :

touch ~/.pgpass
chmod 600 ~/.pgpass

Ajoutez les informations de connexion sous le format suivant :

hostname:port:database:username:password

Une fois ce fichier configuré, toutes les commandes PostgreSQL comme pg_dump, pg_restore et psql pourront être exécutées sans nécessiter de saisir un mot de passe manuellement.

Sauvegarder toutes les bases de données

Pour sauvegarder toutes les bases de données, utilisez la commande pg_dumpall :

pg_dumpall -U <username> -f full_dump.sql

Cela génère un fichier SQL contenant toutes les bases de données du cluster.

Sauvegarder uniquement les schémas

Si vous souhaitez sauvegarder uniquement la structure des tables (sans les données) :

pg_dump -U <username> -d <nom_base_de_donnees> -s -f structure.sql

Restaurer vos bases de données PostgreSQL

En cas de problème, voici comment restaurer vos bases PostgreSQL depuis un fichier dump.

Réinstaller PostgreSQL (facultatif)

Si PostgreSQL a été supprimé ou corrompu, réinstallez-le via :

sudo apt install postgresql

Recréer vos bases de données et utilisateurs

Avant de restaurer, assurez-vous que la base de données cible existe :

CREATE DATABASE ma_base_de_donnees;

Créer un utilisateur PostgreSQL :

CREATE USER nouveau_utilisateur WITH PASSWORD 'mot_de_passe';

Attribuer les droits nécessaires à cet utilisateur :

GRANT ALL PRIVILEGES ON DATABASE ma_base_de_donnees TO nouveau_utilisateur;

Restaurer une base de données PostgreSQL

Pour restaurer une base de données depuis un fichier dump au format SQL :

psql -U <username> -d <nom_base_de_donnees> -f full_dump.sql

Si votre sauvegarde est au format personnalisé :

pg_restore -U <username> -d <nom_base_de_donnees> -c sauvegarde.dump

PostgreSQL Cheatsheet : commandes utiles

Afficher les bases de données disponibles :

\l

Se connecter à une base de données :

\c nom_base_de_donnees

Lister les tables d'une base de données :

\dt

Afficher les utilisateurs PostgreSQL :

SELECT usename FROM pg_user;

En suivant ces bonnes pratiques, vous garantissez la sécurité et l'intégrité de vos bases de données PostgreSQL, tout en optimisant vos processus de sauvegarde et de restauration.