PostgreSQL master-slave database replication

Why I needed it?
I am currently working with an awesome startup which is building software for pharmacy, doctors, diagnostic center and hospital in Bangladesh. So far over 100 pharmacy have subscribed to our service, and some of them are open 24 hours a day. At first we used to take backup of our database (Postgres) per hour using bacman. With increasing subscriber it was becoming harder and harder to take backup. Creating a dump can put a lot of pressure on the system. We were using aggressive parallelism and get out of CPU cycles and some queries suffered. So we decided to separate our database server from app server. Cause then I would be able to take backup without request being failed at client end.
Moreover In this method records of the master server are duplicated to the slave servers and servers are all synced up. Application server can read from the slave servers using the IP addresses of the slave servers. And if master server fails, one of the slave servers can take over and converted to new master easily (We will cover this part later).
Networking of Machine
All my machines are in the same network to save latency.
Application Server - 158.245.249.214
Master PostgreSQL Server - 158.245.240.209
Slave PostgreSQL Server - 158.245.203.119PostgreSQL Installation
We will install PostgreSQL on both master & slave. I am using PostgreSQL 12 for this. Follow same installation procedures on both system stated bellow (master & slave).
sudo apt -y update
sudo apt -y install nano wgetWe need to import GPG key and add PostgreSQL 12 repository into our Ubuntu machine. Run the following commands to accomplish this.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -After importing GPG key, add repository contents to your Ubuntu 18.04 / 16.04 system.
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.listNow the repository has been added successfully, update the package list and install PostgreSQL 12 server and client packages on your Ubuntu 18.04 / Ubuntu 16.04 Linux system.
sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12It will install PostgreSQL server and client on system. After starting service. We will check if service is running. PostgreSQL service to come up after every system reboot.
sudo /etc/init.d/postgresql start
sudo /etc/init.d/postgresql status
sudo systemctl is-enabled postgresql
Configuring Master
Now we will create a database user “replication” who will carry out the task of replication.
su postgres
psqlThis will start psql program where you can create new user
postgres=# CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'giveA5tr0n9Pa55w0rD';See how I checked the List of roles after creating the user by using
\du
We can alter the maximum number of connection allowed to replication user by running following command on psql client
postgres=# ALTER ROLE replication CONNECTION LIMIT -1;Now this is a stand alone database. As we want to make it master of a database cluster, we need to change some configuration. Append following line at the end of `/etc/postgresql/12/main/postgresql.conf` file
listen_addresses = 'localhost,158.245.240.209'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64Please replace `158.245.240.209` IP with your master db’s IP.
Slave server need authentication for replication. Now append following line to`/etc/postgresql/12/main/pg_hba.conf` file
# Replace 158.245.203.119 with slave server's private IPhost replication replication 158.245.203.119/0 md5
Restart the PostgreSQL service, and check if service have started.
sudo /etc/init.d/postgresql restart
sudo /etc/init.d/postgresql status
We have finished all configuration in Master Server and our master server is ready for replication

Configuring Slave
We need to stop PostgreSQL service on slave server first.
sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql status
Append following line at the end of `/etc/postgresql/12/main/postgresql.conf` file
listen_addresses = 'localhost,158.245.203.119'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = onPlease replace `158.245.203.119` IP with your slave db’s IP.

Now append following line to `/etc/postgresql/12/main/pg_hba.conf` file
# Replace 158.245.240.209 with slave master's private IPhost replication replication 158.245.240.209/0 md5

We need to remove all files and folders of PostgreSQL data directory
cd /var/lib/postgresql/12/main/
sudo rm -rfv *Now we will fetch all data of master database to slave database by running following command
sudo su postgres
pg_basebackup -h 158.245.240.209 -U replication -p 5432 -D /var/lib/postgresql/12/main/ -Fp -Xs -P -RReplace the `158.245.240.209` IP with your master’s IP while running the command
It will prompt for password for `replication` PostgreSQL user, that is in our case `giveA5tr0n9Pa55w0rD`.
After it finish the fetching restart the PostgreSQL service.
sudo /etc/init.d/postgresql start
Congratulation you have successfully replicated your database
You can check by making any change on master database, it will be immediately get replicated on slave database

Whats Next?
- Connecting this database system with django application using database router.
- Using PgBouncer for connection pooling.
- Postgres failover implementation.

