How to Install and Configure Master-Slave Replication with PostgreSQL 9.6 on CentOS 7

PostgreSQL or Postgres is an open source object-relational database management system (ORDBMS) with more than 15 years of active development. It's a powerful database server that can handle high workloads. PostgreSQL can be used on Linux, Unix, BSD, and Windows servers.

The master/slave database replication is a process of copying (syncing) data from a database on one server (the master) to a database on another server (the slave). The main benefit of this process is to distribute databases to multiple machines, so when the master server has a problem, there is a backup machine with same data available for handling requests without interruption.

PostgreSQL provides several ways to replicate a database. It can be used for backup purposes and to provide a high availability database server. In this tutorial, we will show you how to install and configure PostgreSQL 9.6 Master-Slave replication on a CentOS 7 server. We will use Hot standby mode, and it's a very good starting point to learn PostgreSQL in depth.

What we will do

  1. Install PostgreSQL 9.6
  2. Start and configure PostgreSQL 9.6
  3. Configure Firewalld
  4. Configure Master server
  5. Configure Slave server
  6. Testing

Prerequisite

    • 1 CentOS 7 server
      • Master - Read and Write Permission - IP: 10.0.15.10
    • 1 CentOS 7 server
      • Slave - Only Read Permission- IP: 10.0.15.11
    • Root Privileges

    Step 1 - Install PostgreSQL 9.6

    In this tutorial, we will discuss how to install the latest PostgreSQL version 9.6 on the CentOS 7 server. By default, the CentOS official repository offers an older version, so we need to install PostgreSQL from the official repository.

    Add new PostgreSQL 9.6 repository to the system.

    yum -y install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

    Now install PostgreSQL 9.6 using the following yum command.

    yum -y install postgresql96-server postgresql96-contrib

    So this way, you can install PostgreSQL 9.6.

    Step 2 - Start and configure PostgreSQL 9.6

    Before starting the postgres service, we need to initialize the database. For this, go to the '/usr/pgsql-9.6/bin' directory and initialize the database with postgres setup 'postgresql96-setup'.

    cd /usr/pgsql-9.6/bin
    ./postgresql96-setup initdb

    Next, start postgres service and enable it to start automatically at system boot.

    systemctl start postgresql-9.6
    systemctl enable postgresql-9.6

    By default, postgres runs on port 5432. So make sure port 5432 state is 'LISTEN' by executing the netstat command in the following way.

    netstat -plntu

    PostgreSQL installation on CentOS 7

    If you do not have a netstat command, install net-tools. It's part from the net-tools.

    yum -y install net-tools

    So PostgreSQL 9.6 has been started. But we still need to configure the password for postgres user. Login as 'postgres' user, and then access the postgres 'psql' shell.

    su - postgres
    psql

    Give the 'postgres' user new password with the query below.

    \password postgres
    Enter new password:

    Set postgres password

    So PostgreSQL 9.6 has been started and a new password for postgres user has been configured.

    Step 3 - Configure Firewalld

    Firewalld is default a firewall management tool on CentOS 7. We will start this service and open the port for PostgreSQL connection.

    Start firewalld and enable it to start automatically at system boot using the following commands:

    systemctl start firewalld
    systemctl enable firewalld

    Next, add new postgres service to firewalld with the following commands.

    firewall-cmd --add-service=postgresql --permanent
    firewall-cmd --reload

    Now check the open port of the service.

    firewall-cmd --list-all

    And you will see the PostgreSQL service has been added to the firewalld.

    Configure the firewall

    NOTE: Run the Step 1, Step 2 and Step 3 on all Master and Slaves.

    Step 4 - Configure Master server

    In this step, we will configure a master server for the replication. This is the main server, allowing read and write process from applications running on it. PostgreSQL on the master runs only on the '10.0.15.10' IP address, and performs streaming replication to the slave server.

    Go to the pgsql data directory '/var/lib/pgsql/9.6/data' and edit the configuration file 'postgresql.conf'.

    cd /var/lib/pgsql/9.6/data
    vim postgresql.conf

    Uncomment the 'listen_addresses' line and change value of the server IP address to '10.0.15.10'.

    listen_addresses = '10.0.15.10'

    Uncomment 'wal_level' line and change the value to 'hot_standby'.

    wal_level = hot_standby

    For the synchronization level, we will use local sync. Uncomment and change value line as below.

    synchronous_commit = local

    Enable archiving mode and give the archive_command variable a command as value.

    archive_mode = on
    archive_command = 'cp %p /var/lib/pgsql/9.6/archive/%f'

    For the 'Replication' settings, uncomment the 'wal_sender' line and change value to 2 (in this tutorial,  we use only 2 servers master and slave), and for the 'wal_keep_segments' value is 10.

    max_wal_senders = 2
    wal_keep_segments = 10

    For the application name, uncomment 'synchronous_standby_names' line and change value to 'pgslave01'.

    synchronous_standby_names = 'pgslave01'

    That's it. Save these changes and exit the editor.

    Moving on, in the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for archiving purposes.

    Create a new directory, change its permission, and change the owner to the postgres user.

    mkdir -p /var/lib/pgsql/9.6/archive/
    chmod 700 /var/lib/pgsql/9.6/archive/
    chown -R postgres:postgres /var/lib/pgsql/9.6/archive/

    Now edit the pg_hba.conf file.

    vim pg_hba.conf

    Paste configuration below to the end of the line.

    # Localhost
    host    replication     replica          127.0.0.1/32            md5

    # PostgreSQL Master IP address
    host    replication     replica          10.0.15.10/32            md5

    # PostgreSQL SLave IP address
    host    replication     replica          10.0.15.11/32            md5

    Save and exit. All configuration is complete. Now, restart PostgreSQL 9.6 using the following command.

    systemctl restart postgresql-9.6

    Configure and restart PostgreSQL on the master server

    Next, we need to create a new user with replication privileges. We will create a new user named 'replica'.

    Login as postgres user, and create a new 'replica' user with password 'aqwe123@'.

    su - postgres
    createuser --replication -P replica
    Enter New Password:

    Create replica user

    So with this, PostgreSQL 9.6 Master configuration has been completed, and the user for replication is created.

    Step 5 - Configure Slave server

    In this step, we will configure the Slave server. We want to replace postgres data directory on the slave server with the postgres data from the master server, and then configure slave server to run under the IP address '10.0.15.11', and finally enable hot_standby on it for allowing only read without write.

    Before we start to configure the slave server, stop the postgres service using the systemctl command below.

    systemctl stop postgresql-9.6

    Then go to the postgres directory, and backup data directory.

    cd /var/lib/pgsql/9.6/
    mv data data-backup

    Create new data directory and change the ownership permissions of the directory to the postgres user.

    mkdir -p data/
    chmod 700 data/
    chown -R postgres:postgres data/

    Next, login as the postgres user and copy all data directory from the 'Master' server to the 'Slave' server as replica user.

    su - postgres
    pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/pgsql/9.6/data -P --xlog
    Password:

    Type your password and wait for data transfer from the master to the slave server.

    Configure postgreSQL on the slave server

    After the transfer is complete, go to the postgres data directory and edit postgresql.conf file on the slave server.

    cd /var/lib/pgsql/9.6/data/
    vim postgresql.conf

    Change the value of 'listen_addresses' to the slave server ip address '10.0.15.11'.

    listen_addresses = '10.0.15.11'

    Enable 'hot_standby' on the slave server by uncommenting the following line and change the value to 'on'.

    hot_standby = on

    That's it. Save the changes and exit the editor.

    Then create new 'recovery.conf' file with vim.

    vim recovery.conf

    Paste the following configuration in it.

    standby_mode = 'on'
    primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave01'
    trigger_file = '/tmp/postgresql.trigger.5432'

    Save the changes and exit the file.

    Note: On the primary_conninfo, input your own server details.

    Change the ownership permissions of the recovery.conf file to that of the 'postgres' user.

    chmod 600 recovery.conf
    chown postgres:postgres recovery.conf

    And start PostgreSQL 9.6 on the slave server.

    systemctl start postgresql-9.6

    The slave server configuration has been completed.

    Now when you check the Slave server, you will see that PostgreSQL 9.6 is running on the server with IP address '10.0.15.11'.

    netstat -plntu

    Check if postgres is running

    Step 6 - Testing

    Installation and configuration for PostgreSQL 9.6 Master-Slave replication are complete. To test the setup, check the state stream of the replication and test for data replication from the Master to the Slave.

    Log in to the Master server and switch to the postgres user.

    su - postgres

    Next, check the streaming state replication of PostgreSQL with the following commands.

    psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
    psql -x -c "select * from pg_stat_replication;"

    You should see the state value is 'streaming', and the sync_state is 'sync'.

    Test PostgreSQL Replication

    Next, test by inserting data from the master and then check all data on the slave server.

    Log in as the postgres user and access the PostgreSQL shell on the 'MASTER' server.

    su - postgres
    psql

    Create new table 'replica_test' and insert some data into it with the following insert queries.

    CREATE TABLE replica_test (test varchar(100));
    INSERT INTO replica_test VALUES ('howtoforge.com');
    INSERT INTO replica_test VALUES ('This is from Master');
    INSERT INTO replica_test VALUES ('pg replication by hakase-labs');

    Create replication table

    And now login to the 'SLAVE' server similar to the way you did on the master.

    su - postgres
    psql

    Check all data from the table 'replica_test' using the query below.

    select * from replica_test;

    Check test data on the slave server

    So you'll see that all the data is replicated from the 'MASTER' server to the 'SLAVE' server.

    Additional test

    Test to perform the 'WRITE' action from the 'SLAVE' server.

    INSERT INTO replica_test VALUES ('this is SLAVE');

    And the result should be 'Cannot execute INSERT'.

    So the installation and configuration of the PostgreSQL 9.6 Master-Slave Replication on CentOS 7 is successful.

    References

    Share this page:

    15 Comment(s)