Trouble with postgres_fdw & dblink extensions
Hi guys, I would be grateful for some help. I am writing you because I am confused about using data foreign wrappers and dblink. I attached simplified script describing the problem. What I am trying to do? I have two databases and I need to copy table from local database to the remote one. For copying is used function which contains a few parts: - loading setting from foreign table (this part became a problematic) - creating destination table on remote db - importing foreign table - insert data into foreign table If query using foreign table (with setting) is performed, command for importing schema does not import new created table (it looks like table is not created yet). So copying ends with error. The second calling of function is all right (because destination table is already created from first calling). If function does not use foreign table, the first calling of function copies all data. Why the new created remote table can not be imported to local database when I had performed query on other foreign table? What am I missing? I am using PG 9.6 (PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit). Best regards, Lukas script.sql Description: application/sql
Foreign table & Connection reset by peer
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? 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 script.sql Description: application/sql
Re: Foreign table & Connection reset by peer
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 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.kla...@aklaver.com >
