replication postgresql-10 with repmgr
1. install postgresql-10 to node1 and node2
rpm -Uvh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install postgresql10-server postgresql10-contrib postgresql10-devel -y
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10
2. install repmgr to node1 and node2
yum install https://dl.2ndquadrant.com/default/release/browse/rpm/packages/centos/7/x86_64/10/repmgr10-4.1.1-1.el7.x86_64.rpm
yum install repmgr10
node2
ubah konfigurasi pada repmgr.conf di node2
vim /etc/repmgr/10/repmgr.conf
node_id=2
node_name='node2'
conninfo='host=192.168.4.161 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/10/data/'
pg_bindir='/usr/pgsql-10/bin/'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr/10/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr/10/repmgr.conf --log-to-file'
3. make some symlink to command repmgr for node2
ln -s /usr/pgsql-10/bin/repmgr /usr/sbin/repmgr
ln -s /usr/pgsql-10/bin/repmgrd /usr/sbin/repmgrd
4. login as postgres user
su - postgres
5. open file postgresql.conf to node2 and node1
vim /var/lib/pgsql/10/data/postgresql.conf
6. uncomment paramater bellow and change the values
listen_addresses = '*'
max_connections = 1000
max_wal_senders = 10
wal_level = hot_standby
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
wal_keep_segments = 5000
shared_preload_libraries = 'repmgr'
if done and then save file
7. open file pg_hba.conf
vim /var/lib/pgsql/10/data/pg_hba.conf
8. uncomment paramater bellow and change the values
local repmgr repmgr trust
host all all 127.0.0.1/32 trust
host repmgr repmgr 192.168.4.0/24 trust
host replication repmgr 192.168.4.0/24 trust
if done and then save file
9. restart service postgresql
systemctl restart postgresql-10
10. make user & db repmgr to mode user postgres
su - postgres
createuser -s repmgr
createdb repmgr -O repmgr
11. in mode user postgres, make schema with initial bellow this,
for example name detik so I make scheme with repmgr_detik
psql
ALTER USER repmgr SET search_path TO repmgr_detik, "$user", public;
12. and then register for node2 to be master with the bellow comment
repmgr -f /etc/repmgr/10/repmgr.conf master register
13. then to make the name of node 2 become a master with this way
psql repmgr
repmgr=# select * from repmgr.nodes ;
node_id | upstream_node_id | active | node_name | type | location | priority | conninfo | repluser | slot_name | config_file
---------+------------------+--------+-----------+---------+----------+----------+----------------------------------------------+----------+-----------+----------------------------
2 | | t | node2 | primary | default | 100 | host=192.168.4.161 user=repmgr dbname=repmgr | repmgr | | /etc/repmgr/10/repmgr.conf
node1
14. change your configuration repmgr.conf in node2
vim /etc/repmgr/10/repmgr.conf
node_id=1
node_name='node1'
conninfo='host=192.168.4.160 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/10/data/'
pg_bindir='/usr/pgsql-10/bin/'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr/10/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr/10/repmgr.conf --log-to-file'
15. make several symlink to command repmgr in node1
ln -s /usr/pgsql-10/bin/repmgr /usr/sbin/repmgr
ln -s /usr/pgsql-10/bin/repmgrd /usr/sbin/repmgrd
16. login as user postgres
su - postgres
17. remove folder data
rm -rf /var/lib/pgsql/10/data
18. then clone node2/master to node1/standby
login as user postgres
su - postgres
repmgr -h 192.168.4.161 -U repmgr -d repmgr -D /var/lib/pgsql/10/data/ -f /etc/repmgr/10/repmgr.conf standby clone
19. restart service postgresql
systemctl restart postgresql-10
20. next, register node1 to be standby in mode user postgres
su - postgres
repmgr -f /etc/repmgr/10/repmgr.conf -D /var/lib/pgsql/10/data/ -h 192.168.4.161 -U repmgr -d repmgr standby register
21. make sure node1 already standby
psql repmgr
repmgr=# select * from repmgr.nodes ;
node_id | upstream_node_id | active | node_name | type | location | priority | conninfo | repluser | slot_name | config_file
---------+------------------+--------+-----------+---------+----------+----------+----------------------------------------------+----------+-----------+----------------------------
2 | | t | node2 | primary | default | 100 | host=192.168.4.161 user=repmgr dbname=repmgr | repmgr | | /etc/repmgr/10/repmgr.conf
1 | 2 | t | node1 | standby | default | 100 | host=192.168.4.160 user=repmgr dbname=repmgr | repmgr | | /etc/repmgr/10/repmgr.conf
22. if you add node standby exsisting
change for configuration repmgr.conf in node2
vim /etc/repmgr/10/repmgr.conf
node_id=1
node_name='node1'
conninfo='host=192.168.4.160 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/10/data/'
pg_bindir='/usr/pgsql-10/bin/'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr/10/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr/10/repmgr.conf --log-to-file'
23. make several symlink for command repmgr in node1
ln -s /usr/pgsql-10/bin/repmgr /usr/sbin/repmgr
ln -s /usr/pgsql-10/bin/repmgrd /usr/sbin/repmgrd
24. login as user postgres
su - postgres
25. remove folder data
rm -rf /var/lib/pgsql/10/data
26. then make clone node2/master to node1/standby
login as postgres user
su - postgres
repmgr -h 192.168.4.161 -U repmgr -d repmgr -D /var/lib/pgsql/10/data/ -f /etc/repmgr/10/repmgr.conf standby clone
27. restart service postgresql
systemctl restart postgresql-10
28. then register node1 to be standby for mode user postgres
su - postgres
repmgr -f /etc/repmgr/10/repmgr.conf -D /var/lib/pgsql/10/data/ -h 192.168.4.161 -U repmgr -d repmgr standby follow
