Comment configurer la réplication maître-esclave pour PostgreSQL 9.6 sur Ubuntu 16.04

PostgreSQL ou Postgres est un système de gestion de base de données objet-relationnelle open source (ORDBMS) avec plus de 15 ans de développement actif. C’est un puissant serveur de base de données capable de gérer des charges de travail élevées. PostgreSQL peut être utilisé sur des serveurs Linux, Unix, BSD et Windows.

La réplication de base de données maître/esclave est un processus de copie (synchronisation) des données d’une base de données sur un serveur (le maître) vers une base de données sur un autre serveur (les esclaves). Le principal avantage de ce processus est de distribuer les bases de données sur plusieurs machines, de sorte que lorsque le serveur maître a un problème, il y a une machine de sauvegarde avec les mêmes données disponibles pour traiter les demandes sans interruption.

PostgreSQL fournit plusieurs façons de répliquer une base de données. Il peut être utilisé à des fins de sauvegarde et pour fournir un serveur de base de données à haute disponibilité. Dans ce tutoriel, nous allons vous montrer comment installer et configurer la réplication maître-esclave de PostgreSQL 9.6 sur un serveur Ubuntu 16.04. Nous utiliserons le mode Hot standby, et c’est un très bon point de départ pour apprendre PostgreSQL en profondeur.

Ce que nous allons faire :

Installer PostgreSQL 9.6.
Configurer le pare-feu UFW.
Configurer le serveur MASTER.
Configurer le serveur SLAVE.
Copier les données PostgreSQL du MASTER au SLAVE.
Tester.

Prérequis :

1 serveur Ubuntu 16.04 - MASTER - Autorisant la lecture et l’écriture - IP : 10.0.15.10
1 serveur Ubuntu 16.04 - SLAVE - Lecture seule - IP : 10.0.15.11
Privilèges root

Étape 1 - Installer PostgreSQL 9.6

Dans ce tutoriel, nous allons installer la dernière version de PostgreSQL 9.6. Dans le dépôt officiel d’Ubuntu, ils ne fournissent que PostgreSQL 9.5, donc nous devons installer la dernière version directement depuis le dépôt PostgreSQL.

Ajoutez le dépôt postgreSQL 9.6 au répertoire sources.list.d.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

Et importez la clé de signature PostgreSQL dans le système.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Maintenant, mettez à jour le dépôt système avec la commande apt.

apt-get update

Ensuite, installez le paquet PosgreSQL 9.6 avec la commande apt ci-dessous.

apt-get install -y postgresql-9.6 postgresql-contrib-9.6

Si l’installation est terminée, ajoutez-le pour qu’il démarre automatiquement au démarrage.

systemctl enable postgresql

Par défaut, PostgreSQL fonctionne sur l’adresse IP localhost (127.0.0.1) avec le port 5432 sur Ubuntu, vérifiez-le avec la commande netstat.

netstat -plntu

Vérifiez si postgres fonctionne

PostgreSQL 9.6 fonctionne sur le système. À l’étape suivante, nous devons configurer un mot de passe pour l’utilisateur postgres.

Depuis le compte root, connectez-vous à l’utilisateur postgres avec la commande su, puis accédez au terminal frontal postgres psql.

su - postgres  
psql

Veuillez changer le mot de passe pour l’utilisateur postgres et vérifier les informations de connexion avec les requêtes postgres ci-dessous.

\password postgres  
\conninfo

Changer le mot de passe postgres

PostgreSQL 9.6 a été installé sur le système, fonctionne sans erreur et le mot de passe pour l’utilisateur postgres a été mis à jour.

Étape 2 - Configurer le pare-feu UFW

UFW ou Uncomplicated Firewall est une application pour gérer le pare-feu basé sur iptables sur Ubuntu. UFW est l’outil de configuration de pare-feu par défaut pour Ubuntu Linux et fournit un moyen convivial de configurer le pare-feu.

Nous pouvons installer ufw depuis le dépôt principal d’Ubuntu avec la commande apt.

apt-get install -y ufw

Ajoutez de nouveaux services au pare-feu UFW : ajoutez les services SSH et PostgreSQL avec les commandes ci-dessous.

ufw allow ssh  
ufw allow postgresql

Activez le pare-feu UFW et vérifiez l’état.

ufw enable  
ufw status

Installer et vérifier le pare-feu UFW

Le pare-feu UFW a été installé et le service PostgreSQL a été ajouté.

REMARQUE :

Exécutez l’étape 1 et l’étape 2 sur le serveur MASTER et le serveur SLAVE.

