Re: postgres_fdw insert extremely slow

On Fri, Nov 27, 2020 at 2:00 PM pabloa98 wrote: > I would like to suggest for postgres_fdw: If the foreign database is > PostgreSQL, > Just to be clear, the "postgres" part of the name means the remote database must be a PostgreSQL database, there is no "if". Likewise, for the extension mysql_f

Re: postgres_fdw insert extremely slow

I would like to suggest for postgres_fdw: If the foreign database is PostgreSQL, the link should just pass through all the CRUD SQL commands to the other database. If the other database is of a version so different that cannot make sense of the CRUD SQL command, it will generate an error and that'

Re: How to debug authentication issues in Postgres

On 11/27/20 12:37 AM, Hemil Ruparel wrote: The log says: > FATAL:  password authentication failed for user "centos" > DETAIL:  Connection matched pg_hba.conf line 88: "host    user password 0.0.0.0/0               scram-sha-256" To me that looks like a strange line f

Re: How to debug authentication issues in Postgres

On 11/27/20 7:15 AM, Hemil Ruparel wrote: The database has been upgraded Just to be clear the postgresql.conf file has: password_encryption = scram-sha-256 set correct? On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver > wrote: On 11/27/20 7:01 AM, Hemil

Re: How to debug authentication issues in Postgres

I will try that. I do not have access to the computer right now On Fri 27 Nov, 2020, 9:25 PM Tom Lane, wrote: > Hemil Ruparel writes: > > Thanks for the clarification. According to this page, > > https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0, > > scram support was adde

Re: How to debug authentication issues in Postgres

Hemil Ruparel writes: > Thanks for the clarification. According to this page, > https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0, > scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using > the java code mentioned above, I still get the same error. If you

Re: How to debug authentication issues in Postgres

Thanks for the clarification. According to this page, https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0, scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using the java code mentioned above, I still get the same error. On Fri, Nov 27, 2020 at 9:06 PM Tom La

Re: How to debug authentication issues in Postgres

Hemil Ruparel writes: > When I try to connect to the database, the log says: >> FATAL: password authentication failed for user "user" >> DETAIL: Connection matched pg_hba.conf line 88: "hostuser > password 0.0.0.0/0 scram-sha-256" > So I think the client is using scra

SV: Problem with pg_notify / listen

Clarification, we upgraded from PG 11.9 to PG 11.10. KR Mikael Gustavsson Från: externaly-forwar...@smhi.se för Gustavsson Mikael Skickat: den 27 november 2020 16:24:38 Till: Tom Lane Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders Ämne:

SV: Problem with pg_notify / listen

Hi. We installed PG 11.10 last week. So the latest release of PG 11. KR Mikael Gustavsson Från: Tom Lane Skickat: den 27 november 2020 16:21:33 Till: Gustavsson Mikael Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders Ämne: Re: Problem wi

Re: Problem with pg_notify / listen

Gustavsson Mikael writes: > After applying the latest patch we have encountered a problem with the > pg_notify queue. What do you mean by "the latest patch", exactly? regards, tom lane

Re: How to debug authentication issues in Postgres

When I try to connect to the database, the log says: > FATAL: password authentication failed for user "user" > DETAIL: Connection matched pg_hba.conf line 88: "hostuser password 0.0.0.0/0 scram-sha-256" So I think the client is using scram-sha-256 On Fri, Nov 27, 2020

Re: How to debug authentication issues in Postgres

The database has been upgraded On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver wrote: > On 11/27/20 7:01 AM, Hemil Ruparel wrote: > > I don't quite get what you mean by upgrading to scram-sha256. I > > installed postgres 13. I haven't upgraded anything yet. > > In postgresql.conf see what password

Re: Deleting takes days, should I add some index?

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera a écrit : > On 2020-Nov-27, Alexander Farber wrote: > > > Referenced by: > > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) > > REFERENCES words_games(gid) ON DELETE CASCADE > > TABLE "words_moves" CONSTRAINT "words_moves

Re: How to debug authentication issues in Postgres

On 11/27/20 7:01 AM, Hemil Ruparel wrote: I don't quite get what you mean by upgrading to scram-sha256. I installed postgres 13. I haven't upgraded anything yet. In postgresql.conf see what password_encryption has been set to. If it is 'scram-sha-256` then it has been upgraded. On Fri, N

Re: Deleting takes days, should I add some index?

On 2020-Nov-27, Alexander Farber wrote: > Referenced by: > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) > REFERENCES words_games(gid) ON DELETE CASCADE > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) > REFERENCES words_games(gid) ON DELETE

Re: How to debug authentication issues in Postgres

I don't quite get what you mean by upgrading to scram-sha256. I installed postgres 13. I haven't upgraded anything yet. On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > Did you correctly upgrade your whole environment to scram-sha-256? > > > > > > > To

SV: Problem with pg_notify / listen

Hi David, Thanks for fast reply! We had to restart the server to avoid problems with a full queue så process is no longer with us. The queue is at 25% now and we have minimised the payload to avoid problems during the weekend. # select * from pg_notification_queue_usage() ; pg_notification_

Deleting takes days, should I add some index?

Hello, I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I have the following 2 tables there: words_ru=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default --+

AW: How to debug authentication issues in Postgres

Did you correctly upgrade your whole environment to scram-sha-256? To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users

Re: Problem with pg_notify / listen

On Friday, November 27, 2020, Gustavsson Mikael wrote: > Hi. > > After applying the latest patch we have encountered a problem with the > pg_notify queue. > > The queue is filling up and starts issuing warnings like > WARNING: NOTIFY queue is 87% full > DETAIL: The server process with PID 29699

Problem with pg_notify / listen

Hi. After applying the latest patch we have encountered a problem with the pg_notify queue. The queue is filling up and starts issuing warnings like WARNING: NOTIFY queue is 87% full DETAIL: The server process with PID 2969993 is among those with the oldest transactions. NOTIFY queue cannot b

Re: postgres_fdw insert extremely slow

On 27.11.2020 10:11, pabloa98 wrote: On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe > wrote: On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote: > So even if Mats where to break this query: > > INSERT INTO foreign.labels (address, labels)

Re: postgres_fdw insert extremely slow

On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe wrote: > On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote: > > So even if Mats where to break this query: > > > > INSERT INTO foreign.labels (address, labels) > > SELECT address_id, ARRAY_AGG(name) AS labels > > FROM labels > > GROUP BY 1 > > LIM

Re: How to debug authentication issues in Postgres

The log says: > FATAL: password authentication failed for user "centos" > DETAIL: Connection matched pg_hba.conf line 88: "hostuser password 0.0.0.0/0 scram-sha-256" I can't understand where is the problem as both psql and pgadmin connect without problems using the sam

Re: How to debug authentication issues in Postgres

Sorry. This was the replication section: local replication all peer hostreplication all 127.0.0.1/32 scram-sha-256 hostreplication all ::1/128 scram-sha-256 On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe wrote: > On

Re: How to debug authentication issues in Postgres

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote: > I have restarted postgres quite a few times to try making configuration > changes and it > is always back up. I don't know how. Feels weird to me. I didn't add the line > "local replication all". It was there by default I don't believe t

Re: How to debug authentication issues in Postgres

I have restarted postgres quite a few times to try making configuration changes and it is always back up. I don't know how. Feels weird to me. I didn't add the line "local replication all". It was there by default On Fri, Nov 27, 2020 at 1:24 PM Laurenz Albe wrote: > On Fri, 2020-11-27 at 12:44