From: | Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | How to determine whether I'm running on a standby? |
Date: | 2022-02-10 19:54:05 |
Message-ID: | 45425911-b002-4c2d-256c-49877eca7457@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-02-10 19:56:42 | Re: How to determine whether I'm running on a standby? |
Previous Message | Guyren Howe | 2022-02-10 19:40:04 | Re: Can we go beyond the standard to make Postgres radically better? |