What Replica Mode Does Not Switch Off

The second session, and the way back

Because the mode is a session GUC, a second connection to the same database that has set nothing of its own is still enforcing, right through the load.

INSERT INTO orders (customer_id, amount, tenant_id) VALUES (998, 10, 1);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key is not present in table "customers".

That containment is the parameter's best property and the reason the lever belongs in a restore pipeline rather than in a shell somebody happens to be typing in. pg_settings reports its context as superuser, and since PostgreSQL 15 the privilege can be handed to a single role with GRANT SET ON PARAMETER session_replication_role TO loader. The refusal a role meets without that grant, along with the restore case the whole lever exists for, is in the data-only restore report.

The accounting gets thin on the way back out. RESET session_replication_role re-checks nothing, which surprises nobody, but pg_constraint goes on reporting convalidated t and conenforced t for every constraint on orders while those two orphans sit there, and the statement people reach for to settle their nerves settles nothing at all.

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
ALTER TABLE
SELECT o.id, o.customer_id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL ORDER BY o.id;
 id | customer_id 
----+-------------
  1 |           2
  2 |         999
(2 rows)

VALIDATE CONSTRAINT is defined against constraints marked NOT VALID, and handed one that PostgreSQL already considers valid it returns success without reading a row. Finding the damage takes the anti-join above, written by you, against the tables you touched, and a schema-only pg_dump taken afterwards says nothing about replication anywhere, correctly, since nothing in the schema was ever changed.

DISABLE TRIGGER USER against DISABLE TRIGGER ALL

The second lever writes to the catalog, so its effect outlives the session that set it and reaches everyone connected. Run as the owner, ALTER TABLE orders DISABLE TRIGGER USER leaves the foreign key fully armed, since USER skips internal triggers by definition, and the orphan insert is rejected exactly as at the baseline. Both application triggers go quiet, the ENABLE ALWAYS one included, which is precisely the trigger replica could not reach, and one writer survives.

SELECT source FROM order_log ORDER BY id;
         source         
------------------------
 rule, default (ORIGIN)
(1 row)

Rules are rewrite-time machinery, so no spelling of DISABLE TRIGGER touches them, and under replica that same rule had been the one to fall silent. Each lever leaves a different set of side effects running, which is worth knowing before you switch from one to another halfway through a load.

ALL belongs to a different privilege class, and the owner of the table is refused.

ALTER TABLE orders DISABLE TRIGGER ALL;
ERROR:  permission denied: "RI_ConstraintTrigger_c_16420" is a system trigger

A superuser gets ALTER TABLE, after which pg_trigger.tgenabled reads D for all five triggers on orders, the two internal RI ones included, against a baseline of O for the default trigger, A for always and R for replica. The orphan now goes in for everybody, and the second connection that was rejected in the previous section gets INSERT 0 1 on the identical statement.

Cascades are the trap in this one. Switching off every trigger on orders does not stop ON DELETE CASCADE from deleting orders rows, because the triggers that carry out the action live on the parent side of the constraint.

SELECT tgrelid::regclass AS on_table, tgname, tgenabled FROM pg_trigger WHERE tgconstraint = (SELECT oid FROM pg_constraint WHERE conname = 'orders_customer_id_fkey') ORDER BY 1, 2;
 on_table  |            tgname            | tgenabled 
-----------+------------------------------+-----------
 customers | RI_ConstraintTrigger_a_16418 | O
 customers | RI_ConstraintTrigger_a_16419 | O
 orders    | RI_ConstraintTrigger_c_16420 | D
 orders    | RI_ConstraintTrigger_c_16421 | D
(4 rows)

Deleting bo in that state emptied his orders, the way it does at the baseline, which is the opposite of what happens under replica with nothing disabled at all. A schema-only pg_dump then carries half the arrangement forward, in three lines.

ALTER TABLE public.orders DISABLE TRIGGER trg_always;
ALTER TABLE public.orders DISABLE TRIGGER trg_origin;
ALTER TABLE public.orders DISABLE TRIGGER trg_replica;

Nothing in that dump mentions the internal RI triggers, so the file describes a database whose foreign keys enforce while its application triggers stay switched off, a combination nobody chose, and re-enabling reads no data on the way back either.

ALTER TABLE orders ENABLE TRIGGER ALL;
ALTER TABLE
SELECT o.id, o.customer_id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL ORDER BY o.id;
 id | customer_id 
----+-------------
  7 |         999
 12 |         998
(2 rows)

In the container, DISABLE TRIGGER USER held a ShareRowExclusiveLock on the table for the length of its transaction.

NOT ENFORCED, one constraint at a time

PostgreSQL 18 made enforceability a property you can toggle, documented for foreign keys, and PostgreSQL 19 extends the same syntax to CHECK constraints. On 18.6 the foreign key accepts it while the CHECK is refused with cannot alter enforceability of constraint "orders_amount_check" of relation "orders". On 19beta3 the CHECK accepts it, a row with an amount of -5 goes straight in, and the refusal for NOT NULL spells out the rule.

ALTER TABLE orders ALTER CONSTRAINT orders_amount_not_null NOT ENFORCED;
ERROR:  cannot alter enforceability of constraint "orders_amount_not_null" of relation "orders"
HINT:  Only foreign key and check constraints can change enforceability.