Étape 3 - Configurer le serveur PostgreSQL MASTER

Le serveur maître a l’adresse IP 10.0.15.10, et le service postgres fonctionnera sous cette IP avec le port par défaut. Le serveur maître aura la permission de LIRE et d’ÉCRIRE dans la base de données, et effectuera une réplication en continu vers le serveur esclave.

Allez dans le répertoire de configuration postgres ‘/etc/postgresql/9.6/main‘ et éditez le fichier postgresql.conf avec vim.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Décommentez la ligne ‘listen_addresses’ et changez la valeur pour l’adresse IP du serveur maître ‘10.0.15.10‘.

listen_addresses = '10.0.15.10'

Décommentez la ligne ‘wal_level’ et changez la valeur pour ‘hot_standby‘.

wal_level = hot_standby

Pour le niveau de synchronisation, nous utiliserons la synchronisation locale. Décommentez et changez la valeur de la ligne comme ci-dessous.

synchronous_commit = local

Activez le mode d’archivage et changez l’option archive_command en la commande ‘cp %p /var/lib/postgresql/9.6/main/archive/%f’.

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'

Pour les paramètres de ‘Réplication’, dans ce tutoriel, nous n’utilisons que 2 serveurs, maître et esclave, décommentez la ligne ‘wal_sender‘ et changez la valeur à 2, et pour la valeur ‘wal_keep_segments‘ c’est 10.

max_wal_senders = 2  
wal_keep_segments = 10

Pour le nom de l’application, décommentez la ligne ‘synchronous_standby_names’ et changez la valeur pour le nom ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Enregistrez le fichier et quittez l’éditeur.

Dans le fichier postgresql.conf, le mode d’archivage est activé, donc nous devons créer un nouveau répertoire pour l’archive. Créez un nouveau répertoire d’archive, changez les permissions et changez le propriétaire en utilisateur postgres.

mkdir -p /var/lib/postgresql/9.6/main/archive/  
chmod 700 /var/lib/postgresql/9.6/main/archive/  
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

Ensuite, éditez le fichier pg_hba.conf pour la configuration d’authentification.

vim pg_hba.conf

Collez la configuration ci-dessous à la fin de la ligne.

# Localhost  
host    replication     replica          127.0.0.1/32            md5  
   
# Adresse IP du PostgreSQL Master  
host    replication     replica          10.0.15.10/32            md5  
   
# Adresse IP du PostgreSQL SLave  
host    replication     replica          10.0.15.11/32            md5

Enregistrez et quittez, puis redémarrez PostgreSQL.

systemctl restart postgresql

PostgreSQL fonctionne sous l’adresse IP 10.0.15.10, vérifiez-le avec la commande netstat.

netstat -plntu

Configurer la réplication Postgres

Ensuite, créez un nouvel utilisateur pour la réplication. Nous allons créer un nouvel utilisateur nommé ‘replica‘ avec le mot de passe ‘aqwe123@‘. Veuillez choisir un mot de passe sécurisé ici pour votre configuration ! Connectez-vous à l’utilisateur postgres et accédez au terminal frontal postgres psql.

su - postgres  
psql

Créez un nouvel utilisateur ‘replica‘ avec le mot de passe ‘aqwe123@‘ avec la requête postgres ci-dessous.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

Maintenant, vérifiez le nouvel utilisateur avec la requête ‘du‘ ci-dessous, et vous verrez l’utilisateur replica avec des privilèges de réplication.

\du

Créer un utilisateur de réplication postgres

La configuration du serveur MASTER est terminée.

Étape 4 - Configurer le serveur SLAVE

Le serveur SLAVE a l’adresse IP 10.0.15.11. Et ce serveur n’aura qu’une permission de LECTURE sur la base de données. Le serveur de base de données Postgres fonctionnera sous l’adresse IP du serveur, pas une IP localhost.

Arrêtez le service postgres sur le serveur esclave avec la commande systemctl ci-dessous.

systemctl stop postgresql

Allez dans le répertoire de configuration Postgres ‘/etc/postgresql/9.6/main‘, puis éditez le fichier de configuration ‘postgresql.conf‘.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Décommentez la ligne listen_addresses et remplacez la valeur par l’adresse IP du serveur esclave 10.0.15.11.

listen_addresses = '10.0.15.11'

Décommentez la ligne ‘wal_level’ et changez la valeur pour ‘hot_standby‘.

wal_level = hot_standby

Pour le niveau de synchronisation, décommentez la ligne synchronous_commit et changez la valeur comme indiqué ci-dessous.

