Postgresql and logical replication

November 27, 2017
PostgreSQL docker replication pgen

PostgreSQL and the logical replication

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 will allow you to replicate only certains tables. The operating principle is the following:

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

More informations : restrictions

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

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.

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

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

Create the subscription again:

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

Now in the logs :

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 :

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

Our logical replication is now working.

Setup a PostgreSQL cluster with repmgr and pgbouncer

December 22, 2018
PostgreSQL replication pgen repmgr pgbouncer

Setup a streaming replication with PostgreSQL 10

March 11, 2018
postgresql pgen

OpenBSD / PostgreSQL / Authentication

November 29, 2017
openbsd postgresql pgen