When using transactional xCluster Disaster Recovery (DR) in YugabyteDB, the DR replica is intentionally read-only for user tables.
The primary accepts writes; the replica blocks them. This is great for safety… but it means your application or script may need to know:
- ● Am I currently connected to the DR primary or the DR replica database?
This tip shows a safe, SQL-only trick to reliably determine:
select dr_role();
-- PRIMARY (you’re connected to the DR primary)
-- REPLICA (you’re connected to the DR replica DB)
-- UNKNOWN (detection not possible)
🌋 How Transactional xCluster DR Handles Writes
In transactional xCluster DR:
● The DR primary accepts inserts, updates, deletes.
● The DR replica rejects all user DML on replicated tables with:
ERROR: Data modification is forbidden on database that is the target of a transactional xCluster replication
This behavior is the foundation of our detection method.
🧱 Step 1: Create a Tiny “Probe” Table (On the DR Primary)
In the DR-protected database, create:
create schema if not exists dr_meta;
create table if not exists dr_meta.dr_role_probe (
id bigint primary key
);
🔍 Important:
This table must be part of the transactional xCluster DR replication configuration, just like your application tables.
If it isn’t replicated, it will remain writable on the DR replica… and you won’t be able to detect the DR role reliably.
🔎 Step 2: Validate That DR Protects the Probe Table (On the DR Replica)
On the DR replica, run:
set yb_non_ddl_txn_for_sys_tables_allowed = off;
insert into dr_meta.dr_role_probe(id) values (1);
You should see the DR error:
ERROR: Data modification is forbidden on database that is the target of a transactional xCluster replication
If the insert succeeds, the probe table isn’t included in the DR setup yet… fix that before continuing.
⚙️ Step 3: Create the dr_role() Function
There is one important detail:
- ⚠️
yb_non_ddl_txn_for_sys_tables_allowed
On the DR replica, if this GUC is set to true, even user-table DML may succeed, which breaks our detection.
Example:
insert into test select 1; -- FAILS on DR replica
set yb_non_ddl_txn_for_sys_tables_allowed = true;
insert into test select 1; -- NOW SUCCEEDS!
Because of this, our function must check this GUC, and if it’s on, return 'UNKNOWN'.
The dr_role() function will do the following:
● Checks whether yb_non_ddl_txn_for_sys_tables_allowed is off; if not, returns UNKNOWN.
● Confirms the probe table (dr_meta.dr_role_probe) exists.
● Attempts a normal INSERT into the probe table using a random ID.
● If the insert succeeds, it deletes all rows from the probe table and returns PRIMARY.
● If the insert fails with the xCluster DR write-protection error, it returns REPLICA.
● For any other error, returns UNKNOWN.
create or replace function dr_role()
returns text
language plpgsql
volatile
as $$
declare
guc_value text;
guc_norm text;
probe_id bigint;
begin
---------------------------------------------------------------------------
-- 0. Check yb_non_ddl_txn_for_sys_tables_allowed
-- When this GUC is enabled, writes may succeed even on DR replicas,
-- so write-based detection becomes unreliable.
---------------------------------------------------------------------------
begin
guc_value := current_setting('yb_non_ddl_txn_for_sys_tables_allowed', true);
exception
when undefined_object then
guc_value := 'false'; -- GUC not present on this YB version
when others then
guc_value := null;
end;
guc_norm := lower(coalesce(guc_value, 'false'));
if guc_norm not in ('off', 'false', '0', 'no') then
return 'UNKNOWN';
end if;
---------------------------------------------------------------------------
-- 1. Verify probe table exists
---------------------------------------------------------------------------
perform 1
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'dr_meta'
and c.relname = 'dr_role_probe';
if not found then
return 'UNKNOWN';
end if;
---------------------------------------------------------------------------
-- 2. Perform the write probe
-- On PRIMARY :
-- - INSERT works (or triggers unique_violation)
-- - DELETE cleans up so the table stays empty
--
-- On REPLICA :
-- - INSERT raises the xCluster DR error
-- - Return REPLICA and do NOT try to delete
---------------------------------------------------------------------------
probe_id := (random() * 1000000000000)::bigint; -- 0 .. 1e12
begin
insert into dr_meta.dr_role_probe(id) values (probe_id);
-- Primary: clean up
delete from dr_meta.dr_role_probe;
return 'PRIMARY';
exception
when unique_violation then
-- Still primary: clean up
delete from dr_meta.dr_role_probe;
return 'PRIMARY';
when others then
if sqlerrm like '%Data modification is forbidden on database that is the target of a transactional xCluster replication%' then
return 'REPLICA';
else
return 'UNKNOWN';
end if;
end;
end;
$$;
🧭 Expected Behavior
Assuming the probe table exists and the GUC is false:
If the GUC is true, or anything strange happens:
Example:
YugabyteDB Anywhere xCluster configuration:
DR Primary:
test=# select * from dr_meta.dr_role_probe;
id
----
(0 rows)
test=# select dr_role();
dr_role
---------
PRIMARY
(1 row)
test=# select * from dr_meta.dr_role_probe;
id
----
(0 rows)
DR Replica:
test=# SELECT * FROM dr_meta.dr_role_probe;
id
----
(0 rows)
test=# select dr_role();
dr_role
---------
REPLICA
(1 row)
test=# SELECT * FROM dr_meta.dr_role_probe;
id
----
(0 rows)
⚙️ Operational Notes & Caveats
✔️ Safe on both primary and replica
The probe insert either:
● Works → classified as PRIMARY → the table is immediately emptied, no rows left behind
● Fails → classified as REPLICA → table untouched
✔️ Use sparingly
Call at:
● app startup
● health checks
● DR automation
● scheduled monitoring jobs
No need to call per query.
✔️ GUC matters
If you intentionally or accidentally enable:
set yb_non_ddl_txn_for_sys_tables_allowed = on;
then writes may succeed on the replica → dr_role() returns UNKNOWN.
✔️ Probe table stays empty on primary
- ● No row accumulation.
- ● No manual cleanup needed.
🟦 Summary
This Tip provides a clean, reliable, SQL-only way to detect:
select dr_role(); -- PRIMARY | REPLICA | UNKNOWN
It:
● Works with transactional xCluster DR
● Requires no YBA API calls
● Leaves zero lasting footprint on the primary
● Hands you a reliable DR-role signal for apps, scripts, and health checks
This avoids any dependence on YBA APIs, external scripts, or actual mutations to real data.
Have Fun!
