0

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.

Improve this question
0

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 atable ENABLE REPLICA TRIGGER tgname;
Improve this answer
4
  • 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 ? – user351107 Feb 11 '20 at 18:36
  • Yes to the first question, no to the second. – Laurenz Albe Feb 11 '20 at 18:38
  • wouldnt adding logical replication to the master add more load to it ? It is better off then having the triggers in the master itself? – user351107 Feb 11 '20 at 20:00
  • 1
    Logical replication is way cheaper than a trigger. – Laurenz Albe Feb 11 '20 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.