[pgpool-general: 6311] Re: Standby node's status is "down" when running "show pool_nodes" command

[Mike Yeap]

Hi Pierre, thanks, I noticed the /tmp/pgpool_status file had a status of
"up" for node 1 and "down" for node 2 (even though node 2 was actually up).
So I tried stopping pgpool, delete the /tmp/pgpool_status file and startup
pgpool, now the /tmp/pgpool_status has an "up" status for both nodes.

But now pgpool log shows following errors repetitively:

Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [39-1] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]ERROR:  invalid LSN format
Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [39-2] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]DETAIL:  wrong log location format: 110001
Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [39-3] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]CONTEXT:  while checking replication time lag
Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [40-1] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]LOG:  get_query_result: no rows returned
Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [40-2] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]DETAIL:  node id (0)
Nov 26 10:41:06 hoopa-uat-pgpool-1 pgpool[6491]: [40-3] 2018-11-26
10:41:06: pid 6491: db [No Connection]: user [No Connection]: client_app
[No Connection]CONTEXT:  while checking replication time lag

And in the Postgres log I got this error:

2018-11-26 10:45:27.925 +08 [PID=1701 application="[unknown]"
user_name=pgpool database=postgres host(port)=10.31.1.238(45800)] ERROR:
function pg_current_xlog_location() does not exist at character 8
2018-11-26 10:45:27.925 +08 [PID=1701 application="[unknown]"
user_name=pgpool database=postgres host(port)=10.31.1.238(45800)] HINT:  No
function matches the given name and argument types. You might need to add
explicit type casts.
2018-11-26 10:45:27.925 +08 [PID=1701 application="[unknown]"
user_name=pgpool database=postgres host(port)=10.31.1.238(45800)]
STATEMENT:  SELECT pg_current_xlog_location()

Shouldn't it be calling pg_current_wal_lsn() instead? My PostgreSQL version
is 11.1

Thank you!

Regards,
Mike Yeap

On Fri, Nov 23, 2018 at 6:21 PM Pierre Timmermans <ptim007 at yahoo.com> wrote:

> You have to look in the pgpool log file why pgpool decided to detach the
> node, it typically happens when the healtch check fails (db was stopped, no
> network connectivity, etc.)
>
> To put it back up you must use the pcp_attach_node command, it will be
> something like
>
> pcp_attach_node -h localhost -p 9898 -w 1
>
> note: -w is to disable password prompting, it requires to have a .pcppass
> file in your home directory with the right permissions.
>
> Pierre
>
>
> On Friday, November 23, 2018, 11:14:03 AM GMT+1, Mike Yeap <
> wkk1020 at gmail.com> wrote:
>
>
> Hi, I'm new to Pgpool-II and trying a setup based on a two nodes streaming
> replication configuration. But the "show pool_nodes" command shows a "down"
> status for the standby node:
>
> [postgres at hoopa-uat-1 log]$ psql -h hoopa-uat-pgpool-1 -p 9999 -U
> postgres -c "show pool_nodes" postgres
>
>  node_id | hostname    | port | status | lb_weight |  role   | select_cnt
> | load_balance_node | replication_delay | last_status_change
>
> ---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+--------------------
>  0       | hoopa-uat-1 | 5432 | up     | 0.500000  | primary | 0
> | true              | 0                 | 2018-11-23 17:57:16
>  1       | hoopa-uat-2 | 5432 | down   | 0.500000  | standby | 0
> | false             | 0                 | 2018-11-23 17:57:16
>
> Actually the standby node is up, and I can run queries on it.
>
> Can someone please enlighten me?
>
> pgpool-II version 4.0.1
> PostgreSQL version 11.1
>
> Thank you.
> Regards,
> Mike Yeap
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20181126/223ce5ad/attachment.htm>