Mise en place d’une réplication synchrone entre deux bases PostgreSQL

Nous allons mettre en place une réplication synchrone entre deux bases PostgreSQL

On part du principe que PostgreSQL est installé sur les serveurs

Dans notre exemple il y aura deux serveurs

• 192.168.1.114 qui sera le Master
• 192.168.1.184 qui sera le Slave

Dans notre exemple PostgreSQL est en version 17.7

1 – Préparer le master

Toutes les commandes seront à écrire sur le serveur master

• Éditer le fichier /etc/postgresql/17/main/postgresql.conf

nano /etc/postgresql/17/main/postgresql.conf

• Modifier / créer / de-commenter les lignes suivantes

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB

# Pour la réplication synchrone :
synchronous_standby_names = 'node184'
synchronous_commit = on

(node184 sera le nom du standby que l’on paramétrera plus tard)

• Éditer le fichier /etc/postgresql/17/main/pg_hba.conf

nano /etc/postgresql/17/main/pg_hba.conf

• Rajouter sous host all all 0.0.0.0/0 md5

host    replication     replicator     192.168.1.184/32     md5

(Attention aux tabulations)

(192.168.1.184 est l’ip du slave selon la configuration)

(replicator est le nom de l’utilisateur qui fera la réplication et que l’on va créer à l’étape suivante)

• Créer l’utilisateur qui va servir pour la réplication

sudo -i -u postgres
psql
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'motdepasse';

(Penser à mettre un mot de passe fort pour l’utilisateur)

• Relancer PostgreSQL pour mettre à jour le nouveau paramétrage

systemctl reload postgresql

2 – Préparer le Slave

Toutes les commandes seront à écrire sur le serveur slave

• On stoppe PostgreSQL

systemctl stop postgresql

• on supprime les données sur le serveur

rm -rf /var/lib/postgresql/17/main/*

• on récupére la base du Master

pg_basebackup -h 192.168.1.114 -U replicator -D /var/lib/postgresql/17/main -Fp -Xs -P -R

(Attention le 192.168.1.114 est l’ip du master à adapter selon la configuration)

-R pour créer automatiquement le fichier standby.signal et postgresql.auto.conf (ces fichiers servent à dire au serveur qu’il n’est plus un serveur en lecture-écriture mais que l’écriture se fait exclusivement à partir du master)

-Xs pour copier les WAL necessaires

• On va vérifier le fichier /var/lib/postgresql/17/main/postgresql.auto.conf

nano /var/lib/postgresql/17/main/postgresql.auto.conf

• on devrait avoir une ligne comme ça dedans

primary_conninfo = 'user=replicator password=motdepasse channel_binding=prefer host=192.168.1.114 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

(Bien vérifier le mot de passe et l’ip du master)

• Modifier la ligne pour ajouter application_name=node184 aprés port =5432

(node184 correspond au node que l’on a mis à l’étape 1 dans le fichier /etc/postgresql/17/main/postgresql.conf à la ligne synchronous_standby_names =)

• Changer les droits du fichier standby.signal pour les donner à postgres

chown postgres:postgres /var/lib/postgresql/17/main/standby.signal
chmod 600 /var/lib/postgresql/17/main/standby.signal

3 – Configuration de la réplication sur le Slave

• Éditer le fichier /etc/postgresql/17/main/postgresql.conf
• Ajouter / modifier / de-commenter

hot_standby = on

• Mettre au propre les droits du slave

chown -R postgres:postgres /var/lib/postgresql/17/main
chmod 700 /var/lib/postgresql/17/main

• Démarrer postgresql

systemctl start postgresql

4 – Vérification

4.1 Sur le master

sudo -i -u postgres
psql
SELECT application_name, state, sync_state FROM pg_stat_replication;

On devrait avoir

SHOW synchronous_standby_names;
SHOW synchronous_commit;

Tout est bien sur on

4.2 Sur PgAdmin (si installé)

• On insère une données sur le master

• La donnée est bien envoyées sur le slave