Обсуждение: [BUGS] postgres client connection issue

Поиск
Список
Период
Сортировка

[BUGS] postgres client connection issue

От
Ranjeet Singh03
Дата:

Hi Team,

 

We have client-server model for our application and client (running on SuSE Linux box) connect to postgres database (running on SuSE Linux box).

However all works fine but few times we have seen that client does not make connection to postgres database, which fails the order.

 

Below is the error logged in log file:

 

DBD::Pg::st execute failed: server closed the connection unexpectedly

This probably means the server terminated abnormally

 

Could you please advise how can this issues be fixed?

 

Your help would be much appreciated. Thanks in advance.

 

Thanks & Regards,

Ranjeet Singh

Technology Analyst, Infosys Ltd

Electronics City, Bengaluru, Karnataka, India 560100

M: +91 9663748161

E: ranjeet_singh03@infosys.com

Telstra Internet Direct (TID)

Description: Description: Description: A NEW BTE_Email-signature_

 

Re: [BUGS] postgres client connection issue

От
Tom Lane
Дата:
Ranjeet Singh03 <Ranjeet_Singh03@infosys.com> writes:
> Below is the error logged in log file:
> DBD::Pg::st execute failed: server closed the connection unexpectedly
> This probably means the server terminated abnormally

What appears in the server's log when this happens?  What PG version
is it?

            regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] postgres client connection issue

От
Ranjeet Singh03
Дата:

Thanks Tom for acknowledge my mail. Below is the detail as you requested:

 

PG Version DBD::Pg         : 2.10.3

 

TCP parameters in postgresql.conf configuration file:

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;

                                        # 0 selects the system default

#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;

                                        # 0 selects the system default

#tcp_keepalives_count = 0               # TCP_KEEPCNT;

                                        # 0 selects the system default

 

 

Postgers server logs:

 

2017-02-01 10:56:27 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 10:56:27 LOG:  unexpected EOF on client connection

2017-02-01 10:57:19 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 10:57:19 LOG:  unexpected EOF on client connection

2017-02-01 11:50:56 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 11:50:56 LOG:  unexpected EOF on client connection

2017-02-01 12:11:48 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 12:11:48 LOG:  unexpected EOF on client connection

2017-02-01 12:11:54 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 12:11:54 LOG:  unexpected EOF on client connection

2017-02-01 12:32:42 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 12:32:42 LOG:  unexpected EOF on client connection

2017-02-01 13:00:01 LOG:  unexpected EOF on client connection

2017-02-01 13:00:01 LOG:  unexpected EOF on client connection

2017-02-01 13:00:01 LOG:  unexpected EOF on client connection

2017-02-01 13:30:01 LOG:  unexpected EOF on client connection

2017-02-01 14:00:23 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 14:00:23 LOG:  unexpected EOF on client connection

2017-02-01 14:10:02 LOG:  unexpected EOF on client connection

2017-02-01 14:13:33 LOG:  unexpected EOF on client connection

2017-02-01 14:13:36 LOG:  unexpected EOF on client connection

2017-02-01 14:58:47 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 14:58:47 LOG:  unexpected EOF on client connection

2017-02-01 15:01:36 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 15:01:36 LOG:  unexpected EOF on client connection

2017-02-01 15:46:34 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 15:46:34 LOG:  unexpected EOF on client connection

2017-02-01 15:48:02 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 15:48:02 LOG:  unexpected EOF on client connection

2017-02-01 16:28:29 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 16:28:29 LOG:  unexpected EOF on client connection

2017-02-01 16:40:04 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 16:40:04 LOG:  unexpected EOF on client connection

2017-02-01 16:53:10 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 16:53:10 LOG:  unexpected EOF on client connection

2017-02-01 16:56:48 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 16:56:48 LOG:  unexpected EOF on client connection

2017-02-01 17:22:46 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 17:22:46 LOG:  unexpected EOF on client connection

2017-02-01 17:38:44 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 17:38:44 LOG:  unexpected EOF on client connection

2017-02-01 18:06:12 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 18:06:12 LOG:  unexpected EOF on client connection

2017-02-01 18:18:00 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 18:18:00 LOG:  unexpected EOF on client connection

2017-02-01 19:22:37 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 19:22:37 LOG:  unexpected EOF on client connection

2017-02-01 20:57:29 LOG:  could not receive data from client: Connection reset by peer

2017-02-01 20:57:29 LOG:  unexpected EOF on client connection

 

Thanks & Regards,

Ranjeet Singh

Technology Analyst, Infosys Ltd

Electronics City, Bengaluru, Karnataka, India 560100

M: +91 9663748161

E: ranjeet_singh03@infosys.com

Telstra Internet Direct (TID)

Description: Description: Description: A NEW BTE_Email-signature_

 

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, 3 February 2017 8:21 PM
To: Ranjeet Singh03 <Ranjeet_Singh03@infosys.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] postgres client connection issue

 

Ranjeet Singh03 <Ranjeet_Singh03@infosys.com> writes:

> Below is the error logged in log file:

> DBD::Pg::st execute failed: server closed the connection unexpectedly

> This probably means the server terminated abnormally

 

What appears in the server's log when this happens?  What PG version is it?

 

                                                regards, tom lane

Re: [BUGS] postgres client connection issue

От
Tom Lane
Дата:
Ranjeet Singh03 <Ranjeet_Singh03@infosys.com> writes:
> Thanks Tom for acknowledge my mail. Below is the detail as you requested:
> PG Version DBD::Pg         : 2.10.3

That's not the Postgres version ... but anyway, given that the server
is seeing this:

> 2017-02-01 10:56:27 LOG:  could not receive data from client: Connection reset by peer
> 2017-02-01 10:56:27 LOG:  unexpected EOF on client connection

while the client is seeing this:

>> DBD::Pg::st execute failed: server closed the connection unexpectedly

it sure looks like this is basically a network problem: something in
between is killing the connection.

If the sessions that are dying had been sitting idle for awhile, the
most likely theory is that there's something with an activity timeout,
probably a router.  If you can't identify the something and change its
settings, it might help to enable TCP keepalives.  Setting
tcp_keepalives_idle to something less than the shortest observed
failure interval ought to do it.  (The effective default is probably
something like an hour, and at a guess, you might need to set it to
something near 5 or 10 minutes.)

If you're seeing active sessions die like this, that moves it out of
the sphere of "something's misconfigured" and into the sphere of
"something's broken".  Could be as simple as a flaky cable.  Looking
into the kernel logs for the communicating machines might help; if
there's a router in between, try to enable event logging on it.

            regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] postgres client connection issue

От
Tom Lane
Дата:
I wrote:
> it sure looks like this is basically a network problem: something in
> between is killing the connection.

BTW, in case it's not obvious, you should now be thinking in terms of
"localize the network problem", and PG might be able to help you with
that.  If the client connections are coming from multiple places, pay
attention to which one(s) are seeing problems.  The default logging
options you seem to be using aren't going to be much help, but you could
turn on log_connections and/or log_disconnections, and adjust
log_line_prefix to help you identify just which connections are losing.

            regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs