CentOS7.6 PostgreSQL Clustering & Pgpool

• Master 와 Standby 생성
• PostgreSQL Streaming Replication 기능으로 동기화
• Pgpool load_balance_mode 로 SELECT 조회를 분산
• 여기서는 동일 서버 내에서 데이터베이스를 분리 (Port : 5432, 5433)

PostgreSQL

1. 저장소 설치

# rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Updating / installing...
1:pgdg-redhat-repo-42.0-4 ################################# [100%]

2. PosrtgreSQL 설치

# yum -y install postgresql11-server.x86_64

Installed:
postgresql11-server.x86_64 0:11.4-1PGDG.rhel7

Dependency Installed:
libicu.x86_64 0:50.1.2-17.el7 postgresql11.x86_64 0:11.4-1PGDG.rhel7 postgresql11-libs.x86_64 0:11.4-1PGDG.rhel7

Complete!

3. 계정 postgres 비밀번호 변경

# passwd postgres
Changing password for user postgres.
New password:

4. Data Directory 생성

# mkdir /data
# chown -R postgres.postgres /data
# chmod -R 700 /data

5. 데이터베이스 클러스터 생성 (Master Server)

# su - postgres
# /usr/pgsql-11/bin/initdb /data/pgsql

Success. You can now start the database server using:

/usr/pgsql-11/bin/pg_ctl -D /data/pgsql -l logfile start

6. 환경설정
9.6 버전부터 archive 와 hot_standby 가 replica 로 통일되었다.

# vi /data/pgsql/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5432

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = replica

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Servers  -
# max_wal_senders : Standby Node 수
max_wal_senders = 2
max_replication_slots = 2

7. 방화벽 설정

# vi /data/pgsql/pg_hba.conf

# IPv4 local connections: Pgpool로 연결할 원격 서버 IP SSL 허용
host    all             all             127.0.0.1/32            trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
# Pg Data 복사해갈 원격 IP 허용
host    replication     replication      127.0.0.1/32            md5

8. 구동
postgresql.conf에 설정한 Port 번호와 다른 번호로 구동시킬 때는 옵션 o 를 사용한다.

/usr/pgsql-11/bin/pg_ctl -D {DatabaseFolderPath} -o "-F -p {PortNumber}" -l {LogFileName} start

# /usr/pgsql-11/bin/pg_ctl -D /data/pgsql -l /data/pgsql/logfile start

waiting for server to start.... done
server started

• 오류 : /bin/sh: logfile: Permission denied 옵션 l 을 사용할 때 파일명만 지정하니 /data 폴더에 생성 됨

# chown -R postgres.postgres /data

9. Replication Slot 생성

# psql postgres
postgres=# SELECT * FROM pg_create_physical_replication_slot('repl_slot');

slot_name | lsn
-----------+-----
repl_slot |
(1 row)

10. 복제에 사용할 사용자 생성

postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'replica' LOGIN;

CREATE ROLE

11. Standby Server 생성

/usr/pgsql-11/bin/pg_basebackup -h {외부 서버 IP} -D {외부 저장 위치} -U {복제 사용자} -v -P -X stream

# /usr/pgsql-11/bin/pg_basebackup -p 5432 -h 127.0.0.1 -D /data/pgsql2 -U replication -v -P -X stream

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3789"
24551/24551 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

12. Standby Server postgresql.conf 수정
동일 서버 내에 생성했기 때문에 Port 번호도 수정

# vi /data/pgsql2/postgresql.conf

port = 5433

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Standby Servers -
hot_standby = on
hot_standby_feedback = on

13. recovery.conf 생성

# vi /data/pgsql2/recovery.conf

# Standby Server
standby_mode='on'
# Master Server Connection info
primary_conninfo='host=127.0.0.1 port=5432 user=replication password=replica'
# Master Server Replication Slot Name
primary_slot_name='repl_slot'

trigger_file='/data/pgsql2/failover_trigger'

14. Standby Server 구동

# /usr/pgsql-11/bin/pg_ctl -D /data/pgsql2 -l /data/pgsql2/logfile start

