PostgreSQL 10 et la réplication logique – Mise en oeuvre
Cet article est la suite d’une série d’articles sur la réplication logique dans la version 10 de PostgreSQL
Celui-ci va porter sur la mise en œuvre de la réplication logique.
- PostgreSQL 10 et la réplication logique - Fonctionnement
- PostgreSQL 10 et la réplication logique - Mise en oeuvre
- PostgreSQL 10 et la réplication logique - Restrictions
Table des matières
Installation
Lors de la rédaction de cet article la version 10 n’est pas encore sortie. Cependant la communauté met à disposition les paquets des versions bêtas. Bien entendu, à ne pas utiliser en production.
Installation du dépôt pgdg (PostgreSQL Developpement Group)
Il suffit d’aller sur le site http://www.postgresql.org puis « download » -> « debian ». Sur cette page, le site vous indique la marche à suivre pour installer le dépôt du pgdg. Cependant il ne va vous proposer que les versions stables. Mais, le site vous renvoie vers une page du wiki :
For more information about the apt repository, including answers to frequent questions, please see the apt page on the wiki.
Sur la page wiki vous trouverez :
For packages of development/alpha/beta versions of PostgreSQL, see the FAQ entry about beta versions.
Qui vous indique :
To use packages of postgresql-10, you need to add the 10 component to your
/etc/apt/sources.list.d/pgdg.listentry, so the 10 version of libpq5 will be available for installation
Donc pour debian, les commandes se résument à :
1wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
2sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main 10" > /etc/apt/sources.list.d/pgdg.list'
3sudo apt-get update
Installation des paquets
1sudo apt install postgresql-10
Création des instances
Une première instance est créée lors de l’installation. Nous allons installer une seconde instance. Ainsi l’une sera le « publisher » et l’autre sera « subscriber ».
1# pg_lsclusters
2Ver Cluster Port Status Owner Data directory Log file
310 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
4
5# pg_createcluster 10 sub
6Creating new PostgreSQL cluster 10/sub ...
7/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/sub --auth-local peer --auth-host md5
8The files belonging to this database system will be owned by user "postgres".
9This user must also own the server process.
10
11The database cluster will be initialized with locale "en_US.UTF-8".
12The default database encoding has accordingly been set to "UTF8".
13The default text search configuration will be set to "english".
14
15Data page checksums are disabled.
16
17fixing permissions on existing directory /var/lib/postgresql/10/sub ... ok
18creating subdirectories ... ok
19selecting default max_connections ... 100
20selecting default shared_buffers ... 128MB
21selecting dynamic shared memory implementation ... posix
22creating configuration files ... ok
23running bootstrap script ... ok
24performing post-bootstrap initialization ... ok
25syncing data to disk ... ok
26
27Success. You can now start the database server using:
28
29/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/sub -l logfile start
30
31Ver Cluster Port Status Owner Data directory Log file
3210 sub 5433 down postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log
33
34# pg_ctlcluster 10 sub start
35
36# pg_lsclusters
37Ver Cluster Port Status Owner Data directory Log file
3810 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
3910 sub 5433 online postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log
Mise en place de la réplication logique
Prérequis
Il y a très peu de changements à apporter à la configuration par défaut. La plupart des paramètres sont déjà positionnés pour mettre en place la réplication logique. Toutefois il y a un paramètre à modifier sur l’instance qui « publie » : le wal_level. Celui-ci est à “replica” , il doit être placé à “logical” .
Ca se passe dans /etc/postgresql/10/main/postgresql.conf
1wal_level = logical
Pour appliquer le changement il faut redémarrer l’instance :
1pg_ctlcluster 10 main restart
Publication
Créons une base b1 qui contient une table t1.
1postgres=# create database b1;
2CREATE DATABASE
3postgres=# \c b1
4You are now connected to database "b1" as user "postgres".
5
6b1=# create table t1 (c1 text);
7CREATE TABLE
8b1=# insert into t1 values ('un');
9INSERT 0 1
10b1=# select * from t1;
11 c1
12----
13 un
14(1 row)
Ensuite nous allons jouer l’ordre pour créer la publication :
1b1=# CREATE PUBLICATION pub1 FOR TABLE t1 ;
Et c’est tout! Notez qu’il est possible d’utiliser le mot clé « FOR ALL TABLES » pour que le PostgreSQL ajoute toutes les tables présentes et futures à la publication.
On peut vérifier que la publication a bien été créée avec la méta-commande psql suivante :
1b1=# \dRp+
2 Publication pub1
3 All tables | Inserts | Updates | Deletes
4------------+---------+---------+---------
5 f | t | t | t
6Tables:
7 "public.t1"
Souscription
La réplication logique ne réplique pas les ordres DDL, nous devons donc créer la table sur l’instance « sub ». Il n’est pas nécessaire d’avoir le même nom de base, nous allons donc créer une base b2.
1postgres@blog:~$ psql -p 5433
2psql (10beta2)
3Type "help" for help.
4
5postgres=# CREATE DATABASE b2;
6CREATE DATABASE
7postgres=# \c b2
8You are now connected to database "b2" as user "postgres".
9b2=# create table t1 (c1 text);
10CREATE TABLE
Comme pour la publication, la souscription se crée avec un ordre SQL :
1b2=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1' PUBLICATION pub1;
2NOTICE: created replication slot "sub1" on publisher
3CREATE SUBSCRIPTION
4b2=# select * from t1;
5 c1
6----
7 un
8(1 row)
La partie la plus importante est : CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1'
En effet, on indique au « subscriber » comment se connecter à la publication. Dans mon exemple les deux instances sont sur la même machine et écoutent sur une socket locale. Si vous avez des instances sur différentes machines il faudra spécifier l’adresse IP. Ensuite le port et ne surtout pas oublier la base où a été créée la publication.
Comme vous pouvez le voir, le moteur s’est automatiquement chargé de rapatrier les données qui étaient déjà présentes dans la table t1.
Maintenant si j’ajoute des lignes dans la table t1, elles seront automatiquement répliquées sur l’instance « sub » :
1postgres@blog:~$ psql b1
2psql (10beta2)
3Type "help" for help.
4
5b1=# insert into t1 values ('deux');
6INSERT 0 1
7postgres=# \q
8postgres@blog:~$ psql -p 5433 b2
9psql (10beta2)
10Type "help" for help.
11b2=# select * from t1;
12 c1
13------
14 un
15 deux
16(2 rows)
Comme pour la publication, il existe une méta-commande pour afficher les souscriptions :
1\dRs+
2 List of subscriptions
3 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
4------+----------+---------+-------------+--------------------+----------------------------------------------
5 sub1 | postgres | t | {pub1} | off | host=/var/run/postgresql port=5432 dbname=b1
6(1 row)
C’est tout pour cet article, dans un prochain article nous verront les restrictions d’usage.
J’ai occupé pendant 7 ans divers postes d’ingénieur système et réseau. Et c’est à partir de 2013 que j’ai commencé à mettre les doigts dans PostgreSQL. J’ai occupé des postes de consultant, formateur, mais également DBA de production (Doctolib, Peopledoc).
Ce blog me permet de partager mes connaissances et trouvailles, ainsi que mes interventions et conférences.