Récupération à un instant donné (PITR) dans PostgreSQL

La récupération à un instant donné (PITR) est une fonctionnalité robuste dans PostgreSQL qui est devenue encore plus efficace et conviviale avec l’avènement de PostgreSQL. Elle permet aux administrateurs de restaurer une base de données PostgreSQL à un moment spécifique dans le passé. Cela est particulièrement utile si vous gérez la récupération après sinistre pour un système à grande échelle avec une charge de transactions importante.

Ce blog explorera le PITR et vous équipera de connaissances sur les pièges potentiels et leurs solutions, garantissant une mise en œuvre fluide et réussie. Nous partagerons également ses principaux avantages et détaillerons une mise en œuvre étape par étape de PostgreSQL.

Composants Clés

La mise en œuvre du PITR implique deux composants clés :

1. Sauvegarde de Base

Une sauvegarde de base est un instantané de la base de données à un moment spécifique. Elle inclut tous les fichiers de données, fichiers de configuration et métadonnées nécessaires pour restaurer la base de données à son état d’origine. La sauvegarde de base sert de point de départ pour le PITR.

2. Journaux de Pré-écriture (WAL)

Les fichiers WAL enregistrent chaque changement apporté à la base de données. Ces journaux stockent les modifications requises pour récupérer la base de données à son état à un moment spécifique. Lorsque vous effectuez un PITR, vous rejouez les fichiers WAL séquentiellement pour recréer l’état de la base de données souhaité.

Pourquoi Utiliser le PITR ?

Le PITR est bénéfique dans plusieurs scénarios :

Annuler des Changements Accidentels

Des opérations accidentelles, telles qu’une instruction DELETE ou DROP sans clause WHERE, peuvent entraîner une perte de données significative. Avec PITR, vous pouvez récupérer la base de données dans un état juste avant l’erreur, préservant ainsi des données critiques.

Récupérer d’une corruption de données

Les bugs d’application, les pannes matérielles ou la corruption de disque peuvent causer des incohérences de données. PITR vous permet de restaurer un instantané propre de la base de données et de rejouer uniquement les modifications valides, minimisant ainsi les temps d’arrêt et la perte de données.

Restaurer pour des tests ou un débogage

Les développeurs ont souvent besoin de répliquer une base de données de production à des fins de débogage ou de test. PITR permet la création d’un instantané de la base de données à un moment spécifique, facilitant des expériences contrôlées sans affecter les données en direct.

Récupération après sinistre

PITR est essentiel pour les stratégies de récupération après sinistre. En cas de défaillances catastrophiques, telles que des catastrophes naturelles ou des cyberattaques, vous pouvez rapidement restaurer la base de données à son dernier état cohérent, assurant ainsi la continuité des affaires.

Utilisation efficace des ressources

En combinant des sauvegardes de base périodiques avec des fichiers WAL, le PITR minimise le besoin de sauvegardes complètes fréquentes, ce qui permet d’économiser de l’espace de stockage et de réduire les temps de sauvegarde. Le PITR est également une méthode de récupération exacte, permettant de récupérer à une seconde précise et minimisant le risque de perte de données lors d’un incident. Il est suffisamment flexible pour gérer divers scénarios de récupération, d’un retour en arrière d’une seule transaction à une restauration complète de la base de données de manière efficace.

Quoi de neuf dans PostgreSQL 17 pour le PITR ?

PostgreSQL 17 introduit plusieurs améliorations pour le PITR, axées sur la performance, l’utilisabilité et la compatibilité :

Synchronisation des slots de basculement

Les slots de réplication logique prennent désormais en charge la synchronisation lors des basculements. Cela garantit que les WAL nécessaires pour le PITR sont conservés même après un basculement, réduisant ainsi l’intervention manuelle.

Amélioration de la compression WAL

L’algorithme de compression WAL a été mis à jour pour améliorer l’efficacité du stockage, réduisant l’espace nécessaire pour l’archivage des WAL. Cela est particulièrement bénéfique pour les systèmes à grande échelle avec des taux de transactions élevés.

Vitesses de récupération plus rapides

Les optimisations dans le processus de replay WAL entraînent des temps de récupération plus rapides, en particulier pour les grands ensembles de données.

Meilleure compatibilité avec la réplication logique

PITR s’intègre désormais mieux aux configurations de réplication logique, facilitant la récupération des clusters qui utilisent la réplication physique et logique.

Contrôle granulaire de l’archivage WAL

PostgreSQL 17 offre plus de contrôle sur l’archivage WAL, vous permettant d’affiner les politiques de rétention pour correspondre aux exigences de récupération.

Étapes détaillées pour effectuer PITR dans PostgreSQL

Suivez ces étapes pour configurer et effectuer PITR. Avant d’utiliser PITR, vous aurez besoin de :

• Archivage WAL : Activer et configurer l’archivage WAL.
• Sauvegarde de base : Effectuer une sauvegarde de base complète en utilisant pg_basebackup ou pgBackRest.
• Stockage sécurisé : Assurez-vous que les sauvegardes et les fichiers WAL sont stockés en toute sécurité, de préférence hors site.

1. Configurer l’archivage WAL

L’archivage WAL est essentiel pour PITR car il stocke les changements incrémentiels entre les sauvegardes. Pour configurer l’archivage WAL, mettez à jour le fichier postgresql.conf, en définissant :

wal_level = replica          # Ensures sufficient logging for recovery

​

archive_mode = on            # Enables WAL archiving

​

archive_command = 'cp %p /path/to/wal_archive/%f'  # Command to archive WALs

​

max_wal_senders = 3          # Allows replication and archiving

