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:
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.
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?
