Monday, August 15, 2011

Slony-I trigger handling

  • Triggers and functions are one aspect of database replication that I did not take into account when deliberating about database replication. I was focused solely on data,  but RAF today pointed this important part out. So disabling and enabling triggers on Postgresql is as straight forward as this:


From the documentation:

These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed. The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers will fire when the replication role is "origin" (the default) or "local". Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.

The concern about triggers and replication is that they are bound to cause fuzz (i.e. data duplication) if enabled in both master and replicas. Slony-I takes advantage of postgresql trigger behavior so triggers within replicas are not fired upon.

  • In Postgresql >= 8.3 ,there's a session variable called "session_replication_role" that in conjunction with the enable/disable trigger characteristic handles trigger management in a rather straight forward fashion.
One thing that caught my attention is that supposedly, if you delete the slony schema created during the cluster initialization,  you leave your database as if slony-I were never there. I'm not sure how to test this as I'm not so acquainted with triggers "under the hood", but I'll figure that out when SCC teach us about that particular matter.
I would love to experiment with Postgresql 9.0 >= native replication once I've gotten a better grasp of replication in general.

No comments:

Post a Comment