Thread: Reporting from Standby
Good afternoon.PG 12.12, PGDG ubuntu 18.04 LTS build.We have a primary with a couple of physical/streaming replicas. One intended for quick lookups and another intended for long-running reports (aka "the reporting replica"). There's also a DR replica in another region but we don't touch that for obvious reasons. All streaming replicas currently make use of replication slots on the primary as well. We have WAL archiving to cloud storage and restore/recovery handled via pgBackrest.I'm running into the age-old dilemma on the reporting replica where queries are being killed with the "canceling statement due to conflict with recovery" error. We have hot_standby_feedback=on and both max_standby_streaming_delay and max_standby_archive_delay are set to 5 minutes (300s). I'm fine increasing those delay parameters to 15 minutes or more. Like I said this replica is dedicated to long-running reports like month-end reports that you'd see today on the first of the month.I searched around and I see some suggestions like disabling hot_standby_feedback on replicas like this to minimize impact on the primary, in addition to increasing the delays, or even disabling the delays by setting them to -1. With replication slots in use, I want to make sure that WAL retention doesn't fill up the WAL volume, would it make sense to not use a slot for this replica (and/or not use streaming replication)?
What is the replication conflict you are facing - Can you share output from pg_stat_database_conflicts ?
datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-------+-----------+------------------+------------+----------------+-----------------+----------------
17156 | postgres | 0 | 0 | 0 | 0 | 0
13399 | template0 | 0 | 0 | 0 | 0 | 0
16400 | template1 | 0 | 0 | 0 | 0 | 0
16401 | foo | 0 | 0 | 6736 | 35 | 0
www.seiler.us
On Thu, Dec 1, 2022 at 1:31 PM Mahesh Shetty <maheshetty20@gmail.com> wrote:What is the replication conflict you are facing - Can you share output from pg_stat_database_conflicts ?The errors stopped a few hours ago so I'm not sure if this info is meaningful or not, but here it is. The "foo" database (real name changed) is obviously the app database where all the querying takes place.postgres=# select * from pg_stat_database_conflicts;
datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-------+-----------+------------------+------------+----------------+-----------------+----------------
17156 | postgres | 0 | 0 | 0 | 0 | 0
13399 | template0 | 0 | 0 | 0 | 0 | 0
16400 | template1 | 0 | 0 | 0 | 0 | 0
16401 | foo | 0 | 0 | 6736 | 35 | 0(4 rows)--Don Seiler
www.seiler.us
Good afternoon.PG 12.12, PGDG ubuntu 18.04 LTS build.We have a primary with a couple of physical/streaming replicas. One intended for quick lookups and another intended for long-running reports (aka "the reporting replica"). There's also a DR replica in another region but we don't touch that for obvious reasons. All streaming replicas currently make use of replication slots on the primary as well. We have WAL archiving to cloud storage and restore/recovery handled via pgBackrest.I'm running into the age-old dilemma on the reporting replica where queries are being killed with the "canceling statement due to conflict with recovery" error. We have hot_standby_feedback=on and both max_standby_streaming_delay and max_standby_archive_delay are set to 5 minutes (300s). I'm fine increasing those delay parameters to 15 minutes or more. Like I said this replica is dedicated to long-running reports like month-end reports that you'd see today on the first of the month.
I searched around and I see some suggestions like disabling hot_standby_feedback on replicas like this to minimize impact on the primary,
in addition to increasing the delays, or even disabling the delays by setting them to -1. With replication slots in use, I want to make sure that WAL retention doesn't fill up the WAL volume, would it make sense to not use a slot for this replica (and/or not use streaming replication)?
Hot_standby_feedback=on is supposed to prevent this type of conflict, not cause it. I don't know what corner case you might be hitting where it is failing to prevent the conflict. But in any case it is hard to see how turning it off is going to make the conflicts better. On the other hand, you could argue that since it is failing to fix the problem, then you might as well turn it off--it can cause bloat on the master, and while that is not the problem you are complaining about, why risk bloat if you aren't getting a benefit?
in addition to increasing the delays, or even disabling the delays by setting them to -1. With replication slots in use, I want to make sure that WAL retention doesn't fill up the WAL volume, would it make sense to not use a slot for this replica (and/or not use streaming replication)?I don't see why either toggling hot_standby_feedback or lengthening the delay would cause the WAL to fill up. I guess if the datafiles get bloated, they could squeeze the space used for pg_wal, but if they are on different volumes that shouldn't happen. And I guess lengthening the max delay could cause the WAL volume to fill up on the replica, which could then back up through the slot to fill up the WAL volume on the master. But unless you are already very close to the edge, I don't think lengthening the delay by 10 minutes would cause a problem.
Since you already have archiving set up, you could configure your standbys to go fetch WAL from the archive should they need to, in which case you should be able to dispense with the slots without problem.
www.seiler.us
À: "pgsql-admin" <pgsql-admin@postgresql.org>
Envoyé: Jeudi 8 Décembre 2022 00:00:09
Objet: Using non-Super user with PgAmdin
Sent: Wednesday, December 7, 2022 6:18 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Using non-Super user with PgAmdin
À: "pgsql-admin" <pgsql-admin@postgresql.org>
Envoyé: Jeudi 8 Décembre 2022 00:00:09
Objet: Using non-Super user with PgAmdin
Hi Sarwar, > On 08/12/2022 00:51 CET M Sarwar <sarwarmd02@outlook.com> wrote: > > I have created a user with my name and unable to verify the same using pgadmin > or any other way. For many of you this question is a novice. I am seeking the > help after spending sometime on this. You can point me to the document if you > or anybody can think of anything or find it. > Thank you! > Sarwar A few questions: * What Postgres version do you use? * What error message do you get when connecting with your new user? I assume that you get an error when you say "unable to verify". * How did you create that user? Was it CREATE USER[1] or createuser via command line? In case of CREATE ROLE[2] you must also specify attribute LOGIN which effectively is the same as CREATE USER. The login privilege must also be enabled when creating roles via pgAdmin.[3] * What does your pg_hba.conf[4] look like? You must configure the right databases, usernames, and client addresses before connecting. [1] https://www.postgresql.org/docs/current/sql-createuser.html [2] https://www.postgresql.org/docs/current/sql-createrole.html [3] https://www.pgadmin.org/docs/pgadmin4/latest/role_dialog.html [4] https://www.postgresql.org/docs/current/auth-pg-hba-conf.html P.S. Please don't hijack threads. -- Erik
Hi Sarwar,
> On 08/12/2022 00:51 CET M Sarwar <sarwarmd02@outlook.com> wrote:
>
> I have created a user with my name and unable to verify the same using pgadmin
> or any other way. For many of you this question is a novice. I am seeking the
> help after spending sometime on this. You can point me to the document if you
> or anybody can think of anything or find it.
> Thank you!
> Sarwar
A few questions:
* What Postgres version do you use?
* What error message do you get when connecting with your new user? I assume
that you get an error when you say "unable to verify".
* How did you create that user? Was it CREATE USER[1] or createuser via command
line? In case of CREATE ROLE[2] you must also specify attribute LOGIN which
effectively is the same as CREATE USER. The login privilege must also be
enabled when creating roles via pgAdmin.[3]
* What does your pg_hba.conf[4] look like? You must configure the right
databases, usernames, and client addresses before connecting.
[1] https://www.postgresql.org/docs/current/sql-createuser.html
[2] https://www.postgresql.org/docs/current/sql-createrole.html
[3] https://www.pgadmin.org/docs/pgadmin4/latest/role_dialog.html
[4] https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
P.S. Please don't hijack threads.
--
Erik
This is not a “smart” approach.
The application should run with “least privilege”, and the developers should develop within the same context.
There are far to many applications that demands some sort of elevated permissions to work.
From: JP Pozzi <jpp@jppozzi.dyndns.org>
Sent: torsdag 8. desember 2022 00:19
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Using non-Super user with PgAmdin
Hello,
It's quite normal and smart not to give developers superuser rights and Pgadmin is OK with that.
Regards
JP P
De: "M Sarwar" <sarwarmd02@outlook.com>
À: "pgsql-admin" <pgsql-admin@postgresql.org>
Envoyé: Jeudi 8 Décembre 2022 00:00:09
Objet: Using non-Super user with PgAmdin
Hello All,
After working in the databases for the decades, recently I started a project postgress side newly.
I have a question on pgadmin access.
Can I use other than super user to login into pgadmin?
I am trying to provide access to all the development team members without giving them access to super user, postgress.
Thanks,
Sarwar
8 times ocp dba
3 cloud certificates