EPAS 17에 pgpool-ll을 구축하였다.
필요한 서버는..
Primary 서버, Standby 서버, 그리고 pgpool을 구축할 서버 (AP서버같은 느낌인듯...)가 필요하다..
1. Primary, Standby 모두 pgpool extension을 설치해준다
dnf install edb-as17-pgpool45-extensions
2.primary 서버에서 pool_adm, pool_recovery extension 설치
edb=# create extension pgpool_adm ;CREATE EXTENSION edb=# create extension pgpool_recovery ;CREATE EXTENSION
3. pg_hba.conf 설정
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 trusthost all all 192.168.56.151/24 trust
host all all 192.168.56.152/24 trust
host all all 192.168.56.153/24 trust# IPv6 local connections:
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust#efm
host replication all 192.168.56.151/24 trust
host replication all 192.168.56.152/24 trust
host replication all 192.168.56.153/24 trusthost replication repl 192.168.56.151/24 trust
host replication repl 192.168.56.152/24 trust
host replication repl 192.168.56.153/24 trust
host all all 127.0.0.1/32 trusthost all all 192.168.56.151/24 trust
host all all 192.168.56.152/24 trust
host all all 192.168.56.153/24 trust# IPv6 local connections:
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust#efm
host replication all 192.168.56.151/24 trust
host replication all 192.168.56.152/24 trust
host replication all 192.168.56.153/24 trusthost replication repl 192.168.56.151/24 trust
host replication repl 192.168.56.152/24 trust
host replication repl 192.168.56.153/24 trust
4. enterprisedb OS 유저 sudo 권한 부여
$ vi /etc/sudoers...enterprisedb ALL=NOPASSWD: ALL
5. 준비한 pgpool 서버에 pgpool 설치해준다
dnf install edb-pgpool45
6. pgpool configuration 설정 파일을 설정한다.
cd /etc/sysconfig/edb/pgpool4.5cp pgpool.conf.sample pgpool.conf
cp pool_hba.conf.sample pool_hba.conf
cp pcp.conf.sample pcp.conf
cp pool_hba.conf.sample pool_hba.conf
cp pcp.conf.sample pcp.conf
7. enterprisedb OS유저 접속 후
pg_md5 enterprisedb 'enterprisedb 비밀번호'
실행한다.
그러면 md5로 암호화된 password가 표시된다.
[enterprisedb@localhost.localdomain:/var/lib/edb]$pg_md5 -m -u enterprisedb 1234891380f05b138eb6aa16260ca67d3bf3
이를, pcp.conf 파일에 넣어준다
[root@localhost pgpool4.5]# cat pcp.conf...enterprisedb:md59d582de9da7dc8a2d7f5f8ffe274daf9
8. pool_hba.conf 설정
[root@localhost pgpool4.5]# cat pool_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
# TYPE DATABASE USER CIDR-ADDRESS METHOD# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
9. pgpool.conf 설정
backend_clustering_mode ='streaming_replication'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
enable_pool_hba = on
pool_passwd = 'pool_passwd'
backend_hostname0 = '192.168.56.151'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = '192.168.56.151'backend_hostname1 = '192.168.56.152'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/var/lib/edb/as17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = '192.168.56.152'health_check_period = 10
health_check_timeout = 30
health_check_user = 'enterprisedb'
health_check_password = '1234'
health_check_database = 'edb'
health_check_max_retries = 5sr_check_period = 10
sr_check_user = 'enterprisedb'
sr_check_password = '1234'
sr_check_database = 'edb'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
#-----------------
# ESCALATION SETTINGS
#-----------------
enable_consensus_with_half_votes = on
#-----------------
# WATCHDOG (pgpool 노드 상태를 모니터링 / pgpool 자체 고가용성)
#-----------------
#use_watchdog = on
#delegate_ip = '192.168.56.121'
#if_cmd_path = '/usr/bin/'
#arping_path = '/usr/bin/'
#if_up_cmd = 'sudo ip addr add 192.168.56.121/24 dev enp0s8 label enp0s8:1'
#if_down_cmd = 'sudo ip addr del 192.168.56.121/24 dev enp0s8'
#arping_cmd = 'sudo arping -U 192.168.56.121 - 1 -I enp0s8'# WATCHDOG COMMUNICATION SETTINGS
#hostname0 = '192.168.56.151'
#wd_port0 = 9000
#pgpool_port0 = 9999
#hostname1 = '192.168.56.152'
#wd_port1 = 9000
#pgpool_port1 = 9999
#wd_lifecheck_method = 'heartbeat'
#wd_interval = 10
#wd_heartbeat_keepalive = 2
#wd_heartbeat_deadtime = 30
#heartbeat_hostname0 = '192.168.56.151'
#heartbeat_port0 = 9694
#heartbeat_device0 = ''
#heartbeat_hostname1 = '192.168.56.152'
#heartbeat_port1 = 9694
#heartbeat_device1 = ''
#wd_escalation_command = '/etc/sysconfig/edb/pgpool4.5/escalation.sh'
#---------
# LOGGING
#---------
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/edb/pgpool4.5/'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
#-----------------
# SOCKET DIRECTORY
#-----------------
socket_dir = '/var/run/edb/pgpool4.5/'
pcp_socket_dir = '/var/run/edb/pgpool4.5/'
wd_ipc_socket_dir = '/var/run/edb/pgpool4.5/'
pid_file_name = '/var/run/edb/pgpool4.5/pgpool.pid'
#-----------------
# FAILOVER COMMAND
#-----------------
failover_command = '/etc/sysconfig/edb/pgpool4.5/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'#---------------
# FOLLOW COMMAND
#---------------
follow_primary_command = '/etc/sysconfig/edb/pgpool4.5/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'#----------------
# ONLINE RECOVERY
#----------------
recovery_user = 'repl'
recovery_password = 'repl'
recovery_1st_stage_command = 'recovery_1st_stage'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
enable_pool_hba = on
pool_passwd = 'pool_passwd'
backend_hostname0 = '192.168.56.151'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = '192.168.56.151'backend_hostname1 = '192.168.56.152'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/var/lib/edb/as17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = '192.168.56.152'health_check_period = 10
health_check_timeout = 30
health_check_user = 'enterprisedb'
health_check_password = '1234'
health_check_database = 'edb'
health_check_max_retries = 5sr_check_period = 10
sr_check_user = 'enterprisedb'
sr_check_password = '1234'
sr_check_database = 'edb'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
#-----------------
# ESCALATION SETTINGS
#-----------------
enable_consensus_with_half_votes = on
#-----------------
# WATCHDOG (pgpool 노드 상태를 모니터링 / pgpool 자체 고가용성)
#-----------------
#use_watchdog = on
#delegate_ip = '192.168.56.121'
#if_cmd_path = '/usr/bin/'
#arping_path = '/usr/bin/'
#if_up_cmd = 'sudo ip addr add 192.168.56.121/24 dev enp0s8 label enp0s8:1'
#if_down_cmd = 'sudo ip addr del 192.168.56.121/24 dev enp0s8'
#arping_cmd = 'sudo arping -U 192.168.56.121 - 1 -I enp0s8'# WATCHDOG COMMUNICATION SETTINGS
#hostname0 = '192.168.56.151'
#wd_port0 = 9000
#pgpool_port0 = 9999
#hostname1 = '192.168.56.152'
#wd_port1 = 9000
#pgpool_port1 = 9999
#wd_lifecheck_method = 'heartbeat'
#wd_interval = 10
#wd_heartbeat_keepalive = 2
#wd_heartbeat_deadtime = 30
#heartbeat_hostname0 = '192.168.56.151'
#heartbeat_port0 = 9694
#heartbeat_device0 = ''
#heartbeat_hostname1 = '192.168.56.152'
#heartbeat_port1 = 9694
#heartbeat_device1 = ''
#wd_escalation_command = '/etc/sysconfig/edb/pgpool4.5/escalation.sh'
#---------
# LOGGING
#---------
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/edb/pgpool4.5/'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
#-----------------
# SOCKET DIRECTORY
#-----------------
socket_dir = '/var/run/edb/pgpool4.5/'
pcp_socket_dir = '/var/run/edb/pgpool4.5/'
wd_ipc_socket_dir = '/var/run/edb/pgpool4.5/'
pid_file_name = '/var/run/edb/pgpool4.5/pgpool.pid'
#-----------------
# FAILOVER COMMAND
#-----------------
failover_command = '/etc/sysconfig/edb/pgpool4.5/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'#---------------
# FOLLOW COMMAND
#---------------
follow_primary_command = '/etc/sysconfig/edb/pgpool4.5/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'#----------------
# ONLINE RECOVERY
#----------------
recovery_user = 'repl'
recovery_password = 'repl'
recovery_1st_stage_command = 'recovery_1st_stage'
10. pgpool 기동
root 유저 : systemctl start edb-pgpool-4.5.service[root@localhost pgpool4.5]# systemctl status edb-pgpool-4.5.service
● edb-pgpool-4.5.service - pgpool-II service script for EDB Postgres Advanced Server
Loaded: loaded (/usr/lib/systemd/system/edb-pgpool-4.5.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2025-05-22 09:46:46 KST; 7min ago
Process: 1816 ExecStopPost=/bin/bash -c rm -f /var/lock/edb/pgpool4.5/edb-pgpool-4.5 (code=exited, status=0/SUCCESS)
Process: 1813 ExecStop=/bin/bash -c /usr/lib/systemd/system/edb-pgpool-4.5.sh stop (code=exited, status=0/SUCCESS)
Process: 1895 ExecStartPost=/bin/bash -c touch /var/lock/edb/pgpool4.5/edb-pgpool-4.5 (code=exited, status=0/SUCCESS)
Process: 1854 ExecStart=/bin/bash -c /usr/lib/systemd/system/edb-pgpool-4.5.sh (code=exited, status=0/SUCCESS)
Main PID: 1855 (pgpool)
Tasks: 38 (limit: 48943)
Memory: 148.5M
CGroup: /system.slice/edb-pgpool-4.5.service
├─1855 /usr/edb/pgpool4.5/bin/pgpool -D -f /etc/sysconfig/edb/pgpool4.5/pgpool.conf -n -F /etc/sysconfig/edb/pgpool4.5/pcp.conf -a /etc/sysconfig/edb/pgpool4.5/pool_hba.conf
├─1857 pgpool: PgpoolLogger
├─1893 pgpool: health check process(0)
├─1894 pgpool: health check process(1)
├─1942 pgpool: PCP: wait for connection request
├─1944 pgpool: worker process
├─1945 pgpool: wait for connection request
├─1951 pgpool: wait for connection request
├─1952 pgpool: wait for connection request
├─1953 pgpool: wait for connection request
├─1954 pgpool: wait for connection request
├─1955 pgpool: wait for connection request
├─1956 pgpool: wait for connection request
├─1957 pgpool: wait for connection request
├─1958 pgpool: wait for connection request
├─1959 pgpool: wait for connection request
├─1960 pgpool: wait for connection request
├─1961 pgpool: wait for connection request
├─1962 pgpool: wait for connection request
├─1963 pgpool: wait for connection request
├─1964 pgpool: wait for connection request
├─1965 pgpool: wait for connection request
├─1966 pgpool: wait for connection request
├─1967 pgpool: wait for connection request
├─1968 pgpool: wait for connection request
├─1969 pgpool: wait for connection request
├─1970 pgpool: wait for connection request
├─1971 pgpool: wait for connection request
├─1972 pgpool: wait for connection request
├─1973 pgpool: wait for connection request
├─1974 pgpool: wait for connection request
├─1975 pgpool: wait for connection request
├─1976 pgpool: wait for connection request
├─1977 pgpool: wait for connection request
├─1978 pgpool: wait for connection request
├─1979 pgpool: wait for connection request
├─1981 pgpool: wait for connection request
└─1987 pgpool: wait for connection request 5월 22 09:46:44 localhost.localdomain systemd[1]: Starting pgpool-II service script for EDB Postgres Advanced Server...
5월 22 09:46:46 localhost.localdomain systemd[1]: Started pgpool-II service script for EDB Postgres Advanced Server.
● edb-pgpool-4.5.service - pgpool-II service script for EDB Postgres Advanced Server
Loaded: loaded (/usr/lib/systemd/system/edb-pgpool-4.5.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2025-05-22 09:46:46 KST; 7min ago
Process: 1816 ExecStopPost=/bin/bash -c rm -f /var/lock/edb/pgpool4.5/edb-pgpool-4.5 (code=exited, status=0/SUCCESS)
Process: 1813 ExecStop=/bin/bash -c /usr/lib/systemd/system/edb-pgpool-4.5.sh stop (code=exited, status=0/SUCCESS)
Process: 1895 ExecStartPost=/bin/bash -c touch /var/lock/edb/pgpool4.5/edb-pgpool-4.5 (code=exited, status=0/SUCCESS)
Process: 1854 ExecStart=/bin/bash -c /usr/lib/systemd/system/edb-pgpool-4.5.sh (code=exited, status=0/SUCCESS)
Main PID: 1855 (pgpool)
Tasks: 38 (limit: 48943)
Memory: 148.5M
CGroup: /system.slice/edb-pgpool-4.5.service
├─1855 /usr/edb/pgpool4.5/bin/pgpool -D -f /etc/sysconfig/edb/pgpool4.5/pgpool.conf -n -F /etc/sysconfig/edb/pgpool4.5/pcp.conf -a /etc/sysconfig/edb/pgpool4.5/pool_hba.conf
├─1857 pgpool: PgpoolLogger
├─1893 pgpool: health check process(0)
├─1894 pgpool: health check process(1)
├─1942 pgpool: PCP: wait for connection request
├─1944 pgpool: worker process
├─1945 pgpool: wait for connection request
├─1951 pgpool: wait for connection request
├─1952 pgpool: wait for connection request
├─1953 pgpool: wait for connection request
├─1954 pgpool: wait for connection request
├─1955 pgpool: wait for connection request
├─1956 pgpool: wait for connection request
├─1957 pgpool: wait for connection request
├─1958 pgpool: wait for connection request
├─1959 pgpool: wait for connection request
├─1960 pgpool: wait for connection request
├─1961 pgpool: wait for connection request
├─1962 pgpool: wait for connection request
├─1963 pgpool: wait for connection request
├─1964 pgpool: wait for connection request
├─1965 pgpool: wait for connection request
├─1966 pgpool: wait for connection request
├─1967 pgpool: wait for connection request
├─1968 pgpool: wait for connection request
├─1969 pgpool: wait for connection request
├─1970 pgpool: wait for connection request
├─1971 pgpool: wait for connection request
├─1972 pgpool: wait for connection request
├─1973 pgpool: wait for connection request
├─1974 pgpool: wait for connection request
├─1975 pgpool: wait for connection request
├─1976 pgpool: wait for connection request
├─1977 pgpool: wait for connection request
├─1978 pgpool: wait for connection request
├─1979 pgpool: wait for connection request
├─1981 pgpool: wait for connection request
└─1987 pgpool: wait for connection request 5월 22 09:46:44 localhost.localdomain systemd[1]: Starting pgpool-II service script for EDB Postgres Advanced Server...
5월 22 09:46:46 localhost.localdomain systemd[1]: Started pgpool-II service script for EDB Postgres Advanced Server.
11. pgpool 서버에서 enterprisedb OS 유저로 , 접속한다.
[enterprisedb@localhost.localdomain:/var/lib/edb]$psql -h 192.168.56.153 -p 9999 -U enterprisedb
psql(17.4.0 서버)도움말을 보려면 "help"를 입력하십시오.edb=#edb=# show pool_nodes;
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.151 | 5444 | up | up | 0.500000 | primary | primary | 3 | true | 0 | | | 2025-05-22 09:46:56
1 | 192.168.56.152 | 5444 | down | up | 0.500000 | standby | standby | 1 | false | 0 | | | 2025-05-22 09:47:34
(2개 행)
12. 테스트
간단한 테스트를 해본다.
edb=# select inet_server_addr();
inet_server_addr
------------------
192.168.56.152
(1개 행)
inet_server_addr
------------------
192.168.56.152
(1개 행)
현재 접속된 DB는 Secondary 다.
Secondary DB를 내린 후 다시 접속 시도한 후 확인해보면,
[enterprisedb@localhost.localdomain:/var/lib/edb]$psql -h 192.168.56.153 -p 9999 -U enterprisedb
psql(16.8.0, 17.4.0 서버)
경고: psql 메이저 버전 16, 서버 메이저 버전 17.
일부 psql 기능이 작동하지 않을 수도 있습니다.
도움말을 보려면 "help"를 입력하십시오.edb=# select inet_server_addr();
inet_server_addr
------------------
192.168.56.151
(1개 행)
1번 노드로 넘어갔다.
'Postgresql, EPAS > HA' 카테고리의 다른 글
| Repmgr (0) | 2025.06.18 |
|---|---|
| EFM (0) | 2025.06.18 |