waiting for server to start.... done
server started

15. Streaming Replication 동작 확인

# psql postgres
postgres=# SELECT * FROM pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------+----------+-------------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+----------- -+---------------+------------
14035 | 16384 | replication | walreceiver | 127.0.0.1 | | 43568 | 2019-06-21 10:39:23.934657+09 | | streaming | 0/3000140 | 0/3000140 | 0/3000140 | 0/3000140 | | | | 0 | async
(1 row)

Pgpool 

1. CentOS 버전 확인

# grep . /etc/*release

/etc/centos-release:CentOS Linux release 7.6.1810 (Core)
/etc/os-release:NAME="CentOS Linux"
/etc/os-release:VERSION="7 (Core)"
/etc/os-release:ID="centos"
/etc/os-release:ID_LIKE="rhel fedora"
/etc/os-release:VERSION_ID="7"
/etc/os-release:PRETTY_NAME="CentOS Linux 7 (Core)"
/etc/os-release:ANSI_COLOR="0;31"
/etc/os-release:CPE_NAME="cpe:/o:centos:centos:7"
/etc/os-release:HOME_URL="https://www.centos.org/"
/etc/os-release:BUG_REPORT_URL="https://bugs.centos.org/"
/etc/os-release:CENTOS_MANTISBT_PROJECT="CentOS-7"
/etc/os-release:CENTOS_MANTISBT_PROJECT_VERSION="7"
/etc/os-release:REDHAT_SUPPORT_PRODUCT="centos"
/etc/os-release:REDHAT_SUPPORT_PRODUCT_VERSION="7"
/etc/redhat-release:CentOS Linux release 7.6.1810 (Core)
/etc/system-release:CentOS Linux release 7.6.1810 (Core)

2. OS 버전에 맞는 Repository 설치 (참조)

# yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm

Running transaction
Installing : pgpool-II-release-4.0-1.noarch 1/1
Verifying : pgpool-II-release-4.0-1.noarch 1/1

Installed:
pgpool-II-release.noarch 0:4.0-1

Complete!

• Repository로 설치된 내용

# yum list available | grep pgpool

pgpool-II-pg10.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg10-debuginfo.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg10-devel.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg10-extensions.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg11.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg11-debuginfo.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg11-devel.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg11-extensions.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg93.x86_64 4.0.4-1pgdg.rhel7 pgpool40
pgpool-II-pg93-debuginfo.x86_64 4.0.4-1pgdg.rhel7 pgpool40
pgpool-II-pg93-devel.x86_64 4.0.4-1pgdg.rhel7 pgpool40
pgpool-II-pg93-extensions.x86_64 4.0.4-1pgdg.rhel7 pgpool40
pgpool-II-pg94.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg94-debuginfo.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg94-devel.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg94-extensions.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg95.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg95-debuginfo.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg95-devel.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg95-extensions.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg96.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg96-debuginfo.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg96-devel.x86_64 4.0.5-1pgdg.rhel7 pgpool40
pgpool-II-pg96-extensions.x86_64 4.0.5-1pgdg.rhel7 pgpool40

3. Pgpool Install
Pgpool-ll YUM 저장소 패키지명은 PostgreSQL 버전 번호가 포함되어 있다.

• PostgreSQL Version 확인

# /usr/pgsql-11/bin/postgres --version

postgres (PostgreSQL) 11.4

• 설치

# yum -y install pgpool-II-pg11*

Total 557 kB/s | 3.4 MB 00:00:06
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-9.2.24-1.el7_5.x86_64 1/5
Installing : pgpool-II-pg11-4.0.5-1pgdg.rhel7.x86_64 2/5
Installing : pgpool-II-pg11-extensions-4.0.5-1pgdg.rhel7.x86_64 3/5
Installing : pgpool-II-pg11-devel-4.0.5-1pgdg.rhel7.x86_64 4/5
Installing : pgpool-II-pg11-debuginfo-4.0.5-1pgdg.rhel7.x86_64 5/5
Verifying : pgpool-II-pg11-extensions-4.0.5-1pgdg.rhel7.x86_64 1/5
Verifying : postgresql-libs-9.2.24-1.el7_5.x86_64 2/5
Verifying : pgpool-II-pg11-debuginfo-4.0.5-1pgdg.rhel7.x86_64 3/5
Verifying : pgpool-II-pg11-devel-4.0.5-1pgdg.rhel7.x86_64 4/5
Verifying : pgpool-II-pg11-4.0.5-1pgdg.rhel7.x86_64 5/5

