Wednesday, October 17, 2018

replication postgresql-10 with repmgr

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

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...