synchronous_commit = local

Pour le paramètre de réplication, décommentez la ligne max_wal_senders et remplacez la valeur par ‘2‘ car nous n’utilisons que 2 serveurs. Et pour wal_keep_segments, changez la valeur en ‘10‘.

max_wal_senders = 2  
wal_keep_segments = 10

Décommentez la ligne ‘synchronous_standby_names’ pour le nom de l’application et changez la valeur pour le nom ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Activez hot_standby pour le serveur esclave en décommentant la ligne suivante et changez la valeur en ‘on‘.

hot_standby = on

Enregistrez le fichier et quittez l’éditeur.

Étape 5 - Copier les données PostgreSQL du MASTER au SLAVE

Ensuite, nous voulons remplacer le répertoire principal de postgres sur le serveur ‘SLAVE‘ par le répertoire principal de données du serveur ‘MASTER‘.

Connectez-vous au serveur SLAVE et accédez à l’utilisateur postgres.

su - postgres

Allez dans le répertoire de données postgres ‘main‘ et sauvegardez-le en renommant le nom du répertoire.

cd 9.6/  
mv main main-bekup

Créez un nouveau répertoire ‘main‘ en tant qu’utilisateur ‘postgres‘ et assurez-vous d’avoir des permissions comme le répertoire main-bekup.

mkdir main/  
chmod 700 main/

Ensuite, copiez le répertoire principal du serveur MASTER vers le serveur SLAVE avec la commande pg_basebackup, nous allons utiliser l’utilisateur replica pour effectuer cette copie de données.

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog  
Mot de passe :

Lorsque le transfert de données est terminé, allez dans le répertoire principal de données et créez un nouveau fichier recovery.conf.

cd /var/lib/postgresql/9.6/main/  
vim recovery.conf

Collez la configuration ci-dessous :

standby_mode = 'on'  
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'  
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'  
trigger_file = '/tmp/postgresql.trigger.5432'

Enregistrez et quittez, puis changez les permissions du fichier à 600 avec chmod.

chmod 600 recovery.conf

Configurer le serveur esclave postgres

Maintenant, démarrez PostgreSQL 9.6 sur le serveur SLAVE et assurez-vous que le service postgres fonctionne sur l’adresse IP 10.0.15.11 avec netstat.

systemctl start postgresql  
netstat -plntu

Le transfert de données et la configuration pour le serveur SLAVE sont terminés.

Étape 6 - Test

Pour tester, nous allons vérifier l’état de la réplication de PostgreSQL 9.6 et essayer de créer une nouvelle table sur le serveur MASTER, puis vérifier la réplication en vérifiant toutes les données du serveur SLAVE.

Connectez-vous au serveur MASTER et connectez-vous à l’utilisateur postgres.

su - postgres

Exécutez les commandes psql ci-dessous pour voir l’état de la réplication.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  
psql -x -c "select * from pg_stat_replication;"

Vous obtiendrez les résultats comme ci-dessous.

Vérifiez l'état de la réplication postgres

Ensuite, essayez de créer une nouvelle table depuis le serveur MASTER. Connectez-vous à l’utilisateur postgres sur le serveur maître.

su - postgres  
psql

Et créez une nouvelle table nommée ‘replica_test‘ et insérez des données dans la table en exécutant les requêtes postgres ci-dessous.

CREATE TABLE replica_test (hakase varchar(100));  
INSERT INTO replica_test VALUES ('howtoforge.com');  
INSERT INTO replica_test VALUES ('Ceci est du Master');  
INSERT INTO replica_test VALUES ('pg replication par hakase-labs');

Ajouter des données de test

Ensuite, connectez-vous à l’utilisateur postgres sur le serveur SLAVE et accédez au terminal psql.

su - postgres  
psql

Vérifiez les données dans la table ‘replica_test‘ avec la requête postgres ci-dessous.

select * from replica_test;

Et vous obtiendrez les mêmes données que sur le MASTER, elles sont répliquées du serveur MASTER au serveur SLAVE.

Vérifiez la réplication des données

Test supplémentaire :

Testez l’écriture sur le serveur SLAVE avec la requête ci-dessous.

INSERT INTO replica_test VALUES ('c'est SLAVE');

Et vous obtiendrez un message d’erreur concernant la requête ‘Impossible d’exécuter INSERT‘ sur le serveur SLAVE.

L’installation et la configuration de PostgreSQL 9.6 avec réplication maître-esclave sur Ubuntu 16.04 Xenial Xerus ont été réussies.

Référence

https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/