-1

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:

  1. 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.

  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?

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.

erTugRul
  • 101
  • 5

2 Answers2

2

What you would like to do is normally done with a Multi-master setup (write to both servers at the same time). Postgresql-10 doesn't have this feature by default, so what you are trying to do will always be a kind of a hack.

Typically you setup a master and a slave, then replicate the data with something like wal-shipping (streaming replication), and then monitor this with something like repmgr. Enabling synchronus replication will ensure that both servers has the same information, but you only write it to the master!

When repmgr detects that the master is failed, it can automatically promote the slave to master (and vice versa). And if you use synchronus replication, they will have the exact same data. Repmgr can do the failover automatically.

I would also use keepalived to have a specific IP address being used on the host that is postgresql master, and you will configure this IP as destination to your clients. On top this you could also add something like pgpool2 to load-balance reads and route queries to the master automatically, but please note that there is a lot of components to setup and manage properly.

If this isn't sufficient, and that you are really only satisfied with a Master/Master setup, then you will need to use some commercial solution, like Postgresql BDR which can handle the complexity for you.

Fredrik
  • 528
  • 2
  • 10
0

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.

Colin 't Hart
  • 283
  • 2
  • 16