Postgresql et la réplication logique
November 27, 2017
PostgreSQL docker replication pgfrPostgreSQL 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