High Availability PostgreSQL Cluster dengan Pgpool-II (Bagian ke-3)

Komunitas openSUSE Indonesia

TL;DR: Karena cukup panjang tulisan akan dibagi dalam 3 tulisan

  • Bagian 1 : membahas instalasi dan konfigurasi master postgresql
  • Bagian 2 : membahas instalasi dan konfigurasi slave postgresql
  • Bagian 3 : membahas instalasi dan konfigurasi pgpool-II

Tulisan ini adalah Bagian ke-3.

4. Instalasi pgpool

Pgpool-II sudah terdapat di OBS repositori. Versinya sedikit terlambat dari versi rilis di web Pgpool. Saat tulisan ini dibuat versi Pgpool di website Pgpool adalah 4.1. Sementara paket di OBS repositori masih versi 4.0.6.

Tambah repositori, install pgpool dan postgresql. Sebenarnya postgresql di mesin pgpool kita butuhkan hanya clientnya saja, untuk mengakses database (psql) dan backup (pg_dump) misalnya. Tapi untuk memudahkan kita install juga postgresql-server walaupun tidak perlu dijalankan

1
2

zypper ar -e -f https://download.opensuse.org/repositories/server:/database:/postgresql/openSUSE_Leap_15.1/ postgresql
zypper in --no-recommends pgpool-II postgresql12-pgpool-II postgresql12 postgresql12-server postgresql12-contrib

Selanjutnya edit file /etc/hosts, misalnya pgsql1 untuk master, pgsql2 untuk slave1, pgsql3 untuk slave2.

1
2
3
4
5

127.0.0.1       localhost
10.100.1.5      pgpool
10.100.1.6      pgsql1
10.100.1.7      pgsql2
10.100.1.8      pgsql3

Perhatikan bahwa kita menginstal pula paket postgresql12-pgpool-II, paket ini berisi library dan extension pgpool untuk PostgreSQL, ditandai dengan .sql dan .control. File-filenya tersimpan di direktori /usr/lib/postgresql12/lib64/, /usr/lib/postgresql12/lib64/bitcode/ dan /usr/share/postgresql12/extension/.

Langkah berikutnya kita memasang extension pgpool tersebut pada database postgresql. Kita pasang extension pgpool tersebut pada template1. Lakukan 1 kali saja di master karena nanti akan otomatis tereplikasi di slave.

1
2
3
4

sudo -i -u postgres
psql template1
CREATE EXTENSION pgpool_recovery;
CREATE EXTENSION pgpool_adm;

Periksa bahwa extension telah terpasang pada template1

1

\dx

Pada master akan seperti

Pada slave

Pada saatnya nanti kita gunakan template1 sebagai template database yang akan kita buat sehingga otomatis database yang terbentuk akan memiliki extension pgpool. Misalnya gunakan pgadmin, buatlah sebuah database baru dengan dengan menggunakan template1 di master, maka database akan dibuat dengan extension pgpool_adm dan pgpool_recovery. Database dengan kondisi yang sama otomatis akan terbentuk di slave. Kalau berhasil artinya sulapan kita bekerja 🙂

Langkah berikutnya adalah mengkonfigurasi file /etc/pgpool-II/pcp.conf. File ini berisi user dan password pgpool untuk admnistrasi management pgpool. Password dihash md5 dan formatnya adalah

 username:[md5 encrypted password]

Buat password dalam format md5 dengan

$ pg_md5 your_password
    1060b7b46a3bd36b3a0d66e0127d0517

Atau jika anda tidak ingin memperlihatkan password anda dengan cara:

 $ pg_md5 -p
    password: your_password

Isi file pcp.conf dengan menggunakan hasil md5 tersebut misal

admin:97bf34d31a8710e6b1649fd33357f783

Walaupun tidak wajib kita bisa mengisikan username dan password yang sama dengan user PostgreSQL agar mempermudah dalam mengakses dan mengingatnya.

Jangan lupa lengkapi file /etc/pgpool-II/pool_hba.conf dengan ip address dari masing-masing server PostgreSQL (sesuaikan dengan ip mesin master dan slave), misalnya

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         10.100.1.6/32         md5
host    all         all         10.100.1.7/32         md5
host    all         all         10.100.1.8/32         md5

Selanjutnya kita harus mengkonfigurasi file /etc/pgpool-II/pgpool.conf. Sesuai dengan dokumentasi pgpool maka running mode yang dianjurkan secara default adalah streaming replication mode. Dalam mode ini PostgreSQL bertanggung jawab terhadap sinkronisasi database. Load balancing dimungkinkan untuk dijalankan dalam mode ini. Contoh konfigurasinya diberikan ketika kita menginstall pgpool yaitu pada file /etc/pgpool-II/pgpool.conf.sample-stream. Di  bawah ini adalah contoh dari /etc/pgpool-II/pgpool.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266

# - pgpool Connection Settings -
listen_addresses = '*'
port = 5432
socket_dir = '/var/run/pgpool'
 
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'
listen_backlog_multiplier = 2
serialize_accept = off
 
# - Backend Connection Settings -
backend_hostname0 = 'gis_pgsql1' # sesuaikan dengan HOSTNAME master postgresql
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
 
backend_hostname1 = 'gis_pgsql2' # sesuaikan dengan HOSTNAME slave postgresql
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
 
