Configuration d'un cluster PostgreSQL avec réplication et équilibrage de charge

Dans ce guide, nous détaillerons la mise en place d'une architecture PostgreSQL haute disponibilité, intégrant la réplication de flux et le groupement de connexions avec pgpool.

Architecture Globale

L'architecture se compose des éléments suivants :

  • Master (192.168.123.180) : Serveur PostgreSQL principal et nœud pgpool.
  • Slave1 (192.168.123.181) : Serveur PostgreSQL secondaire (réplique) et nœud pgpool.
  • Slave2 (192.168.123.182) : Serveur PostgreSQL secondaire (réplique).
  • VIP (192.168.123.183) : Adresse IP virtuelle utilisée pour l'accès transparent à la base de données.

Installation et Configuration de PostgreSQL

Installation des paquets PostgreSQL

Téléchargez et installez les paquets RPM nécessaires pour PostgreSQL 9.5.


yum install -y postgresql95-libs-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-contrib-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-server-9.5.8-1PGDG.rhel6.x86_64.rpm

Initialisation et Démarrage

  1. Initialisez la base de données : service postgresql-9.5 initdb
  2. Configurez le démarrage automatique : chkconfig postgresql-9.5 on

Le répertoire d'installation par défaut est /var/lib/pgsql/9.5.

Configuration de la Réplication de Flux (Asynchrone)

Serveur Master

  1. Modification de pg_hba.conf : Autorisez les connexions pour la réplication et les utilisateurs.

# Pour les connexions PostgreSQL standard
host   all             all              192.168.123.180/32      trust
host   all             all              192.168.123.181/32      trust
host   all             all              192.168.123.182/32      trust
# Pour la réplication
host   replication      replica          192.168.123.181/32      trust
host   replication      replica          192.168.123.182/32      trust

trust est utilisé pour des raisons de simplicité sur un réseau privé. Adaptez-le si une authentification par mot de passe est requise.

  1. Modification de postgresql.conf : Configurez les paramètres de réplication et d'écoute.

data_directory = '/app/pgsql/data' # Répertoire de données personnalisé
listen_addresses = '*'             # Écoute sur toutes les interfaces
archive_mode = on                  # Activation de l'archivage WAL
archive_command = 'cp %p /app/pgsql/pg_archive/%f' # Commande d'archivage
wal_level = hot_standby            # Niveau WAL pour la réplication
max_wal_senders = 16               # Nombre maximum de connexions de réplication
wal_keep_segments = 256            # Nombre de segments WAL à conserver
wal_sender_timeout = 60s           # Délai d'attente pour l'envoi WAL
max_connections = 99               # Connexions maximales (doit être supérieur sur les esclaves)
  1. Création et configuration du répertoire de données :

