I have PostgreSQL 14 on two Linux servers. There is a database on both servers, let’s say database ‘A‘ in server-1 and database ‘B‘ server-2. I have a software that generates some data, and while I keep that data primarily in ‘A’, I would like to use ‘B’ as the “back-up” database, meaning that ‘A’ and ‘B’ should have the exact same data.

Therefore, I need to handle the case of ‘A’ or ‘B’ failing to receive data. For example when ‘A’ fails, I would like to be able to read and write with ‘B’, and update ‘A’ later. Or the opposite, when ‘B’ is down, ideally the database ‘A’ should still receive the data, and when ‘B’ is back again, it should be updated with the missing piece.

In order to achieve the above, I read about:

  • Logical replication ( I would like to avoid the complexity that the bidirectional replication brings and simply use logical rep)

  • Handling failover and failback - preferably in an automated way?

Now I know how to configure the databases for logical rep, and set up database 'A' as the publisher and 'B' as the receiver; however, I am still not sure how I would write in ‘B’ when the primary database ‘A’ is down - so the configuration of this is not clear to me.

I read about ‘pg_rewind’, but can I even use it with logical rep configuration - in an automated way?

If you have any input to make things more clear in my head, it is highly appreciated.


  • You want some sort of multi-master solution. You cannot have that with plain PostgreSQL, and it is always a complicated solution. Use one of the trusted high availability solutions that rely on streaming replication. Aug 30, 2022 at 5:39
  • @LaurenzAlbe Thank you for your reply, I got the inspiration from the comment written under this post: dba.stackexchange.com/questions/218129/… . The problem described in this link is very similar to mine, and in the comments, someone suggests using 'standard replication' with 'pg_rewind'; however, I can't find the right documentation to figure out how the configuration would be
    – pgresdbq
    Aug 30, 2022 at 7:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.