[pgpool-hackers: 3965] Re: Determine which node to promote (prefer one with lesser replication lag)

[Tatsuo Ishii]

> Hello
> 
> I have a setup with 1 primary, 2 synchronous standbys behind 1 pgpool without watchdog.
> 
> “SHOW POOL_NODES;" displays Node 2 as the synchronously replicated node and Node 1 as the “potential" node. But when the current master dies and failover happens, pgpool promotes Node 1 instead of 2.
> 
> 
> postgres=# show pool_nodes;
>  node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
> ---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------
> 0 | dbod-ag-pg01.ch | 6601 | up | 0.333333 | primary | 1 | true | 0 | | | 2021-07-08 21:45:45
> 1 | dbod-ag-pg02.ch | 6604 | up | 0.333333 | standby | 0 | false | 0 | streaming | potential | 2021-07-09 05:49:24
> 2 | dbod-ag-pg03.ch | 6600 | up | 0.333333 | standby | 0 | false | 0 | streaming | sync | 2021-07-08 21:50:43
> (3 rows)
> 
> 
> My query is that, is there a way to tell pgpool to promote the standby with lesser replication lag?

There's no straight way for this with Pgpool-II.

> I plan to use synchronous replication with “remote_write” (and not remote_apply) level, so it’s quite possible that a replication lag will persist.

In my understanding, if the mode is "remote_write", it is guaranteed
that all the WAL logs have already been sent to the synchronous
standby node when clients get commit reply from the primary
server. This means that a replication does not persist as long as the
standby runs as a synchronous standby.

> Also, I have another query - At the time of starting up of pgpool, if one of the (standby) nodes provided in the initial backend configuration is not alive, pgpool continuously waits up to reach that node without starting up. Is there a way to start pgpool by ignoring that node in such a case? Should I send you a separate mail for this query?

Yes. There's a text file called "pgpool_status" under "logdir".

$ cat log/pgpool_status 
up
up
up

Each line represents the last status of each node. If you want to
ignore node 2 for example, you can edit the third line to:

up
up
down

and start pgpool (be sure that you do not add -D option, since the
option ignores pgpool_status). The nodes will be in down status and
ignored by pgpool.

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