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 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

• 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
  

• 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.

• 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.

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

Et refaire notre subscription:

```bash
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 :

```bash
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

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

Notre réplication logique est fonctionnelle.

• 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:
  
   ```bash
   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:
     
   ```bash
   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:
  
   ```sql
   pea_repl=# alter table hashes add column gold boolean default false ;
   ALTER TABLE
  

     
   Et la réplication va repartir toute seule (et donc supprimer les données suite au *delete*):
  
   ```bash
   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:
     
   ```bash
   pea_repl=# create table hash2hash (id serial, value char(33), primary key(value) ) ;
   CREATE TABLE
  

     
   On *rafraîchit* la *subscription* sur le slave:
     
   ```bash
   pea_repl=# alter subscription subhash refresh publication ;
   ALTER SUBSCRIPTION
  

     
   Les logs sur le slave:
     
   ```bash
   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:
     
   ```bash
   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