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


    • 1 CentOS 7 server
      • Master - Read and Write Permission - IP:
    • 1 CentOS 7 server
      • Slave - Only Read Permission- IP:
    • 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

    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

    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 '' 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 ''.

    listen_addresses = ''

    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            md5

    # PostgreSQL Master IP address
    host    replication     replica            md5

    # PostgreSQL SLave IP address
    host    replication     replica            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 '[email protected]'.

    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 '', 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 -U replica -D /var/lib/pgsql/9.6/data -P --xlog

    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 ''.

    listen_addresses = ''

    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= port=5432 user=replica [email protected] 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 ''.

    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

    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 ('');
    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

    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.


    Share this page:

    Suggested articles

    15 Comment(s)

    Add comment


    By: Paul Naro

    Excellent article!

    By: renek

    how about performing a failover (make slave writable) and failback (reverse replication)?

    By: Luis

    Such a nice article, was able to follow along and successfully set up replication. 

    But, how can I provide an unique ip for my clients, so if a server goes down it would be transparent for my users? 


    By: Akarsh

    I have followed all the steps ,I am using Postgresql 10

    But I am not getting  state value is 'streaming', and the sync_state is 'sync' and also application_name.

    By: Gaurav

    Hi Akarsh,

    I have configured on postgres10 on Linux with the help of this doc. It is working fine for me. Just Double check these parameters on master and slave.

    On Master, in postgresql.conf,

    synchronous_standby_names = 'pgslave01' And in Slave, in recovery.confstandby_mode = 'on'primary_conninfo = 'host= port=5432 user=replica [email protected] application_name=pgslave01'

    Application_name=synchronous_standby_names, should be same in master and slave.


    By: Eduardo


    @Gaurav thank you very much for your information, right now I am needing to add the steps that you configured to do the failover and failback, there is a possibility that you help with this information because I do not know how to do it, since the replica is already working, now I need that failover when the master has errors.


    Thanks for your help

    By: new user

    was the page translated accidentally?

    By: Gaurav

    Hi There,

    Excellent Artical. Short and crisp.

    Please add more steps for below test:

    (1) Create cascade standby from slave.

    Master --> Slave1 (Hot Standby) --> Slave2 (cascade warm standby)

    (2) Failover and Failback statergy

    (1) Failover:

    Master Fails. Slave1 will be master and slave2 will be warm standby


    When Master comes back, slave1 will become hot standby again and slave2 will be warm standby.

    (2) Failover:

    Master and Slave1 - both fails. Slave2 will be master.


    When Master and Slave1 comes back, The role back should happen as before.


    Is there any way to use pg_rewind feature in this approach?


    I am testing all above. If I find something, I will post here.

    Once again, Thanks for the tutorial.





    By: Eduardo Coavas



    Gaurav I am new to this Postgresql medium, and I would like if you could share with me the procedure you used to apply


    (1) Create standby cascade from slave.


    Master -> Slave1 (Hot Standby) -> Slave2 (standby cascade)


    (2) Failover and Failback statergy


    (1) Failover:


    The teacher fails. Slave1 will be master and slave2 will be on active standby




    When the Master returns, slave1 will go back to hot standby and slave2 will be back to hot standby.


    (2) Failover:


    Master and Slave1: both fail. Slave2 will be the master.




    When Master and Slave1 returns, the return role must happen as before.


    I appreciate what you can help me

    By: Krysztophe

    The archive_command as written here copies the WALs directly into the pg_xlog of the secondary server. This works but is a bad practice — you shall never mess with a server WALs!. 

    A better target would be a repository somewhere else. Then the recovery.conf should contain a restore_command  like 'cp /mnt/server/archivedir/%f %p', and a archive_cleanup_command to clean the used WALs.

    By: shivali

    Excellent article !!

    Each and every step mentioned here.

    By: new user

     create a file named "recovery.conf". and where to create? path?

    By: Wasan191

    What OSI can tell path

    By: Rajat sharma

    I have followed all the steps , I have done it.

    By: Wasan191

    me too