Comment migrer les bases de données PostgreSQL : Un guide complet pour les environnements de production

La migration de base de données est l'une des opérations les plus critiques dans la vie d'un développeur ou d'un DBA. Que vous passiez d'un serveur local à un service cloud géré comme Amazon RDS ou Google Cloud SQL, que vous mettiez à niveau vers une version majeure plus récente, ou que vous réorganisiez simplement votre schéma sur plusieurs clusters, les enjeux sont élevés. Une migration échouée peut entraîner une perte de données, une longue période d'indisponibilité et un impact commercial significatif.

Ce guide va au-delà des simples sauvegardes pour explorer des stratégies robustes de qualité production pour la migration des bases de données PostgreSQL. Nous couvrirons les solutions en une seule commande pour les petits ensembles de données, la synchronisation continue des données pour les migrations sans interruption, et les meilleures pratiques pour la validation et le retour arrière.

Stratégie 1 : L'approche classique pg_dump

Pour les petites bases de données (généralement inférieures à 100 Go) ou lors de fenêtres de maintenance planifiées où une interruption de service est acceptable, pg_dump reste la méthode la plus simple et la plus fiable. Il génère un script SQL ou une archive au format personnalisé qui peut être restaurée sur le serveur de destination.

La clé pour utiliser pg_dump efficacement dans un contexte de migration est d'utiliser le format personnalisé (-Fc), qui permet une restauration parallèle et offre une plus grande flexibilité.

# Depuis le serveur source
pg_dump -Fc -h source_host -U source_user -d my_database -f backup.dump

# Sur le serveur de destination
pg_restore -h dest_host -U dest_user -d new_database backup.dump

Bien que simple, cette méthode nécessite une période d'interruption où l'application ne peut pas écrire dans la base de données. Si vous avez une application en direct, vous devez vous assurer que l'application est mise hors ligne ou pointée vers un réplica en lecture seule pendant le processus de sauvegarde pour garantir la cohérence des données.

Stratégie 2 : Migration sans interruption avec la réplication logique

Pour les applications critiques qui ne peuvent pas se permettre d'interruptions, la réplication logique est la référence. Cette méthode vous permet de maintenir les bases de données source et de destination synchronisées en continu, vous permettant de basculer le trafic à un moment précis.

Étape 1 : Préparer la destination

Créez d'abord une base de données vide sur le serveur cible avec le même schéma. Assurez-vous que le wal_level est défini sur logical sur les serveurs source et de destination.

Étape 2 : Créer la publication et l'abonnement

Sur le serveur source, créez une publication pour les tables que vous souhaitez migrer :

CREATE PUBLICATION my_migration_pub FOR TABLE users, orders, products;

Sur le serveur de destination, créez un abonnement qui se connecte à la source :

CREATE SUBSCRIPTION my_migration_sub
CONNECTION 'host=source_ip dbname=my_database user=replicator password=secret'
PUBLICATION my_migration_pub;

PostgreSQL commencera désormais à synchroniser les données de la source vers la destination. Vous pouvez suivre la progression via la vue système pg_stat_subscription. Une fois la synchronisation initiale terminée et que la destination a rattrapé les modifications en direct, vous pouvez procéder au basculement.

Validation et basculement

Avant de basculer le trafic, effectuez une validation finale. Vérifiez les comptes de lignes et les sommes de contrôle pour les tables critiques sur les deux instances. Lorsque vous êtes prêt à basculer, arrêtez les écritures sur la base de données source (ou mettez en pause l'abonnement sur la destination pour garantir la cohérence).

-- Mettre en pause l'abonnement pour garantir la synchronisation finale
SELECT pg_subscription_rel.*, pg_stat_replication.*
FROM pg_subscription_rel 
JOIN pg_stat_replication ON pg_subscription_rel.sql_localid = pg_stat_replication.pid;

Une fois que le retard de réplication est nul, désactivez l'abonnement, modifiez la chaîne de connexion de votre application pour qu'elle pointe vers la nouvelle base de données, et réactivez les écritures. Surveillez attentivement l'application pendant les premières heures pour garantir sa stabilité.

Conclusion

Le choix de la bonne stratégie de migration dépend de la taille de vos données, du temps d'indisponibilité acceptable et de la complexité de votre infrastructure. Pour les petits changements, pg_dump est suffisant. Pour les exigences de niveau entreprise, la réplication logique offre la sécurité et la continuité nécessaires pour des transitions transparentes. Testez toujours votre processus de migration dans un environnement de préproduction avant de le tenter en production pour atténuer les risques et garantir une opération fluide.