Réplication en continu avec PostgreSQL 9.6, 10 & 11 (Streaming replication)

Introduction

Plusieurs modes de réplication sont disponibles avec PostgreSQL.

Dans cet article, une réplication en continu (streaming replication) avec PostgreSQL 9.6 est mise en place sur des serveurs Linux Ubuntu 18.04, c’est très simple. Dans la réplication en continu, le serveur de standby se connecte au serveur primaire, lequel diffuse les enregistrements WAL (Write Ahead Log) au serveur de standby au fur et à mesure qu’ils sont générés, sans attendre que le fichier WAL soit rempli. La réplication en continu permet à un serveur de standby de rester plus à jour que ce qui est possible avec le transfert des journaux.

Le serveur de standby peut être en lecture seule (read only) pour le reporting par exemple.
Un point de replication (replication slot) garantit que les fichiers WAL nécessaires au serveur de standby ne sont pas supprimés dans le serveur primaire avant leur traitement par le serveur de standby. Plusieurs points de réplication peuvent être définis en fonction du nombre de serveurs standby.

(LSN : Log Sequence Number)

La procédure décrite ci-dessous est valable pour PostgreSQL 9.6, 10 et 11. Pour PostgreSQL versions 12 et supérieures, la configuration est légèrement différente et non abordée ici. Ce n’est pas important pour la configuration de la réplication PostgreSQL mais l’emplacement des fichiers WAL diffère entre PostgreSQL 9.6 et PostgreSQL 10/11.

PostgreSQL 9.6	`$PGDATA/pg_xlog`
PostgreSQL 10/11	`$PGDATA/pg_wal`

Le contexte est le suivant : 1 serveur primaire, 1 serveur standby.

Serveur primaire : VPSFRSQLPAC1 Serveur standby : VPSFRSQLPAC2 Binaires (PostgreSQL 9.6.15) : /opt/postgres/pgsql-9.6/bin $PATH : /opt/postgres/pgsql-9.6/bin:$PATH $PGLIB : /opt/postgres/pgsql-9.6/lib Port : 30001 $PGDATA : /sqlpac/postgres/srvpg1 $CFG : /opt/postgres/dba/srvpg1/cfg Fichiers de configuration : $CFG/postgresql.conf $CFG/pg_hba.conf $CFG/pg_ident.conf Contrôle du serveur PostgreSQL :

pg_ctl start|stop|restart…  -D $CFG

Préparation du serveur primaire

Paramètres systèmes

Le serveur primaire doit être éventuellement redémarré, notamment avec PostgreSQL 9.6, pour appliquer au minimum les paramètres statiques suivants :

listen_addresses : *
wal_level : replica pour la réplication en continu.
max_replication_slots : au moins 1 point de réplication (1 standby). Appliquer un nombre de points de réplication plus élevé si d’autres serveurs standby ou logiques seront configurés.
max_wal_senders : au moins 3 émetteurs WAL (1 standby + 2 pour pg_basebackup). Appliquer un nombre d’émetteurs WAL plus élevé si d’autres serveurs standby ou logiques seront configurés.

$CFG/postgresql.conf

listen_addresses = '*'
wal_level=replica
max_replication_slots=3
max_wal_senders=3

Pour PostgreSQL 10 et 11, les valeurs par défaut sont déjà ajustées pour de la réplication. Vérifier toutefois les paramètres.

PostgreSQL 10 / 11	Valeurs par défaut
`wal_level max_replication_slots max_wal_senders`	`replica 10 10`

postgres@vpsfrsqlpac1$ pg_ctl restart -D $CFG

Rôle replication

Créer un rôle avec le privilège replication, ce rôle sera utilisé par le serveur de standby pour se connecter au serveur primaire :

create role repmgr with replication login encrypted password '***********';

Ajouter le rôle dans le fichier pg_hba.conf du serveur primaire avec l’adresse IP du serveur de standby, ceci autorisera les connexions depuis le serveur de standby. Ne pas oublier aussi de gérer d’éventuelles règles firewall existantes qui pourraient bloquer.

$CFG/pg_hba.conf

host    replication     repmgr          51.xxx.xxx.xxx/32           md5

Ici, les connexions SSL ne sont pas implémentées.

Recharger la configuration :

