pgpool2 로드밸런서

사전 준비

 

Master-Slave 설정

https://muyeon95.tistory.com/272

AWS EC2 ubuntu postgresql replication (Master-Slave)

 

아래 포스팅을 참고해 pgpool 인스턴스를 생성

 

https://muyeon95.tistory.com/187

AWS EC2 배포하기

 

master, slave, pgpool 인스턴스에 5432, 9999, 9898 추가

---

pgpool 설치

 

sudo apt-get update

sudo apt-get install pgpool2

sudo cp /usr/share/doc/pgpool2/examples/pgpool.conf.sample /etc/pgpool2/pgpool.conf

sudo cp /usr/share/doc/pgpool2/examples/pcp.conf.sample /etc/pgpool2/pcp.conf

---

md5 계정 생성

 

sudo pg_md5 -m -u postgres yourPassword

sudo cat /etc/pgpool2/pool_passwd

복사한 후

 

sudo vi /etc/pgpool2/pcp.conf

하단에 추가한다.

 

---

옵션 설정

 

sudo vi /etc/pgpool2/pgpool.conf

## 옵션이 많아 변경한 부분만 적는다.

pcp_port = 9898


listen_addresses = '*'

port = 9999

backend_hostname0 = ‘Master IP’
                                   
backend_port0 = 5432
                                   
backend_weight0 = 1
                                   
backend_data_directory0 = '/var/lib/postgresql/14/main'
                                   
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   
                                   
backend_application_name0 = 'server0'
                                   
backend_hostname1 = ’Slave IP’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/14/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'

replication_mode = off

master_slave_mode = on

load_balance_mode = on

sr_check_period = 5

sr_check_user = 'postgres'

sr_check_password = 'yourPassword'

sr_check_database = 'postgres'

---

재시작

 

sudo systemctl stop pgpool2.service

sudo systemctl start pgpool2.service

sudo systemctl status pgpool2.service

아마 작동하지 않을 것이다.

 

---

Master 와 Slave 에 pgpool 주소 추가

 

sudo vi /etc/postgresql/14/main/pg_hba.conf

host    all             all             <Pgpool IP>/32         trust

---

확인

 

sudo cat /var/log/postgresql/pgpool_status

 

만약 unused 나 down 으로 되어있다면?

 

sudo systemctl stop pgpool2.service 

sudo rm -rf /var/log/postgresql/pgpool_status 

sudo systemctl start pgpool2.service 

sudo cat /var/log/postgresql/pgpool_status

---

pgadmin을 통해 확인

 

포트는 9999 이다.

 

show pool_nodes;