Comment configurer la réplication physique avec PostgreSQL
Réplication physique ou en streaming
La solution de réplication la plus couramment utilisée est Journal d'écriture (WAL) Réplication d'expédition ou de streaming. Le serveur de base de données de secours ou l'esclave de réplication est configuré pour établir la connexion avec le serveur primaire / maître. Les enregistrements WAL sont diffusés vers le serveur de secours avant de remplir le fichier WAL. Les données sont transmises au serveur de secours en mode asynchrone par défaut. Cela signifie que les données sont transmises au serveur de secours après avoir engagé la transaction dans le serveur principal. Cela peut entraîner une perte de données car si le serveur maître se bloque sans engager de transaction, cette transaction ne sera pas reproduite au serveur de secours.
L'architecture de la réplication physique
Un processus d'arrière-plan appelé Wal Sender démarre sur la machine principale après avoir configuré les configurations de la réplication physique. Il accepte une demande de la veille et diffuse les enregistrements de WAL à la veille. Un autre processus d'arrière-plan appelé le récepteur WAL commence sur la machine d'esclaves qui reçoit et applique les modifications de la base de données principale à la base de données de l'esclavage. La réplication physique peut être définie par le diagramme suivant.
Caractéristiques de réplication physique
Les données entières d'un seul cluster sont copiées par le processus de réplication physique, où le cluster est l'ensemble des bases de données gérées par la base de données PostgreSQL. La machine source est appelée serveur principal et la machine de destination est appelée le serveur de secours. Certaines caractéristiques importantes de la réplication physique ont été mentionnées ci-dessous.
Fichiers wal
La série commandée de WAL Records est générée par le serveur PostgreSQL. Ces enregistrements seront transportés vers une autre machine en utilisant la réplication physique, et la modification sera effectuée dans la base de données locale. Les enregistrements WAL sont divisés en fichiers de taille égale qui sont appelés segments WAL. Ces fichiers sont créés sur le répertoire PG_WAL, et les anciens fichiers WAL sont supprimés lorsqu'ils ne sont plus nécessaires.
Standard chaud, froid et chaud
Le serveur de secours chaud est utilisé pour stocker les données à jour et permettre aux clients d'exécuter la transaction en lecture seule. Le serveur de secours froid n'est pas opérationnel normalement, et il commence lorsqu'une échec se produit. Le serveur de secours chaud fonctionne similaire au serveur de secours chaud, sauf qu'il ne peut pas établir la connexion avec le client.
Mode de récupération
Les serveurs de secours chauds et chauds s'exécutent en mode de récupération, et Postgres importera et appliquera les fichiers WAL générés par un serveur principal en mode de récupération.
Avantages de la réplication physique ou en streaming
- Il peut être utilisé pour maintenir la sauvegarde de la base de données et aide à récupérer les données lorsque le serveur principal se bloque.
- Il vaut mieux utiliser des opérations en lecture seule.
- Il garantit qu'une copie de la dernière opération du serveur principal sera enregistrée.
- Un ou plusieurs serveurs de secours peuvent être connectés au serveur principal, et les flux d'informations de journal du serveur principal vers tous les serveurs de secours connectés. Si l'un des serveurs de secours est déconnecté ou retardé pour une raison quelconque, le streaming se poursuivra pour un autre serveur de secours.
- Si le serveur principal est déconnecté ou arrêté pour une raison.
Inconvénients de la réplication physique ou en streaming
- Il reproduit les données de manière asynchrone par défaut. Cela signifie que les fichiers WAL qui ne sont pas copiés sur le serveur de secours peuvent être supprimés lorsque des modifications sont effectuées dans le serveur principal. Il est préférable de définir une valeur plus élevée dans les Wal_Keep_Segments pour éviter la perte de données.
- Il ne prend pas en charge la réplication entre différents serveurs postgresql.
- Les données ne sont pas plus sécurisées dans cette réplication car les données sensibles peuvent être extraites sans authentification utilisateur.
- Il ne tronque pas la commande.
- Il ne prend pas en charge les tables étrangères, la vue et les tables racine de partition.
- Il ne prend pas en charge la réplication des grands objets.
- Il ne prend pas en charge les commandes de schéma de base de données et de DDL.
Configuration de la réplication physique
Les étapes de l'implémentation de la réplication logique dans la base de données PostgreSQL ont été présentées dans cette partie de ce tutoriel.
Conditions préalables
Configurer le maître et les nœuds répliques
Vous pouvez définir le maître et les nœuds répliques de deux manières. Une façon consiste à utiliser deux ordinateurs distincts où le système d'exploitation Ubuntu est installé, et une autre façon consiste à utiliser deux machines virtuelles qui sont installées sur le même ordinateur. Le processus de test du processus de réplication physique sera plus facile si vous utilisez deux ordinateurs distincts pour le nœud maître et le nœud de réplique car une adresse IP spécifique peut être facilement attribuée pour chaque ordinateur. Mais si vous utilisez deux machines virtuelles sur le même ordinateur, l'adresse IP statique devra être définie pour chaque machine virtuelle et s'assurer que les deux machines virtuelles peuvent communiquer entre elles via l'adresse IP statique. J'ai utilisé deux machines virtuelles pour tester le processus de réplication physique dans ce tutoriel. Le nom d'hôte du nœud maître a été défini sur Fahmida-Master, et le nom d'hôte du nœud de réplique a été défini sur fahmida-esclave ici.
Installez PostgreSQL sur les nœuds maître et répliques
Vous devez installer la dernière version du serveur de base de données PostgreSQL sur deux machines avant de démarrer les étapes de ce tutoriel. La version 14 de PostgreSQL a été utilisée dans ce tutoriel. Exécutez les commandes suivantes pour vérifier la version installée du PostgreSQL dans le nœud maître.
Exécutez la commande suivante pour devenir un utilisateur racine.
$ sudo -i
Exécutez les commandes suivantes pour vous connecter en tant qu'utilisateurs de Postgres avec des privilèges de superutiliser et établir une connexion avec la base de données PostgreSQL.
$ su - Postgres
$ psql
La sortie montre que PostgreSQL version 14.4 a été installé sur Ubuntu version 22.04.1.
Vous devez installer la même version postgresql dans le nœud de réplique car la réplication logique ne peut pas être configurée entre différentes versions du serveur PostgreSQL.
Configuration du nœud principal
Les configurations nécessaires pour le nœud principal ont été présentées dans cette partie du tutoriel. Les tâches suivantes seront effectuées dans le nœud principal.
- Modifier le postgresql.Fichier Conf pour configurer l'adresse IP et le niveau WAL.
- Créez un utilisateur de rôle avec ce mot de passe pour établir une connexion avec le nœud principal à partir du nœud de réplique.
- Modifier pg_hba.Fichier Conf pour ajouter des informations de connexion réseau
Modifier le postgresql.confli déposer
Vous devez configurer l'adresse IP du nœud principal dans le fichier de configuration PostgreSQL nommé postgresql.confli qui est situé sur l'emplacement, / etc / postgresql / 14 / main / postgresql.confli. Connectez-vous en tant qu'utilisateur racine dans le nœud principal et exécutez la commande suivante pour modifier le fichier.
$ nano / etc / postgresql / 14 / main / postgresql.confli
Découvrez le écouter_address variable dans le fichier, supprimez le hachage (#) du début de la variable pour décommenter la ligne. Vous pouvez définir un astérisque (*) ou l'adresse IP du nœud principal pour cette variable. Si vous définissez Asterisk (*), le serveur principal écoutera toutes les adresses IP. Il écoutera l'adresse IP spécifique si l'adresse IP du serveur principal est définie sur cette variable. Dans ce didacticiel, l'adresse IP du serveur principal qui a été définie sur cette variable est 192.168.dix.5.
écouter_addressess = “"Ensuite, découvrez le Wal_level variable pour définir le type de réplication. Ici, la valeur de la variable sera un réplique.
wal_level = répliqueExécutez la commande suivante pour redémarrer le serveur PostgreSQL après avoir modifié le postgresql.confli déposer.
$ systemctl redémarrer postgresql
*** Remarque: Après la configuration de la configuration, si vous faites face à un problème démarrant le serveur PostgreSQL, exécutez les commandes suivantes pour la version 14 PostgreSQL.
$ sudo chmod 700 -r / var / lib / postgresql / 14 / main
$ sudo -i -u postgres
# / usr / lib / postgresql / 14 / bin / pg_ctl redémarrage -d / var / lib / postgresql / 14 / main
Vous pourrez vous connecter avec le serveur PostgreSQL après avoir exécuté la commande ci-dessus.
Créer un rôle / utilisateur pour la réplication
Un rôle / un utilisateur avec une autorisation spécifique est requis pour la réplication. Exécutez la commande SQL suivante pour créer un rôle avec l'utilisateur pour la réplication.
# Créer un répliquant de rôle avec le mot de passe de connexion de réplication '12345';La sortie suivante apparaîtra si le rôle est créé avec succès.
Modifier le pg_hba.confli déposer
Vous devez configurer l'adresse IP du nœud secondaire dans le fichier de configuration PostgreSQL nommé pg_hba.confli qui est situé sur l'emplacement, / etc / postgresql / 14 / main / pg_hba.confli. Connectez-vous en tant qu'utilisateur racine dans le nœud principal et exécutez la commande suivante pour modifier le fichier.
$ nano / etc / postgresql / 14 / main / pg_hba.confli
Ajouter les informations suivantes à la fin de ce fichier.
hôte / 32 scram-sha-256L'IP du serveur esclave est défini sur «192.168.dix.dix" ici. Selon les étapes précédentes, la ligne suivante a été ajoutée au fichier.
Réplication de la réplication de l'hôte 192.168.dix.10/32 SCRAM-SHA-256Redémarrer le serveur postgresql
Exécutez la commande suivante pour redémarrer le serveur PostgreSQL en tant qu'utilisateur racine.
$ systemctl redémarrer postgresql
Configuration du nœud de réplique
Les tâches nécessaires seront effectuées pour le nœud de réplique où la copie de la base de données principale sera stockée. Le contenu de la base de données existante du nœud de réplique sera supprimé pour continuer à sauvegarder la base de données du nœud principal et faire le serveur postgresql du nœud de réplique en lecture seule.
Arrêtez le serveur PostgreSQL sur le nœud de réplique
Exécutez la commande suivante après la connexion en tant qu'utilisateur racine pour arrêter le serveur PostgreSQL.
$ systemctl stop postgresql
Supprimer le contenu existant du nœud de réplique
Exécutez la commande suivante pour supprimer le contenu de la base de données existante du serveur postgresql du nœud de réplique. Il est nécessaire à des fins de réplication. Le nœud de réplique sera travaillé dans un mode en lecture seule après avoir exécuté la commande suivante
$ rm -rf / var / lib / postgresql / 14 / main / *
Tester le processus de réplication physique
Vous devez créer une base de données avec une ou plusieurs tables dans le nœud principal pour tester si la réplication physique fonctionne correctement ou non. Ici, la base de données existante nommée postgres du serveur principal a été utilisé à des fins de test. Si vous le souhaitez, vous pouvez créer le tableau en créant une nouvelle base de données. Les tâches suivantes seront effectuées dans cette partie du tutoriel.
- Ajoutez une nouvelle table dans la base de données sélectionnée du nœud principal.
- Ajouter un enregistrement à la table.
- Stockez la copie de la base de données du nœud principal au nœud de réplique.
- Créer une table dans le serveur principal
Connectez-vous à la base de données PostgreSQL du nœud principal et exécutez l'instruction SQL suivante pour créer un employés table dans la base de données existante postgres. Si vous le souhaitez, vous pouvez créer une nouvelle base de données et créer le tableau en sélectionnant la nouvelle base de données. Je n'ai pas créé de nouvelle base de données ici. Ainsi, le tableau sera créé sur la base de données par défaut. Le tableau des employés contiendra 5 champs. Ce sont des identifiants, un nom, une adresse, un e-mail et un téléphone.
# Créer des employés de table (Clé primaire en série ID,
nom varchar (15) pas null,
Texte d'adressage non nul,
Email Varchar (30),
Téléphone char (14) pas nul);
Exécutez l'instruction SQL suivante pour insérer un enregistrement dans la table.
# Insérer dans les employés (nom, adresse, e-mail, téléphone)Valeurs ('Farheen Hasan', '12 / A, Dhanmondi, Dhaka.',' ar @ gmail.com ',' +8801826783423 ');
Exécutez l'instruction SQL suivante pour lire le contenu du tableau des employés.
# SELECT * parmi les employés;
Si le tableau est créé et qu'un enregistrement est inséré dans le tableau avec succès, la sortie suivante apparaîtra après avoir exécuté la requête sélectionnée. Un enregistrement a été inséré dans la table qui est montrée dans la sortie.
Copiez la base de données sur un serveur de réplique
Connectez-vous à la base de données PostgreSQL du serveur de réplique et exécutez l'instruction SQL suivante pour créer une copie du postgres base de données du serveur principal dans le serveur de réplique. Après avoir exécuté l'instruction, il demandera le mot de passe de l'utilisateur qui a été créé à l'étape précédente. Si l'authentification est effectuée avec succès, alors la réplication sera lancée.
# pg_basebackup -r -h 192.168.dix.5 -U répliseur -d / var / lib / postgresql / 14 / main -pLa sortie suivante montre que la réplication est effectuée avec succès et 34962 Ko a été copiée.
Exécutez la commande suivante après avoir terminé la réplication en tant qu'utilisateur racine pour redémarrer le serveur PostgreSQL dans le nœud de réplique.
$ systemctl redémarrer postgresql
Maintenant, connectez-vous au serveur PostgreSQL sur le nœud de réplique et exécutez l'instruction SQL suivante pour vérifier si le employés' La table a été copiée dans le nœud de réplique ou non.
# SELECT * parmi les employés;La sortie suivante montre le contenu du employés' Tableau du réplique le nœud est le même que le contenu du employés' Tableau du primaire nœud.
Vous pouvez ajouter un ou plusieurs enregistrements ou mettre à jour des enregistrements ou supprimer des enregistrements dans le tableau des employés du nœud principal ou ajouter une ou plusieurs tables dans la base de données sélectionnée du nœud principal et vérifier la base de données du nœud de réplique pour vérifier que le contenu mis à jour de la base de données principale est répliquée correctement dans la base de données du nœud de réplique ou non.
Insérez de nouveaux enregistrements dans le nœud principal:
Maintenant, exécutez la commande INSERT suivante pour ajouter trois autres enregistrements dans le tableau des employés du postgres base de données située dans le primaire nœud.
# Insérer dans les employés (nom, adresse, e-mail, téléphone)Valeurs ('Abir Hossain', '10, Jigatola, Dhaka.',' Abir @ gmail.com ',' +8801888564345 '),
('Nila Chowdhury', '67 / B, Mirpur, Dhaka.',' nila @ gmail.com ',' +8801799453123 '),
('Mizanur Rahman', '8 / C, Malibag, Dhaka.',' Mizan @ gmail.com ',' +8801957864564 ');
Exécutez la commande suivante pour vérifier le contenu actuel du tableau des employés dans le nœud principal.
# SELECT * parmi les employés;
La sortie suivante montre que trois nouveaux enregistrements ont été correctement insérés dans le tableau.
Vérifiez le nœud de réplique après l'insertion:
Maintenant, vous devez vérifier si le tableau des employés du nœud de réplique a été mis à jour ou non. Connectez-vous au serveur postgresql du nœud de réplique et exécutez la commande suivante pour vérifier le contenu du tableau des employés.
# SELECT * parmi les employés;La sortie suivante montre que trois nouveaux enregistrements ont été insérés dans le employés' Tableau du réplique nœud qui a été inséré dans le primaire nœud du employés' tableau. Ainsi, les modifications dans la base de données principale ont été répliquées correctement dans le nœud de réplique.
Mettre à jour l'enregistrement dans le nœud principal:
Exécutez la commande de mise à jour suivante qui mettra à jour la valeur du champ de téléphone où la valeur du champ de nom est «Nila Chowdhury». Il n'y a qu'un seul enregistrement dans le employés' table qui correspond à l'état de la requête de mise à jour.
# Mise à jour des employés set Téléphone = «+8801911111111» où name = «Nila Chowdhury»;Exécutez la commande suivante pour vérifier le contenu actuel du employés' table dans le primaire nœud.
# SELECT * parmi les employés;La sortie suivante montre que le téléphone La valeur de champ de l'enregistrement particulier a été mise à jour après l'exécution de la requête de mise à jour.
Vérifiez le nœud de réplique après la mise à jour:
Maintenant, vous devez vérifier si le tableau des employés du nœud de réplique a été mis à jour ou non. Connectez-vous au serveur postgresql du nœud de réplique et exécutez la commande suivante pour vérifier le contenu du tableau des employés.
# SELECT * parmi les employés;La sortie suivante montre qu'un enregistrement a été mis à jour dans le employés' Tableau du réplique nœud, qui a été mis à jour dans le primaire nœud du employés' tableau. Ainsi, les modifications dans la base de données principale ont été répliquées correctement dans le nœud de réplique.
Supprimer l'enregistrement dans le nœud principal:
Exécutez la commande de suppression suivante qui supprimera un enregistrement du employés' Tableau du primaire Node où la valeur du champ de messagerie est «abir @ gmail.com ». Il n'y a qu'un seul enregistrement dans le employés' table qui correspond à l'état de la requête de suppression.
# Supprimer des employés où email = 'abir @ gmail.com ';Exécutez la commande suivante pour vérifier le contenu actuel du employés' table dans le primaire nœud.
# SELECT * parmi les employés;La sortie suivante montre qu'un enregistrement a été supprimé après avoir exécuté la requête de suppression.
Vérifiez le nœud de réplique après la suppression de l'enregistrement:
Maintenant, vous devez vérifier si le tableau des employés du nœud de réplique a été supprimé ou non. Connectez-vous au serveur postgresql du nœud de réplique et exécutez la commande suivante pour vérifier le contenu du tableau des employés.
# SELECT * parmi les employés;La sortie suivante montre qu'un enregistrement a été supprimé dans le employés' Tableau du réplique nœud, qui a été supprimé dans le primaire nœud du employés' tableau. Ainsi, les modifications dans la base de données principale ont été répliquées correctement dans le nœud de réplique.
Conclusion
Le but de la réplication physique pour maintenir la sauvegarde de la base de données, l'architecture de la réplication physique, les avantages et les inconvénients de la réplication physique et les étapes de la mise en œuvre de la réplication physique dans la base de données postgresql ont été expliqués dans ce tutoriel avec des exemples. J'espère que le concept de réplication physique sera effacé pour les utilisateurs et que les utilisateurs pourront utiliser cette fonctionnalité dans leur base de données PostgreSQL après avoir lu ce tutoriel.