[pgpool-hackers: 2483] Re: Dealing with logical replication

[Tatsuo Ishii]

I have committed the first cut of patch to deal with PostgreSQL 10's
logical replication.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=184b412db44644c66d56af249368b69bfad8b8f8

From: Tatsuo Ishii <ishii at sraoss.co.jp>
Subject: [pgpool-hackers: 2444] Dealing with logical replication
Date: Thu, 13 Jul 2017 15:15:01 +0900 (JST)
Message-ID: <20170713.151501.919414340630172858.t-ishii at sraoss.co.jp>

> Now that PostgreSQL 10 will introduce logical replication, Pgpool-II
> may need to do something. Here are my thoughts.
> 
> 1) Need to have new mode
> 
> We have streaming replication mode, native replication mode, master
> slave mode and raw mode. Logical replication replication does not fit
> into none of them because: 1) slave can be updated 2) not all table
> are replicated. So we need a new mode, say, "logical replication
> mode".

New master/slave sub mode "logical" is added to pgpool.conf.

master_slave_mode = on
master_slave_sub_mode = 'logical'

Note that to use the mode, you need to set "ALWAYS_MASTER" flag to the
master node.

backend_flag0 = ALWAYS_MASTER

Also new sample configuration file "pgpool.conf.sample-logical" is
added.

In this mode Pgpool-II behaves almost same as streaming replication
mode. In the source code I added new macro "SL_MODE" which is or'ing
of STREAM and LOGICAL macro. Most of the STREAM macro is replaced by
SL_MODE macro.

> 2) If #1 is our direction, pgpool_setup need to deal with it. Since
> the tool is aiming for testing/debugging, probably
> 
> - Similar to the native replication mode (i.e. physically replicating
>   databases in the initial processing).

I added new option "-m l", which will create a logical replication
test cluster. If we have two PostgreSQL clusters, with the mode those
clusters have identical contents. Also "wal_level = logical" is set
which is required to enable logical replication. Here is an example
session.

$ pgpool_setup -m l
Satrting set up in logical replication mode
[snip]
#1 port is 11002
#2 port is 11003
pgpool port is 11000
pcp port is 11001
The info above is in README.port.

$ ./startall 
waiting for server to start....19696 2017-08-17 16:28:28 JST LOG:  listening on IPv4 address "0.0.0.0"[snip]
server started

$ psql -p 11000 test	<-- Pgpool-II port
psql (11devel)
Type "help" for help.

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt | load_balance_node | replication_delay 
---------+----------+-------+--------+-----------+--------+------------+-------------------+-------------------
 0       | /tmp     | 11002 | up     | 0.500000  | master | 0          | false             | 0
 1       | /tmp     | 11003 | up     | 0.500000  | slave  | 0          | true              | 0
(2 rows)

test=# CREATE PUBLICATION mypub FOR ALL TABLES;
CREATE PUBLICATION
test=# CREATE TABLE t1 AS SELECT * FROM generate_series(1,100) a(i);
SELECT 100

$ psql -p 11003 test <-- slave port
psql (11devel)
Type "help" for help.
test=# CREATE TABLE t1(i int);
CREATE TABLE
test=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=test port=11002' PUBLICATION mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
test=# SELECT count(*) FROM t1;
 count 
-------
   100
(1 row)

test=# 

> - Recovery script or failover script is empty.
> 
> - Do we need a initial publication/subscription setting?

Not implemented.

> 3) What is "logical replication mode" anyway?
> 
> In my mind it should behave like the streaming replication mode. It
> may be required that only particular tables are load balanced. For
> now, we could ask users to use *redirect directives. More sophisticated
> load balancing could be invented, but I feel like it's too early to
> design it (maybe we should start it in Pgpool-II 3.8?)
> 
> An interesting idea would be, "auto copying DDLs". As you might know,
> logical replication does not replicate DDLs. It maybe convenient for
> users to let DDLs be copied by Pgpool-II (same thing can be said to
> TRUNCATE).

I have discussed in pgsql-hackers list but the responses were not very
positive, mainly difficulty with implementation.

https://www.postgresql.org/message-id/flat/CAMsr%2BYFMcHSN_SbrT0dXHhvrzCKHhiU22pGQwYxTO9pndyg4kw%40mail.gmail.com#CAMsr+YFMcHSN_SbrT0dXHhvrzCKHhiU22pGQwYxTO9pndyg4kw@mail.gmail.com

So I gave up the idea.

> Comments are welcome.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers