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