backend_hostname2 = 'gis_pgsql3' # sesuaikan dengan HOSTNAME slave postgresql
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
 
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
allow_clear_text_frontend_auth = off
 
# - SSL Connections -
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = off
 
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
 
# - Concurrent session and pool size -
num_init_children = 80
max_pool = 4
 
# - Life time -
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
 
#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
 
# - Where to log -
log_destination = 'syslog'
 
# - What to log -
log_line_prefix = '%t: pid %p: '
log_connections = on
log_hostname = off
log_statement = on
log_per_node_statement = on
log_client_messages = on
log_standby_delay = 'if_over_threshold'
 
# - Syslog specific -
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
 
# - Debug -
log_error_verbosity = default
 
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
 
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/var/run/pgpool'
 
#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
 
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
 
#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
 
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
 
# - Degenerate handling -
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
 
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
 
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
black_query_pattern_list = ''
 
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off
disable_load_balance_on_write = 'transaction'
 
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
 
master_slave_mode = on
master_slave_sub_mode = 'stream'
 
# - Streaming -
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'password'
sr_check_database = 'postgres'
delay_threshold = 10000000
 
# - Special commands -
follow_master_command = ''
 
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 5
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'password'
health_check_database = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
 
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = ''
failback_command = ''
failover_on_backend_error = on
detach_false_primary = off
search_primary_node_timeout = 300
 
#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
 
recovery_user = 'postgres'
recovery_password = 'password'
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
 
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
 
# - Enabling -
#use_watchdog = on
use_watchdog = off
 
# -Connection to up stream servers -
trusted_servers = ''
ping_path = '/bin'
 
# - Watchdog communication Settings -
wd_hostname = ''
wd_port = 9000
wd_priority = 1
 
wd_hostname = ''
wd_port = 9000
wd_priority = 2
 
wd_hostname = ''
wd_port = 9000
wd_priority = 3
 
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
 
# - Virtual IP control Setting -
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
 
# - Behaivor on escalation Setting -
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
 
# - Watchdog consensus settings for failover -
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
 
# - Lifecheck Setting -
 
# -- common --
wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
 
# -- heartbeat mode --
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
 
other_pgpool_hostname0 = 'host_ip1'
other_pgpool_port0 = 5432
other_wd_port0 = 9000
 
# -- query mode --
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
 
#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on
 
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''

Perhatikan pada bagian Backend Connection Setting pada file konfigurasi di atas kita set weight=1. Ini artinya kita berusaha agar perbandingan beban dari masing-masing backend adalah sama.

Selanjutnya ada 1 file lagi yang harus kita lengkapi yaitu /etc/pgpool-II/pool_passwd. Isilah file ini dengan username dan password yang sama dengan user postgresql. Entry dari file ini pool_passwd ini harus dibuat dengan menjalankan

pg_md5 -f /etc/pgpool-II/pgpool.conf -m -u postgres postgrespassword

Terakhir jalankan service pgpool

1
2

sudo systemctl start pgpool-II.service
sudo systemctl enable pgpool-II.service

Kita bisa mencoba bagaimana pgpool bekerja dengan mentest membuat database dan menghapusnya melalui pgadmin. Kita koneksikan pgadmin kita ke server pgpool (masukkan ip address dan port mesin pgpool, username dan password pgpool), buat database maka database akan terbentuk di master dan slave. Hapus database melalui pgpool maka database akan terhapus di master dan slave.

Koneksi ke database dari aplikasi kita tinggal diarahkan ke mesin pgpool (tidak lagi ke mesin postgresql). Dengan cara demikian, proses WRITE akan dilakukan oleh pgpool ke mesin master, sedangkan proses READ akan dibagi ke mesin master dan slave. Untuk membuktikannya anda dapat menjalankan htop di setiap mesin postgresql dan lihat distribusi bebannya. Bisa juga dengan membandingkan log dari pgpool untuk melihat proses INSERT, UPDATE, SELECT dilakukan ke mesin mana saja. Misalnya dengan menjalankan

1
2
3
4
5
6

sudo journalctl -u pgpool-II | grep "node id: 0" | grep INSERT
sudo journalctl -u pgpool-II | grep "node id: 1" | grep INSERT
sudo journalctl -u pgpool-II | grep "node id: 2" | grep INSERT
sudo journalctl -u pgpool-II | grep "node id: 0" | grep SELECT
sudo journalctl -u pgpool-II | grep "node id: 1" | grep SELECT
sudo journalctl -u pgpool-II | grep "node id: 2" | grep SELECT

Masih ada yang belum ditulis dalam artikel ini yaitu bagaimana membuat mesin pgpool menjadi HA juga. Mudah-mudahan kedepannya dapat saya tuliskan.

Selamat mencoba, have fun

medwinz

 

1 10 Januari 2020
Panduan

Comments: 1

  1. Kang Asep berkata:
    9 Agustus 2020 pukul 1:47 am

    terima kasih sangat membantu artikelnya…
    ditunggu untuk tutorial PG_POOL HA nya..

     Reply

Add your comment

Situs ini menggunakan Akismet untuk mengurangi spam. Pelajari bagaimana data komentar Anda diproses.

Pos-pos Terbaru
Komentar Terbaru
Arsip
Kategori

©  2024 openSUSE.ID. Built using WordPress and the Materialis Theme