[pgpool-general: 8660] R: Failover doesn't promote standby server to new one (PgPool-II 4.1.3 PostGres 13)

[Marco Ferrara]

Hi Bo, 

the shutdown command is corrected executed (attached log files from servers) but the situation still all standby servers and no primary

[postgres at sv25243 ~]$ pcp_watchdog_info -p 9898 -h vip-amr-prod -U pgpool
Password:
3 YES sv25243:9999 Linux sv25243 sv25243

sv25243:9999 Linux sv25243 sv25243 9999 9000 4 MASTER
sv25244:9999 Linux sv25244 sv25244 9999 9000 7 STANDBY
sv25242:9999 Linux sv25242 sv25242 9999 9000 7 STANDBY
[postgres at sv25243 ~]$  psql -h vip-amr-prod -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id |       hostname       | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | sv25244 | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2023-03-17 15:57:27
 1       | sv25243 | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2023-03-17 15:57:27
 2       | sv25242 | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2023-03-17 15:57:27
(3 rows)

[postgres at sv25243 ~]$ pcp_watchdog_info -p 9898 -h vip-amr-prod -U pgpool
Password:
3 YES sv25243:9999 Linux sv25243 sv25243

sv25243:9999 Linux sv25243 sv25243 9999 9000 4 MASTER
sv25244:9999 Linux sv25244 sv25244 9999 9000 7 STANDBY
sv25242:9999 Linux sv25242 sv25242 9999 9000 7 STANDBY
[postgres at sv25243 ~]$  psql -h vip-amr-prod -p 9999 -U pgpool postgres -c "show pool_nodes"
^C
[postgres at sv25243 ~]$  psql -h vip-amr-prod -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id |       hostname       | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | sv25244 | 5432 | down   | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-03-20 22:10:58
 1       | sv25243 | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-03-17 15:57:27
 2       | sv25242 | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 |                   |                        | 2023-03-17 15:57:27
(3 rows)

MARCO FERRARA
IT Department

A.   OPPENT S.P.A. Via Fiuggi 38/A, 20159 Milano, Italy





Company | Mobile Robots | Trade Shows | LinkedIn | YouTube





-----Messaggio originale-----
Da: Bo Peng <pengbo at sraoss.co.jp> 
Inviato: lunedì 20 marzo 2023 12:31
A: Marco Ferrara <m.ferrara at oppent.it>
Cc: pgpool-general at pgpool.net
Oggetto: Re: [pgpool-general: 8645] Failover doesn't promote standby server to new one (PgPool-II 4.1.3 PostGres 13)

Hello,

> i've configured the cluster following the guide https://www.pgpool.net/docs/41/en/html/example-cluster.html, so I obtain the situation with one primary and two standby.
> 
>   *   When I switch off pgpool service on primary e new pgpool is elected ad master and is ok
>   *   When I switch off postgres, failover.sh is launched, a new postgres is promoted as primary but in show_pgpool_nodes the up nodes are all in standby mode. If I restart postgres service and pgpool service, on the elected standby it appears as new primary but no streaming with the others.
> 
> What I have to check?

Please check the pgpool.log if failover.sh succeeded and after running failover.sh if pgpool was able to detect the new primary.

--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PgPool.zip
Type: application/x-zip-compressed
Size: 39136 bytes
Desc: PgPool.zip
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230320/ff7d4391/attachment-0001.bin>