We have to set up PostgreSQL clustering and failover of the Master database to the Slave database.
What we are working with:
- 2 Windows servers, running on 2016
- PostgreSQL version 11 (clustering set up)
- On-prem installation (no internet connection)
We are setting up the production environment for on-prem Windows servers for a chatbot, using Botpress. The Botpress application uses Postgres as its internal database. We have 2 servers, each server has a Botpress application and a Postgres database.
A load balancer will spread the load between the two Botpress applications (on the 2 different servers). Both Botpress applications will be connected to the same Postgres database (Master). The Master database will be replicated on the Slave database. See diagram 1.
In case the Master Postgres database is down, both Botpress applications need to change to connect to the Salve Postgres database. See diagram 2. The Slave database should then become the Master.
We need a script using PowerShell that:
- Is implemented on both servers.
- Loops indefinitely and do a Write query to the current Master Postgres database, which checks if it is available.
- If the current Master database is down elect the Slave database using pg_ctl promote.
- Restart the Botpress service.
- The Slave database should then become the Master database