Postgresql and Logical Replication

  • Mon, Nov 27, 2017
  • 5-minute read
!
Warning: This post is over 365 days old. The information may be out of date.

In this post, I will test the new replication in PostgreSQL 10 : logical replication.

Don’t forget to read the very good documentation

Logical replication

Before the version 10 of PostgreSQL, we could only replicate the whole cluster (all the databases). As usual with PostgreSQL, everything works well but in some case, it was not very usefull (why replicate all your To of data when you only need xx Go ?).

Logical replication will allow you to replicate only certains tables. The operating principle is the following:

  • Choose the tables on the master you want to replicate and create a publication
  • On the slave, we will create a subscription

Warning: the database schema and DDL comands are not replicated. You will have to keep them in sync.

More informations : restrictions

Setup our lab with Docker

We will test the logical replication using Docker

  • Create our environment test with 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

The master

  • Change the WAL level:

    The logical replication needs at least a WAL level >= logical. We will change this setting and restart the container.

    % 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

  • Create a database and a 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=#

Warning : the tables must have a primary key or an unique index. If not, you will have to define a replica identity

  • Insert some data :

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

  • Create an user for the replication:

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

  • Create a publication for the table hashes:

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

  • Change rights for the user replicate:

    pea=# GRANT SELECT ON hashes TO replicate  ;
GRANT

Our replicate user must be able to read the data so we give it the read only bit.

Now our master is ready. Let’s go to the slave.

The slave

  • Create a database:

    % 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=#

  • Create a subscription:

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

PostgreSQL is not happy. The table doesn’t exist, so we will create it.

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

Create the subscription again:

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

Now in the logs :

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

All the data in the table are copied :

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

Our logical replication is now working.

And then ?

  • Add a column and drop some data:

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

    Now we have an error message on the 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

    The master is not happy too:

    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

    Create the column on the slave:

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

    And the logical replication will now restart :

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

  • Add a table, insert some data and update the 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

    Add the table on the slave:

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

    Refresh the subscription on the slave:

    pea_repl=# alter subscription subhash refresh publication ;
ALTER SUBSCRIPTION

    Watch logs on the 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

    Check on the slave :

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

  • Stop logical replication on the slave :

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

Related Posts

PostgreSQLDocker replicationpgen