Thread: How to determine whether I'm running on a standby?
Hi!
I am running PostgreSQL 13.5, one primary cluster and two standby clusters, managed by PgBouncer which will automatically promote one of the standbys to the primary in case of failure. I have a job that is cleaning "old data", with "old data" being whatever business side of the company deems as "old data". The cleanup is a PgPL/SQL procedure which uses a bunch of DELETE commands. The procedure works fine on the primary but reports a bunch of errors on the read-only standbys. In another database system, I have the table called V$DATABASE and the column named DATABASE_ROLE which can be either primary or some kind of standby (physical or logical). Is there anything like that in the world of Postgres? I would like to do something like this:
DECLARE
DB_ROLE VARCHAR2(64);
BEGIN
SELECT DATABASE_ROLE INTO DB_ROLE FROM V$DATABASE;
IF DB_ROLE = 'PRIMARY' THEN
<do a bunch of deletions>
ELSE
exit;
END IF;
END;
What would a similar idiom in the PostgreSQL world look like? I have the job in crontab on the primary and I don't really know which of the 3 clusters will be my primary at any given moment. So far, I am using manual labor to do that, which is silly. Knowing Postgres idioms, there will probably be a function for this but I was unable to figure out which one.
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
The procedure works fine on the primary but reports a bunch of errors on the read-only standbys.
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL
On Thu, Feb 10, 2022 at 12:54 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:The procedure works fine on the primary but reports a bunch of errors on the read-only standbys.
A standby is in perpetual recovery mode.
(Just like in SQL Server.)
Angular momentum makes the world go 'round.