postgres@vpsfrsqlpac1$ pg_ctl reload -D $CFG

Replication slot

Créer un point de réplication sur le serveur primaire.

select * from pg_create_physical_replication_slot('standby1');
 slot_name | xlog_position
-----------+---------------
 standby1  |
(1 row)

select slot_name, restart_lsn from pg_replication_slots;
 slot_name | restart_lsn
-----------+-------------
 standby1  |
(1 row)

Le point de réplication (restart_lsn) sera initialisé lors de la sauvegarde du serveur primaire avec pg_basebackup.

À partir de PostgreSQL 11, il n’est pas obligatoire de créér manuellement le point de réplication, celui-ci pourra être créé et initialisé avec pg_basebackup.

Sauvegarde du serveur primaire (pg_basebackup)

La sauvegarde du serveur primaire est réalisée avec pg_basebackup.

postgres@vpsfrsqlpac1$ pg_basebackup -D /sqlpac/postgres/backup/srvpg1 \
                         -X stream \
                         --write-recovery-conf \
                         --slot=standby1 \
                         --dbname="host=localhost user=postgres port=30001"

À partir de PostgreSQL 11, ajouter l’argument --create-slot si le point de réplication n’a pas été créé précédemment.

Avec l’option --slot donnant le nom du point de réplication, il est ainsi garanti que le serveur principal ne supprime pas les données WAL nécessaires entre la fin de la sauvegarde de base et le début de la réplication en continu.

Lorsque la sauvegarde est terminée, le point de réplication standby1 est alors défini :

select slot_name, restart_lsn from pg_replication_slots;
          
 slot_name | restart_lsn
-----------+-------------
 standby1  | 0/33000000

L’option --write-recovery-conf (ou -R) écrit un fichier recovery.conf à la racine du répertoire de la sauvegarde. Ce fichier empêchera une erreur de manipulation lors du démarrage du serveur de standby, ce fichier indique en effet qu’il s’agit d’un serveur de standby ainsi que le nom du point de réplication :

/sqlpac/postgres/backup/srvpg1/recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=localhost port=30001 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'standby1'

Activation du serveur de standby

Installer la sauvegarde du serveur primaire réalisée précédemment dans le répertoire des données du serveur de standby ($PGDATA).

recovery.conf

S’assurer que le fichier recovery.conf est bien installé dans le répertoire des données du serveur de standby avec l’option standby_mode = 'on' et le nom du point de réplication.

Mettre à jour ce fichier pour les paramètres de connexion au serveur primaire.

$PGDATA : /sqlpac/postgres/srvpg1/recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=repmgr host=vpsfrsqlpac1 port=30001 password=************'
primary_slot_name = 'standby1'

postgresql.conf

Si les connexions en lecture seule sont autorisées, vérifier que le paramètre hot_standby est à on pour le serveur de standby (on par défaut à partir de PostgreSQL 10) :

$CFG/postgresql.conf

hot_standby = on

Démarrage du serveur de standby

À présent le serveur de standby peut être démarré.

postgres@vpsfrsqlpac2$ pg_ctl start -D $CFG

Lorsqu’il n’y a pas d’erreur, dans le fichier de log du serveur de standby :

LOG:  entering standby mode
LOG:  redo starts at 0/33000028
LOG:  consistent recovery state reached at 0/34000000
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/34000000 on timeline 1

Le serveur de standby est en mode récupération (recovery) :

postgres@vpsfrsqlpac2$ psql -p30001

select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t

Si le point de réplication n’a pas été défini et que les fichiers WAL nécessaires ont été supprimés du serveur primaire, l’erreur suivante se produit :

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000000000010 has already been removed

Test de la réplication

Créér une table heartbeat dans le serveur primaire, cette table sera mise à jour toute les minutes :

postgres@vpsfrsqlpac1$ psql -p30001

create table heartbeat ( reptimestamp timestamp );

insert into heartbeat values (now());

select * from heartbeat;
        reptimestamp
----------------------------
 2019-11-22 09:04:36.399274

Vérifier la réplication sur le serveur de standby :

postgres@vpsfrsqlpac2$ psql -p30001

select * from heartbeat;
        reptimestamp
----------------------------
 2019-11-22 09:04:36.399274

Pause / Reprise de la réplication

Pour mettre en pause, reprendre la réplication, dans le serveur standby :

