Master-Slave replication on PostgreSQL on Rhel/Centos 6.5

Master server:-192.168.100.122
Hostname:-server2.soumya.com

Slave server:-192.168.100.175
Hostname:-server1.soumya.com

Postgresql Version:9.4-1
Linux version:Rhel 6.3


Install postgresql in both server:-
Step 1:-Download the repository
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

# vi /etc/yum.repos.d/centos.repo
add the following lines
[centos-6-base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
enabled=1


step 2:- Install Postgresql required packages:-
# yum install postgresql94-server postgresql94-contrib
Incase if the above command gives error regarding the public key, we can use the following command
cd /etc/pki/rpm-gpg
rpm --import RPM-GPG-KEY-CentOS-6

#yum install postgresql94-server

# service postgresql-9.4 initdb

OR

# service postgresql initdb

# chkconfig postgresql-9.4 on

Step 3:-Start postgresql:-

[root@infosystem ~]# service postgresql-9.4 start
Starting postgresql service: [  OK  ]

Check status of postgresql:-
[root@infosystem ~]# service postgresql status
postmaster (pid  4260) is running...

Step 4:- Perform this in both server:-

PostgreSQL creates a user called "postgres" in order to handle its initial databases.
We will configure ssh access between our servers to make transferring files easier.

#passwd postgres
login to postgres user

#su - postgres

Generate an ssh key for the postgres user:(Do this is both server)
In Master server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Slave server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Master server:-
$ cd /var/lib/pgsql/.ssh
$ cp authorized_keys server2_authorized_keys
$ scp server2_authorized_keys postgres@server1:/var/lib/pgsql/
$ rm -rf authorized_keys

In slave server:-
$ cd /var/lib/pgsql/.ssh
$ cat server2_authorized_keys >>authorized_keys
$ scp authorized_keys postgres@server2:/var/lib/pgsql/

Now test the connectivity from both server.
From master server:-
ssh server1 date

From slave server:-
ssh server2 date

Step 5:-Configure the Master Server.

# su - postgres

$ psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'redhat';"

Next, we will move to the postgres configuration directory:
$ cd /var/lib/pgsql/9.4/data

At any place not at the bottom of the file, add a line to let the new user get access to this server:
$ vi pg_hba.conf

host    replication     rep     192.168.100.175/32   md5

:wq

Next, open the main postgres configuration file:

$ vi postgresql.conf

Find these parameters. Uncomment them if they are commented, and modify the values according to what we have listed below:

listen_addresses = 'localhost,192.168.100.122'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
Save and close the file.


Restart the master server to implement your changes from root user:-
# service  postgresql-9.4 restart

Step 6.Configure the Slave Server.

Begin on the slave server by shutting down the postgres database software:

# service postgresql-9.4 stop


On the slave server, We then will transfer the database data from master server:-

$ cd /var/lib/pgsql/9.4/data
$ rm -rf *
$ pg_basebackup -D /var/lib/pgsql/9.4/data -h 192.168.100.122 -U rep

Now we can see all the files from the master server has been copied into the slave server.

We will be making some similar configuration changes to postgres files,
so change to the configuration directory:

# su - postgres
$ cd /var/lib/pgsql/9.4/data


Adjust the access file to allow the other server to connect to this.
This is in case we need to turn the slave into the master later on down the road.

$ vi pg_hba.conf

Again, add this line somewhere not at the end of the file:

host    replication     rep     192.168.100.122/32  md5

Save and close the file.

Next, open the postgres configuration file:

$ vi postgresql.conf

You can use the same configuration options you set for the master server, modifying only the IP address
to reflect the slave server's address:

listen_addresses = 'localhost,192.168.100.175'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on


--Save and exit.

Step 7.Create recovery.conf file

Here, we need to create a recovery file called recovery.conf:
$ cd /var/lib/pgsql/9.4/data
$ vi recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.100.122 port=5432 user=rep password=redhat'
trigger_file = '/tmp/postgresql.trigger.5432'

--save and exit.

Test the replication:-
On Master server:-
$ su - postgres
$psql
#CREATE TABLE rep_test (test varchar(40));
Now, we can insert some values into the table with the following commands:

INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');
To exit from psql shell
\q

Now on slave server:-
$ su - postgres
$psql
#SELECT * FROM rep_test;

      test    
-----------------
 data one
 some more words
 lalala
 hello there
 blahblah
(5 rows)

Now lets see if try insert data from slave server :-

#INSERT INTO rep_test VALUES ('oops');
ERROR:  cannot execute INSERT in a read-only transaction

As we can see, we are unable to insert data into the slave. This is because the data is only being
transferred in one direction. In order to keep the databases consistent, postgres must make the slave
read-only.




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.