[pgpool-general: 6748] Re: failover: no valid backend node found, no failover happens

[Tatsuo Ishii]

> Hi,
> I installed a new PgPool-II cluster with 3 dedicated nodes separated from
> the PostgreSQL nodes.
> PgPool-II version is 4.0.2-1 from Debian Buster, and PostgreSQL version is
> 11.5 from official PostgresSQL APT repository.
> 
> I configured the failover script, but when I test the master node shutdown,
> automatic failover does not happens from PgPool-II because it does not find
> any valid backend node:

At the time when you shutdown the primary PostgreSQL, other standby
PostgreSQL were aleady in down status, right? If so, it's not possible
to find any alternative to the previous primary PostgreSQL becuase
other PostgreSQL are all down.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> ================================================================
> Oct 21 18:22:26 proxy1 pgpool[9764]: [53-1] 2019-10-21 18:22:26: pid 9764:
> LOG:  watchdog is informed of failover start by the main process
> Oct 21 18:22:26 proxy1 pgpool[9764]: [53-2] 2019-10-21 18:22:26: pid 9764:
> LOCATION:  watchdog.c:2784
> Oct 21 18:22:26 proxy1 pgpool[9763]: [29-1] 2019-10-21 18:22:26: pid 9763:
> LOG:  starting degeneration. shutdown host postgres1(5432)
> Oct 21 18:22:26 proxy1 pgpool[9763]: [29-2] 2019-10-21 18:22:26: pid 9763:
> LOCATION:  pgpool_main.c:1867
> Oct 21 18:22:26 proxy1 pgpool[9763]: [30-1] 2019-10-21 18:22:26: pid 9763:
> WARNING:  All the DB nodes are in down status and skip writing status file.
> Oct 21 18:22:26 proxy1 pgpool[9763]: [30-2] 2019-10-21 18:22:26: pid 9763:
> LOCATION:  pgpool_main.c:3847
> Oct 21 18:22:26 proxy1 pgpool[9763]: [31-1] 2019-10-21 18:22:26: pid 9763:
> LOG:  failover: no valid backend node found
> Oct 21 18:22:26 proxy1 pgpool[9763]: [31-2] 2019-10-21 18:22:26: pid 9763:
> LOCATION:  pgpool_main.c:1917
> Oct 21 18:22:26 proxy1 pgpool[9763]: [32-1] 2019-10-21 18:22:26: pid 9763:
> LOG:  Restart all children
> Oct 21 18:22:26 proxy1 pgpool[9763]: [32-2] 2019-10-21 18:22:26: pid 9763:
> LOCATION:  pgpool_main.c:2017
> Oct 21 18:22:26 proxy1 pgpool[9763]: [33-1] 2019-10-21 18:22:26: pid 9763:
> LOG:  execute command: /usr/local/bin/pgpool-failover.sh 0 postgres1 0 -1
> "" ""
> Oct 21 18:22:26 proxy1 pgpool[9763]: [33-2] 2019-10-21 18:22:26: pid 9763:
> LOCATION:  pgpool_main.c:3064
> Oct 21 18:22:26 proxy1 pgpool[9763]: quarantine done. shutdown host
> postgres1(5432)Mon 21 Oct 2019 06:22:26 PM CEST
> ================================================================
> 
> failover_command is:
> failover_command = '/usr/local/bin/pgpool-failover.sh %d %h %P %m %H %R'
> 
> So when PgPool executes the failover command, %H is empty (5th argument):
> /usr/local/bin/pgpool-failover.sh 0 postgres1 0 -1 "" ""
> 
> For this reason the command is not executed on any PostgreSQL backend and
> the SSH command exits without doing anything, and the PgPool cluster
> becomes inconsistent (I have to restart everything to resume it).
> 
> ================================================================
> This is the node status in PgPool:
> 
> postgres=# SHOW POOL_NODES;
> DEBUG:  SimpleQuery
> DETAIL:  nodes reporting
>  node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | last_status_change
> ---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
>  0       | postgres1 | 5432 | up     | 0.333333  | primary | 0          |
> true              | 0                 | 2019-10-21 18:29:33
>  1       | postgres2 | 5432 | down   | 0.333333  | standby | 0          |
> false             | 0                 | 2019-10-21 18:29:33
>  2       | postgres3 | 5432 | down   | 0.333333  | standby | 0          |
> false             | 0                 | 2019-10-21 18:29:33
> (3 rows)
> ================================================================
> 
> Could you help me please?
> Thanks!