Conflict resolution preparation v7

Set up a database user with certain properties so that you can modify the publication table rows to resolve conflicts manually.

Manual conflict resolution typically requires modifying rows in one or more publication tables to correct wrong entries. You can perform these changes using a utility such as PSQL or pgAdmin (Postgres Enterprise Manager Client in Advanced Server).

Usually, manual publication table corrections are isolated. Limit modifications to the publication tables you're directly changing. Don't replicate them to the other primary nodes as normally occurs in the multi-master replication system.

To prevent replication of changes to a publication table, prevent the Replication Server insert, update, and delete triggers on the publication tables from activating when you make these corrections to the publication table rows. If any of the insert, update, or delete triggers antivate, an entry is added to the publication table’s shadow table. This entry results in a transaction replicated to the other primary nodes the next time synchronization replication occurs.

To prevent the triggers on the publication tables from activating, during the session in which you modify the publication table rows, set the database server configuration parameter session_replication_role to replica. (The default setting of session_replication_role is origin, in which case the triggers activate.)

The suggested method to ensure the replica setting is in effect is to create a database user with a default session setting of replica for this parameter. Whenever you connect to a database with this database user, the replica setting is in effect during that session.

Connect to a primary node with this database user whenever you plan to make manual corrections to the publication tables in that node that you don't want to replicate to the other primary nodes.

In the following example, database superuser MMRmaint is created and modified for this purpose:

MMRnode_a=# CREATE ROLE MMRmaint WITH LOGIN SUPERUSER PASSWORD 'password';
CREATE ROLE
MMRnode_a=# ALTER ROLE MMRmaint SET session_replication_role TO replica;
ALTER ROLE

When connected to a database with this database user, you can confirm this setting is in effect during the session using the following command:

MMRnode_a=# SHOW session_replication_role;

 session_replication_role
--------------------------
 replica
(1 row)