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

[Bo Peng]

Hi,

> 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]"

It seems that the version of PostgreSQL couldn't detected.

Could you share the debug log of Pgpool-II?

On Mon, 26 Nov 2018 10:46:39 +0800
Mike Yeap <wkk1020 at gmail.com> wrote:

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


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan