PostgreSQL permet nativement la mise en place de réplication physique en log shipping ou streaming replication. Ces méthodes permettent de configurer une réplication au niveau du cluster de bases de données dans son entièreté.
Si l’on souhaite répliquer par base de données ou bien choisir plus finement un sous-ensemble de données à répliquer, alors il faudra se tourner vers une réplication logique.
pglogical répond à ce besoin en permettant la mise en place d’un jeu de réplication (Replication Set) qui consiste en une liste de tables qui seront répliquées sur l’instance distante. Cette réplication logique peut également être une bonne méthode d’upgrade vers une version majeure supérieure.

1. Environnement

Le lab pour cette installation consiste en deux serveurs CentOS 7.8 hébergeant chacun une instance PostgreSQL 12.

• pg1 – Serveur Provider qui contient une base de données à répliquer (dbname « communes »)
• pg2 – Serveur Subscriber qui sera la destination de la réplication

L’instance sur pg1 a été chargée avec un jeu de données (référentiel des communes, départements et régions de France) dans la base de données communes.
L’objectif est de répliquer un sous-ensemble de cette base de données sur pg2.

2. Installation

L’outil pglogical est fourni par 2nd Quadrant. Par conséquent, le package peut être installé à partir de leur repository.

Se connecter en tant que root au serveur provider, et installer le repository officiel de 2ndQuadrant :

curl https://dl.2ndquadrant.com/default/release/get/11/rpm | bash
yum update

Installer le package pglogical :

yum install pglogical_12.x86_64

Répéter ces opération sur le serveur subscriber afin de disposer de l’extension sur les deux nœuds.

3. Configuration

Configurer l’instance provider pour charger les bibliothèques pglogical et configurer le niveau de wal pour supporter la réplication logique :

vi ${PGDATA}/postgresql.conf
--------------------------------------
wal_level = 'logical'	
max_worker_processes = 10  
max_replication_slots = 10 
max_wal_senders = 10       
shared_preload_libraries = 'pglogical'
--------------------------------------
pg_ctl restart

Créer l’extension pglogical :

psql -d communes
postgres=# CREATE EXTENSION pglogical;

Configurer l’instance subscriber pour charger les bibliothèques pglogical :

vi ${PGDATA}/postgresql.conf
--------------------------------------
max_worker_processes = 10  
shared_preload_libraries = 'pglogical'
--------------------------------------
pg_ctl restart

Créer la base de données communes vide et y créer l’extension pglogical :

createdb communes
psql -d communes
postgres=# CREATE EXTENSION pglogical;

Sur les deux nœuds, créer l’utilisateur de réplication :

psql
postgres=# CREATE ROLE pgl_rep WITH SUPERUSER REPLICATION LOGIN ENCRYPTED PASSWORD 'secret';

Sur les deux nœuds, ajouter dans le fichier pg_hba.conf les lignes permettant la connexion à la pseudo-database « replication » ainsi que la base de données concernée par la réplication « communes ».

host	replication     pgl_rep		127.0.0.1/32	md5
host	communes        pgl_rep		127.0.0.1/32	md5
host	communes	pgl_rep		hote_distant	md5

Sur le provider, créer le noeud pour la réplication :

SELECT pglogical.create_node(
    node_name := 'pg1_provider',
    dsn := 'host=pg1.lan port=5432 dbname=communes'
);

Créer un jeu de réplication et y ajouter la table des communes (towns) seulement :

select pglogical.create_replication_set('communes');
select pglogical.replication_set_add_table('communes','towns'); # Note : La table doit avoir une clé primaire

Sur le subscriber, créer le noeud pour la réplication de la base de données communes :

SELECT pglogical.create_node(
    node_name := 'pg2_subscriber',
    dsn := 'host=pg2.lan port=5432 dbname=communes'
);

Créer la table towns vide pour préparer son alimentation par la souscription :

psql -d communes
postgres=# CREATE TABLE public.towns (
			id integer NOT NULL,
			code character varying(10) NOT NULL,
			article text,
			name text NOT NULL,
			department character varying(4) NOT NULL
			);

Souscrire à l’abonnement au jeu de réplication « communes » :

postgres=#SELECT pglogical.create_subscription(
			subscription_name := 'sub_communes',
			provider_dsn := 'host=pg1.lan port=5432 dbname=communes',
			replication_sets := '{communes}',
			synchronize_structure := 'false'
			);

La table commune doit alors faire l’objet d’une synchronisation initiale, ce qui fait la table est alimentée directement :

psql -d communes
communes=# select count(*) from towns;
 count
-------
 36684
(1 ligne)

On retrouve bien ici l’intégralité de lignes la table dans sa copie sur pg2. Chaque nouveau INSERT,DELETE ou UPDATE exécuté sur la table sera répliqué au niveau SQL vers l’instance pg2, la maintenant ainsi à jour.

Ici, nous avons opté pour la choix d’une unique table pour le jeu de réplication, mais il est également possible d’ajouter tout un schéma dans le jeu de tables via la fonction pglogical.replication_set_add_all_tables.

 
Et si vous souhaitez vous former sur PostgreSQL, découvrez notre offre de formations PostgreSQL.