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:97bf34d31a8710e6b1649fd33357f783Walaupun 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 md5Selanjutnya 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
Comments: 1
terima kasih sangat membantu artikelnya…
ditunggu untuk tutorial PG_POOL HA nya..