Sauvegardes logiques ou physiques
pg_dump et sauvegardes de base.
Sauvegardes logiques ou physiques est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.
Qu’est-ce qu’une sauvegarde de base de données ?
Une sauvegarde est une copie des données de votre base de données qui peut servir à restaurer le système après une perte de données, une corruption ou un sinistre. Sans sauvegardes fiables, une simple défaillance matérielle ou un DELETE accidentel peut détruire définitivement des mois ou des années de données.
PostgreSQL fournit deux grandes catégories de stratégies de sauvegarde : sauvegardes logiques et sauvegardes physiques. Chacune présente des caractéristiques, des cas d’utilisation et des compromis distincts que tout DBA doit comprendre.
Explication des sauvegardes logiques
Une sauvegarde logique exporte la base de données sous forme d’instructions SQL lisibles par l’humain — CREATE TABLE, INSERT, COPY et commandes similaires. L’outil le plus courant pour cela dans PostgreSQL est pg_dump.
Comme la sortie est du SQL brut, une sauvegarde logique est portable : vous pouvez la restaurer vers une autre version de PostgreSQL, un autre système d’exploitation ou même restaurer sélectivement des tables ou des schémas individuels. Le compromis est que l’exportation et la restauration de grandes bases de données peuvent être lentes.
Utiliser pg_dump pour créer une sauvegarde logique
L’utilitaire pg_dump s’exécute depuis la ligne de commande, et non dans SQL. Il se connecte à un serveur PostgreSQL en fonctionnement et exporte la base de données choisie. Vous pouvez produire du SQL brut, un format compressé personnalisé ou un format en répertoire.
Le SQL ci-dessous simule ce qu’une sauvegarde logique capture : la structure et les données d’une table sous forme d’instructions reproductibles.
-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer, amount, created_at) VALUES
('Alice', 149.99, '2024-01-15 09:30:00+00'),
('Bob', 89.50, '2024-01-16 14:00:00+00'),
('Carol', 210.00, '2024-01-17 11:15:00+00');Formats de sortie de pg_dump
pg_dump prend en charge quatre formats de sortie, chacun adapté à différentes méthodes de restauration :
- plain — un script SQL brut, lisible dans n’importe quel éditeur de texte.
- custom — un format binaire compressé, le plus flexible, qui prend en charge la restauration en parallèle.
- directory — un fichier par table, qui prend en charge l’exportation et la restauration en parallèle.
- tar — une archive tar du format en répertoire.
Le format custom est recommandé pour les grandes bases de données, car pg_restore peut restaurer les objets en parallèle à l’aide de -j N processus.
-- Checking which databases exist before choosing what to back up
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY pg_database_size(datname) DESC;Restaurer une sauvegarde logique
Une sauvegarde logique au format SQL brut se restaure avec psql. Une sauvegarde au format custom nécessite pg_restore. Les deux outils rejouent les instructions SQL pour recréer les tables, les index, les contraintes et les données.
Comme les sauvegardes logiques contiennent du SQL, vous pouvez les modifier avant de les restaurer — par exemple pour ne restaurer qu’une seule table ou pour changer le nom d’un schéma. Cette flexibilité est l’un des principaux avantages de l’approche logique.
-- After restoring a backup, verify row counts match expectations
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;Explication des sauvegardes physiques
Une sauvegarde physique (également appelée sauvegarde de base) copie les fichiers de données bruts utilisés par PostgreSQL sur disque — les pages, les segments WAL (journal à écriture anticipée) et les fichiers de configuration. Le résultat est un instantané binaire de l’ensemble du cluster à un instant donné.
Les sauvegardes physiques sont généralement bien plus rapides à restaurer pour les grandes bases de données, car aucune réexécution de SQL n’est nécessaire ; PostgreSQL relit simplement les fichiers à leur emplacement et rejoue WAL pour atteindre un état cohérent.
pg_basebackup : effectuer une sauvegarde physique
pg_basebackup est l’outil PostgreSQL standard pour les sauvegardes physiques. Il diffuse le répertoire de données depuis un serveur primaire en fonctionnement via une connexion de réplication. Vous avez besoin d’un utilisateur doté des privilèges de réplication et de wal_level défini sur replica ou une valeur supérieure.
Dans la base de données, vous pouvez interroger les paramètres de réplication pour confirmer que le serveur est correctement configuré avant de tenter une sauvegarde de base.
-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'max_wal_senders',
'archive_mode',
'archive_command'
)
ORDER BY name;Archivage de WAL et récupération à un instant donné
Une sauvegarde de base capture un instant donné. Pour effectuer une récupération à n’importe quel point après cette sauvegarde, PostgreSQL rejoue les segments WAL archivés — on parle de récupération à un instant donné (PITR).
Lorsque archive_mode = on et archive_command sont configurés, PostgreSQL copie les segments WAL terminés vers un emplacement d’archive. Pendant la récupération, restore_command récupère ces segments afin que le serveur puisse les rejouer jusqu’à l’heure cible souhaitée.
-- Inspect current WAL position and archive status
SELECT
pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
archived_count,
failed_count,
last_archived_wal,
last_archived_time
FROM pg_stat_archiver;Comparer les sauvegardes logiques et physiques
Le choix entre les sauvegardes logiques et physiques dépend de vos exigences :
- Logique (pg_dump) : portable d’une version à l’autre, permet une restauration partielle, lisible par l’humain, mais lente pour les grandes bases de données et sans granularité au niveau des sous-transactions.
- Physique (pg_basebackup + WAL) : restauration rapide pour les grands clusters, prend en charge PITR, spécifique à une version (doit être restaurée vers la même version majeure) et restaure l’intégralité du cluster — vous ne pouvez pas restaurer une seule table.
Les environnements de production utilisent généralement les deux : des sauvegardes de base physiques nocturnes avec archivage continu de WAL, ainsi que des exports logiques périodiques pour la portabilité et les restaurations ciblées.
Vérifier l’intégrité des sauvegardes
Une sauvegarde qui n’a jamais été testée n’est pas une sauvegarde — c’est un espoir. Validez toujours les sauvegardes en les restaurant dans un environnement de test et en vérifiant les données.
Pour les sauvegardes logiques, une vérification rapide de l’intégrité consiste à compter les lignes et à comparer les sommes de contrôle. Pour les sauvegardes physiques, PostgreSQL 14+ a introduit pg_verifybackup, qui vérifie le fichier manifeste écrit par pg_basebackup.
-- After a test restore, compare row counts across critical tables
SELECT
relname AS table_name,
n_live_tup AS live_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;Surveillance et planification des sauvegardes
Automatiser et surveiller les sauvegardes est tout aussi important que de les effectuer. Suivez la date de leur dernière exécution, leur durée et leur réussite ou leur échec. PostgreSQL expose des métadonnées utiles à cette fin.
Pour les sauvegardes physiques, pg_stat_archiver affiche le dernier archivage réussi et les éventuels échecs. Pour les sauvegardes logiques, encapsulez pg_dump dans un script qui consigne l’heure de début, l’heure de fin, la taille du fichier et le code de sortie dans une table de surveillance ou un système d’alerte.
-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type TEXT NOT NULL CHECK (backup_type IN ('logical', 'physical')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
size_bytes BIGINT,
status TEXT NOT NULL DEFAULT 'running',
notes TEXT
);
-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;Sauvegardes logiques et physiques : vérification rapide
Testez votre compréhension des stratégies de sauvegarde logiques et physiques dans PostgreSQL.
Récapitulatif de la leçon : sauvegardes logiques et physiques
Dans cette leçon, vous avez découvert deux stratégies fondamentales de sauvegarde PostgreSQL :
- Les sauvegardes logiques utilisent
pg_dumppour exporter les bases de données sous forme d’instructions SQL. Elles sont portables, lisibles par l’humain et permettent les restaurations partielles, mais peuvent être lentes pour les très grandes bases de données. - Les sauvegardes physiques utilisent
pg_basebackuppour copier les fichiers de données bruts. Associées à l’archivage de WAL, elles permettent des restaurations rapides et la récupération à un instant donné, mais sont spécifiques à une version et restaurent toujours l’intégralité du cluster. - Les systèmes de production combinent généralement les deux stratégies : des sauvegardes de base physiques avec archivage de WAL pour une récupération rapide et précise, ainsi que des exports logiques périodiques pour la portabilité.
- Testez toujours la restauration de vos sauvegardes. Une sauvegarde non testée ne peut pas être considérée comme fiable en cas de sinistre réel.
Comprendre ces deux approches est essentiel pour concevoir un plan robuste de reprise après sinistre pour tout déploiement PostgreSQL.
Questions Fréquemment Posées
La leçon « Sauvegardes logiques ou physiques » est-elle gratuite ?
Oui — le texte complet de « Sauvegardes logiques ou physiques » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Sauvegardes logiques ou physiques » ?
pg_dump et sauvegardes de base. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Academy ?
Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.
Combien de temps prend la leçon « Sauvegardes logiques ou physiques » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?
Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Sauvegardes logiques ou physiques
- Récupération à un instant donné
- Tester vos restaurations
- Planification de la reprise après sinistre