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

[Bo Peng]

hello,

> 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)

failover.sh script was executed, but pgpool could not find the new primary.
Could you add "set -o xtrace" to the failover.sh script to verify
if the script was executed successfully.

------
Mar 20 22:05:52 sv25243 pgpool[397550]: [24-1] 2023-03-20 22:05:52: pid 397550: LOG: 
execute command: /etc/pgpool-II-13/failover.sh 0 sv25244.regsj.intern 5432 /var/lib/pgsql/13/data 1 sv25243.regsj.intern 0 0 5432 /var/lib/pgsql/13/data sv25244.regsj.intern 5432
------

Could you share your pgpool.conf?

-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/