mkdir -p /app/pgsql/data
chown postgres:postgres /app/pgsql/data
mkdir -p /app/pgsql/pg_archive
chown postgres:postgres /app/pgsql/pg_archive
chmod 700 /app/pgsql/data
chmod 700 /app/pgsql/pg_archive
cp -r /var/lib/pgsql/9.5/data/* /app/pgsql/data/
chown -R postgres:postgres /app/pgsql/data
  1. Mise à jour du script de démarrage : Modifiez /etc/init.d/postgresql-9.5 pour pointer vers le nouveau PGDATA.

PGDATA=/app/pgsql/data
  1. Démarrage du service PostgreSQL : service postgresql-9.5 start
  2. Création d'un utilisateur de réplication : Connectez-vous à psql et créez un rôle dédié.

su postgres
psql
CREATE ROLE replica login replication encrypted password 'replica';
\q

Serveur Esclave (Slave1, Slave2)

  1. Préparation du répertoire de données :

mkdir -p /app/pgsql/data
chown -R postgres:postgres /app/pgsql/data
mkdir /app/pgsql/pg_archive
chown postgres:postgres /app/pgsql/pg_archive/
chmod 700 /app/pgsql/pg_archive
  1. Sauvegarde de base : Utilisez pg_basebackup pour copier les données du master.

su - postgres
pg_basebackup -h 192.168.123.180 -U replica -D /app/pgsql/data -X stream -P
  1. Mise à jour du script de démarrage : Modifiez /etc/init.d/postgresql-9.5 pour pointer vers le nouveau PGDATA.

PGDATA=/app/pgsql/data
  1. Configuration de recovery.conf : Créez ce fichier pour spécifier les paramètres de récupération.

# Copiez l'exemple et modifiez-le
cp /usr/pgsql-9.5/share/recovery.conf.sample /app/pgsql/data/recovery.conf
vim /app/pgsql/data/recovery.conf
       


standby_mode = on
primary_conninfo = 'host=192.168.123.180 port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0' # Utilisé pour le basculement
  1. Configuration de postgresql.conf sur l'esclave :

vim /app/pgsql/data/postgresql.conf
max_connections = 999     # Doit être supérieur au master
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on # Permet aux requêtes de lecture sur l'esclave de fournir un feedback au maître
  1. Redémarrage du service PostgreSQL : service postgresql-9.5 restart

Vérification de la Réplication

  1. Sur le master, vérifiez l'état de la réplication :

SELECT client_addr, state, sync_state FROM pg_stat_replication;
  1. Créez une base de données sur le master : CREATE DATABASE test;
  2. Sur un esclave, vérifiez que la base de données est répliquée :

\l

Installation et Configuration de pgpool-II

pgpool-II agit comme un intermédiaire pour la mise en commun des connexions, l'équilibrage de charge et la gestion du basculement.

Configuration de l'accès sans mot de passe SSH

Assurez-vous que l'utilisateur postgres peut se connecter sans mot de passe entre les nœuds master et slave1.

  1. Définissez un mot de passe temporaire pour l'utilisateur postgres (en tant que root) : passwd postgres (ex: 123456).
  2. Sur le master, générez les clés SSH pour l'utilisateur postgres : su postgres && ssh-keygen -t rsa (appuyez sur Entrée pour les valeurs par défaut).
  3. Copiez la clé publique sur slave1 : ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa 192.168.123.181.
  4. Répétez le processus pour permettre à slave1 de se connecter au master sans mot de passe.

Installation de pgpool-II

Téléchargez et installez le paquet RPM pgpool-II.


# Téléchargez le paquet approprié depuis http://www.pgpool.net/yum/rpms/
yum install -y pgpool-II-pg95-3.6.0-1pgdg.rhel6.x86_64.rpm

Configuration de pgpool-II

Serveur Master (192.168.123.180)

  1. Configuraton de pool_hba.conf : Doit correspondre aux permissions de pg_hba.conf.

# Exemple (ajustez selon vos besoins)
host    all             all             192.168.123.0/24        trust
  1. Configuration du mot de passe PCP : Générez un mot de passe chiffré avec pg_md5pwd et collez-le dans /etc/pgpool-II/pcp.conf.
  2. Configuration des permissions des commandes système : (En tant que root)

chmod u+s /sbin/ifconfig
chmod u+s /usr/sbin/ip # Si disponible, sinon ignorez
  1. Configuration principale de pgpool.conf :

# Connexions
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898

# Connexions backend
backend_hostname0 = '192.168.123.180' # Ce nœud lui-même (Master)
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.123.181' # Slave1
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# Authentification
enable_pool_hba = off
pool_passwd = '/etc/pgpool-II/pool_password' # Fichier contenant le mot de passe chiffré généré

# Fichiers
pid_file_name = '/var/run/pgpool/pgpool.pid' # Assurez-vous que ce répertoire existe et a les bonnes permissions

# Modes
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

# Vérification de réplication
sr_check_period = 5
sr_check_user = 'replica' # L'utilisateur créé pour la réplication
sr_check_password = 'replica'
sr_check_database = 'postgres'

# Vérification de santé
health_check_period = 10
health_check_timeout = 20
health_check_user = 'replica'
health_check_password = 'replica'
health_check_database = 'postgres'

# Basculement
failover_command = '/opt/pgpool/failover_stream.sh %d %H /tmp/trigger_file0'

# Watchdog
use_watchdog = on
wd_hostname = '192.168.123.180' # IP de ce nœud
wd_port = 9000
delegate_IP = '192.168.123.183' # VIP
if_cmd_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0' # Adaptez eth0 si nécessaire
if_down_cmd = 'ifconfig eth0:0 down'

# Heartbeat
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = '192.168.123.181' # IP du nœud esclave
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eth0' # Adaptez eth0 si nécessaire

# Connexions avec l'autre pgpool
other_pgpool_hostname0 = '192.168.123.181'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
  1. Création du script de basculement : Créez /opt/pgpool/failover_stream.sh (voir section script de basculement plus bas).

Serveur Esclave (Slave1)

La configuration de pgpool.conf sur Slave1 est similaire à celle du master, mais les paramètres wd_hostname et heartbeat_destination0 doivent être inversés.


# ... (mêmes paramètres que le master pour listen_addresses, port, etc.)

# Connexions backend
backend_hostname0 = '192.168.123.180' # Master
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.123.181' # Ce nœud lui-même (Slave1)
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# ... (mêmes paramètres pour la réplication, santé, etc.)

# Watchdog
use_watchdog = on
wd_hostname = '192.168.123.181' # IP de ce nœud
wd_port = 9000
delegate_IP = '192.168.123.183' # VIP
if_cmd_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0' # Adaptez eth0 si nécessaire
if_down_cmd = 'ifconfig eth0:0 down'

# Heartbeat
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = '192.168.123.180' # IP du nœud master
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eth0' # Adaptez eth0 si nécessaire

# Connexions avec l'autre pgpool
other_pgpool_hostname0 = '192.168.123.180'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

Créez également le script de basculement sur ce nœud.

Script de Basculement (failover_stream.sh)

Ce script est exécuté par pgpool lors d'une défaillance.

Sur le Master (/opt/pgpool/failover_stream.sh) :


#!/bin/sh
# $1: ID du nœud défaillant. $2: Nom d'hôte du nouveau maître. $3: Chemin du fichier de déclenchement.

FAILED_NODE=$1
NEW_MASTER_HOSTNAME=$2
TRIGGER_FILE=$3

# Si l'esclave tombe, ne rien faire.
if [ $FAILED_NODE -eq 1 ]; then
    exit 0;
fi

# Si le maître tombe, créer le fichier de déclenchement sur l'esclave pour qu'il prenne le relais.
# Ceci est une simplification, une logique plus robuste est souvent nécessaire.
echo "Primary node ($FAILED_NODE) failed. Triggering failover..."
/usr/bin/ssh -T $NEW_MASTER_HOSTNAME "touch $TRIGGER_FILE"
exit 0;

Sur Slave1 (/opt/pgpool/failover_stream.sh) :


#!/bin/sh
# $1: ID du nœud défaillant. $2: Nom d'hôte du nouveau maître. $3: Chemin du fichier de déclenchement.

FAILED_NODE=$1
NEW_MASTER_HOSTNAME=$2
TRIGGER_FILE=$3

# Si le maître tombe, créer le fichier de déclenchement sur cet esclave pour qu'il prenne le relais.
if [ $FAILED_NODE -eq 0 ]; then
    echo "Primary node ($FAILED_NODE) failed. Triggering failover..."
    touch $TRIGGER_FILE
    exit 0;
fi

# Si l'autre esclave tombe, ne rien faire.
exit 0;

Assurez-vous que les répertoires /opt/pgpool, /var/log/pgpool et /var/run/pgpool existent et que l'utilisateur postgres y a les permissions nécessaires.

Donnez les permissions d'exécution aux scripts : chmod 755 /opt/pgpool/failover_stream.sh.

Configuration des hôtes

Modifiez le fichier /etc/hosts sur tous les nœuds pour inclure les mappings IP :


192.168.123.180 master
192.168.123.181 slave1
192.168.123.182 slave2

Création de l'utilisateur de vérification de santé

Sur le master, créez l'utilisateur utilisé par pgpool pour les vérifications :


su postgres
psql
CREATE USER repuser WITH PASSWORD 'repuser';
GRANT SELECT ON pg_database TO repuser; -- Accès minimum nécessaire pour la vérification
\q

Ouverture des ports du pare-feu

Ouvrez les ports suivants sur tous les nœuds : 5432 (PostgreSQL), 9999 (pgpool), 9898 (pgpool PCP), 9000 (watchdog), 9694 (heartbeat).

Démarrage de pgpool-II

Démarrez pgpool sur le master, puis sur slave1.


# Sur Master
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1

# Sur Slave1
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1

Vérifiez que l'adresse IP virtuelle (VIP) apparaît sur le master : ifconfig.

Connexion à la base de données via pgpool

Utilisez l'adresse IP virtuelle pour vous connecter.


su postgres
psql -h 192.168.123.183 -p 9999

Pour vérifier l'état des nœuds pgpool :


show pool_nodes;

Pour arrêter pgpool proprement : pgpool -m fast stop.

Étiquettes: PostgreSQL ha réplication pgpool streaming replication

Publié le 18 juillet à 04h32

Étiquettes Populaires

©2026 L'Atelier Monstre