RE: Connection issue
Hi, Can you do some tests in both (Mac and Windows ) ? * telnet {IP_Server} {5432} * ping {IP_Server} * tracert {IP_Server} Regards __ Saludos Ing. Ricardo Martín Gomez DBA - SysAdmin De: Adrian Klaver Enviado: martes, 5 de febrero de 2019 12:44 Para: Maximilian Tyrtania Cc: pgsql-general@lists.postgresql.org Asunto: Re: Connection issue On 2/5/19 7:18 AM, Maximilian Tyrtania wrote: > Hi, > >> On 5. Feb 2019, at 15:57, Adrian Klaver wrote: >> >> On 2/5/19 1:48 AM, Maximilian Tyrtania wrote: >>> For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) >>> from my Windows installation (run via Parallels on my Mac), no matter what >>> app I use (tried psql, PGAdmin and my own app). >> >> Has it ever connected? > > Yes, it connected without issues last week. > >> Have you recently updated any of the involved software? > > Well, sure, but after I saw I couldn't connect I downloaded the current > version of PGAdmin and psql and couldn't connect with them either. > >> More below. >> >>> When trying to connect from PGAdmin 4 the server logs say: >>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0: >>> Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de >>> Port=50262 >>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT: >>> BackendInitialize, postmaster.c:4249 >>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: >>> konnte Daten vom Client nicht empfangen: Connection reset by peer >>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: >>> pq_recvbuf, pqcomm.c:978 >>> ("konnte Daten vom Client nicht empfangen" means "couldn't receive data >>> from client") >>> PGAdmin says the usual " Unable to connect to server:..." >>> No issue connecting from the Mac. >>> I see in the logfile that other windows users did connect successfully from >>> their windows boxes so I am ready to believe that maybe my Parallels >>> somehow disconnects the connection quickly or something. >>> Another strange thing is that if I try to connect from my Xojo app (which >>> uses libpq internally) it says:SSL error: unknown protocol. I had no issue >>> connecting last week from that installation so I am at my wits end... >> >> Where is the Xojo app running? >> >> Is the pg_hba.conf on the server set up to allow connections from the client >> machines? > > Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a > pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac > with the same credentials. I wonder if this is maybe some Parallels issue. Well if you could connect from Parallels last week, you upgraded it and now you cannot connect then I would suspect it. This assumes that the Xojo app is also running in the Parallels instance. Given the SSL error you might see if they changed anything in that area. > > Thanks, > Max > -- Adrian Klaver adrian.kla...@aklaver.com
Re: Auto close idle connections for specific user (not by pg_cancel command)
You are right. So it is not suitable for your case. Sorry Le mar. 5 févr. 2019 à 19:27, Ravi Krishna a écrit : > > > > > https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/ > > > > And use alter role in database set idle_in_transaction_session_timeout > to... > > > > https://www.postgresql.org/docs/10/sql-alterrole.html > > But this works only for those sessions which has started a transaction and > not committed within the session_timeout period, right ?
Re: Auto close idle connections for specific user (not by pg_cancel command)
Or you could use Parameter idle_in_transaction_session_timeout https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/ And use alter role in database set idle_in_transaction_session_timeout to... https://www.postgresql.org/docs/10/sql-alterrole.html Regards Le mar. 5 févr. 2019 à 18:59, Thomas Poty a écrit : > Hi, > Maybe pgterminator could help you > https://github.com/trustly/pgterminator > Regards > Thomas > > Le mar. 5 févr. 2019 à 18:52, Pavel Stehule a > écrit : > >> Hi >> >> >> út 5. 2. 2019 v 18:47 odesílatel Emi napsal: >> >>> Hello, >>> >>> In psql, may I know is there a way/config that idle connections could be >>> auto closed (but not by pg_cancel_backend command) for specific user(s) >>> please? >>> >>> For example, >>> select usename, waiting, query_start from pg_stat_activity >>> where usename like 'connUser1' and >>>current_query=''; >>> >>> All connections from connUser1, if idle more than 1 day, psql auto close >>> them. >>> >> >> What I know, there is not any timeout for this purpose. >> >> Maybe pgbouncer can do this https://pgbouncer.github.io/config.html >> >> client_idle_timeout >> >> Regards >> >> Pavel >> >>> >>> Thanks a lot. >>> >>
Re: Auto close idle connections for specific user (not by pg_cancel command)
Hi, Maybe pgterminator could help you https://github.com/trustly/pgterminator Regards Thomas Le mar. 5 févr. 2019 à 18:52, Pavel Stehule a écrit : > Hi > > > út 5. 2. 2019 v 18:47 odesílatel Emi napsal: > >> Hello, >> >> In psql, may I know is there a way/config that idle connections could be >> auto closed (but not by pg_cancel_backend command) for specific user(s) >> please? >> >> For example, >> select usename, waiting, query_start from pg_stat_activity >> where usename like 'connUser1' and >>current_query=''; >> >> All connections from connUser1, if idle more than 1 day, psql auto close >> them. >> > > What I know, there is not any timeout for this purpose. > > Maybe pgbouncer can do this https://pgbouncer.github.io/config.html > > client_idle_timeout > > Regards > > Pavel > >> >> Thanks a lot. >> >
Re: Auto close idle connections for specific user (not by pg_cancel command)
Hi út 5. 2. 2019 v 18:47 odesílatel Emi napsal: > Hello, > > In psql, may I know is there a way/config that idle connections could be > auto closed (but not by pg_cancel_backend command) for specific user(s) > please? > > For example, > select usename, waiting, query_start from pg_stat_activity > where usename like 'connUser1' and >current_query=''; > > All connections from connUser1, if idle more than 1 day, psql auto close > them. > What I know, there is not any timeout for this purpose. Maybe pgbouncer can do this https://pgbouncer.github.io/config.html client_idle_timeout Regards Pavel > > Thanks a lot. >
Auto close idle connections for specific user (not by pg_cancel command)
Hello, In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please? For example, select usename, waiting, query_start from pg_stat_activity where usename like 'connUser1' and current_query=''; All connections from connUser1, if idle more than 1 day, psql auto close them. Thanks a lot.
Re: Connection issue
On 2/5/19 7:18 AM, Maximilian Tyrtania wrote: Hi, On 5. Feb 2019, at 15:57, Adrian Klaver wrote: On 2/5/19 1:48 AM, Maximilian Tyrtania wrote: For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from my Windows installation (run via Parallels on my Mac), no matter what app I use (tried psql, PGAdmin and my own app). Has it ever connected? Yes, it connected without issues last week. Have you recently updated any of the involved software? Well, sure, but after I saw I couldn't connect I downloaded the current version of PGAdmin and psql and couldn't connect with them either. More below. When trying to connect from PGAdmin 4 the server logs say: 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0: Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT: BackendInitialize, postmaster.c:4249 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte Daten vom Client nicht empfangen: Connection reset by peer 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf, pqcomm.c:978 ("konnte Daten vom Client nicht empfangen" means "couldn't receive data from client") PGAdmin says the usual " Unable to connect to server:..." No issue connecting from the Mac. I see in the logfile that other windows users did connect successfully from their windows boxes so I am ready to believe that maybe my Parallels somehow disconnects the connection quickly or something. Another strange thing is that if I try to connect from my Xojo app (which uses libpq internally) it says:SSL error: unknown protocol. I had no issue connecting last week from that installation so I am at my wits end... Where is the Xojo app running? Is the pg_hba.conf on the server set up to allow connections from the client machines? Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac with the same credentials. I wonder if this is maybe some Parallels issue. Well if you could connect from Parallels last week, you upgraded it and now you cannot connect then I would suspect it. This assumes that the Xojo app is also running in the Parallels instance. Given the SSL error you might see if they changed anything in that area. Thanks, Max -- Adrian Klaver adrian.kla...@aklaver.com
Re: Connection issue
Hi, > On 5. Feb 2019, at 15:57, Adrian Klaver wrote: > > On 2/5/19 1:48 AM, Maximilian Tyrtania wrote: >> For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) >> from my Windows installation (run via Parallels on my Mac), no matter what >> app I use (tried psql, PGAdmin and my own app). > > Has it ever connected? Yes, it connected without issues last week. > Have you recently updated any of the involved software? Well, sure, but after I saw I couldn't connect I downloaded the current version of PGAdmin and psql and couldn't connect with them either. > More below. > >> When trying to connect from PGAdmin 4 the server logs say: >> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0: >> Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262 >> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT: >> BackendInitialize, postmaster.c:4249 >> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: >> konnte Daten vom Client nicht empfangen: Connection reset by peer >> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: >> pq_recvbuf, pqcomm.c:978 >> ("konnte Daten vom Client nicht empfangen" means "couldn't receive data from >> client") >> PGAdmin says the usual " Unable to connect to server:..." >> No issue connecting from the Mac. >> I see in the logfile that other windows users did connect successfully from >> their windows boxes so I am ready to believe that maybe my Parallels somehow >> disconnects the connection quickly or something. >> Another strange thing is that if I try to connect from my Xojo app (which >> uses libpq internally) it says:SSL error: unknown protocol. I had no issue >> connecting last week from that installation so I am at my wits end... > > Where is the Xojo app running? > > Is the pg_hba.conf on the server set up to allow connections from the client > machines? Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac with the same credentials. I wonder if this is maybe some Parallels issue. Thanks, Max
Re: Connection issue
On 2/5/19 1:48 AM, Maximilian Tyrtania wrote: For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from my Windows installation (run via Parallels on my Mac), no matter what app I use (tried psql, PGAdmin and my own app). Has it ever connected? Have you recently updated any of the involved software? More below. When trying to connect from PGAdmin 4 the server logs say: 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0: Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT: BackendInitialize, postmaster.c:4249 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte Daten vom Client nicht empfangen: Connection reset by peer 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf, pqcomm.c:978 ("konnte Daten vom Client nicht empfangen" means "couldn't receive data from client") PGAdmin says the usual " Unable to connect to server:..." No issue connecting from the Mac. I see in the logfile that other windows users did connect successfully from their windows boxes so I am ready to believe that maybe my Parallels somehow disconnects the connection quickly or something. Another strange thing is that if I try to connect from my Xojo app (which uses libpq internally) it says:SSL error: unknown protocol. I had no issue connecting last week from that installation so I am at my wits end... Where is the Xojo app running? Is the pg_hba.conf on the server set up to allow connections from the client machines? Tia, Max -- Adrian Klaver adrian.kla...@aklaver.com
Connection issue
For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from my Windows installation (run via Parallels on my Mac), no matter what app I use (tried psql, PGAdmin and my own app). When trying to connect from PGAdmin 4 the server logs say: 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0: Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT: BackendInitialize, postmaster.c:4249 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte Daten vom Client nicht empfangen: Connection reset by peer 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf, pqcomm.c:978 ("konnte Daten vom Client nicht empfangen" means "couldn't receive data from client") PGAdmin says the usual " Unable to connect to server:..." No issue connecting from the Mac. I see in the logfile that other windows users did connect successfully from their windows boxes so I am ready to believe that maybe my Parallels somehow disconnects the connection quickly or something. Another strange thing is that if I try to connect from my Xojo app (which uses libpq internally) it says:SSL error: unknown protocol. I had no issue connecting last week from that installation so I am at my wits end... Tia, Max
Re: oracle_fwd - is it safe or not?
On 31/01/2019 18:53, Bruce Momjian wrote: > On Thu, Jan 31, 2019 at 05:37:00PM +0100, Piotr Włodarczyk wrote: >> Our administrator have some doubt about this extension. He asked us, are we >> sure that this extension safe enough and didn't make any kernel panic >> sytuation >> beacuse as all another extension, this one can operate on system kernel >> level. >> >> Therefore if it's possible can You tell me more about safety this extension >> and >> release procedure and community of this module? > > Uh, oracle_fwd uses a kernel module? Are you sure? That is surprising. Perhaps he meant that the extension runs in the PostgreSQL "kernel" and could crash PostgreSQL, which is true of any extension. But oracle_fdw seems reasonably popular, so I would expect a base level of quality. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services