postgres@vpsfrsqlpac2$ psql -p30001

PostgreSQL 9.6 PostgreSQL 10 / 11

Pause de la réplication

	PostgreSQL 9.6	PostgreSQL 10 / 11
Pause de la réplication	`select pg_xlog_replay_pause();` `2019-11-22 19:58:33.686 CET LOG: recovery has paused`	`select pg_wal_replay_pause();`
Reprise de la réplication	`select pg_xlog_replay_resume();`	`select pg_wal_replay_resume();`
Réplication en pause ?	`select pg_is_xlog_replay_paused();` `pg_is_xlog_replay_paused -------------------------- f`	`select pg_is_wal_replay_paused();` `pg_is_wal_replay_paused -------------------------- f`

select pg_xlog_replay_pause();

2019-11-22 19:58:33.686 CET LOG:  recovery has paused

select pg_wal_replay_pause();

Reprise de la réplication

select pg_xlog_replay_resume();

select pg_wal_replay_resume();

Réplication en pause ?

select pg_is_xlog_replay_paused();

 pg_is_xlog_replay_paused
--------------------------
 f

select pg_is_wal_replay_paused();

 pg_is_wal_replay_paused
--------------------------
 f

Informations essentielles sur la réplication

Le monitoring de la réplication n’est pas la sujet de cet article, néanmoins ci-dessous les informations essentielles sur l’état de la réplication.

Serveur de standby : pg_stat_wal_receiver

Dans la standby, consulter la vue pg_stat_wal_receiver :

postgres@vpsfrsqlpac2$ psql -p30001

\x on;
select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------------------------------------------
pid                   | 2262
status                | streaming
receive_start_lsn     | 0/97000000
receive_start_tli     | 1
received_lsn          | 0/99000920
received_tli          | 1
last_msg_send_time    | 2019-11-22 18:17:46.355579+01
last_msg_receipt_time | 2019-11-22 18:17:46.355835+01
latest_end_lsn        | 0/99000760
latest_end_time       | 2019-11-22 18:15:46.232277+01
slot_name             | standby1
conninfo              | user=repmgr password=******** dbname=replication host=vpsfrsqlpac1 port=30001 …

Le process id du récepteur WAL est : 2262

postgres@vpsfrsqlpac2$ ps -ef | grep 'postgres' | grep 2262

postgres  2262 32104  0 18:35 ?        00:00:04 postgres: wal receiver process   streaming 0/99000920

Serveur primaire : pg_stat_replication et pg_replication_slots

Dans la serveur primaire, consulter pg_stat_replication et pg_replication_slots :

postgres@vpsfrsqlpac1$ psql -p30001

\x on;
select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid              | 6247
usesysid         | 16384
usename          | repmgr
application_name | walreceiver
client_addr      | 51.xxx.xxx.xxx
client_hostname  |
client_port      | 41354
backend_start    | 2019-11-22 09:35:42.41099+01
backend_xmin     |
state            | streaming
sent_location    | 0/99000920
write_location   | 0/99000920
flush_location   | 0/99000920
replay_location  | 0/99000840
sync_priority    | 0
sync_state       | async
\x on;
select * from pg_replication_slots;
-[ RECORD 1 ]-------+-----------
slot_name           | standby1
plugin              |
slot_type           | physical
datoid              |
database            |
active              | t
active_pid          | 6247
xmin                |
catalog_xmin        |
restart_lsn         | 0/99000920
confirmed_flush_lsn |

Le process id de l’émetteur WAL est : 6247

postgres@vpsfrsqlpac1$ ps -ef | grep 'postgres' | grep 6247

postgres  6247  5576  0 18:35 ?        00:00:00 postgres: wal sender process repmgr 51.xxx.xxx.xxx(41354) streaming 0/99000920

Conclusion

La réplication en continu (Streaming replication) avec PostgreSQL 9.6 est très simple à mettre en route, peut-être l’une des architectures de réplication les plus faciles.

Ne pas oublier les points de réplication (replication slots) ! Seuls les points de réplication garantissent que les serveurs de standby ne tomberont pas en désynchronisation du serveur primaire.

Réplication en continu avec PostgreSQL 9.6, 10 & 11 (Streaming replication) - Bases de données PostgreSQL Standby