Installed:
pgpool-II-pg11.x86_64 0:4.0.5-1pgdg.rhel7 pgpool-II-pg11-debuginfo.x86_64 0:4.0.5-1pgdg.rhel7
pgpool-II-pg11-devel.x86_64 0:4.0.5-1pgdg.rhel7 pgpool-II-pg11-extensions.x86_64 0:4.0.5-1pgdg.rhel7

Dependency Installed:
postgresql-libs.x86_64 0:9.2.24-1.el7_5

Complete!

4. 설치된 내용 확인

# rpm -ql pgpool-II-pg11

/etc/pgpool-II/pcp.conf
/etc/pgpool-II/pgpool.conf
/etc/pgpool-II/pgpool.conf.sample-logical
/etc/pgpool-II/pgpool.conf.sample-master-slave
/etc/pgpool-II/pgpool.conf.sample-replication
/etc/pgpool-II/pgpool.conf.sample-stream
/etc/pgpool-II/pool_hba.conf /etc/sysconfig/pgpool
… (생략)

# rpm -qa pgpool*

pgpool-II-pg11-devel-4.0.5-1pgdg.rhel7.x86_64
pgpool-II-pg11-4.0.5-1pgdg.rhel7.x86_64
pgpool-II-release-4.0-1.noarch
pgpool-II-pg11-debuginfo-4.0.5-1pgdg.rhel7.x86_64
pgpool-II-pg11-extensions-4.0.5-1pgdg.rhel7.x86_64

5. pgpool.conf 복사 및 수정
어떻게 작동시킬 것인가에 따라 필요한 conf 파일을 복사해서 사용한다. (참조)

• 스트리밍 복제모드(Streaming replication mode) : stream PostgreSQL이 데이터베이스를 동기화하고 Pgpool은 Load Balancing을 담당
• 논리 복제모드(logical replication mode) : logical PostgreSQL이 테이블 동기화를 담당하며 Pgpool은 Load Balancing을 담당
• 마스터 슬레이브 모드(master slave mode(slony mode)) : master-slavePgpool이 데이터베이스 동기화와 Load Balancing을 담당
• 네이티브 복제모드(native replication mode) : replication동기식으로 동기화를 수행
• 원시 모드(raw mode) : sample
• 설명 문서 : https://www.pgpool.net/docs/latest/en/html/configuring-pgpool.html

# cd /etc/pgpool-II/
# cp pgpool.conf.sample-stream pgpool.conf

6. 실행 중인 PostgreSQL Data Directory 확인

# ps -ef | grep postgres

postgres 11759 1 0 13:05 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D /data/pgsql
postgres 11828 1 0 13:07 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D /data/pgsql2
… (생략)

7. 환경 설정
외부에서 DB 접근 시 Pgpool로 접근해야 정상 작동한다.
DB 서버 포트로 바로 접근해서 수정하면 Pgpool이 오류난다.

# vi /etc/pgpool-II/pgpool.conf

#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
# 외부 접속 정보
#  '*' for all, '' for no TCP/IP connections
listen_addresses = '*'
port = 9999

#------------------------------------------------------------------------------
# - Backend Connection Settings -
#------------------------------------------------------------------------------
# backend_hostname : Pgpool이 서버에 접속할 때 사용할 hostname 또는 IP
# backend_port : 포트번호
# backend_weight : 백엔드의 Load balancing 비율
# backend_data_directory : 백엔드의 데이터베이스 클러스터 디렉터리
# backend_flag : 백엔드 동작을 제어
# 백엔드를 여러 개 사용할 경우 매개변수 이름 끝에 숫자(DB node ID)를 추가한다.
# 0은 서버를 하나만 사용한다. 숫자가 가장 낮은 DB noid ID가 마스터 노드가 된다.
backend_hostname0 = '127.0.0.1'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = ‘127.0.0.1'
backend_port1 = 5434
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql2'
backend_flag1 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# 로그 파일 저장 위치 지정
logdir = '/var/log/pgpool'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
# replication 모드 활성화/비활성화
replication_mode = off
replicate_select = off
insert_lock = off

