PostgreSQL et la réplication logique

Cet article va tester la nouvelle fonctionnalité disponible depuis PostgreSQL 10.0 : la réplication logique.

Pour en savoir plus, l’excellente documentation de PostgreSQL

La réplication logique

Avant la version 10 de PostgreSQL, la réplication permettait uniquement de répliquer tout le cluster (c’est à dire l’ensemble des bases). Comme d’habitude avec PostgreSQL, tout fonctionne correctement mais il faut bien avouer que dans certains cas, cela ne s’avère pas très pratique (pourquoi répliquer vos xx To de données quand seulement quelques Go vous intéressent ? ).

La réplication logique va nous permettre de répliquer uniquement certaines tables. Le principe (simplifié) va être le suivant:

• On sélectionne les tables sur le master et on fait une publication de ces dernières (afin de pouvoir les répliquer)
• Sur le slave, on va faire une subscription afin de pouvoir récupérer le contenu de ces tables

Attention: les changements de structures et les commandes DDL ne passent pas dans la réplication logique. Il faudra les rejouer à la main sur le slave.

Plus d’informations ici : restrictions

Mise en oeuvre avec Docker

Notre petit lab va se faire avec Docker afin de pouvoir tester assez rapidement.

• Création de l’environnement Docker

  % docker network create lab
  f77d78a9a512af1afbbc7e5cb00a7e186d82ce1ba122c9c7353be8db344497ab
  % docker container run --detach --name pgmaster --network lab -e POSTGRES_PASSWORD=password postgres:10.1-alpine
  c5511d8d383fdd4c899dbd1cd03c16b6044168eca78052f9d5ed48e484c257f2
  % docker container run --detach --name lab --network lab -e POSTGRES_PASSWORD=password postgres:10.1-alpine
  8f2a1a43f211d45314bf52eadef66d125be5b9ad37ab16f881f039b32bd887d4
  

Le master

• Activation du bon niveau de WAL:

  La réplication logique demande un niveau de WAL >= logical. Nous allons donc le changer et redémarrer le container pour que cela soit pris en compte.

  % docker container exec -it pgmaster /bin/sh
  / # psql -U postgres
  psql (10.1)
  Type "help" for help.
  
  postgres=# ALTER SYSTEM SET wal_level = 'logical';
  ALTER SYSTEM
  postgres=# exit
  % docker container restart pgmaster
  

• Création d’une base et d’une table hashes:

  % docker container exec -it pgmaster /bin/sh
  / # psql -U postgres
  psql (10.1)
  Type "help" for help.
  
  postgres= create database pea ;
  CREATE DATABASE
  postgres=# \c pea 
  You are now connected to database "pea" as user "postgres".
  pea=# create table hashes (id serial, value char(33), primary key(value) ) ;
  CREATE TABLE
  pea=#
  

  Attention : les tables doivent avoir une clef primaire ou un index unique. Si ce n’est pas le cas, il faudra définir une replica identity

• Remplissage de la table :

  pea=# insert into hashes (select generate_series(1,1000),md5(random()::text)) ;
  INSERT 0 1000
  

• Création d’un utilisateur pour la réplication (toujours sur le master):

  pea=# CREATE ROLE replicate WITH LOGIN PASSWORD 'Azerty' REPLICATION ;
  CREATE ROLE
  

• Création d’une publication pour la table hashes:

  pea=# CREATE PUBLICATION pubhash FOR TABLE hashes ;
  CREATE PUBLICATION
  

• Modification des droits pour l’utilisateur faisant la réplication:

  pea=# GRANT SELECT ON hashes TO replicate  ;
  GRANT
  

  Il est important que l’utilisateur faisant la réplication (ici replicate) puisse accéder aux données de la table. Il faut donc lui donner les droits en lecture.

Maintenant notre master est prêt et nous allons passer sur le slave.

Le slave

• Création d’une base:

  % docker container exec -it pgslave /bin/sh 
  / # psql -U postgres
  psql (10.1)
  Type "help" for help.
  
  postgres=# create database pea_repl ;
  CREATE DATABASE
  postgres=# \c pea_repl ;
  You are now connected to database "pea_repl" as user "postgres".
  pea_repl=#
  

