[pgpool-general: 7187] Re: Error with pg_dump

[Bo Peng]

Hi,

> I add that I tried to check the active connections and I see numerous 
> queries in idle state with the words:
>
> DISCARD ALL

If these connections with query "DISCARD ALL" was not closed after 300 seconds,
I think you may hit a pgpool's bug.
The bug is that if the primary node is "server2", 
the "connection_life_time" paramater doesn't work.

Due to pgpool's connection pooling capability, 
the connection between pgpool and postgres will be kept in the pool
even if client has closed the session.
And the timeout of the cached connection can be specified in "connection_life_time" parameter.

This bug will be fixed by the next release:
https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=96fa3c92f96dd831202937b02b13c5d9a730e1d9


On Wed, 08 Jul 2020 13:25:24 +0000
"Giancarlo Celli" <giancarlo.celli at flottaweb.com> wrote:

> I'm sending you pgpool.conf
> 
> I have 2 nodes:
> 
> server1:
> eno1:         inet xx.xx.xx.xx
> eno2:         inet 192.168.0.11
> 
> server2:
> eno1:         inet yy.yy.yy.yy
> eno1:0:      inet zz.zz.zz.zz
> eno2:         inet 192.168.0.12
> 
> the two servers are also connected on the second network interface 
> directly through a cross cable.
> 
> 
> 
> ------ Messaggio originale ------
> Da: "Bo Peng" <pengbo at sraoss.co.jp>
> A: "Giancarlo Celli" <giancarlo.celli at flottaweb.com>
> Cc: pgpool-general at pgpool.net
> Inviato: 07/07/2020 15:01:59
> Oggetto: Re: [pgpool-general: 7125] Re: Error with pg_dump
> 
> >Hello,
> >
> >Could you share your pgpool.conf and provide a scenario to reproduce this issue?
> >
> >On Mon, 06 Jul 2020 15:31:09 +0000
> >"Giancarlo Celli" <giancarlo.celli at flottaweb.com> wrote:
> >
> >>  I add that I tried to check the active connections and I see numerous
> >>  queries in idle state with the words:
> >>
> >>  DISCARD ALL
> >>
> >>  Why?
> >>
> >>
> >>  ------ Messaggio originale ------
> >>  Da: "Giancarlo Celli" <giancarlo.celli at flottaweb.com>
> >>  A: pgpool-general at pgpool.net
> >>  Inviato: 06/07/2020 17:21:35
> >>  Oggetto: [pgpool-general: 7124] Error with pg_dump
> >>
> >>  >Hi,
> >>  >recently I'm having problems with pg_dump on pgpool. The master node
> >>  >server crashes and the switch occurs on the standby. Here are the lines
> >>  >of the pgpool.log:
> >>  >
> >>  >LOG:  pool_read_kind: error message from master backend:sorry, too many
> >>  >clients already
> >>  >ERROR:  unable to read message kind
> >>  >DETAIL:  kind does not match between master(45) slot[1] (52)
> >>  >LOG:  watchdog received the failover command from remote pgpool-II node
> >>  >"xx.xx.xx.xx:5432 Linux server1"
> >>  >LOG:  watchdog is processing the failover command
> >>  >[DEGENERATE_BACKEND_REQUEST] received from xx.xx.xx.xx:5432 Linux
> >>  >server1
> >>  >LOG:  remote pgpool-II node "xx.xx.xx.xx:5432 Linux server1" is
> >>  >requesting to become a lock holder for failover ID: 0
> >>  >LOG:  lock holder request denied to remote pgpool-II node
> >>  >"xx.xx.xx.xx:5432 Linux server1"
> >>  >DETAIL:  local pgpool-II node "yy.yy.yy.yy:5432 Linux server2" is
> >>  >already holding the locks
> >>  >LOG:  received the failover command lock request from remote pgpool-II
> >>  >node "xx.xx.xx.xx:5432 Linux server1"
> >>  >LOG:  remote pgpool-II node "xx.xx.xx.xx:5432 Linux server1" is
> >>  >checking the status of [FAILOVER] lock for failover ID 0
> >>  >
> >>  >It would seem to depend on the number of connections exceeded (from the
> >>  >string that appears in the log: "sorry, too many clients already"). I
> >>  >already have max_connections = 300. Is there a way to verify that this
> >>  >was the problem?
> >>  >if I tried to execute the pg_dump directly on the standby postgresql
> >>  >server, without going through pgpool, using port 5433, could I avoid
> >>  >the problem according to you?
> >
> >
> >--
> >Bo Peng <pengbo at sraoss.co.jp>
> >SRA OSS, Inc. Japan


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