Bidirectional conflict resolution setup for PostgreSQL-to-PostgreSQL pipeline #

For bi-directional replication, both postgresql instances function as source and destination. So it is necessary to have appropriate permissions on both instances. In addition, PostgreSql origin set up is needed to be done on both postgresql nodes.

The following diagram shows bidirectional replication topology.

flowchart LR PN1[(PostgreSQL_node_one)] RN1(Replicant_one) PN2[(PostgreSQL_node_two)] RN2(Replicant_two) PN1-->RN1 RN1-->PN2 PN2-->RN2 RN2-->PN1

This section describes PostgreSQL node setup as well as Replicant setup for bidirectional replication.

PostgreSQL setup #

First node #

Create database permission for replication user:
```
ALTER USER replicate CREATEDB;
```
Create replication permission to replicate user:
```
ALTER USER replicate REPLICATION;
```

Create replication slot:

SELECT 'init' FROM pg_create_logical_replication_slot('io_replicate', 'wal2json');

Create replication origin with other node name:

SELECT pg_replication_origin_create('node2');

Super permission to query replication origin:
```
ALTER USER replicate WITH SUPERUSER;
```

Second node #

Create database permission for replication user:
```
ALTER USER replicate CREATEDB;
```
Create replication permission to replicate user:
```
ALTER USER replicate REPLICATION;
```

Create replication slot:

SELECT 'init' FROM pg_create_logical_replication_slot('io_replicate', 'wal2json');

Create replication origin with other node name:

SELECT pg_replication_origin_create('node1');

Change to superuser for permission to query replication origin:
```
ALTER USER replicate WITH SUPERUSER;
```

Replication configuration #

We need to specify replication names and filter node names in the Extractor configuration file for both directions. We use the following two parameters to specify node names and filter node names:

node-name. Specifies the name of the node from which data is being extracted.
filter-node-name. Specifies the name of the node—records from this node are filtered out.

For better understanding, see the following samples for both nodes.

First node #

realtime:
  threads: 4
  fetch-size-rows: 10000
  fetch-duration-per-extractor-slot-s: 3
  _traceDBTasks: true
  node-name: node1
  filter-node-name: node2

Second node #

realtime:
  threads: 4
  fetch-size-rows: 10000
  fetch-duration-per-extractor-slot-s: 3
  _traceDBTasks: true
  node-name: node2
  filter-node-name: node1