Database Cluster

Terminology

Cluster: A set of three servers (Master + Slave + DR) & one witness erver
Instance: A running instance of PostgreSQL on each server to manage the one/more databases
Database: A database to manage the application/microservices connections & replication etc.
Schema: Every microservice has its own schema

Microserviec connect to Master DB via VIP
Witness server to determine which DB node is Master/Slave via Heart Beat Mechanism
Read/Write process will be done on the Master DB
Streaming Replication is performed asynchronously to ensure data from Master is always written into Slave and DR
Other system if needed can read data from Slave without interfering with the active Master node connection pool

Upon a TCP heartbeat check failure, witness server will trigger an automatic failover
An automated failover happens within a few seconds
During this period, the witness server will re-elect the original slave node to be a new master
Application microservices will re-establish coneections to the VIP
Once the failed original master node is back online, the witness server will re-elect the original master to be the new slave
Stream application will commence from new master to the new slave after config change
Microservices and databases will not be affected despite witness server failure, witness server is leveraging on the VM failover

Segrate databases into multiple clusters
Allow independent management of data module and mitigating a single point of failure impact for entire application