[pgpool-general: 7428] Re: Can logical replication be used with pgpool?

[Bo Peng]

Hi,

On Mon, 1 Mar 2021 15:27:30 +0100
Gunnar Morling <gunnar at hibernate.org> wrote:

> Hi,
> 
> For Debezium [1], a tool for change data capture, we've received the
> request to test streaming changes from Postgres via pgpool [2]. While
> Debezium can successfully connect via JDBC and obtain an initial
> snapshot of the data, streaming changes via logical replication fails,
> the replication stream seems not to be propagated through pgpool (see
> below for the exception).
> 
> Is this expected and logical replication cannot be used with pgpool,
> or is there some kind of configuration that needs to be applied to
> pgpool in order to use it with clients like Debezium?

Pgpool-II's logical replication mode has some limitations:

https://www.pgpool.net/docs/latest/en/html/configuring-pgpool.html#RUNNING-MODE
----------
In logical replication mode, PostgreSQL is responsible for synchronizing tables. Load balancing is possible in the mode. Since logical replication does not replicate all tables, it's user's responsibility to replicate the table which could be load balanced. Pgpool-II load balances all tables. This means that if a table is not replicated, Pgpool-II may lookup outdated tables in the subscriber side. 
----------

> Thanks a lot for any pointers,
> 
> --Gunnar
> 
> [1] https://debezium.io/
> [2] https://issues.redhat.com/browse/DBZ-2803
> 
> 
> ------ Exception below ------
> connect_1    | Caused by: org.postgresql.util.PSQLException: Database
> connection failed when writing to copy
> connect_1    | at
> org.postgresql.core.v3.QueryExecutorImpl.flushCopy(QueryExecutorImpl.java:1080)
> connect_1    | at
> org.postgresql.core.v3.CopyDualImpl.flushCopy(CopyDualImpl.java:28)
> connect_1    | at
> org.postgresql.core.v3.replication.V3PGReplicationStream.updateStatusInternal(V3PGReplicationStream.java:193)
> connect_1    | at
> org.postgresql.core.v3.replication.V3PGReplicationStream.timeUpdateStatus(V3PGReplicationStream.java:184)
> connect_1    | at
> org.postgresql.core.v3.replication.V3PGReplicationStream.readInternal(V3PGReplicationStream.java:126)
> connect_1    | at
> org.postgresql.core.v3.replication.V3PGReplicationStream.readPending(V3PGReplicationStream.java:80)
> connect_1    | at
> io.debezium.connector.postgresql.connection.PostgresReplicationConnection$1.readPending(PostgresReplicationConnection.java:462)
> connect_1    | at
> io.debezium.connector.postgresql.PostgresStreamingChangeEventSource.searchWalPosition(PostgresStreamingChangeEventSource.java:271)
> connect_1    | at
> io.debezium.connector.postgresql.PostgresStreamingChangeEventSource.execute(PostgresStreamingChangeEventSource.java:131)
> connect_1    | ... 7 more
> connect_1    | Caused by: java.net.SocketException: Broken pipe (Write failed)
> connect_1    | at
> java.base/java.net.SocketOutputStream.socketWrite0(Native Method)
> connect_1    | at
> java.base/java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:110)
> connect_1    | at
> java.base/java.net.SocketOutputStream.write(SocketOutputStream.java:150)
> connect_1    | at
> java.base/java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:81)
> connect_1    | at
> java.base/java.io.BufferedOutputStream.flush(BufferedOutputStream.java:142)
> connect_1    | at org.postgresql.core.PGStream.flush(PGStream.java:592)
> connect_1    | at
> org.postgresql.core.v3.QueryExecutorImpl.flushCopy(QueryExecutorImpl.java:1078)
> connect_1    | ... 15 more
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan