HowtoPostgreSQL/ReplicationPhysique

Streaming Replication avec PostgreSQL

Streaming Replication avec PostgreSQL

https://www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION

La réplication en flux (Streaming Replication) est disponible à partir de la version 9.0 de PostgreSQL. Celle-ci est aussi appelée Réplication Physique en opposition à la Réplication Logique apparue dans la version 10.

Caractéristiques

Réplication de type asynchrone (le maître et le réplica peuvent ne pas être synchro à un instant t) ou synchrone (une transaction est commitée uniquement si elle a pu être écrite sur le maître et envoyé au réplica) ;
réplication basée sur les journaux binaires générés par PostgreSQL (WAL, pour Write Ahead Log) ;
réplication de l’intégralité des données et structures (toutes les bases de données sont répliquées, avec leurs tables et leurs données, pas de granularité possible). Cette fonctionnalité commencera à être introduite à partir de la 9.3 ;
le serveur jouant le rôle de réplica ne peut être interrogé qu’en lecture ;
possibilité de mettre en cascade plusieurs réplicas.

Par rapport au mode de réplication Hot Standby, l’avantage avec la réplication en flux est qu’il n’est pas besoin d’attendre qu’un journal soit plein et fermé pour qu’il soit communiqué au serveur réplica. À l’inverse cela introduit une légère charge supplémentaire (au niveau CPU) puisqu’un (ou plusieurs) processus supplémentaire doit tourner sur le maître (wal_senders).

Pré-requis

Prérequis pour pouvoir mettre en place une infra avec 1 maître et 1 réplica :

même architecture (32 ou 64 bits) sur les 2 serveurs ;
même version majeure de PostgreSQL sur les 2 serveurs (et même version mineure est conseillé) ;

Installation de PostgreSQL

Voir la documentation présente sur la page principale https://wiki.evolix.org/HowtoPostgreSQL#installation

Configuration du serveur maître

Note : Cette configuration est relative au serveur maître, mais elle doit également être reprise à l’identique sur le serveur réplica, dans le cas où les rôles doivent être échangés (suite à un failover/switchover).

Décommenter ou ajouter les directives suivantes dans le fichier /etc/postgresql/9.6/main/postgresql.conf :

# Nécessaire pour que le réplica puisse se connecter.
listen_addresses = '*'

# Nombre maximum de processus walsender à lancer (mettre au moins le même
# nombre que de serveur réplica qui se connecteront dessus + 1 (reconnexions,
# maintenance...).
# (1 processus = 1 connexion)
max_wal_senders = 2

# « niveau de verbosité » des journaux PostgreSQL. Le niveau minimum
# nécessaire (replica) pour que le réplica soit accessible en lecture.
# Si une réplication logique est aussi en place il faut le niveau maximum (logical)
wal_level = replica

# Activation de l'archivage des WAL. Nécessaire pour pouvoir remettre en
# place facilement la réplication suite à un failover/switchover.
# Ceci n'est pas obligatoire pour la mise en place d'une réplication physique si on utilise on slot de réplication
archive_mode = on
archive_command = 'rsync %p 192.0.2.2:/srv/pg-archives/%f'

Créer un utilisateur dédié pour la réplication :

postgres=# CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'PASSWORD';

On peut le faire aussi comme ceci, avec l’utilisateur Unix postgres :

postgres@hostname:~$ createuser --no-superuser --no-createrole --no-createdb --replication -P repl

Autoriser le serveur réplica à se connecter au maître :

host    replication     repl             192.0.2.2/32         md5
hostssl replication     repl             192.0.2.2/32         md5

Configuration du serveur réplica

Note 1 : A partir de Postgresql 10 il n’est plus necessaire de modifier la variable hot_standby, ni de créer au préalable le fichier recovery.conf, voir directement la section Synchronisation initiale des données (Méthode courante)