• Création d’une subscription:

  pea_repl=# CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty' PUBLICATION pubhash ;
  ERROR:  relation "public.hashes" does not exist
  

  Bon la c’est moche.. PostgreSQL se plaint que la table n’existe pas. Nous allons donc la créer.

  pea_repl=# create table hashes (id serial, value char(33), primary key(value) ) ;
  CREATE TABLE
  

  Et refaire notre subscription:

  pea_repl=# CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty' PUBLICATION pubhash ;
  NOTICE:  created replication slot "subhash" on publisher
  CREATE SUBSCRIPTION
  

  Dans les logs nous pouvons faire ceci :

  2017-11-27 09:16:20.442 UTC [87] LOG:  logical replication apply worker for subscription "subhash" has started
  2017-11-27 09:16:20.451 UTC [88] LOG:  logical replication table synchronization worker for subscription "subhash", table "hashes" has started
  2017-11-27 09:16:20.472 UTC [88] LOG:  logical replication table synchronization worker for subscription "subhash", table "hashes" has finished
  

  A noter que si la table contient des données, elles seront automatiquement copiées

  pea_repl=# select count(*) from hashes ;
   count 
  -------
    1000
  (1 row)
  

Notre réplication logique est fonctionnelle.

Et après ?

• Ajout d’une colonne sur le master et suppression des données:

  pea=# alter table hashes add column gold boolean default false ;
  ALTER TABLE
  pea=# delete from hashes ;
  DELETE 1000
  

  Nous avons maintenant un message d’erreur dans les logs du slave:

  2017-11-27 09:25:25.886 UTC [87] ERROR:  logical replication target relation "public.hashes" is missing some replicated columns
  2017-11-27 09:25:25.887 UTC [1] LOG:  worker process: logical replication worker for subscription 16394 (PID 87) exited with exit code 1
  

  Le master se plaint aussi:

  2017-11-27 09:27:41.303 UTC [135] LOG:  starting logical decoding for slot "subhash"
  2017-11-27 09:27:41.303 UTC [135] DETAIL:  streaming transactions committing after 0/1617B40, reading WAL from 0/165E1D8
  2017-11-27 09:27:41.303 UTC [135] LOG:  logical decoding found consistent point at 0/165E1D8
  2017-11-27 09:27:41.303 UTC [135] DETAIL:  There are no running transactions.
  2017-11-27 09:27:41.309 UTC [135] LOG:  could not send data to client: Connection reset by peer
  2017-11-27 09:27:41.309 UTC [135] CONTEXT:  slot "subhash", output plugin "pgoutput", in the change callback, associated LSN 0/165F0B0
  

  On répercute donc le changement sur le slave:

  pea_repl=# alter table hashes add column good boolean default false ;
  ALTER TABLE
  

  Et la réplication va repartir toute seule (et donc supprimer les données suite au delete):

  2017-11-27 09:28:46.502 UTC [154] LOG:  logical replication apply worker for subscription "subhash" has started
  

• Ajout d’une table, insertion de données et modification de la publication:

  % docker container exec -it pgmaster /bin/sh
  / # psql -U postgres pea
  psql (10.1)
  Type "help" for help.
  
  pea=# create table hash2hash (id serial, value char(33), primary key(value) ) ;
  CREATE TABLE
  pea=# grant select on hash2hash to replicate ;
  GRANT
  pea=# insert into hash2hash (select generate_series(1,1000),md5(md5(random()::text))) ;
  INSERT 0 1000
  pea=# alter publication pubhash add table hash2hash ;
  ALTER PUBLICATION
  

  Maintenant sur le slave, on rajoute la table manquante:

  pea_repl=# create table hash2hash (id serial, value char(33), primary key(value) ) ;
  CREATE TABLE
  

  On rafraîchit la subscription sur le slave:

  pea_repl=# alter subscription subhash refresh publication ;
  ALTER SUBSCRIPTION
  

  Les logs sur le slave:

  2017-11-27 10:13:21.097 UTC [244] LOG:  logical replication table synchronization worker for subscription "subhash", table "hash2hash" has started
  2017-11-27 10:13:21.132 UTC [244] LOG:  logical replication table synchronization worker for subscription "subhash", table "hash2hash" has finished
  

  On vérifie:

  pea_repl=# select count(*) from hash2hash ;
   count 
  -------
  1000
  (1 row)
  

• Arrêter la réplication sur le slave:

  pea_repl=# alter subscription subhash disable ;
  ALTER SUBSCRIPTION
  pea_repl=# drop subscription subhash ;
  NOTICE:  dropped replication slot "subhash" on publisher
  DROP SUBSCRIPTION