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 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 vice-versa, 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 for vice-versa, 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?
Kindly suggest which one is better way or any other solution you have. Sorry for my language if I am unable to express my problem. Ask me for any clarification.