I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same setup in Recovery Center. planning to setup postgres in streaming mode. I am focusing on DB replication model.What kind of replication should I have between the 2 servers ? Is something synchronous feasible.
I did try the replication process with hot standby mode in postgres, But have some confusions which needs some guidance.
Configuration Steps: https://pastebin.com/7DJAvnPr
*Consideration:
DB is successfully configured in streaming mode "hot standby" (Primary) DC server --> (Secondary) DR Server
Network failure switching to secondary server will be done automatically during permanent failures FROM DC to DR
Network Switching back will be done automatically from DR to DC
OS : Windows server , DB : Postgres 9.6
Normal Operation:
With hot standby, the replication is working as expected and DB is getting synced as expected.
Operation live in secondary Server:
Since the DB is already in sync state all my required info are intact with my DC.
since the postegres in setup in slave mode,which is read-only in my case. We were not able to write data in DB.
Would like to understand is there any internal PostgreSQL mechanism available for making this DB as master
or
Should i reset the posgres configuration to master and restart postgres service
Recovered state (switching back from DR server to DC server):
I am not able to visualize what should be done. and how should we proceed with the DB sync from secondary to primary. Would like some advise here
** I know I got Windows machines and I can make a Windows Cluster(that postgresql listener I wanted) and It should failover automatically between the postgresql windows services in case one stops.
