• Home
  • Devops
  • Opensource
More

    Creating HA Postgres Setup on CentOS with streaming replication

    In this blog we will create the master-slave setup of the postgres using the streaming replication. All below steps are tested with the centos.

    HA Postgres Setup:

    Type of the replication — Streaming Replication

    Step 1: Install the Postgres on both master and slave (Centos 7)

    $ rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

    $ yum install postgresql10-server postgresql10
    Init Postgres:
    $ /usr/pgsql-10/bin/postgresql-10-setup initdb
    Start and Enable the postgres service:
    $ sudo systemctl start postgresql-10.service
    $ sudo systemctl enable postgresql-10.service
    Make sure the postgres is working:
    $ su — postgres -c “psql”
    psql (10.0)
    Type “help” for help.
    postgres=#

    Step 2: Configure SSH passwordless connection for postgres user

    Create the public and private keys:

    ## On postgres101.us-west-2.compute.internal — Master
    ## Create a private key for postgres user
    sudo -u postgres ssh-keygen -b 2048 -t rsa -N “” -C “[email protected]
    ## On postgres102.us-west-2.compute.internal — Slave
    ## Create a private key for postgres user
    sudo -u postgres ssh-keygen -b 2048 -t rsa -N “” -C “[email protected]

    Copy the public keys:

    Slave to Master Password less:

    On postgres101.us-west-2.compute.internal — Master Copy the key /var/lib/pgsql/.ssh/id_rsa.pub
    
    On postgres102.us-west-2.compute.internal — Slave Paste the key
    /var/lib/pgsql/.ssh/authorized_keys

    Master to Slave Password less:

    On postgres102.us-west-2.compute.internal — Slave Copy the key /var/lib/pgsql/.ssh/id_rsa.pub
    
    On postgres101.us-west-2.compute.internal — Master Paste the key
    /var/lib/pgsql/.ssh/authorized_keys

    Test the connection :

    On master: SSH into slave without password

    On master postgres101.us-west-2.compute.internal:

    $ sudo su postgres
    bash-4.2$ ssh [email protected]
    Last login: Wed Oct 24 05:27:46 2018 from postgres101.us-west-2.compute.internal
    -bash-4.2$

    On Slave: SSH into the master without password

    On slave postgres102.us-west-2.compute.internal:

    $ sudo su postgres
    bash-4.2$ ssh [email protected]
    Last login: Wed Oct 24 05:27:46 2018 from postgres102.us-west-2.compute.internal
    -bash-4.2$

    If you are able to log in then the SSH passwordless connection is successful.

    Step 3: Setup the repmgr for master and slave.

    $ curl https://dl.2ndquadrant.com/default/release/get/10/rpm | sudo bash
    $ yum install repmgr10

    Create the replication repmgr user on master and slave:

    $ sudo -i -u postgres
    postgres$ createuser — replication — createdb — createrole — superuser repmgr
    postgres$ psql -c ‘ALTER USER repmgr SET search_path TO repmgr_test, “$user”, public;’
    postgres$ createdb repmgr — owner=repmgr

    Change the postgresql.conf on master and slave : Few settings are dependent on the type of server you are using. Check the tool: https://pgtune.leopard.in.ua/#/ for some exact numbers.

    For archive make sure you have the directory in our example it’s /mnt/server/archivedir. If not create the directory and allow postgres user to access it on both master and slave.

    $ mkdir -p /mnt/server/archivedir
    $ sudo chown -R postgres:postgres /mnt/server/archivedir

    Add the following lines with appropriate values in: /var/lib/pgsql/10/data/postgres.conf on master and slave. Keep all other parameters as default.

    Change the configuration of the pg_hba.conf: /var/lib/pgsql/10/data/pg_hba.conf
    Allow the repmgr user trust for the PostgreSQL servers:
    Add following lines in /var/lib/pgsql/10/data/pg_hba.conf on master and slave.

    
    #The following setting is for the replication master and server
    # Master — postgres101.us-west-2.compute.internal — 192.168.10.5/32
    # Slave — postgres102.us-west-2.compute.internal — 192.168.10.6/32
    
    host repmgr repmgr 192.168.10.5/32 trust
    host repmgr repmgr 192.168.10.6/32 trust
    
    host replication repmgr 192.168.10.5/32 trust
    host replication repmgr 192.168.10.6/32 trust
    
    

    Save the configuration.

    Restart the postgres :

    $ sudo systemctl restart postgresql-10.service

    Verify the connection:

    # On Master — postgres101.us-west-2.compute.internal
    sudo su postgres
    psql ‘host=postgres102.us-west-2.compute.internal dbname=repmgr user=repmgr’

    # On Standby — postgres102.us-west-2.compute.internal
    sudo su postgres
    psql ‘host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr’

    Modify the repmgr configuration with respect to master and slave.

    On master :

    conninfo — It is the URL where repmgr will able to connect local postgres. According to the configuration, it will change. For master it is :postgres101.us-west-2.compute.internal

    node_id — It must be unique (integer)
    node_name- It must be a unique string (hostname for example)
    Please check the comments in the configuration file for the option usage.
    File location : /etc/repmgr/10/repmgr.conf

    For logging make sure the file is created and have the appropriate permission for the postgres user. In this example,

    # On master
    $ touch /var/log/repmgr.log
    $ sudo chown postgres:postgres /var/log/repmgr.log

    The failover for this approach is Manual. We are not considering the automatic failover as of now.

    Register the Primary server with repmgr:

    On master run the following: ( Make sure the PostgreSQL is running on master )

    $ sudo su postgres
    bash-4.2$ cd /usr/pgsql-10/bin
    bash-4.2$ ./repmgr -f /etc/repmgr/10/repmgr.conf primary register
    # Verify the repmgr registration
    bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf cluster show
    ID | Name | Role | Status | Upstream | Location | Connection string
    — — + — — — — — — — — — — — — — — — — — + — — — — -+ — — — — — -+ — — — — — — — — — — — — — — — — — + — — — — — + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
    1 | postgres101.us-west-2.compute.internal | primary | * running | | default | host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr
    bash-4.2$

    For the first time, it will create the extension and register the master as the primary node.

    Start the repmgr service on master :

    $ sudo systemctl start repmgr10.service

    Register the standby server:

    Modify following in the repmgr.conf on standby: Change the settings according to your configuration.

    Make sure on slave you have the log file.

    #slave
    $ touch /var/log/repmgr.log
    $ sudo chown postgres:postgres /var/log/repmgr.log

    Clone from the master and register standby.

    Run following on standby:

    $ sudo systemctl stop postgresql-10.service
    $ sudo su postgres
    bash-4.2$cd /usr/pgsql-10/bin
    bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf — force -h postgres101.us-west-2.compute.internal -d repmgr -U repmgr — verbose standby clone
    bash-4.2$ exit
    $ sudo systemctl start postgresql-10.service
    $ sudo su postgres
    bash-4.2$cd /usr/pgsql-10/bin
    bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf — force standby register
    #Verify connections
    bash-4.2$ ./repmgr -f /etc/repmgr/10/repmgr.conf cluster show
    ID | Name | Role | Status | Upstream | Location | Connection string
    — — + — — — — — — — — — — — — — — — — — + — — — — -+ — — — — — -+ — — — — — — — — — — — — — — — — — + — — — — — + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
    1 | postgres101.us-west-2.compute.internal | primary | * running | | default | host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr
    2 | postgres102.us-west-2.compute.internal | standby | running | postgres101.us-west-2.compute.internal | default | host=postgres102.us-west-2.compute.internal dbname=repmgr user=repmgr

    This will set up the replication between the master and the slave. Remember the slave act as the read-only replica of the master. So you must always write into the master. To verify the replication is in place create a database.

    # On master
    $ sudo su postgres
    $ psql
    postgres=# CREATE DATABASE y;
    CREATE DATABASE
    postgres=#
    # On slave
    $ sudo su postgres
    $ psql
    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    — — — — — -+ — — — — — + — — — — — + — — — — — — -+ — — — — — — -+ — — — — — — — — — — — -
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    y | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (2 rows)

    This verifies your replication is working. Postgres is deployed with HA status and having streaming replication.

    Recent Articles

    SED Command : Search And Replace in file

    This is 1m read blog. In this blog we will check how to replace source string with replacement string for file with...

    How to check top 10 CPU consuming processes on Linux ?

    In shared linux server environment it is important to track the usage of high CPU consuming processes. If...

    Explained: Keep Specific Metrics in Prometheus using Prometheus Job

    This is 1m read article written by Kuberneteslab. In this article we will discuss about the keep action for keeping certain metrics in...

    How to check the Disk Statistics on Linux using Terminal ?

    This is 1m read article written by Kuberneteslab. In this article we will discuss about the disk statistics and how to view...

    How to get resources from Kubernetes Cluster using Kubectl ?

    Get Resources from K8S Cluster using Kubectl cmd: In this article we will look into some commands...

    Related Stories

    Subscribe
    Notify of
    guest
    0 Comments
    Inline Feedbacks
    View all comments

    Stay on op - Ge the daily news in your inbox

    0
    Would love your thoughts, please comment.x
    ()
    x