Note 2 : Cette configuration est relative au serveur réplica, mais elle doit également être reprise à l’identique sur le serveur maître, en renommant le fichier recovery.conf pour qu’il ne soit pas pris en compte.

Note 3 : En Debian 8 ou 9 et postgresql >v9 on ferra comme ceci :

Décommenter ou ajouter les directives suivantes dans le fichier /etc/postgresql/9.6/main/postgresql.conf :

# Le serveur est en mode réplica en lecture seule
hot_standby = on

Créer un fichier recovery.conf situé dans le datadir avec les infos suivantes :

standby_mode = 'on'
primary_conninfo = 'host=192.0.2.1 user=repl password=PASSWORD application_name=foo'
archive_cleanup_command = '/usr/lib/postgresql/9.6/bin/pg_archivecleanup /srv/pg-archives/ %r'
recovery_target_timeline = 'latest'

Il est nécessaire que ce fichier appartienne à l’utilisateur postgres, notamment en cas de promotion en master (car PostgreSQL va renommer le fichier en recovery.done) :

# chown postgres:postgres ~postgres/9.6/main/recovery.conf

Synchronisation initiale des données (Méthode obsolète)

Arrêter PostgreSQL sur le réplica ;
sur le maître, indiquer à PostgreSQL qu’on commence une sauvegarde. Il va notamment faire un checkpoint dans son WAL courant et retourner sa position :

postgres$ psql -c "SELECT pg_start_backup('synchro initiale')"

lancer le rsync du datadir vers le réplica :

# rsync -avz --delete --exclude /pg_xlog/* --exclude /postmaster.* --exclude /recovery.* ~postgres/9.6/main/ 192.0.2.2:~postgres/9.6/main/

indiquer à PostgreSQL que le backup est terminé :

postgres$ psql -c "SELECT pg_stop_backup()"

Synchronisation initiale des données (Méthode courante)

