Re: 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(at)gmail(dot)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

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(at)gmail(dot)com>:

> On Mon, Nov 21, 2016 at 8:32 AM, Vladimir Svedov <vodevsh(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)com>:

> On Tue, Nov 22, 2016 at 5:05 AM, Vladimir Svedov <vodevsh(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)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(dot)janes(at)gmail(dot)com>:

> On Mon, Nov 21, 2016 at 6:32 AM, Vladimir Svedov <vodevsh(at)gmail(dot)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
>