[pgpool-general: 8018] Re: Pool Nodes are shown as down even if PG and PGPOOL are up

Lucas root at sud0.nz
Wed Feb 9 07:53:13 JST 2022 

> On 5/02/2022, at 1:45 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> Hi,
>> 
>> I’m migrating my Database cluster to PostgreSQL 14 and have been trying PGPOOL for the Load Balancer feature only.
>> 
>> At the moment I have two servers, and even though PGPOOL and Postgres are up and running on both of them, “show pool_nodes” shows as one of them being down.
> 
> [snip]
> 
> Which version of Pgpool-II is this?

Version pgpool-II version 4.1.4 (karasukiboshi)

> 
> Can you share pgpool log upon starting up?

Sure… Logs are below:

Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:07: pid 1306560: LOG:  reading status file: 0 th backend is set to down status
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:07: pid 1306560: LOG:  memory cache initialized
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:07: pid 1306560: DETAIL:  memcache blocks :64
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:07: pid 1306560: LOG:  pool_discard_oid_maps: discarded memqcache oid maps
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:07: pid 1306560: LOG:  waiting for watchdog to initialize
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  setting the local watchdog node name to "pgsql-awsbau-masterdb.prod.foo.com:9999 Linux pgsql-awsbau-masterdb.prod.foo.com"
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  watchdog cluster is configured with 1 remote nodes
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  watchdog remote node:0 on pgsql-awsbau-slave01.prod.foo.com:9000
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  interface monitoring is disabled in watchdog
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  watchdog node state changed from [DEAD] to [LOADING]
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  new outbound connection to pgsql-awsbau-slave01.prod.foo.com:9000
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  setting the remote node "pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com" as watchdog cluster master
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  watchdog node state changed from [LOADING] to [INITIALIZING]
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  new watchdog node connection is received from "10.0.9.120:28815"
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: LOG:  new node joined the cluster hostname:"pgsql-awsbau-slave01.prod.foo.com" port:9000 pgpool_port:9999
Feb  8 22:49:07 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:07: pid 1306563: DETAIL:  Pgpool-II version:"4.1.4" watchdog messaging version: 1.1
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  watchdog node state changed from [INITIALIZING] to [STANDBY]
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  successfully joined the watchdog cluster as standby node
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: DETAIL:  our join coordinator request is accepted by cluster leader node "pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com"
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  watchdog process is initialized
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: DETAIL:  watchdog messaging data version: 1.1
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  Pgpool-II parent process received watchdog quorum change signal from watchdog
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  new IPC connection received
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  new IPC connection received
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  watchdog cluster now holds the quorum
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: DETAIL:  updating the state of quarantine backend nodes
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  new IPC connection received
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  new IPC connection received
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306569]: 2022-02-08 22:49:08: pid 1306569: LOG:  2 watchdog nodes are configured for lifecheck
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  we have joined the watchdog cluster as STANDBY node
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: DETAIL:  syncing the backend states from the MASTER watchdog node
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306569]: 2022-02-08 22:49:08: pid 1306569: LOG:  watchdog nodes ID:0 Name:"pgsql-awsbau-masterdb.prod.foo.com:9999 Linux pgsql-awsbau-masterdb.prod.foo.com"
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306569]: 2022-02-08 22:49:08: pid 1306569: DETAIL:  Host:"pgsql-awsbau-masterdb.prod.foo.com" WD Port:9000 pgpool-II port:9999
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306569]: 2022-02-08 22:49:08: pid 1306569: LOG:  watchdog nodes ID:1 Name:"pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com"
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306569]: 2022-02-08 22:49:08: pid 1306569: DETAIL:  Host:"pgsql-awsbau-slave01.prod.foo.com" WD Port:9000 pgpool-II port:9999
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  new IPC connection received
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  received the get data request from local pgpool-II on IPC interface
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: LOG:  get data request from local pgpool-II node received on IPC interface is forwarded to master watchdog node "pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com"
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306563]: 2022-02-08 22:49:08: pid 1306563: DETAIL:  waiting for the reply...
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  master watchdog node "pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com" returned status for 2 backend nodes
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  backend:1 is set to UP status
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: DETAIL:  backend:1 is UP on cluster master "pgsql-awsbau-slave01.prod.foo.com:9999 Linux pgsql-awsbau-slave01.prod.foo.com"
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  Setting up socket for 0.0.0.0:9999
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  Setting up socket for :::9999
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306605]: 2022-02-08 22:49:08: pid 1306605: LOG:  PCP process: 1306605 started
Feb  8 22:49:08 pgsql-awsbau-masterdb pgpool[1306560]: 2022-02-08 22:49:08: pid 1306560: LOG:  pgpool-II successfully started. version 4.1.4 (karasukiboshi)
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306571]: 2022-02-08 22:49:09: pid 1306571: LOG:  set SO_REUSEPORT option to the socket
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306570]: 2022-02-08 22:49:09: pid 1306570: LOG:  set SO_REUSEPORT option to the socket
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306571]: 2022-02-08 22:49:09: pid 1306571: LOG:  creating socket for sending heartbeat
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306571]: 2022-02-08 22:49:09: pid 1306571: DETAIL:  set SO_REUSEPORT
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306570]: 2022-02-08 22:49:09: pid 1306570: LOG:  creating watchdog heartbeat receive socket.
Feb  8 22:49:09 pgsql-awsbau-masterdb pgpool[1306570]: 2022-02-08 22:49:09: pid 1306570: DETAIL:  set SO_REUSEPORT


> 
> One thing I found weird is show pool_nodes shows "role" fields as
> "slave" or "master". However I see:
> 
>> backend_clustering_mode = 'streaming_replication'
> 
> In this case the role field should be either "primary" or standby.

Hmm... interesting!

My goal with PGPool is to only use it as Load Balancer. I have deployed PG-Pool across all my PG nodes (2 in total for testing). One master and one Slave, using PG’s native streaming replication feature.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220209/8f146497/attachment.htm>

More information about the pgpool-general mailing list