How to set up bidirectional replication with Postgresql

Question

I have two Linux servers having Postgresql-10 on each of them. On both of them, there is a database, say dbexample, having same schema, table, everything. Both of them are capturing data from an application and storing them on their database (dbexample).Let me tell you since both are capturing data from the same application, there is no mismatch in data at a time.

Now, what is my problem? At a time server 'A' fails due to any reason. That moment it is not able to capture data from the application. But since second is UP and have captured all the data on its database, it must have captured all the data for that time being when server A was DOWN.

Now, When Server A is UP I want all the data to be replicated on this server A from server B for that time, so that I can have all the data same on both the servers.

Considering the same scenario in case of failure of server B, I want the things to work in the opposite direction, i.e. data from Server A to be replicated to Server B.

I have gone through many tutorials and have come-up with below solutions:

1. To set Logical replication between both servers and make Publisher and Subscriber of each other. (But that is possible one way not bidirectional, if I am not wrong). So please suggest what could be the possible solution.

2. To arrange a server C and restore the schema only of database, and make Server A publisher and Server C its subscriber. Server C publisher and Server B its subscriber. Server B Publisher and Server A its subscriber. It will be like:-

A will pull data from B,

B will pull data from C, and

C will pull data from A.

Is it feasible when A and B already have data in their databases?

Which approach is better, or is there any other solution?

• mustaccio: You're looking for bidirectional replication, also known as multimaster.
• erTugRul: I have read bidirectional replication also, but didn't get how to apply that in this case.

Answer 1

You don't need bidirectional replication and its additional complexity.

Simply use standard replication, and setup B as a standby for A.

If A fails, let B take over as master, and then reconfigure A as standby for B, possibly using pg_rewind to do this.

Repeat as necessary should either node fail.

• erTugRul: Thanks for your response. Let me check how pg_rewind works.