While the constraint is off the catalog is honest about it, with conenforced and convalidated both f and \d orders printing ON DELETE CASCADE NOT ENFORCED on the foreign-key line. Scope here is the constraint, so every session sees it, ON DELETE CASCADE stops running as it does under replica, and the dump keeps the state.

    ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customers(id) ON DELETE CASCADE NOT ENFORCED;

On 19beta3 a switched-off CHECK reaches the dump the same way, as ADD CONSTRAINT orders_amount_check CHECK ((amount > (0)::numeric)) NOT ENFORCED;.

Switching back is the one route of the three that reads the data. With an orphan in the table and the policy out of the way, ENFORCED scans and stops.

ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey ENFORCED;
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

The constraint stays at conenforced f afterwards, so the failure is clean rather than half applied, and the key it names is the row to go and fix. For a CHECK constraint on 19beta3 the message is check constraint "orders_amount_check" of relation "orders" is violated by some row, with no row named. While enforcement is off, VALIDATE CONSTRAINT answers cannot validate NOT ENFORCED constraint, and adding a constraint NOT VALID is a genuinely different bit, since it reads conenforced t, convalidated f and enforces against new rows while nobody has vouched for the old ones. On both directions of the toggle an AccessExclusiveLock was taken in the container, which makes this the lever least suited to a live table.

Row-level security survives all three

Nothing above switched off the policy, and the cross-tenant insert was rejected under replica, under both spellings of DISABLE TRIGGER, and with the foreign key NOT ENFORCED.

INSERT INTO orders (customer_id, amount, tenant_id) VALUES (1, 10, 2);
ERROR:  new row violates row-level security policy for table "orders"

Bulk loading meets its own wall here, well before any lever is involved, because COPY FROM into a table with RLS forced, run by the owner, is refused outright.

COPY orders (customer_id, amount, tenant_id) FROM STDIN;
ERROR:  COPY FROM not supported with row-level security
HINT:  Use INSERT statements instead.

With RLS switched off on the table, COPY under replica behaves like INSERT, taking a row whose customer does not exist and stopping on the negative amount with new row for relation "orders" violates check constraint "orders_amount_check".

The last observation needs its scoping read before its result. The table has row-level security enabled and forced, the policy compares tenant_id against current_setting('app.tenant', true)::int, and the owner of the table runs every statement. Session A sets app.tenant, marks the foreign key NOT ENFORCED and inserts an orphan, leaving two rows in orders. Session B is a fresh connection that never set app.tenant, so current_setting('app.tenant', true) is NULL, the policy evaluates to NULL for every row, and the table looks empty to it.

SELECT count(*) AS rows_this_session_can_see FROM orders;
 rows_this_session_can_see 
---------------------------
                         0
(1 row)

ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey ENFORCED;
ALTER TABLE

The scan raised no error, and pg_constraint now reports conenforced t, convalidated t. When session C sets the tenant again, the orphan is still there and the constraint is doing its job on new rows.

SELECT o.id, o.customer_id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL;
 id | customer_id 
----+-------------
  2 |         999
(1 row)

INSERT INTO orders (customer_id, amount, tenant_id) VALUES (998, 10, 1);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key is not present in table "customers".

From that moment on new rows are checked, and the row already there was never looked at. Those same three sessions with a plain ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT end identically, and both sequences behaved the same way on 18.6 and on 19beta3. The controls matter as much as the result, so take FORCE off the table with ALTER TABLE orders NO FORCE ROW LEVEL SECURITY, which restores the owner's ordinary bypass, and the same ENFORCED fails on customer 999, as it does when a superuser runs it. Asking for SET row_security = off produces query would be affected by row-level security policy for table "orders", and using RESET app.tenant in the same session instead of opening a fresh one leaves the placeholder as an empty string, so the scan errors with invalid input syntax for type integer: "".

The documentation says that referential integrity checks bypass row-level security. The scan that ran here sits behind ENFORCED and is a different query, so the reading to take away is operational. Switch enforcement back on from a session that can see every row in the table, or lift FORCE for the duration, and run the anti-join yourself before you trust convalidated.

Before you reach for any of them

Start by asking what actually has to stop. Insert order in a schema whose foreign keys form a cycle is not an enforcement problem, since deferrable constraints hold the check until COMMIT without dropping it, and the circular foreign key guide works that through against real output. For what a foreign key promises and to whom, referential integrity is the explainer, and a one-paragraph version of this parameter sits in the PostgreSQL test data cookbook, next to generate_series and \copy.

Where the rows are being generated rather than moved between databases, none of this has to come out. Tools like Seedfast take a different route, reading the schema and generating rows that already satisfy the constraints, so nothing has to be switched off. Any lever you do pull is worth pulling narrowly, with the anti-join written before the load rather than after it.

Frequently asked questions

Related guides

• The dump that breaks its own restore. Where these levers show up in a restore pipeline, and what each one costs there.
• PostgreSQL test data: a syntax cookbook. generate_series, \copy and pg_dump --data-only, with the one-paragraph version of this parameter.
• Circular foreign key seed: three workarounds that actually run. Deferrable constraints and nullable back-edges, for the schemas that have no valid load order.
• Referential integrity in PostgreSQL. What the constraint promises, before you decide to suspend it.
• The Postgres insert that fails right after a successful load. The other thing a bulk load leaves behind, sitting in the sequences.