Configuration Parameter and Table Setting Requirements v6.2

Depending upon the multi-master replication system environment, certain configuration settings may be required in order for the conflict resolution process to operate properly.

The following are required only for the log-based method. These do not apply to the trigger-based method.

  • track_commit_timestamp. Any Postgres 9.6 and later database server containing a primary node must have its track_commit_timestamp configuration parameter enabled. The track_commit_timestamp parameter is located in the postgresql.conf file. If track_commit_timestamp is not enabled, then update/update conflicts are not automatically resolved such as by using the earliest timestamp of the conflicting transactions. As a result, these conflicting transactions are left in a pending state. See Automatic Conflict Resolution Example for an example of how update/update conflicts are automatically resolved.
  • REPLICA IDENTITY FULL. If update/update conflicts are expected to occur on a given publication table, then the REPLICA IDENTITY setting for the table must be set to FULL on every primary node. The case where update transactions occur on separate primary nodes, but updating different columns in the same row, is not considered an update/update conflict. However, if REPLICA IDENTITY is not set to FULL, then this case will be recorded as an update/update conflict.

The REPLICA IDENTITY option is set to FULL using the ALTER TABLE command as shown by the following:

ALTER TABLE schema.table_name REPLICA IDENTITY FULL

The following is an example of the ALTER TABLE command:

ALTER TABLE edb.dept REPLICA IDENTITY FULL;

The REPLICA IDENTITY setting can be displayed by the PSQL utility using the \d+ command:

edb=# \d+ edb.dept
                                  Table "edb.dept"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 deptno | numeric(2,0)          | not null  | main     |              |
 dname  | character varying(14) |           | extended |              |
 loc    | character varying(13) |           | extended |              |
Indexes:
    "dept_pk" PRIMARY KEY, btree (deptno)
    "dept_dname_uq" UNIQUE CONSTRAINT, btree (dname)
Referenced by:
    TABLE "emp" CONSTRAINT "emp_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno)
    TABLE "jobhist" CONSTRAINT "jobhist_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL
Replica Identity: FULL
Note

In addition to conflict resolution requirements, the REPLICA IDENTITY FULL setting may be required on publication tables for other reasons in xDB Replication Server. See Table Settings and Restrictions for Table Filters for additional requirements.