Thread: postgres 9.3 postgres_fdw ::LOG: could not receive data from client: Connection reset by peer

Hi,
I have this question. Looked for a help on http://dba.stackexchange.com/
No success.
Maybe you can answer?..
Thank you in advance


"FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...

Symptoms:

  1. I run in psql query SELECT * from FOREIGN_TABLE. No log generated
  2. I run in bash psql -c "SELECT * from LOCAL_TABLE". No log generated
  3. I run in bash psql -c "SELECT * from FOREIGN_TABLE"::LOG: could not receive data from client: Connection reset by peer generated in postgres log

I can't set logging lower and yet this message distracts. Please share any idea how to set up env to avoid generating this message?.. I feel I'm missing something obvious, but can't see what.

PS. I tried running -f file instead of -c "SQL". Of course it did not change a thing. And of course I tried putting \q to file with same result...


On Mon, Nov 21, 2016 at 8:32 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:

> I have this question. Looked for a help on http://dba.stackexchange.com/
> No success.

A link to the actual question would be appreciated.

> "FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...
>
> Symptoms:
>
> I run in psql query SELECT * from FOREIGN_TABLE. No log generated
> I run in bash psql -c "SELECT * from LOCAL_TABLE". No log generated
> I run in bash psql -c "SELECT * from FOREIGN_TABLE". ::LOG: could not receive data from client: Connection reset by
peergenerated in postgres log
 

Please provide more information, and preferably a self-contained
test case (one that anyone can run on an empty database to see the
problem).

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Hi,
Sorry - tried to reproduce on other machine and gather all statements. And failed
Installed 9.3 (which has those symptoms) and still can't reproduce.
Must be platform specific, not version

2016-11-21 21:58 GMT+00:00 Kevin Grittner <kgrittn@gmail.com>:
On Mon, Nov 21, 2016 at 8:32 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:

> I have this question. Looked for a help on http://dba.stackexchange.com/
> No success.

A link to the actual question would be appreciated.

> "FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...
>
> Symptoms:
>
> I run in psql query SELECT * from FOREIGN_TABLE. No log generated
> I run in bash psql -c "SELECT * from LOCAL_TABLE". No log generated
> I run in bash psql -c "SELECT * from FOREIGN_TABLE". ::LOG: could not receive data from client: Connection reset by peer generated in postgres log

Please provide more information, and preferably a self-contained
test case (one that anyone can run on an empty database to see the
problem).

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Nov 22, 2016 at 5:05 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:
> Hi,
> Sorry - tried to reproduce on other machine and gather all statements. And
> failed
> Installed 9.3 (which has those symptoms) and still can't reproduce.
> Must be platform specific, not version

Probably the foreign server isn't configured properly, and points to a
host/port to that resets the connection when you attempt to connect to
it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



No, I select from it OK.
The bug(?) is that when I do it in oppened psql session it produces no log, and when I run same select as psql -c "SELECT..." it gives the above

2016-11-22 20:18 GMT+00:00 Robert Haas <robertmhaas@gmail.com>:
On Tue, Nov 22, 2016 at 5:05 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:
> Hi,
> Sorry - tried to reproduce on other machine and gather all statements. And
> failed
> Installed 9.3 (which has those symptoms) and still can't reproduce.
> Must be platform specific, not version

Probably the foreign server isn't configured properly, and points to a
host/port to that resets the connection when you attempt to connect to
it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Nov 23, 2016 at 3:08 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:
> No, I select from it OK.
> The bug(?) is that when I do it in oppened psql session it produces no log,
> and when I run same select as psql -c "SELECT..." it gives the above

OK, that's pretty weird.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



On Mon, Nov 21, 2016 at 6:32 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:
Hi,
I have this question. Looked for a help on http://dba.stackexchange.com/
No success.
Maybe you can answer?..
Thank you in advance


"FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...

Symptoms:

  1. I run in psql query SELECT * from FOREIGN_TABLE. No log generated
  2. I run in bash psql -c "SELECT * from LOCAL_TABLE". No log generated
  3. I run in bash psql -c "SELECT * from FOREIGN_TABLE"::LOG: could not receive data from client: Connection reset by peer generated in postgres log

Which server log file is this generated in, the local or the foreign?  Whichever it is, is there an entry in the logfile for the other server which seems to match up to this one?  That may have more useful details.

Cheers,

Jeff
Local server log has the line and remote table log is empty (it is configured for minimum warning and when I produce one it appears in log OK)
And I have new details - it happens on some additional environments - not constantly. Some hours it happens every time, then just stops appearing:
postgres@hostname:~$ grep user 9.3/main/pg_log/postgresql-2016-11-22_000000.log  | cat -n
     1  2016-11-22 06:00:02 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     2  2016-11-22 06:00:03 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     3  2016-11-22 06:00:03 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     4  2016-11-22 10:06:08 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     5  2016-11-22 10:06:08 UTC 127.0.0.1 user ::LOG:  could not receive data from client: Connection reset by peer
     6  2016-11-22 10:06:08 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     7  2016-11-22 11:25:27 UTC 127.0.0.1  user::LOG:  could not receive data from client: Connection reset by peer
     8  2016-11-22 11:25:27 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
     9  2016-11-22 11:25:27 UTC 127.0.0.1 user::LOG:  could not receive data from client: Connection reset by peer
this is log from local server where user runs same bunch of three statements MINUTELY

But when it start happening if I psql to db, same statement produces NOT this string in log.

Here reason why you should not follow this post any more:
postgres=# select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

I will just upgrade it. And this is the only machine I have this weird behavior at.
Sorry guys and thank you for your time

2016-11-23 18:16 GMT+00:00 Jeff Janes <jeff.janes@gmail.com>:
On Mon, Nov 21, 2016 at 6:32 AM, Vladimir Svedov <vodevsh@gmail.com> wrote:
Hi,
I have this question. Looked for a help on http://dba.stackexchange.com/
No success.
Maybe you can answer?..
Thank you in advance


"FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...

Symptoms:

  1. I run in psql query SELECT * from FOREIGN_TABLE. No log generated
  2. I run in bash psql -c "SELECT * from LOCAL_TABLE". No log generated
  3. I run in bash psql -c "SELECT * from FOREIGN_TABLE"::LOG: could not receive data from client: Connection reset by peer generated in postgres log

Which server log file is this generated in, the local or the foreign?  Whichever it is, is there an entry in the logfile for the other server which seems to match up to this one?  That may have more useful details.

Cheers,

Jeff