selamat sore sobat sekolahlinux, jika sebelumnya saya membuat tutorial replikasi master-slave dengan feature yg ada pada postgresql kini saya coba memberikan alternatif replikasi master-slave dengan repmgr berikut ini caranya.
pertama saya akan memberitahukan topologinya:
- node1 (master): 192.168.10.10
- node2 (slave): 192.168.10.20
- node3 (slave): 192.168.10.30
jalankan perintah install postgresql 9.6 pada node1 & node2 & node3
#install repo postgresql 9.6 pada centos 7 rpm -Uvh https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm #install postgresql 9.6 yum install postgresql96-server postgresql96 repmgr96 -y
implementasikan ssh authkey pada node1,node2,node3 untuk user postgres
untuk tutorialnya silahkan dilihat dilink dibawah ini
NODE1 MASTER
jalankan perintah ini untuk generate database
/usr/pgsql-9.6/bin/postgresql96-setup initdb
jika sudah save, lalu selanjutnya buka file repmgr.conf
vim /etc/repmgr/9.6/repmgr.conf
lalu uncomment paramater dibawah dan rubah value nya menjadi seperti dibawah
cluster=sekolahlinux node=1 node_name=node1 conninfo='host=192.168.10.10 user=repmgr dbname=repmgr'
selanjutnya buat beberapa symlink untuk command repmgr yang kita butuhkan
ln -s /usr/pgsql-9.6/bin/repmgr /usr/sbin/repmgr ln -s /usr/pgsql-9.6/bin/repmgrd /usr/sbin/repmgrd
lalu buat masuk menjadi user postgres
su - postgres
buka file postgresql.conf
vim /var/lib/pgsql/9.6/data/postgresql.conf
uncomment paramater dibawah dan rubah value nya seperti dibawah jika berbeda
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
jika sudah save, lalu buka file pg_hba.conf
vim /var/lib/pgsql/9.6/data/pg_hba.conf
lalu uncomment paramater dibawah dan rubah value nya menjadi seperti dibawah
local replication repmgr trust host replication repmgr 127.0.0.1/32 trust host replication repmgr 192.168.10.0/24 trust local repmgr repmgr trust host repmgr repmgr 127.0.0.1/32 trust host repmgr repmgr 192.168.10.0/24 trust
restart service postgresql
service postgresql-9.6 restart
lalu selanjutnya buat user & db repmgr pada mode user postgres
createuser -s repmgr createdb repmgr -O repmgr
pada mode user postgres, buat schema dengan penamaan seperti dibawah, karena nama clusternya sekolahlinux maka saya buat scheme dengan nama repmgr_sekolahlinux seperti dibawah
psql ALTER USER repmgr SET search_path TO repmgr_sekolahlinux, "$user", public;
selanjutnya daftarkan node1 menjadi master dengan perintah dibawah
repmgr -f /etc/repmgr/9.6/repmgr.conf master register
selanjutnya untuk melihat apakah kita sudah berhasil mendaftarkan node1 menjadi master bisa dengan cara dibawah ini
psql repmgr SELECT * FROM repmgr_sekolahlinux.repl_nodes;
maka hasilnya akan seperti dibawah
repmgr=# SELECT * FROM repmgr_sekolahlinux.repl_nodes; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+--------+------------------+--------------+-------+----------------------------------------------+-----------+----------+-------- 1 | master | | sekolahlinux | node1 | host=192.168.10.10 dbname=repmgr user=repmgr | | 100 | t (1 row)
NODE2 SLAVE
pertama buka file repmgr.conf
vim /etc/repmgr/9.6/repmgr.conf
lalu uncomment paramater dibawah dan rubah value nya menjadi seperti dibawah
cluster=sekolahlinux node=2 node_name=node2 conninfo='host=192.168.10.20 user=repmgr dbname=repmgr'
selanjutnya buat beberapa symlink untuk command repmgr yang kita butuhkan
ln -s /usr/pgsql-9.6/bin/repmgr /usr/sbin/repmgr ln -s /usr/pgsql-9.6/bin/repmgrd /usr/sbin/repmgrd
lalu buat masuk menjadi user postgres
su - postgres
jika sudah clone node1/master ke server node2/standby
repmgr -h 192.168.10.10 -U repmgr -d repmgr -D /var/lib/pgsql/9.6/data/ -f /etc/repmgr/9.6/repmgr.conf standby clone
nanti hasil dari command diatas akan seperti dibawah
NOTICE: destination directory '/var/lib/pgsql/9.6/data/' provided NOTICE: starting backup (using pg_basebackup)... HINT: this may take some time; consider using the -c/--fast-checkpoint option NOTICE: standby clone (using pg_basebackup) complete NOTICE: you can now start your PostgreSQL server HINT: for example : pg_ctl -D /var/lib/pgsql/9.6/data start HINT: After starting the server, you need to register this standby with "repmgr standby register"
restart service postgresql
service postgresql-9.6 restart
selanjutnya mendaftarkan node2 menjadi standby pada mode user postgres
repmgr -f /etc/repmgr/9.6/repmgr.conf standby register
jika sudah selanjutnya coba cek apakah node2 sudah standby
psql repmgr SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id;
dan hasilnya akan seperti dibawah ini
repmgr=# SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+--------------+-------+----------------------------------------------+-----------+----------+-------- 1 | master | | sekolahlinux | node1 | host=192.168.10.10 dbname=repmgr user=repmgr | | 100 | t 2 | standby | 1 | sekolahlinux | node2 | host=192.168.10.20 dbname=repmgr user=repmgr | | 100 | t (2 rows)
NODE3 SLAVE
pertama buka file repmgr.conf
vim /etc/repmgr/9.6/repmgr.conf
lalu uncomment paramater dibawah dan rubah value nya menjadi seperti dibawah
cluster=sekolahlinux node=3 node_name=node3 conninfo='host=192.168.10.30 user=repmgr dbname=repmgr'
selanjutnya buat beberapa symlink untuk command repmgr yang kita butuhkan
ln -s /usr/pgsql-9.6/bin/repmgr /usr/sbin/repmgr ln -s /usr/pgsql-9.6/bin/repmgrd /usr/sbin/repmgrd
lalu buat masuk menjadi user postgres
su - postgres
jika sudah clone node1/master ke server node3/standby
repmgr -h 192.168.10.10 -U repmgr -d repmgr -D /var/lib/pgsql/9.6/data/ -f /etc/repmgr/9.6/repmgr.conf standby clone
nanti hasil dari command diatas akan seperti dibawah
NOTICE: destination directory '/var/lib/pgsql/9.6/data/' provided NOTICE: starting backup (using pg_basebackup)... HINT: this may take some time; consider using the -c/--fast-checkpoint option NOTICE: standby clone (using pg_basebackup) complete NOTICE: you can now start your PostgreSQL server HINT: for example : pg_ctl -D /var/lib/pgsql/9.6/data start HINT: After starting the server, you need to register this standby with "repmgr standby register"
restart service postgresql
service postgresql-9.6 restart
selanjutnya mendaftarkan node3 menjadi standby pada mode user postgres
repmgr -f /etc/repmgr/9.6/repmgr.conf standby register
jika sudah selanjutnya coba cek apakah node3 sudah standby
psql repmgr SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id;
dan hasilnya akan seperti dibawah ini
repmgr=# SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+--------------+-------+----------------------------------------------+-----------+----------+-------- 1 | master | | sekolahlinux | node1 | host=192.168.10.10 dbname=repmgr user=repmgr | | 100 | t 2 | standby | 1 | sekolahlinux | node2 | host=192.168.10.20 dbname=repmgr user=repmgr | | 100 | t 3 | standby | 1 | sekolahlinux | node3 | host=192.168.10.30 dbname=repmgr user=repmgr | | 100 | t (3 rows)
MANUAL PROMOTE & MANUAL SWITCH UPSTREAM & SWITCH MASTER
jika node master mati, untuk manual promote standby node menjadi master bisa dengan perintah dibawah
repmgr -f /etc/repmgr/9.6/repmgr.conf standby promote
sedangkan untuk manual switch upstream kalian bisa mengikuti cara dibawah ini (ganti ip_node_master dengan ip address node master yang baru)
repmgr -f /etc/repmgr/9.6/repmgr.conf -D /var/lib/pgsql/9.6/data/ -h ip_node_master -U repmgr -d repmgr standby follow
dan untuk switch master / memaksa node standby menjadi master kalian mengikuti cara berikut, pertama pastikan paramater dibawah sudah aktif pada postgresql.conf
wal_log_hints = on
jika sudah jangan lupa restart postgresql service, lalu masuk dalam mode user postgres dan jalankan command dibawah pada node standby yang akan dijadikan master
repmgr -f /etc/repmgr/9.6/repmgr.conf -C /etc/repmgr/9.6/repmgr.conf standby switchover
AUTOMATION PROMOTE MASTER
jalankan perintah dibawah pada node1 & node2 & node3
buka file postgresql.conf
vim /var/lib/pgsql/9.6/data/postgresql.conf
lalu uncomment paramater dibawah ini dan tambahkan repmgr_funcs, sehingga menjadi seperti dibawah
shared_preload_libraries =repmgr_funcs
jika sudah save, kemudian buka file repmgr.conf
vim /etc/repmgr/9.6/repmgr.conf
rubah dan tambahkan baris dibawah ini jika belum ada sehingga menjadi seperti dibawah
failover=automatic promote_command='repmgr standby promote -f /etc/repmgr/9.6/repmgr.conf --log-to-file' follow_command='repmgr standby follow -f /etc/repmgr/9.6/repmgr.conf --log-to-file'
jika sudah jalankan perintah dibawah
chown -R postgres:postgres /var/lib/pgsql
restart service postgresql
service postgresql-9.6 restart
jika sudah masuk menjadi user postgres dan buat folder repmgr
su - postgres mkdir -p ~/repmgr
lalu jika sudah jalankan perintah dibawah dalam dalam mode user postgres (perintah dibawah ini wajib dijalankan jika ingin automation failover bekerja)
repmgrd -d -f /etc/repmgr/9.6/repmgr.conf --verbose >> $HOME/repmgr/repmgr.log 2>&1
selanjutnya kita akan test dengan mencoba mematikan service postgresql pada node1/master
service postgresql-9.6 stop
lalu coba lihat log /var/log/repmgr/repmgr-9.6.log pada node2 & node3 dengan perintah dibawah
tail -f /var/log/repmgr/repmgr-9.6.log
maka pada node2 hasilnya (node2 akan mempromote dirinya secara otomatis sebagai master yang baru)
-bash-4.2$ tail -f /var/log/repmgr/repmgr-9.6.log
[2017-05-29 08:39:26] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [ERROR] unable to connect to upstream node: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [WARNING] connection to master has been lost, trying to recover... 60 seconds before failover decision
[2017-05-29 08:39:36] [WARNING] connection to master has been lost, trying to recover... 50 seconds before failover decision
[2017-05-29 08:39:46] [WARNING] connection to master has been lost, trying to recover... 40 seconds before failover decision
[2017-05-29 08:39:56] [WARNING] connection to master has been lost, trying to recover... 30 seconds before failover decision
[2017-05-29 08:40:06] [WARNING] connection to master has been lost, trying to recover... 20 seconds before failover decision
[2017-05-29 08:40:16] [WARNING] connection to master has been lost, trying to recover... 10 seconds before failover decision
[2017-05-29 08:40:26] [ERROR] unable to reconnect to master (timeout 60 seconds)...
[2017-05-29 08:40:31] [NOTICE] this node is the best candidate to be the new master, promoting...
[2017-05-29 08:40:31] [NOTICE] Redirecting logging output to '/var/log/repmgr/repmgr-9.6.log'
[2017-05-29 08:40:31] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:40:31] [NOTICE] promoting standby
[2017-05-29 08:40:31] [NOTICE] promoting server using '/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data promote'
[2017-05-29 08:40:33] [NOTICE] STANDBY PROMOTE successful
lalu pada node3 hasilnya akan seperti dibawah (node3 akan follow node2 sebagai upstream yang baru)
-bash-4.2$ tail -f /var/log/repmgr/repmgr-9.6.log
[2017-05-29 08:39:26] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [ERROR] unable to connect to upstream node: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:39:26] [WARNING] connection to master has been lost, trying to recover... 60 seconds before failover decision
[2017-05-29 08:39:36] [WARNING] connection to master has been lost, trying to recover... 50 seconds before failover decision
[2017-05-29 08:39:46] [WARNING] connection to master has been lost, trying to recover... 40 seconds before failover decision
[2017-05-29 08:39:56] [WARNING] connection to master has been lost, trying to recover... 30 seconds before failover decision
[2017-05-29 08:40:06] [WARNING] connection to master has been lost, trying to recover... 20 seconds before failover decision
[2017-05-29 08:40:16] [WARNING] connection to master has been lost, trying to recover... 10 seconds before failover decision
[2017-05-29 08:40:26] [ERROR] unable to reconnect to master (timeout 60 seconds)...
[2017-05-29 08:40:37] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:40:37] [NOTICE] node 2 is the best candidate for new master, attempting to follow...
[2017-05-29 08:40:37] [NOTICE] Redirecting logging output to '/var/log/repmgr/repmgr-9.6.log'
[2017-05-29 08:40:37] [ERROR] connection to database failed: could not connect to server: Connection refused
Is the server running on host "192.168.10.10" and accepting
TCP/IP connections on port 5432?
[2017-05-29 08:40:37] [NOTICE] restarting server using '/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart'
[2017-05-29 08:40:44] [NOTICE] STANDBY FOLLOW successful
[2017-05-29 08:40:45] [NOTICE] node 3 now following new upstream node 2
lalu jalankan perintah dibawah ini pada node2 untuk melihat status masing-masing node
psql repmgr
SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id;
repmgr=# SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+--------------+-------+----------------------------------------------+-----------+----------+-------- 1 | master | | sekolahlinux | node1 | host=192.168.10.10 dbname=repmgr user=repmgr | | 100 | f 2 | master | | sekolahlinux | node2 | host=192.168.10.20 dbname=repmgr user=repmgr | | 100 | t 3 | standby | 2 | sekolahlinux | node3 | host=192.168.10.30 dbname=repmgr user=repmgr | | 100 | t (3 rows)
jika kita lihat diatas node1 yang down statusnya masih master lalu bagaimana caranya untuk menghidupkan kembali node1, dan lalu kemudian menjadikannya standby dan menjadikan node2 sebagai upstream, kalian bisa menjalankan perintah dibawah ini pada node1
su - postgres
delete folder data yang sudah pada postgres sebelumnya
rm -rf /var/lib/pgsql/9.6/data
lalu jika sudah jalankan command dibawah ini pada node1 untuk clone node2
repmgr -h 192.168.10.20 -U repmgr -d repmgr -D /var/lib/pgsql/9.6/data/ -f /etc/repmgr/9.6/repmgr.conf standby clone
selanjutnya dalam mode user root restart postgresql
service postgresql-9.6 restart
lalu masuk kembali menjadi mode user postgres
su - postgres
selanjutnya jalankan command dibawah ini pada node1 untuk follow node2 sebagai upstream
repmgr -f /etc/repmgr/9.6/repmgr.conf -D /var/lib/pgsql/9.6/data/ -h 192.168.10.20 -U repmgr -d repmgr standby follow
hasilnya akan seperti dibawah
NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data/ -m fast restart' waiting for server to shut down.... done server stopped waiting for server to start....< 2017-05-29 08:59:58.926 EDT > LOG: redirecting log output to logging collector process < 2017-05-29 08:59:58.926 EDT > HINT: Future log output will appear in directory "pg_log". done server started NOTICE: STANDBY FOLLOW successful
selanjutnya jalankan perintah dibawah ini pada node1 agar node 1 bisa mengikuti kembali automation promote jika node lainnya mati
repmgrd -d -f /etc/repmgr/9.6/repmgr.conf --verbose >> $HOME/repmgr/repmgr.log 2>&1
selanjutnya jalankan command dibawah ini pada node2/master yang baru untuk cek apakah node1 sudah menjadi standby
su - postgres -bash-4.2$ psql repmgr psql (9.6.3) Type "help" for help. repmgr=# SELECT * FROM repmgr_sekolahlinux.repl_nodes ORDER BY id; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+--------------+-------+----------------------------------------------+-----------+----------+-------- 1 | standby | 2 | sekolahlinux | node1 | host=192.168.10.10 dbname=repmgr user=repmgr | | 100 | t 2 | master | | sekolahlinux | node2 | host=192.168.10.20 dbname=repmgr user=repmgr | | 100 | t 3 | standby | 2 | sekolahlinux | node3 | host=192.168.10.30 dbname=repmgr user=repmgr | | 100 | t (3 rows)
pada hasil diatas terlihat node1 berhasil menjadi standby dan berhasil menjadikan node2 sebagai upstreamnya 😀
ya kiranya sekian tutorial saya kali ini, jika kalian ingin mempelajarinya lebih lanjut silahkan menuju link dibawah