#------------------------------------------------------------------------------
# - Degenerate handling -
#------------------------------------------------------------------------------
# 데이터 정합성 검사
# insert나 update, delete시에 반영되는 로우가 다를시 쿼리실행이 중지됨
replication_stop_on_mismatch = off

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
# Streaming Replication
# 마스터 / 슬레이브 모드 확성화/비활성화
# replication_mode 와 함께 사용할 수 없다. 둘 중 하나만 활성화해서 사용.
master_slave_mode = on

# slony는 Slony-I과 함께 사용
# stream, logical은 PostgrreSQL의 기본 제공 복제시스템
master_slave_sub_mode = ‘stream’

#------------------------------------------------------------------------------
# - Streaming -
#------------------------------------------------------------------------------
# Streaming Replication 방식을 사용할 경우 설정
# sr_check_user 설정을 정확히 안하면 primary node 를 찾지 못함
sr_check_period = 0
sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'postgres'
delay_threshold = 0

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
# 기본 노드를 찾는 시간
search_primary_node_timeout = 10

8. 방화벽 설정
필요에 따라 설정

# vi /etc/pgpool-II/pool_hba.conf

9. Pgpool 구동 + 로그 기록
로그를 확인하여 Pgpool 이 어느 호스트 소켓에 구동됬는 지 확인

Pgpool 옵션

• n : 데몬 모드에서 실행하지 않는다.
• D : 이전 Pgpool 상태로 복원하지 않는다.
• d : 디버깅

# mkdir /var/log/pgpool
# pgpool -f /etc/pgpool-II/pgpool.conf -n -D -d > /var/log/pgpool/pgpool.log 2>&1 &
# cat /var/log/pgpool/pgpool.log

(생략)
2019-06-20 09:54:04: pid 17573: LOG: Setting up socket for 0.0.0.0:9999

10. Load Balance 상태를 확인

# psql -p 9999 -h 0.0.0.0 postgres -c "show pool_nodes;"

node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-
0 | 127.0.0.1 | 5432 | up | 0.500000 | primary | 1 | true | 0 | 2019-06-23 14:04:13
1 | 127.0.0.1 | 5433 | up | 0.500000 | standby | 0 | false | 0 | 2019-06-23 14:04:13
(2 rows)

• down 상태일 경우 Pgpool 정지 후 상태 파일을 삭제하고 구동한다.

# cat pgpool.conf | grep logdir
logdir = '/var/log/pgpool'
# rm -rf /var/log/pgpool/pgpool_status

# psql postgres -c "select pg_is_in_recovery();"

pg_is_in_recovery
-------------------
f
(1 row)

# psql -p 5433 postgres -c "select pg_is_in_recovery();"

pg_is_in_recovery
-------------------
t
(1 row)

11. Replication DB 생성
인자가 없는 createdb는 현재 사용자 이름으로 데이터베이스를 생성

# createdb -p 9999 -h 0.0.0.0 bench_replication

12. 확인

# psql postgres -c "\l"

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------+----------+----------+-------------+-------------+-----------------------
bench_replication | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

# psql -p 5433 postgres -c "\l"

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------+----------+----------+-------------+-------------+-----------------------
bench_replication | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

* Pgpool 정지
Pgpool과 연결된 PostgreSQL에 접속 중이면 중지되지 않는다.

# pgpool stop

2019-06-19 17:49:38: pid 14447: LOG: stop request sent to pgpool. waiting for termination... .
done.
[1]+ Done pgpool -f /etc/pgpool-II/pgpool.conf -n -d > /var/log/pgpool/pgpool.log 2>&1