PostgreSQL - triggers to fire only on replicated db

Question

Is it possible to fire triggers only on a read-only Postgres replicated database?

I want triggers to fire only on the replicated database - not on the master (to avoid load). This is to track - say - all updates/deletes.

Would enabling replica trigger be the way to go? On the primary/master, the triggers remain disabled, but in the replicated db, we can have the triggers fired?

The wal2json plugin is an option, but triggers seem a safer bet.

Answer 1

You can do that with logical replication, but not with streaming (physical) replication, because in the latter case the standby server is read-only.

For logical replication, you have to define the trigger as replica trigger:

CREATE TRIGGER tgname ... ON TABLE atable ...;
ALTER TABLE atable ENABLE REPLICA TRIGGER tgname;

• user351107: if there is a streaming replication already set from master, can logical replication be enabled on the same? can logical replication be enabled on a async replica instead of the master ?
• Laurenz Albe: Yes to the first question, no to the second.
• user351107: wouldnt adding logical replication to the master add more load to it ? It is better off then having the triggers in the master itself?
• Laurenz Albe: Logical replication is way cheaper than a trigger.