Postgres_Master_Slave Replication

The master-slave replication described below has been implemented in two different servers such that all CRUD operations can be done using master postgres and read_only operations can be done using slave_postgres

This plays a very crucial role in scaling the databases as the read-replica adds more availability to the existing database without adding any load on that master postgres machine

read write update delete happens only from the master server and read_replica is used for only the read_queries

All two replicas are implemented in a docker container format using compose files for easy portability:

docker-compose.yml of master_postgres

version: '3.7'
services:
  postgresql-master:
    image: 'bitnami/postgresql:11'
    ports:
      - '1111:5432'
    volumes:
    volumes:
        - ./postgres/data:/bitnami/postgresql/data
        - ./postgresql.conf:/opt/bitnami/postgresql/conf/postgresql.conf 
         
    env_file:
       - .env

.env

POSTGRESQL_REPLICATION_MODE=master #postgres-connection-mode

POSTGRESQL_REPLICATION_USER=postgres #postgres-master-username

POSTGRESQL_REPLICATION_PASSWORD=password #postgres-master-password

POSTGRESQL_USERNAME=postgres #postgresql-master-username

POSTGRESQL_PASSWORD=password #postgresql-master-password

#POSTGRESQL_DATABASE=<db-name>            #postgresql-database-to-use(optional)

docker-compose up -d #to start the master container

If we face any issue with the permission denied error while doing the docker-compose up then we can manually add the permissions to the volume map as below and try:

sudo mkdir -p ./postgres/data #path of the volume

sudo chown -R 1001:1001 ./postgres #path of the volume

we need to add permissions as 1001 because bitnami will try to access the above files from a non-root user and we make sure the permissions are in-line to be able to access those files

first, create the volume map containers as shown above and assign the permissions required for the postgres user to create tables in the specified volume

Tuning postgres for optimizing its performance

next-step Fine-tuning postgres

https://pgtune.leopard.in.ua/#/

first, generate the volume map of postgresql. conf by using the above website and save it in the same name and then

add the below in the volume map of docker-compose

- ./postgresql.conf:/opt/bitnami/postgresql/conf/postgresql.conf

docker container for slave

docker-compose.yml:

version: '3.7'
services:
  postgresql-slave:
    image: 'bitnami/postgresql:11'
    ports:
      - '2222:5432'
    env_file:
      - .env

.env

POSTGRESQL_REPLICATION_MODE=slave #postgres-connection-mode

POSTGRESQL_REPLICATION_USER=slave_user #postgres-slave-username

POSTGRESQL_REPLICATION_PASSWORD=slave_passwd #postgres-slave-password

POSTGRESQL_MASTER_HOST=master-ip #postgresql-master-ip-address

POSTGRESQL_PASSWORD=master-password #postgresql-master-password

POSTGRESQL_MASTER_PORT_NUMBER=master-port #postgresql-master-port

docker-compose up -d #to start the slave container

if there is an error repl_user not found you can create a user in master using

CREATE USER slave_user WITH PASSWORD '<password>';

and then assign the below permissions

login into adminer and check if the slave can access the database and its tables

If the slave cannot read the tables and gets errors in logs such as

ERROR: permission denied for

Then change the privileges for the tables to slave user in master postgres using:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO slave_user;
ALTER USER slave_user WITH SUPERUSER;

Once this is applied the slave should be able to access the database tables with read-only permission

The above complete setup has been made talking postres:11 as a base version

* There might be few changes if there are changes w.r.to the change in version of postgres used

This is my first time writing these articles any suggestions or corrections are highly accepted

let me know if you have any other queries or face any issues while doing the above setup

Thanks to Appareddy Dwarampudi for helping me throughout the process