Designing a Schema for Active-Active Replication

When designing a schema for use pgEdge Platform, you should ensure that:

• All tables include a primary key; this allows pgEdge Platform to replicate INSERT, UPDATE, and DELETE statements. If your table does not include a primary key, only INSERT statements will be replicated.

• At this time, bi-directional replication does not replicate sequences; if you use a sequence as a uniquifier in your primary key, you should instead use a UUID.

• All schema objects (including columns) must exist on each node of the cluster before starting replication. If you start replication between schemas that don't match, pgEdge Platform will return an error (object not found).

• Triggers will fire only from the node on which they were invoked. If you have triggers that are related to the replication process, you should include the ENABLE ALWAYS clause when attaching that trigger to a table:

CREATE TRIGGER ins_trig AFTER INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE ins_history();
ALTER TABLE trans_history ENABLE ALWAYS TRIGGER ins_trig;

After creating or adjusting your schema, you're ready to install pgEdge Platform.

Note: Functions, triggers, views, and other non-data schema objects are not replicated, and must be manually maintained in tandem on each node if used by any process involved with replication unless you have enabled automatic DDL replication. If the object is not involved in replication or actively used by another replicated process, it can reside in the schema, but will be ignored.