close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/PostgreSQL · 2022. 1. 29. fullscreen 넓게보기

[PostgreSQL - Repmgr구성 + PGPOOL&PGBOUNCER] part2

part 1에 이어 proxy구성까지 해보도록 하겠습니다.

 

 

 사전 구성

part1 URL : https://jhdatabase.tistory.com/11

 

[PostgreSQL - repmgr 구성 & Failover test] part1

안녕하세요~오늘은 postgresql의 db서버들을 고가용성 구성을 해볼까 합니다. DB server 3대와 witness 서버를 repmgr로 이중화를 시킨뒤, 윗단에 proxy server를 두어 connection pool과 로드밸런싱 기능으로 구..

jhdatabase.tistory.com

 

 

 

 아키텍처

 

proxy 서버에는 PGPOOL과 PGBOUNCER를 사용할 예정입니다.

각 용도는 간단하게 아래와 같이 사용할 예정입니다.

 

pgpool -> 부하분산용

pgbouncer -> connection pool용

 

 

최종 아키텍처

 

 

PGpool-ll

 

■ pgpool-ll 설치

#yum install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-4.2.1-1pgdg.rhel7.x86_64.rpm
#yum install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-devel-4.2.1-1pgdg.rhel7.x86_64.rpm
#yum install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-debuginfo-4.2.1-1pgdg.rhel7.x86_64.rpm
#yum install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-extensions-4.2.1-1pgdg.rhel7.x86_64.rpm

 

 

■ config 수정

[root@pgpool pgpool-II]# cp pgpool.conf pgpool.conf.backup


