Before we setup replication postgresql, we must setting ssh key authentication. You can show setup with follow link below
1. First step, install postgresql repository in your system with this commands
[root@master ~]# yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm
2. Install postgresql-9.5 and dependency
[root@master ~]# yum install postgresql95-server postgresql95 postgresql95-contrib rsync repmgr95 -y
3. After install postgresql, Initializing pgdata
[root@master ~]#/usr/pgsql-9.5/bin/postgresql95-setup initdb
4. After that create symlink repmgr with this command
[root@master ~]# ln -s /usr/pgsql-9.5/bin/repmgr /usr/sbin/repmgr
[root@master ~]# ln -s /usr/pgsql-9.5/bin/repmgrd /usr/sbin/repmgrd
5. Edit file repmgr, uncommand parammeter and change the value as below
[root@master ~]# nano /etc/repmgr/9.5/repmgr.conf
node_id=1
node_name='masterdb'
conninfo='host=192.168.11.9 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.5/data'
node_name='masterdb'
conninfo='host=192.168.11.9 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.5/data'
6. Open file postgresql.conf, uncommand parammeter and change the value as below
[root@master ~]# nano /var/lib/pgsql/9.5/data/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 1000
max_wal_senders = 10
wal_level = 'hot_standby'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
wal_keep_segments = 100
port = 5432
max_connections = 1000
max_wal_senders = 10
wal_level = 'hot_standby'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
wal_keep_segments = 100
7. open file pg_hba.conf, uncommand parammeter and change value as below
[root@master ~]# nano /var/lib/pgsql/9.5/data/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host repmgr repmgr 192.168.11.9/32 trust
host replication repmgr 192.168.11.9/32 trust
host repmgr repmgr 192.168.11.10/32 trust
host replication repmgr 192.168.11.10/32 trust
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host repmgr repmgr 192.168.11.9/32 trust
host replication repmgr 192.168.11.9/32 trust
host repmgr repmgr 192.168.11.10/32 trust
host replication repmgr 192.168.11.10/32 trust
8. Start postgresql service with type this commands
[root@master ~]# systemctl start postgresql-9.5
9. Enable postgresql service with type this commands
[root@master ~]# systemctl enable postgresql-9.5
10. Login postgresql user postgres, and then create user and db repmgr with follow this commands
[root@master ~]# su - postgres
Last login: Mon Feb 25 12:18:32 WITA 2019 on pts/0
-bash-4.2$ createuser -s repmgr
-bash-4.2$ createdb repmgr -O repmgr
Last login: Mon Feb 25 12:18:32 WITA 2019 on pts/0
-bash-4.2$ createuser -s repmgr
-bash-4.2$ createdb repmgr -O repmgr
11. In postgres user mode, make shcema with type this command as below
-bash-4.2$ psql
psql (9.5.16)
Type "help" for help.
postgres=# ALTER USER repmgr SET search_path TO repmgr, "$user", public;
ALTER ROLE
Postgres=#\q
psql (9.5.16)
Type "help" for help.
postgres=# ALTER USER repmgr SET search_path TO repmgr, "$user", public;
ALTER ROLE
Postgres=#\q
12. And then register node as mastes
-bash-4.2$ repmgr -f /etc/repmgr/9.5/repmgr.conf master register
INFO: connecting to primary database…
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (id: 1) registered
INFO: connecting to primary database…
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (id: 1) registered
13. don’t forget to allow firewall for postgresql, and then reload firewall
[root@master ~]# sudo firewall-cmd --permanent --zone=public --add-service=postgresql
Success
Success
[root@master ~]# sudo systemctl reload firewalld
SLAVE
14. Open file repmgr.conf, uncommand parammeter and change value as below
[root@slave~]# nano /etc/repmgr/9.5/repmgr.conf
node_id=2
node_name='slavedb'
conninfo='host=192.168.11.10 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.5/data'
node_name='slavedb'
conninfo='host=192.168.11.10 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.5/data'
15. After that create symlink repmgr with this command
[root@slave ~]# ln -s /usr/pgsql-9.5/bin/repmgr /usr/sbin/repmgr
[root@slave ~]# ln -s /usr/pgsql-9.5/bin/repmgrd /usr/sbin/repmgrd
16. Login with user postgres and running
[root@slave ~]# su - postgres
[root@slave ~]# repmgr -h 192.168.10.10 -U repmgr -d repmgr -D /var/lib/pgsql/9.5/data/ -f /etc/repmgr/9.5/repmgr.conf standby clone
17. Make sure this step succes and then start postgresql service and enable postrgsql service for autstart service after restart
[root@slave ~]# systemctl start postgresql-9.5
[root@slave ~]# systemctl enable postgresql-9.5
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.5.service to /usr/lib/systemd/system/postgresql-9.5.service.
18. After that register this node to slave mode with command
repmgr -f /etc/repmgr/9.5/repmgr.conf standby register
19. To make sure node registered with repmgr, we can use this command
-bash-4.2$ psql repmgr
psql (9.5.16)
Type "help" for help.
repmgr=# select * from repmgr.nodes;
psql (9.5.16)
Type "help" for help.
repmgr=# select * from repmgr.nodes;
20. To testing replication runnig well, login with user postgres in master node, and then create database. After that check in slave mode the database same with the master. And if we make database in slave mode, we cannot make the database because in slave just read only transaction.
