Deploy PostgreSQL Cluster for Odoo

Build PostgreSQL Cluster (Master + Slave) + pgbouncer as a relay

Hello and greetings everyone.

In this post, I'll be showing you how to deploy PostgreSQL cluster and link it with Odoo production environment.

If you are going to deploy newly fresh production environment, I recommend starting from here before proceeding with Odoo deployment.

We will be using repmgr & pgbouncer tools to implement PostgreSQL cluster.

Here is the link to Github repo. of deployment scripts: https://github.com/mmhy2003/Postgres-Cluster-Deploy


What is:

  1. repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers.

  2. pgbouncer is a lightweight connection pooler for PostgreSQL.


Scenario:

The scenario is, we have 2 PostgreSQL servers master & slave and pgbouncer server to relay connection between servers on failover.

  1. DBHOST1 (Master) 192.168.1.10

  2. DBHOST2 (Slave) 192.168.1.11

  3. PgBouncer 192.168.1.12

Odoo will connect to PostgreSQL database indirectly via pgbouncer.


As shown in this figure Odoo will connect to PgBouncer server to relay requests to PostgreSQL Master DB.

As shown in these figures, when the real Master DB fails, you promote Slave DB to be new Master DB and configure PgBouncer to switch to new Master and then when all issues got fixed on the old Master demote it to be the new Slave DB.


Setup:

All servers have to be running & Ubuntu server 16.04 installed on them.

Important Note: before running installation scripts, you have to edit script parameters specified in it.
Step 1 (Master DB):

Download this script to install Master PostgreSQL Server:

sudo wget https://raw.githubusercontent.com/mmhy2003/Postgres-Cluster-Deploy/master/01-master-install.sh
sudo chmod +x 01-master-install.sh
sudo nano 01-master-install.sh

ODOO_DB_USER="odoo" # DB user going to be used by Odoo
ODOO_DB_PASS="odoo"
MASTER_IP="192.168.1.10" # Master Server IP address
SLAVE_IP="192.168.1.11" # Slave Server IP address
SLAVE_USER="sammy" # SSH user on Slave Server for transfering key files
SLAVE_PASS="sammy"
NETWORK="192.168.1.0/24" # Network IP & Subnet

After modifying script parameters to suit your network setup, save modification by pressing Ctrl+O then Enter then Ctrl+X to exit the editor.

Then run the script to install:

sudo ./01-master-install.sh

Basically, the script will install PostgreSQL and repmgr then configure both of them in addition to generating & transferring password-less SSH key to Slave Server and creating ready scripts in the home directory to help DB admin to promote or demote server easily.

The password-less key file will be used by both PostgreSQL servers to sync data together without manual authenticating.


Step 2 (Slave DB):

Download second script on Slave server to Install PostgreSQL & Password-less SSH keys:

sudo wget https://raw.githubusercontent.com/mmhy2003/Postgres-Cluster-Deploy/master/02-slave-install.sh
sudo chmod +x 02-slave-install.sh
sudo nano 02-slave-install.sh

MASTER_IP="192.168.1.10" # Enter Master server IP address
SLAVE_IP="192.168.1.11" # Enter Slave server IP address

Then, save modification by pressing Ctrl+O then Enter then Ctrl+X to exit the editor.

It will install PostgreSQL, repmgr & password-less keys and clones Master server configurations.


Step 3 (PgBouncer):

Download final script to install PgBouncer server.

sudo wget https://raw.githubusercontent.com/mmhy2003/Postgres-Cluster-Deploy/master/03-pgbouncer-install.sh
sudo chmod +x 03-pgbouncer-install.sh
sudo nano 03-pgbouncer-install.sh

MASTER_IP="192.168.1.10" # Master server IP address
SLAVE_IP="192.168.1.11" # Slave server IP address
ODOO_DB_USER="odoo" # DB user going to be used by Odoo
ODOO_DB_PASS="odoo"

Then, save modification by pressing Ctrl+O then Enter then Ctrl+X to exit the editor.

Now, everything should be OK.


Final Step (Odoo):

Now, go to my Odoo deployment article in order to complete installation of Odoo & Nginx, just ignore PostgreSQL installation step. 

Link:  Professional Odoo 11 Deployment Guide

or

If you already have an Odoo server deployed, open Odoo configuration file and modify DB settings:

db_host = 192.168.1.12 # PgBouncer server IP
db_port = 6432 # PgBouncer port
db_user = odoo # DB user
db_password = odoo # DB user's password


Important Notes:

  1. On both PostgreSQL servers, you should find 2 scripts promote-serverdemote-server to help you change the role of the servers easily in case of failure.

  2. On PgBouncer server, you should find 2 scripts also switch-node1switch-node2 to help you switch easily between Nodes.


Master Failover:

In case the Master DB failover, login to the Slave server and promote it with promote-server script & then login to PgBouncer server and run switch-node# script in order to switch to the new Master (Promoted) DB.

After fixing issues with the old Master server, run demote-server script to change the role of it to be the new Slave.


Thank you.