[root@pgpool pgpool-II]# cp pgpool.conf.sample-stream pgpool.conf
cp: overwrite `pgpool.conf'? y




[root@pgpool ~]# vi /etc/pgpool-II/pgpool.conf

.......

#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------

# - pgpool Connection Settings -
listen_addresses = '*'
port = 5433
socket_dir = '/var/run/postgresql'
....

# - Backend Connection Settings -

backend_hostname0 = '10.70.101.82'    # Host name or IP address to connect to for backend 0
backend_port0 = 5432                        # Port number for backend 0
backend_weight0 = 1                         # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/pgsql/12/data'    # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'                  # Controls various backend behavior
                                                                        # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
                                                                        # or ALWAYS_PRIMARY
backend_application_name0 = 'primary'     
                                            # walsender's application_name, used for "show pool_nodes" command


backend_hostname1 = '10.70.101.83'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'replica1'

backend_hostname1 = '10.70.101.84'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'replica2'

backend_hostname1 = '10.70.101.85'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'repmgr'

.............

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

pid_file_name = '/var/run/pgpool/pgpool.pid'
                                   # PID file name
                                   # Can be specified as relative to the"
                                   # location of pgpool.conf file or
                                   # as an absolute path
                                   # (change requires restart)
logdir = '/var/lib/pgsql/12/data/log'
                                   # Directory of pgPool status file
                                   # (change requires restart)
...........

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------

load_balance_mode = on
                                   # Activate load balancing mode
                                   # (change requires restart)
...............

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'

................

#------------------------------------------------------------------------------
# NATIVE REPLICATION MODE
#------------------------------------------------------------------------------

# - Streaming -

sr_check_period = 5
                                   # Streaming replication check period
                                   # Disabled (0) by default
sr_check_user = 'repmgr'                  //replication 유저 기재
                                   # Streaming replication check user
                                   # This is neccessary even if you disable streaming
                                   # replication delay check by sr_check_period = 0
sr_check_password = 'repmgr'






[root@localhost run]# mkdir -p /var/run/postgresql

[root@localhost ~]# mkdir -p /var/lib/pgsql/12/data

[root@localhost run]# mkdir -p /var/run/pgpool/

[root@localhost run]# mkdir -p /var/lib/pgsql/12/data/log

 

 

■ PGpool 기동

[root@pgpool pgpool-II]# systemctl start pgpool.service

[root@pgpool pgpool-II]# systemctl status pgpool.service
● pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; disabled; vendor preset: disabled)
   Active: active (running) since 목 2022-01-27 08:56:18 KST; 3s ago
Main PID: 7453 (pgpool)
Tasks: 35
   CGroup: /system.slice/pgpool.service
           ├─7453 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -n
           ├─7455 pgpool: wait for connection request
           ├─7456 pgpool: wait for connection request
           ├─7457 pgpool: wait for connection request
           ├─7458 pgpool: wait for connection request
           ├─7459 pgpool: wait for connection request
           ├─7460 pgpool: wait for connection request
           ├─7461 pgpool: wait for connection request
           ├─7462 pgpool: wait for connection request
           ├─7463 pgpool: wait for connection request
           ├─7464 pgpool: wait for connection request
           ├─7465 pgpool: wait for connection request
           ├─7466 pgpool: wait for connection request
           ├─7467 pgpool: wait for connection request
           ├─7468 pgpool: wait for connection request
           ├─7469 pgpool: wait for connection request
           ├─7470 pgpool: wait for connection request
           ├─7471 pgpool: wait for connection request
           ├─7472 pgpool: wait for connection request
           ├─7473 pgpool: wait for connection request
           ├─7474 pgpool: wait for connection request
           ├─7475 pgpool: wait for connection request
           ├─7476 pgpool: wait for connection request
           ├─7477 pgpool: wait for connection request
           ├─7478 pgpool: wait for connection request
           ├─7479 pgpool: wait for connection request
           ├─7480 pgpool: wait for connection request
           ├─7481 pgpool: wait for connection request
           ├─7482 pgpool: wait for connection request
           ├─7483 pgpool: wait for connection request
           ├─7484 pgpool: wait for connection request
           ├─7485 pgpool: wait for connection request
           ├─7486 pgpool: wait for connection request
           ├─7487 pgpool: PCP: wait for connection request
           └─7488 pgpool: worker process
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  health_chec...288
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  health_chec...288
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  memory cach...zed
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: DETAIL:  memcache...:64
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  pool_discar...aps
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  Setting up ...999
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  Setting up ...999
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  find_primar...ode
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7453: LOG:  pgpool-II s...hi)
1월 27 08:56:18 pgpool pgpool[7453]: 2022-01-27 08:56:18: pid 7487: LOG:  PCP process...ted
Hint: Some lines were ellipsized, use -l to show in full.
    
 
 

 

■ pgpool로 접속

[root@pgpool pgpool-II]# psql -h localhost -p 9999 -U postgres
psql (9.2.24, server 12.3)
WARNING: psql version 9.2, server version 12.0.
        Some psql features might not work.
Type "help" for help.


postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight |  role  | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0      | primary  | 5432 | up    | 0.333333  | primary | 0          | true               | 0
1      | replica1 | 5432 | up    | 0.333333  | standby | 0          | false              | 0
2      | replica2 | 5432 | up    | 0.333333  | standby | 0          | false              | 0
3      | repmgr   | 5432 | up    | 0.333333  | standby | 0          | false              | 0
 
 

 

 

 

PGbouncer

 

■ Repository File & 공개 GPG key 다운

 

 

■ /etc/pki/rpm-gpg 공개키 이동

[root@localhost ~]# ls /etc/pki/rpm-gpg/
RPM-GPG-KEY-CentOS-7        RPM-GPG-KEY-CentOS-Testing-7  RPM-GPG-KEY-crunchydata-dev
RPM-GPG-KEY-CentOS-Debug-7  RPM-GPG-KEY-EPEL-7            crunchypg14.repo
 

 

 

■ Repository 등록

[root@localhost ~]# vi /etc/yum.repos.d/crunchypg12.repo
[crunchydatadevpg12]
#Config Version 1.0
name=Crunchy Data Development PostgreSQL 12 $releasever - $basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-crunchydata-dev
[crunchydatadevpg12-source]
#Config Version 1.0
name=Crunchy Data Development PostgreSQL 12 $releasever - Source
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-crunchydata-dev
[crunchydatadevpg12-debuginfo]
#Config Version 1.0
name=Crunchy Data Development PostgreSQL 12 $releasever - Debug
enabled=0
gpgcheck=1
 
 
 
 

■ pgbouncer 설치

[root@localhost ~]# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
[root@localhost ~]# yum install pgbouncer

 

 

 

■ config 수정

[root@localhost ~]# vi /etc/pgbouncer/pgbouncer.ini

[databases]
primary = host=10.70.101.82 port=5432  user=repmgr password=repmgr dbname=postgres

min_pool_size = 10            => DB마다 10개의 connection pool 생성




## socket directory 생성
[root@localhost ~]# mkdir -p /var/run/postgresql
[root@localhost ~]# chmod 777 /var/run/postgresql




## Userlist 수정
해당 password는 primary db에 접속해서 확인 가능

postgres=# select rolname,rolpassword from pg_authid where rolname='repmgr';  
rolname |            rolpassword             
---------+-------------------------------------
repmgr  | md58ea99ab1ec3bd8d8a6162df6c8e1ddcd





[root@localhost ~]# vi /etc/pgbouncer/userlist.txt
"marko" "asdasd"
"postgres" "asdasd"
"pgbouncer" "fake"
"repmgr" "md58ea99ab1ec3bd8d8a6162df6c8e1ddcd"
 
 
 
 

■ pgbouncer 기동 & 접속

[root@localhost ~]# systemctl start pgbouncer.service


[root@localhost ~]# psql -d primary -p 6432 -U repmgr

primary=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
kim       | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
repmgr    | repmgr   | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
test2     | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
(6 rows)




[root@localhost ~]# psql -p 6432 -U postgres pgbouncer
psql (9.2.24, server 1.14.0/bouncer)
WARNING: psql version 9.2, server version 1.14.
         Some psql features might not work.
Type "help" for help.


pgbouncer=# \x
Expanded display is on.


pgbouncer=# show clients;
-[ RECORD 1 ]+------------------------
type         | C
user         | postgres
database     | pgbouncer
state        | active
addr         | unix
port         | 6432
local_addr   | unix
local_port   | 6432
connect_time | 2022-01-26 11:27:37 KST
request_time | 2022-01-26 11:29:20 KST
wait         | 9
wait_us      | 216476
close_needed | 0
ptr          | 0x55c5cdeed090
link         |
remote_pid   | 4021
tls          |




pgbouncer# SHOW POOLS;  --> Connection Pool 조회

 

 

 

 

PGpool & PGbouncer 

 

 

■ config 수정

[root@localhost ~]# vi /etc/pgbouncer/pgbouncer.ini

[databases]
primary = host=10.70.101.82 port=5432  user=repmgr password=repmgr dbname=postgres
test2 = host=127.0.0.1 port=9999 dbname=test2 user=repmgr password=repmgr

 

 

 

■ 접속

[root@localhost ~]# psql -d test2 -U repmgr -p 6432
psql (9.2.24, server 12.3)
WARNING: psql version 9.2, server version 12.0.
         Some psql features might not work.
Type "help" for help.



test2=# show pool_nodes;                                   //  pgbouncer를 통해 pgpool로 DB에 접속
LOG:  statement: show pool_nodes;
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0       | primary  | 5432 | up     | 0.333333  | standby | 1452551    | true              | 0
1       | replica1  | 5432 | up     | 0.333333  | primary | 881353     | false             | 0
2       | replica2  | 5432 | up     | 0.333333  | standby | 587732     | false             | 0
3       | repmgr   | 5432 | up     | 0.333333  | standby | 0            | false              | 0
 
 
 
저작자표시

'DataBase > PostgreSQL' 카테고리의 다른 글

[PostgreSQL - PGDUMP 백업&복구]  (0) 2022.03.24
[PostgreSQL - Wal-g 백업 & 복구] part 2  (0) 2022.03.24
[PostgreSQL - Wal-g 백업] part 1  (0) 2022.03.24
[PostgreSQL - Repmgr 구성 & Failover test] part 1  (0) 2022.01.20
[PostgreSQL - 12.3 install]  (0) 2022.01.20
DataBase/PostgreSQL 관련 글
더 보기

티스토리툴바