Ensuite, après avoir défini les paramètres de configuration, redémarrez le serveur PostgreSQL :

sudo systemctl restart postgresql

Vérifiez l’état de l’archivage WAL avec la commande suivante :

SELECT * FROM pg_stat_archiver;

Recherchez des erreurs dans la vue pg_stat_archiver ou les journaux PostgreSQL.

2. Effectuer une sauvegarde de base

Effectuez une sauvegarde de base à utiliser comme point de départ pour PITR ; en utilisant pg_basebackup, la commande prend la forme :

pg_basebackup -D /path/to/backup_directory -Fp -Xs -P

Cela crée un instantané de base de données cohérent et garantit que les fichiers WAL sont archivés pour la récupération.

3. Valider l’intégrité de la sauvegarde

Utilisez pg_verifybackup pour valider l’intégrité de votre sauvegarde :

pg_verifybackup /path/to/backup_directory

4. Simuler une défaillance

À des fins de démonstration, vous pouvez simuler une défaillance. Par exemple, supprimer accidentellement des données :

DELETE FROM critical_table WHERE id = 123;

5. Restaurer la sauvegarde de base

Avant de restaurer la sauvegarde de base, arrêtez le serveur PostgreSQL :

sudo systemctl stop postgresql

Ensuite, utilisez la commande suivante pour changer le nom du répertoire de données existant :

mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data_old

Ensuite, remplacez le répertoire de données par la sauvegarde de base :

cp -r /path/to/backup_directory /var/lib/pgsql/17/data

Mettez à jour les permissions sur le répertoire de données :

chown -R postgres:postgres /var/lib/pgsql/17/data

6. Configurer la récupération

Pour activer le mode de récupération, vous devez d’abord créer un fichier recovery.signal dans le répertoire de données PostgreSQL :

touch /var/lib/pgsql/17/data/recovery.signal

Ensuite, mettez à jour postgresql.conf, en ajoutant les paramètres suivants :

restore_command = 'cp /path/to/wal_archive/%f "%p"'  # Restore archived WALs

​

recovery_target_time = '2024-11-19 12:00:00'    # Specify target time

​

Alternatively, use recovery_target_lsn or recovery_target_name for more advanced scenarios.

7. Démarrer PostgreSQL en mode de récupération

Redémarrez le serveur PostgreSQL avec la commande :

sudo systemctl start postgresql

Surveillez les journaux pour le progrès de la récupération :

tail -f /var/lib/pgsql/17/pg_log/postgresql.log

PostgreSQL sortira automatiquement du mode de récupération et deviendra opérationnel lorsque la récupération sera terminée.

8. Vérifier la récupération

Après la récupération, validez l’état de la base de données :

SELECT * FROM critical_table WHERE id = 123;

Résoudre les problèmes potentiels

Fichiers WAL manquants ou corrompus

Problème

Les fichiers WAL nécessaires à la récupération sont manquants ou corrompus.

Solution

• Assurez-vous que les sauvegardes et les archives WAL sont régulièrement validées à l’aide d’outils comme pg_verifybackup.
• Utilisez un stockage redondant pour les archives WAL.

Cible de récupération incorrecte

Problème

La récupération s’arrête à un état non souhaité.

Solution

• Vérifiez le recovery_target_time, recovery_target_lsn ou recovery_target_name.
• Utilisez pg_waldump pour inspecter les fichiers WAL à la recherche d’événements cibles.

Goulots d’étranglement de performance pendant la récupération

Problème

La récupération prend trop de temps en raison de gros fichiers WAL.

Solution

• Optimisez les performances de récupération en augmentant maintenance_work_mem et max_parallel_workers.
• Utilisez la compression WAL pour réduire la taille des fichiers.

Problèmes de décalage d’horloge

Problème

Les horodatages de récupération doivent être alignés en raison de différences d’horloge.

Solution

Synchronisez les horloges des serveurs à l’aide d’outils comme NTP.

Archivage WAL mal configuré

Problème

Un archive_command incorrect provoque des échecs d’archivage WAL.

Solution

• Testez le archive_command manuellement : cp /path/to/test_wal /path/to/wal_archive/.
• Assurez-vous d’avoir des permissions suffisantes pour le répertoire d’archive.

Meilleures pratiques pour PITR

1. Automatisez les sauvegardes : Utilisez des outils comme pgBackRest ou Barman pour des sauvegardes programmées et l’archivage WAL.
2. Surveillez l’archivage WAL : Vérifiez régulièrement pg_stat_archiver pour détecter des problèmes.
3. Validez les sauvegardes : Vérifiez toujours l’intégrité des sauvegardes en utilisant pg_verifybackup.
4. Testez les procédures de récupération : Simulez régulièrement des scénarios de récupération pour garantir votre préparation.
5. Sécurisez les archives WAL : Pour les archives WAL, utilisez un stockage sécurisé et redondant, tel que des services cloud ou des disques configurés en RAID.

Conclusion

La récupération à un instant donné (PITR) est essentielle pour maintenir la fiabilité de la base de données et atténuer la perte de données en cas d’incident. Les améliorations de pgEdge et de PostgreSQL 17 rendent le PITR plus rapide, plus efficace et plus facile à gérer, en particulier pour les systèmes à grande échelle ou hautement disponibles.

Suivre les étapes et les meilleures pratiques de ce guide vous aidera à mettre en œuvre et à gérer efficacement le PITR dans vos environnements PostgreSQL. Des tests et une surveillance réguliers sont essentiels pour garantir que les processus de récupération soient disponibles lorsque vous en avez le plus besoin.