tutorial postgresql 9.6 pada centos 7 part 2 (master–slave repmgr with auto promote master)

0
1362

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

 

 

 

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here