search

pgpool / pgpool2

This is the official mirror of git://git.postgresql.org/git/pgpool2.git. Note that this is just a *mirror* - we don't work with pull requests on github. Please subscribe to pgpool-hackers mailing list from our website and submit your patch to this mailing list.
https://www.pgpool.net
Other
326 stars 88 forks source link

INSERT statements sent to standby #13

Closed Raphyyy closed 6 years ago

Raphyyy commented 6 years ago

Hi and thanks for your work.

I use pgpool2 3.7.2 with 2 backend as master-slave mode with native stream replication.

I think I have an issue whenever I do these steps :

  1. Node 0 is master, node 1 is slave. Everything goes well.
  2. I shut node 0, which causes failover and promote node 1 to the new master. Everything goes well, I still can insert datas here.
  3. I do an online recovery of the node 0. So it goes as standby then. Since here, pgpool send all INSERT statement on the backend 0.

Here is pgpool log which demonstrate well the weird behaviour : 

root@srv:~# pgpool -n
2018-04-04 14:26:01: pid 28055:LOG:  Setting up socket for 0.0.0.0:21042
2018-04-04 14:26:01: pid 28055:LOG:  Setting up socket for :::21042
2018-04-04 14:26:01: pid 28055:LOG:  find_primary_node_repeatedly: waiting for finding a primary node
2018-04-04 14:26:01: pid 28055:LOG:  find_primary_node: checking backend no 0
2018-04-04 14:26:01: pid 28055:LOG:  find_primary_node: checking backend no 1
2018-04-04 14:26:01: pid 28055:LOG:  find_primary_node: primary node id is 1
2018-04-04 14:26:01: pid 28055:LOG:  pgpool-II successfully started. version 3.7.2 (amefuriboshi)
2018-04-04 14:26:10: pid 28075:LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 28098 statement: "insert into "***[...]***" message: "cannot execute INSERT in a read-only transaction"

The primary node is node 1, however it send INSERT on the standby. If I shut node 0, INSERT are sent to node 1 just well. If I restart node 0, the problem shows up again.

With select pg_is_in_recovery(); and pcp_node_info I can see that node 1 is the master and node 0 is the standby.

I can manually INSERT on Node 1 directly. On Node 0, I have the same error as expected : cannot execute INSERT in a read-only transaction

With log_error_verbosity = verbose I have no revelant information.

Here is my pgpool.conf :

pgpool.conf.txt

pengbo0328 commented 6 years ago

There was a bug in Pgpool-II 3.7.2 when primary node is not node 0. We reverted the commit that caused this bug and released a update version 3.7.2-2.

Could you try the latest release 3.7.2-2? pgpool-II-pgxx-3.7.2-2pgdg.rhel7.x86_64.rpm

Download from here: http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/

Raphyyy commented 6 years ago

Indeed, issue solved with latest source (https://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=af4ea10d139a7471ee912140e5f44a0d085bee44;sf=tgz)