[pgpool-general: 8717] Re: Postgres 14 / Patroni 3.01 / PGPool 4.3.3..."play nice" together settings

[Tatsuo Ishii]

Hi,

> We want to examine using PGPool as an intelligent proxy only, while letting
> Patronic orchestrate failover/switcher and rewind/recovery.
> 
> Obviously, our failover_command and other similar settings should just be
> blank.  What other settings should I make to allow PGPool to recognize that
> the primary might have changed when PGPool is not executing that change?
> 
> We've tried setting backend_flag0/1/2 = 'DISALLOW_TO_FAILOVER', but PGPool
> did not recognize the change in primary.

You should not set DISALLOW_TO_FAILOVER. Even if you want setup
Pgpool-II as a proxy, you should not turn off failover because
Pgpool-II recognizes the primary change at failover. You just need to
disable failover_command and follow_primary_command. Also I recommend
to disable failover_on_backend_error to avoid accidental failover.
Thus followings are necessary set up:

failover_command = ''
follow_primary_command = ''
failover_on_backend_error = off

With this settings the actual operation looks like this (suppose we
have 3 PostgreSQL servers).

# The Initial state: node 0 is primary.

$ pcp_node_info -w -p 11001
localhost 11002 1 0.333333 waiting up primary primary 0 none none 2023-04-05 11:54:15
localhost 11003 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15
localhost 11004 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15

# Stop the primary

$ pg_ctl -D data0 stop
waiting for server to shut down.... done
server stopped

# node 0 is surely down.

$ pcp_node_info -w -p 11001
localhost 11002 1 0.333333 waiting down primary unknown 0 none none 2023-04-05 11:54:15
localhost 11003 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15
localhost 11004 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15

# make node 1 to new primary.

$ pg_ctl -D data1 promote
waiting for server to promote.... done
server promoted

# As you can see, pgpool reconizes new primary.

$ pcp_node_info -w -p 11001
localhost 11002 3 0.333333 down down standby unknown 0 none none 2023-04-05 11:55:16
localhost 11003 1 0.333333 waiting up primary primary 0 none none 2023-04-05 11:55:16
localhost 11004 1 0.333333 waiting up standby standby 104 none none 2023-04-05 11:54:15

Note that node 2 is not following new primary. Maybe Patroni takes care of this?

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp