Handling conflicts in active-active replication
The pgactive
extension works per database and not per cluster. Each DB instance
that uses pgactive
is an independent instance and can accept data changes from
any source. When a change is sent to a DB instance, PostgreSQL commits it locally and then uses
pgactive
to replicate the change asynchronously to other DB instances. When two
PostgreSQL DB instances update the same record at nearly the same time, a conflict can occur.
The pgactive
extension provides mechanisms for conflict detection and
automatic resolution. It tracks the time stamp when the transaction was committed on both the
DB instances and automatically applies the change with the latest time stamp. The
pgactive
extension also logs when a conflict occurs in the
pgactive.pgactive_conflict_history
table.
The pgactive.pgactive_conflict_history
will keep growing. You may want to
define a purging policy. This can be done by deleting some records on a regular basis or
defining a partitioning scheme for this relation (and later detach, drop, truncate partitions
of interest). To implement the purging policy on a regular basis, one option is to use the
pg_cron
extension. See the following information of an example for the
pg_cron
history table, Scheduling maintenance with the
PostgreSQL pg_cron extension.