officer/pgpool-pg-setup
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
pgpool-pg-setup/README.md
bigdeejay 10e5737d5a 사진 위치 수정
2025-02-03 12:28:03 +09:00

473 lines
13 KiB
Markdown
Raw Permalink Blame History

# [Pgpool-II + Watchdog Setup Example](https://www.pgpool.net/docs/45/en/html/example-cluster.html)
# 기능구현 검증목록
1. 클러스터 내 리더 `pgpool`이 동작하지 않는 경우, `watchdog`에 의해 새로운 리더가 선출되고 관리되어야한다.
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/pic1.png" alt="watchdogs" width="800">
</p>
2. 각 노드 내 `postgres` 서비스가 동작하지 않는 경우, 다른 정상노드가 `primary`로 승격되고 `유일한 쓰기`를 담당한다.
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/pic2.jpg" alt="pg" width="800">
</p>
3. 서비스 장애 발생 시 노드 간의 데이터의 가용성, 무결성 보장
* 테스트를 위해 pg1 서버의 postgresql 서비스 종료
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/recovery_1.png" alt="pg" width="800">
</p>
* 종료 후 접속되지 않는 pg1 서버의 postgresql 서비스
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/recovery_2.png" alt="pg" width="800">
</p>
- `user4` 테이블 생성
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/recovery_3.png" alt="pg" width="800">
</p>
- pg1 서버의 postgresql 서비스 `recovery` 명령어 실행 & 서비스 복구 성공
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/recovery_4.png" alt="pg" width="800">
</p>
- pg1 서버의 postgresql 서비스 복구 후 생성된 `user4` 확인
<p align="center">
<img src="https://gitea.rsonesoft.com/bigdeejay/pgpool_watchdog_setup_example/raw/branch/main/recovery_5.png" alt="pg" width="800">
</p>
# 설치환경
## 서버 3대
- CPU : 2-Core | RAM : 3gb(여유 있는 경우 4gb) | Storage : 20gb
- os : Rocky Linux 9.5 Minimal (tui 기반 설치)
- [가상화 : KVM/QEMU (리눅스 기반 오픈소스 가상화 플랫폼)](https://docs.rockylinux.org/guides/virtualization/libvirt-rocky/)
```sh
sudo virt-install \
--name pg1 \
--ram 3072 \
--vcpus 2 \
--disk path=/var/lib/libvirt/images/pg1.qcow2,size=20,format=qcow2 \
--os-variant rocky9 \
--network network=default \
--location Rocky-9.5-x86_64-minimal.iso \
--graphics none \
--console pty,target_type=serial \
--extra-args 'console=ttyS0,115200n8 serial'
```
- 예시 사용 노드 정보
| Hostname | IP Address |
| -------- | --------------- |
| pg1 | 192.168.122.129 |
| pg2 | 192.168.122.43 |
| pg3 | 192.168.122.7 |
## 패키지 매니저 업데이트
```sh
# [all servers]
sudo dnf update
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17-server
```
## 패키지 업데이트 제한
```text
# [all servers]
# vi /etc/yum.repos.d/pgdg-redhat-all.repo
[pgdg17]
...
exclude=pgpool*
```
## pgpool repository & libmemcached 설치
```sh
# [all servers]
sudo dnf --enablerepo=crb install libmemcached-awesome libmemcached-awesome-tools libmemcached-awesome-devel -y
sudo dnf install memcached -y
sudo systemctl enable memcached
sudo systemctl start memcached
sudo dnf install -y https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-9-x86_64/pgpool-II-release-4.5-1.noarch.rpm
sudo dnf install -y pgpool-II-pg17-*
```
## pgpool repository 등록
```sh
# [all servers]
su - postgres
mkdir /var/lib/pgsql/archivedir
```
## pg init @only primary
```sh
# only [primary server]
/usr/pgsql-17/bin/initdb -D $PGDATA
```
## pg config @only primary
```sh
vi $PGDATA/postgresql.conf
```
```text
listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
```
```sh
/usr/pgsql-17/bin/pg_ctl start -D $PGDATA
```
## pg create user @only primary
```sql
[server1]# psql -U postgres -p 5432
```
```sql
postgres=# SET password_encryption = 'scram-sha-256';
postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
postgres=# \password pgpool
postgres=# \password repl
postgres=# \password postgres
GRANT pg_monitor TO pgpool;
```
## pg hba config @only primary
```text
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all pgpool 192.168.0.0/16 scram-sha-256
host all postgres 192.168.0.0/16 scram-sha-256
host replication repl 192.168.0.0/16 scram-sha-256
```
## ssh config
```sh
su - postgres
mkdir ~/.ssh
chmod 700 ~/.ssh
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
```
```sh
su root
# vi /etc/ssh/sshd_config 파일수정 (안될 시에만 수정)
PubkeyAuthentication yes
systemctl restart sshd
```
```sh
# 방화벽 허용
sudo firewall-cmd --list-all
sudo firewall-cmd --add-service=ssh --permanent
sudo firewall-cmd --reload
```
```sh
# for rocky linux 9(SELinux)
su - postgres
restorecon -Rv ~/.ssh
```
```sh
su - postgres
ssh-copy-id postgres@192.168.x.x # pg1
ssh-copy-id postgres@192.168.x.x # pg2
ssh-copy-id postgres@192.168.x.x # pg3
# 각 서버별 서로 ssh 접속이 비밀번호 없이 가능해야함.
ssh postgres@192.168.x.x -i ~/.ssh/id_rsa_pgpool # pg1
ssh postgres@192.168.x.x -i ~/.ssh/id_rsa_pgpool # pg2
ssh postgres@192.168.x.x -i ~/.ssh/id_rsa_pgpool # pg3
```
``` text
# 1 -> 1, 1 -> 2, 1 -> 3
# 2 -> 1, 2 -> 2, 2 -> 3
# 3 -> 1, 3 -> 2, 3 -> 3
# 모든 노드가 서로 접속 가능해야한다.
# 아래는 2번 노드에서 1번 노드로 접속한 경우
[postgres@pg2 ~]$ ssh postgres@192.168.122.129 -i ~/.ssh/id_rsa_pgpool
Last login: Tue Jan 28 21:26:30 2025 from 192.168.122.43
[postgres@pg1 ~]$
```
## creating .pgpass
``` sh
su - postgres
vi /var/lib/pgsql/.pgpass
server1:5432:replication:repl:<repl user password>
server2:5432:replication:repl:<repl user password>
server3:5432:replication:repl:<repl user password>
server1:5432:postgres:postgres:<postgres user password>
server2:5432:postgres:postgres:<postgres user password>
server3:5432:postgres:postgres:<postgres user password>
chmod 600 /var/lib/pgsql/.pgpass
# 예시
su - postgres
vi /var/lib/pgsql/.pgpass
192.168.122.129:5432:replication:repl:rsonesoft
192.168.122.43:5432:replication:repl:rsonesoft
192.168.122.7:5432:replication:repl:rsonesoft
192.168.122.129:5432:postgres:postgres:rsonesoft
192.168.122.43:5432:postgres:postgres:rsonesoft
192.168.122.7:5432:postgres:postgres:rsonesoft
chmod 600 /var/lib/pgsql/.pgpass
```
```sh
su root
firewall-cmd --permanent --zone=public --add-service=postgresql
firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp --add-port=9694/udp
firewall-cmd --reload
```
## Create pgpool_node_id
```sh
# node 1
echo 0 >> /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id
0
```
```sh
# node 2
echo 1 >> /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id
1
```
```sh
# node 3
echo 2 >> /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id
2
```
## PCP connection authentication
```sh
echo 'pgpool:'`pg_md5 pgpool_password` >> /etc/pgpool-II/pcp.conf
cat /etc/pgpool-II/pcp.conf
# USERID:MD5PASSWD
pgpool:4aa0cb9673e84b06d4c8a848c80eb5d0
# 예시
echo 'pgpool:'`pg_md5 rsonesoft` >> /etc/pgpool-II/pcp.conf
cat /etc/pgpool-II/pcp.conf
# USERID:MD5PASSWD
pgpool:6522e450d3581238b5d3c2b4373f058e
```
## pgpool configuration
- pgpool.conf 파일의 수정사항은 모든 서버가 같은 값을 사용하기 때문에 `primary` 노드의 설정 값만 수정하고 다른 노드에 붙여넣으면 된다.
```sh
vi /etc/pgpool-II/pgpool.conf
listen_addresses = '*'
pcp_listen_addresses = '*'
sr_check_user = 'pgpool'
sr_check_password = ''
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
# - Backend Connection Settings -
backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'
backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/17/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'
# 예시
backend_hostname0 = '192.168.122.129'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg1'
backend_hostname1 = '192.168.122.43'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg2'
backend_hostname2 = '192.168.122.7'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/17/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
```
## failover configuration
```sh
vi /etc/pgpool-II/pgpool.conf
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
```
- 제공된 sample 사용
```sh
cp -p /etc/pgpool-II/sample_scripts/failover.sh.sample /etc/pgpool-II/failover.sh
cp -p /etc/pgpool-II/sample_scripts/follow_primary.sh.sample /etc/pgpool-II/follow_primary.sh
chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
```
- 각 스크립트의 환경변수 수정
``` sh
[all servers]# vi /etc/pgpool-II/failover.sh
...
PGHOME=/usr/pgsql-17
...
[all servers]# vi /etc/pgpool-II/follow_primary.sh
...
PGHOME=/usr/pgsql-17
...
```
``` sh
su - postgres
echo 'localhost:9898:pgpool:pgpool_password' > ~/.pcppass
chmod 600 ~/.pcppass
```
## pgpool recovery configuration @only primary
``` sh
vi /etc/pgpool-II/pgpool.conf
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /var/lib/pgsql/17/data/recovery_1st_stage
cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/17/data/pgpool_remote_start
chown postgres:postgres /var/lib/pgsql/17/data/{recovery_1st_stage,pgpool_remote_start}
# 스크립트 수정
[server1]# vi /var/lib/pgsql/17/data/recovery_1st_stage
...
PGHOME=/usr/pgsql-17
...
[server1]# vi /var/lib/pgsql/17/data/pgpool_remote_start
...
PGHOME=/usr/pgsql-17
...
```
``` sh
[server1]# su - postgres
[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
```
## pgpool hba configuration
``` sh
vi /etc/pgpool-II/pgpool.conf
enable_pool_hba = on
```
``` sh
vi /etc/pgpool-II/pool_hba.conf
host all pgpool 192.168.0.0/16 scram-sha-256
host all postgres 192.168.0.0/16 scram-sha-256
```
``` sh
su - postgres
echo 'rsonesoft' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
```
``` sh
pg_enc -m -k ~/.pgpoolkey -u pgpool -p
# trying to read key from file /var/lib/pgsql/.pgpoolkey
pg_enc -m -k ~/.pgpoolkey -u postgres -p
# trying to read key from file /var/lib/pgsql/.pgpoolkey
cat /etc/pgpool-II/pool_passwd # password : rsonesoft
pgpool:AESphLkYktBq1fqx0S+4gAaBQ==
postgres:AESphLkYktBq1fqx0S+4gAaBQ==
```
## watchdogs configuration
``` sh
vi /etc/pgpool-II/pgpool.conf
use_watchdog = on
```
``` sh
vi /etc/sudoers
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
```
```sh
vi /etc/pgpool-II/pgpool.conf
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
# ip -a 명령어를 통해 현재 네트워크명 파악 후 'enp0s8' 부분 수정
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/16 dev enp0s8 label enp0s8:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/16 dev enp0s8'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'
hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999
```
```sh
# scp 명령어를 통해 그동안 Primary 서버 작업물을 공유한다.
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.7:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.43:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.7:/etc/pgpool-II/pool_hba.conf
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.43:/etc/pgpool-II/pool_hba.conf
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.7:/etc/pgpool-II/escalation.sh
scp -p /etc/pgpool-II/pgpool.conf postgres@192.168.122.43:/etc/pgpool-II/escalation.sh
```
Reference in New Issue View Git Blame Copy Permalink