Arrêter PostgreSQL sur le réplica ;
Supprimer le contenu de /var/lib/postgresql/<version>/<cluster>/*
Autoriser la connexion SSH par clé, de l’utilisateur postgres depuis le master vers le réplica, et également depuis le réplica vers le master.
Créer le fichier .pgpass

postgres@$: echo "*:*:*:repl:mypass" > .pgpass

Faire la syncho des données avec pg_basebackup comme ceci :

# sudo -u postgres pg_basebackup -h IP_PRIMAIRE -D /var/lib/postgresql/<version>/<cluster> -P -R -v -U repl

L’option -D indique le PGDATA du secondaire, dans lequel on veut copier les données.

L’option -R s’occupe de créer le fichier recovery.conf (ou postgresql.auto.conf si version suppérieure à 11) dans /var/lib/postgresql/<version>/<cluster>/

pg_basebackup s’occupe de faire un checkpoint du WAL courant (pg_start_backup) de transférer les fichiers des bases puis d’exécuter la fonction SQL pg_stop_backup

Pendant le transfert des fichiers, la base a pu subir des modifications. C’est pourquoi pg_basebackup attend que le primaire et fait un checkpoint dans sont WAL avant de tranférés les données, par défaut un checkpoint est fait toutes les 5 minutes, donc la commande pg_basebackup peut prendre 5 minutes avant de commencer à synchroniser les données.

Si on veut forcer un checkpoint sur le primaire, on peut utiliser la commande pg_basebackup avec l’option -c fast

Créer un fichier recovery.conf situé dans le datadir avec les infos suivantes (si version inférieure a PG12) :

standby_mode = 'on'
primary_conninfo = 'host=192.0.2.1 user=repl password=PASSWORD application_name=foo'

# Ce qui suit n'est pas obligatoire, c'est dans le cas où l'on archive les WAL sur ce réplica
archive_cleanup_command = '/usr/lib/postgresql/9.6/bin/pg_archivecleanup /srv/pg-archives/ %r'
recovery_target_timeline = 'latest'

Il est nécessaire que ce fichier appartienne à l’utilisateur postgres, notamment en cas de promotion en master (car PostgreSQL va renommer le fichier en recovery.done) :

Si version Postgresql supérieure à 11, le recovery.conf n’existe plus, il est remplacé par le fichier postgresql.auto.conf, et le fichier standby.signal
redémarrer PostgreSQL sur le réplica.

Administration

Création d’un slot de réplication physique pour éviter un décrochage entre le primaire et le secondaire

Pour éviter qu’il y ait un décrochage entre le primaire et le secondaire, dans le cas où le secondaire soit stoppé quelques minutes ou qu’il soit très chargé par des requêtes, on crée un slot de réplication physique sur le primaire comme ceci :

postgres=# SELECT pg_create_physical_replication_slot('nom_du_secondaire');

Sur le secondaire, on rajoute la variable primary_slot_name dans le fichier recovery.conf (version <=11) ou postgresql.conf en v12 avec la mention du slot à utiliser :

primary_slot_name = 'nom_du_secondaire'

On redémarre postgresql sur le secondaire.

Attention: Si on doit stopper le secondaire pendant un temps assez long, il faut supprimer le slot de réplication physique, sinon les WAL vont s’accumuler sur le primaire, ce qui peu crée un danger en cas de saturation de l’espace disque.

Pour supprimer un slot de réplication :

postgres=# SELECT pg_drop_replication_slot ('nom_du_secondaire');

Il est donc impératif de supprimer un slot de réplication si le secondaire a été désactivé ou hors ligne un certain temps, quitte à devoir reconstruire le secondaire complètement.

Pour listés tous les slots de réplication active sur un serveur, que ce soit des slots physique ou logique, on peux le faire avec la requête suivante :

postgres=# select * from pg_replication_slots;

Monitoring

Plusieurs possibilités pour surveiller la réplication :

Voir la position courante dans les logs sur le maître et le réplica (on peut en déduire s’ils sont synchro ou pas) :

# pgrep -lf "wal (sender|receiver) process"
6891 postgres: wal receiver process   streaming 0/C085240

PostgreSQL fournit la vue pg_stat_replication() qui permet de lister les connexions aux processus walsender, avec différentes informations utiles :

postgres=# SELECT * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 29745
usesysid         | 16387
usename          | repl
application_name | foo
client_addr      | 192.0.2.2
client_hostname  |
client_port      | 46581
backend_start    | 2013-04-30 10:39:43.230287-04
state            | streaming
sent_location    | 0/C0873B8
write_location   | 0/C0873B8
flush_location   | 0/C0873B8
replay_location  | 0/C0873B8
sync_priority    | 0
sync_state       | async

Les données à surveiller sont notamment les *_location, qui indique la position courante dans les WAL à différentes étapes de la réplication. Voir https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW pour le détails des champs.
Pour pouvoir quantifier le retard de réplication, on peut utiliser la commande check_postgres avec l’option hot_standby_delay :

$ check_postgres --action=hot_standby_delay --dbhost=localhost --dbport=5432  --dbname=template1 --dbuser=nrpe --dbpass=PASSWORD --dbhost=192.0.2.2 --dbport=5432 --warning=500000 --critical=1000000
POSTGRES_HOT_STANDBY_DELAY OK: DB "template1" (host:192.0.2.2) 0 | time=0.09s replay_delay=12568;500000;1000000  receive-delay=8192;500000;1000000

Où localhost est le maître et 192.0.2.2 le réplica. Les valeurs de replay_delay et receive-delay sont à priori exprimées en octets de WAL à rejouer.

check_pgactivity

On peut surveiller la streaming réplication également avec le check_pgactivity : https://github.com/OPMDG/check_pgactivity

En Debian 10, on installe le paquet check-pgactivity

On surveille la streaming réplication avec le service streaming_delta qui surveile le delta de données entre le primaire et le secondaire.

Le check peut prendre le nom qu’on a donné à la variable application_name sur le secondaire dans le fichier recovery.conf

Si on a seulement un secondaire à surveiller on peut le faire comme ceci :

postgres@serv:~$ /usr/lib/nagios/plugins/check_pgactivity -s streaming_delta --slave 'slave1 192.168.0.2'

Si on a deux secondaires à surveiller :

postgres@serv:~$ /usr/lib/nagios/plugins/check_pgactivity -s streaming_delta --slave 'slave1 192.168.0.2','slave2 192.168.0.3'

Si sur le primaire on a aussi des slot de réplication logique, on peut les exclure du check streaming_delta avec l’option –exclude qui supporte les regex, dans l’exemple on part du principe que les slots de réplication logique sont nommées mysub_* :

postgres@serv:~$ /usr/lib/nagios/plugins/check_pgactivity -s streaming_delta --slave 'slave1 192.168.0.2','slave2 192.168.0.3' --exclude mysub_*

Passer un serveur réplica en maître

Si le maître est toujours joignable, éteindre PostgreSQL en forçant la déconnexion des clients :

# pg_ctlcluster 9.6 main stop -- -m fast

Sur le réplica, faire en sorte que PostgreSQL accepte les connexions en écriture :

# pg_ctlcluster 9.6 main promote

Le réplica va d’abord rattraper son éventuel retard dans le traitement des logs de réplication, puis une fois prêt se mettra à accepter les requêtes en écritures.

Le fichier recovery.conf est renommé en recovery.done pour qu’il ne soit pas lu en cas de redémarrage de PostgreSQL.

Messages de PostgreSQL lors du passage en maître :

2013-04-23 05:54:15 EDT LOG:  received promote request
2013-04-23 05:54:15 EDT LOG:  redo done at 0/6000020
2013-04-23 05:54:15 EDT LOG:  last completed transaction was at log time 2013-04-23 05:54:10.217923-04
2013-04-23 05:54:15 EDT LOG:  selected new timeline ID: 2
2013-04-23 05:54:15 EDT LOG:  archive recovery complete
2013-04-23 05:54:15 EDT LOG:  database system is ready to accept connections
2013-04-23 05:54:15 EDT LOG:  autovacuum launcher started

Note : Attention, à partir du moment où le réplica cesse de lire les journaux du maître, toutes les écritures qui continuent de se faire sur le maître seront perdues. Il faut donc être certain que le maître soit réellement inaccessible avant de faire la bascule.

Rétablissement de la réplication après un failover

État courant : le serveur réplica accepte les écritures suite à la procédure de failover, et le serveur maître contient des données obsolètes car pas mises à jour.

Il faut alors mettre en place le recovery.conf sur l’ancien master et démarrer PostgreSQL. Il va alors rejouer les WAL pour rattraper le retard accumulé, puis se mettre en mettre en mode streaming replication.

Arrêter la réplication

Arrêter/reprendre le “replay” des WAL sur le réplica :

postgres=# SELECT pg_xlog_replay_pause();
postgres=# SELECT pg_xlog_replay_resume();

Arrêter/reprendre le flux des WAL depuis le maître. Il ne semble pas y avoir de solution autre que de couper le flux au niveau réseau. Sur le maître :

# iptables -I INPUT -s 192.0.2.2 -p tcp --dport 5432 -j REJECT
# iptables -D INPUT 1

Diverses notes/spécificités de la réplication

Si une requête sur le serveur réplica bloque la réplication (par exemple un SELECT qui bloque un DROP TABLE) pendant un temps trop long, la requête sur le réplica sera tuée (ici le SELECT). Ce temps est défini par la directive max_standby_streaming_delay sur la configuration du réplica.

Ce comportement peut-être désactivé grâce à la directive hot_standby_feedback, qui fait en sorte que le réplica communique l’état des requêtes au maître, mais cela à un impact sur le maître.