­

Replication setup with PostgreSQL 9.4.1 on RHEL6

9:38 AM

Master Slave Replication setup with PostgreSQL 9.4.1 on RHEL6

         Master Server Configuration

  1. Enable variables in configuration file : vi /var/lib/pgsql/9.4/data/postgresql.conf
    wal_level = hot_standby
    max_wal_senders = 1
    wal_keep_segments = 50
  2. Enable variables in host based configuration file: <replace this ip with your slave host name or IP>
    host replication postgres 192.168.1.2/24 trust
  3. Restart database engine 
    [root@hostname_master ~]# service postgresql-9.4 restart
  4. Take backup and move it to slave server
    [root@hostname_master ~]#  su - postgres
    -bash-4.1$ psql -c "SELECT pg_start_backup('repl_backup');"
    -bash-4.1$tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.4/data
    -bash-4.1$ psql -c "SELECT pg_stop_backup();"
    -bash-4.1$ scp /tmp/db_file_backup.tar root@slave_server_IP:/tmp/      

    Slave server configuration

  5.  Stop service on slave server
    [root@hostname_slave ~]# service postgresql-9.4 stop
  6. Rename slave datadir and untar master backup 
    [root@hostname_slave ~]# mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data.old
    [root@hostname_slave ~]# tar xvfP /tmp/db_file_backup.tar
    [root@hostname_slave ~]# rm -f /var/lib/pgsql/9.4/data/postmaster.pid

  7. Enable hot_standby option in configuration file
    [root@hostname_slave ~]# vi /var/lib/pgsql/9.4/data/postgresql.conf
    Enable "hot_standby = on" in conf file
  8. Copy and Update recovery file
    [root@hostname_slave ~]# cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf
    [root@hostname_slave ~]# chmod 600 /var/lib/pgsql/9.4/data/recovery.conf && chown -R postgres.postgres /var/lib/pgsql/9.4/data
    [root@hostname_slave ~]# vi /var/lib/pgsql/9.4/data/recovery.conf
    standby_mode = on
    primary_conninfo = 'host=hostname_master port=5432'

  9. Start PostgreSQL service
    [root@hostname_slave ~]# service postgresql-9.4 start
    Replication setup complete
  10.  Test your setup by creating a database/table on master and query the same on slave server
    [root@remote_host ~]# psql -U admin -d postgres -h 192.168.1.1
    Password for user admin:
    psql (9.4.1)
    Type "help" for help.
    postgres=# create database test_replication;
    CREATE DATABASE
    postgres=#

    Slave server
    [root@hostname_slave ~]# su - postgres
    -bash-4.1$ psql
    psql (9.4.1)
    Type "help" for help.
    postgres=# \l
                                         List of databases
           Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    ------------------+----------+----------+-------------+-------------+-----------------------
     postgres         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     template0        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
     template1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
     test_replication | admin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    (4 rows)

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results