PostgreSQL: pgpool-II 로드밸런서 전용 세팅

※ PostgreSQL: Setting up pgpool-II as a load balancer only.
※ Version: Linux 8.10 (Rocky), PostgreSQL 16.8, pgpool-II 4.6.

 

안녕하세요. 듀스트림입니다.

 

오늘은 pgpool-II를 로드밸런서로만 사용할 경우의 세팅 값에 대해 알아보겠습니다.

 

설치 방법과 구성도는 이전 포스팅 참고 부탁드립니다.

PostgreSQL: pgpool-II 설치 및 사용법

---

1. pgpool.conf

▸ 파일 편집 모드

vi pgpool.conf

 

▸ 아래 내용 추가

#----------------------------------------
# CONNECTIONS
#----------------------------------------
listen_addresses = '*'                      # Accept connections from any address
port = 9999                                 # Port pgpool-II listens on
pid_file_name = '/pgpool2/etc/popool.pid'   # PID file path
logdir = '/pgpool2/log'                     # Directory for pgpool-II status files
pool_passwd = '/pgpool2/etc/pool_passwd'

#----------------------------------------
# BACKEND NODE SETTINGS
#----------------------------------------
backend_hostname0 = 'hostname or ip'        # Primary PostgreSQL node hostname or IP
backend_port0 = 5432                        # Port of primary PostgreSQL
backend_weight0 = 1                         # Load balancing weight for this node
backend_flag0 = 'ALLOW_TO_FAILOVER'         # Allow pgpool to mark this node down if needed

backend_hostname1 = 'hostname or ip'        # Standby PostgreSQL node
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'

#----------------------------------------
# LOAD BALANCING
#----------------------------------------
load_balance_mode = on                      # Enable load balancing for SELECT queries
ignore_leading_white_space = on             # Ignore leading whitespace in SQL for query parsing
white_function_list = ''                    # Functions considered safe for load balancing
black_function_list = ''                    # Functions that should not be load balanced
statement_level_load_balance = on           # Enable load balancing at the statement level

#---------------------------------------------------------------
# CONNECTION POOL SETTINGS
#---------------------------------------------------------------
num_init_children = 600                     # Number of child processes for handling connections
max_pool = 10                               # Maximum number of cached connections per child process
child_life_time = 900                       # Child process lifetime in seconds
connection_life_time = 3600                 # Connection lifetime in seconds (0 means unlimited)
client_idle_limit = 900                     # Client is disconnected after being idle for that many seconds(0 means no disconnection)

#----------------------------------------
# STREAMING REPLICATION MODE
#----------------------------------------
master_slave_mode = on                      # Enable master-slave mode (required for streaming replication)
master_slave_sub_mode = 'stream'            # Use PostgreSQL streaming replication

#----------------------------------------
# HEALTH CHECK (optional, currently disabled)
#----------------------------------------
sr_check_period = 0                         # Disable streaming replication periodic checks
sr_check_database = 'postgres'
sr_check_user = 'postres'                   # User for streaming replication checks
# sr_check_password = 'your_password'       # Optional if pool_passwd is not used

#----------------------------------------
# FAILOVER / RECOVERY / WATCHDOG
#----------------------------------------
failover_on_backend_error = off             # Do not auto failover on backend error
use_watchdog = off                          # Disable pgpool's internal watchdog (HA handled externally)
enable_pool_hba = off                       # Do not use pool_hba.conf (use PostgreSQL's pg_hba.conf instead)
enable_shared_relcache = off                # Disable shared relcache (safe for simple use cases)

#----------------------------------------
# LOG SETTINGS
#----------------------------------------
log_destination = 'stderr'                  # Log output destination (stderr, syslog, etc.)
logging_collector = on                      # Enable logging to file
log_directory = '/postgres/pgpool/log'      # Directory where log files are stored
log_filename = 'pgpool_%Y%m%d_%H%M%S.log'   # Log file naming pattern with timestamp
log_file_mode = 0600                        # File permissions for log files
log_per_node_statement = on                 # Log which node a query was sent to (for debugging)
log_hostname = on                           # Log client hostnames

#---------------------------------------------------------------
# SSL CONNECTION SETTINGS
#---------------------------------------------------------------
ssl = on                                   # Enable SSL for client connections
ssl_key = '/ssl/server.key'                # Path to the SSL private key file
ssl_cert = '/ssl/server.crt'               # Path to the SSL certificate file
ssl_ca_cert = '/ssl/root.crt'              # Path to the Certificate Authority (CA) certificate file

---

2. pgpool_node_id

▸ Master 서버

echo "0" | sudo tee /postgres/pgpool/etc/pgpool_node_id

 

 

▸ Slave 서버

echo "1" | sudo tee /postgres/pgpool/etc/pgpool_node_id

---

3.1 pool_passwd: md5

필요시 생성

 

▸ 비밀번호 md5 해시 값 변환

pg_md5 <비밀번호>

 

▸ 파일 편집 모드

# .conf와 같은 경로에 있어야 함.
vi pool_passwd

 

▸ 아래 내용 추가

<PostgreSQL_User>:<비밀번호 해시 변환 값>

 

▸ 권한 설정

chmod 600 pool_passwd

---

3.2 pool_passwd: scram-sha-256

필요시 생성

 

▸ 파일 생성 및 권한 설정

touch pool_passwd
chmod 600 pool_passwd

 

▸ .pgpoolkey 파일 생성 및 권한 설정

echo '아무_문자열' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey

 

▸ AES 암호화

./pgpool2/bin/pg_enc -k ~/.pgpoolkey -u <PG_USER> -p

---

4. .pgpass 생성

필요시 생성

 

▸ 파일 편집 모드

vi .pgpass

 

▸ 아래 내용 추가

# host:port:database:user:passwd
<Primary_host>:5432:postgres:postgres:postgres
<Standby_host>:5432:postgres:postgres:postgres
<VIP>:9999:*:postgres:postgres

 

▸ 권한 설정

chmod 600 ~/.pgpass

---

5. pgpool-II 데몬 설정

▸ 파일 편집 모드

vi /etc/systemd/system/pgpool.service

 

▸ 아래 내용 추가

[Unit]
Description=pgpool-II
After=network.target

[Service]
Type=simple
TimeoutStartSec=300
Environment=PGPOOLKEYFILE=/root/.pgpoolkey
ExecStart=/pgpool2/bin/pgpool -n -f /postgres/pgpool/etc/pgpool.conf
ExecReload=/bin/kill -HUP $MAINPID
PIDFile=/pgpool2/etc/pgpool.pid

[Install]
WantedBy=multi-user.target

 

▸ 권한 설정 및 서비스 시작

chmod 644 /etc/systemd/system/pgpool.service
systemctl daemon-reload
systemctl start pgpool
systemctl enable pgpool

---

+ 테스트용 for문

for i in {1..10}; do /postgres/bin/psql -h <VIP> -U postgres -p 9999 -d postgres -c "SELECT inet_server_addr();"; done

---

오늘은 로드밸런서로서의 역할만 할 수 있는 pgpool-II 세팅 방법에 대해서 알아보았습니다.

 

pgpool-II 의 모든 기능을 사용하려면 조금 무거워지는데, 이런 가벼운 세팅은 자원 소모도 적어서 괜찮은 거 같습니다.

(4core 기준 평균 1% 정도 cpu 사용률을 보입니다.)

 

오늘은 여기까지~