Thanks for quick answer. I am feeling a little embarrassed that I did not find this before... The thread definitely helped me to understand more.
When I execute query with foreign tables, the connection is created (according pg_stat_activity). The question is what I should do for ending the connection. I can get PID of connection and force to end it by pg_terminate_backend, but that is not "clean way".
Unfortunately I can not disable SSL encryption as it is mentioned in the thread. But it is interesting for me, that the log message (resetting by peer) is created only in encrypted connection, because in both way the connection stayed defined in pg_stat_activity after query on foreign table.
so 23. 2. 2019 v 22:48 odesílatel Adrian Klaver <adrian.klaver@xxxxxxxxxxx> napsal:
On 2/23/19 1:28 PM, Lukáš Sobotka wrote:
> Hi guys,
>
> I would be grateful for some advice about foreign tables. I attached
> simplified script describing the problem for better understanding.
>
> What I am trying to do:
>
> I am copying tables between two databases and for copying is used
> function which are called from bash script.
>
> What is problem:
>
> I am creating foreign tables, selecting from them, dropping them and
> everything works well. But always when I am working with them and the
> session ends, message about connection resetting appears in log file of
> remote database. It is showing "postgres@adam_db LOG: could not receive
> data from client: Connection reset by peer". I am calling the command
> often, so this message fill my log file quite a fast. I tried to find
> some info about this message, without success.
>
> Where can be problem?
See if the thread below applies:
https://www.postgresql.org/message-id/4004.1521759312%40sss.pgh.pa.us
>
> Is there some different/correct way how to use foreign tables?
> How can I closed connection properly?
>
> For reproducing you can use attached script and after that execute next
> command:
> /psql -c 'SELECT * FROM ft_numbers'
> "postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/
>
> I also tried to drop server after selection but it also logs the
> message. Command which I executed:/
> psql -c 'SELECT * FROM ft_numbers; DROP SERVER adam_server CASCADE;'
> "postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/
>
> I tried to replicate problem in databases with different versions - all
> of them ended with the message in log.Used versions:
>
> * PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu
> 9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
> * PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
> * PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
>
> Best regards,
>
> Lukas
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
