PostgreSQL HA 구조

• Streming Replication은 WAL(Write Ahead Log)를 전달해서 Replication을 Transaction Log Shipping 방법 중에 하나이다.
  WAL을 짧게 요약해서 설명하면 Database 변경 사항만을 저장한 Log를 말하며, Transanction Log Shping을 이용한 Replication은 양쪽 DB의 원본이 동일하게 출발하였다면,
  Active DB Node Server에서 발생하는 변경사항을 기록한 WAL 파일들을 Hot-Standby DB Node Server에 순서에 맞춰 적용시키면 동일한 DB로 되는 개념을 바탕으로 이루어 졌다.
  

모듈 설치 및 적용 방법

pg_stat_statements

• Postgresql-contrib 패키지는 Postgresql 확장 모듈이 담겨져 있으므로 원하는 모듈을 적용하고 싶을 때 extension 설정을 하면 해당 모듈 및 postgresql.conf 파일에 Configuration을 적용 시킬 수 있습니다.
  Postgresql-contrib 확장 모듈 사용법 : https://www.postgresql.org/docs/12/contrib.html

# postgresql12-contrib 패키지 설치
$ sudo yum -y install postgresql12-contrib

# postgresql.conf 설정
$ sudo vi /data/postgres/data/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# postgresql 재시작
$ sudo systemctl restart postgresql-12

# DB 접속 및 모듈 확장
$ sudo su postgres
$ psql
postgres=# create extension pg_stat_statements;
postgres=# \dx
List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_cron            | 1.3     | public     | Job scheduler for PostgreSQL
 pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

pg_cron

# pg_cron 설치
$ sudo yum -y install pg_cron_12

# pg_cron 설정
$ sudo vi /data/postgres/data/postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

# postgresql 재시작
$ sudo systemctl restart postgresql-12

# psql 접속 및 스키마 생성
$ sudo su postgres
$ cd
$ psql
postgres# create extension pg_cron;

서버 구축 방법

Postgresql 12 설치(Master, Slave 동일)

$ sudo yum -y update

# PostgreSQL Repository 추가
$ sudo vi /etc/yum.repos.d/pgdg.repo
[PostgresqlRepo]
name=PostgreSQL 12 for RHEL/CentOS 7 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0

# 패키지 색인파일 업데이트
$ sudo yum makecache

# PostgreSQL-12 설치
$ sudo yum -y install postgresql12 postgresql12-server

# PostgreSQL DB 서비스 초기화 및 시작
$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

$ sudo systemctl enable --now postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
 
$ systemctl status postgresql-12

# 버전확인
$ psql --version
psql (PostgreSQL) 12.5

Postgresql 12 기본 환경설정

# DB 접속
$ sudo su - postgres
$ psql

# peer 인증
$ CREATE USER user PASSWORD 'pwd' SUPERUSER;

# Postgresql 설정
$ sudo - su postgres
$ vi /var/lib/psql/data/postgresql.conf
# 맨 하단 부 아래내용 추가
listen_addresses = '*'

$ vi /var/lib/psql/data/pg_hba.conf
# 맨 하단부 아래내용 추가
host    all          all               0.0.0.0/0       md5

# Postgresql 재시작
$ sudo systemctl restart postgresql-12

# AWS 보안그룹(인바운드) 포트 추가
port : 5432
ip : 0.0.0.0/0 또는 접속할 자신의 ip

Postgresql 12 RAID 0 구성(Master, Slave 동일)

• AWS EBS(Elastic Block Store) 볼륨 추가 및 연결
  
• RAID 0 구성

# 파티션 조회
$ lsblk

# 파일시스템 유형 확인
$ sudo file -s /dev/nvme1n1
/dev/xvdf: data
$ sudo file -s /dev/nvme2n1
/dev/xvdf: data
  - data로 출력 시 디바이스에는 파일시스템이 없는 관계로 파일시스템 생성

# 파티션 확인 후 연결 된 볼륨 파일시스템 생성
$ sudo mkfs -t xfs /dev/nvme1n1
$ sudo mkfs -t xfs /dev/nvme2n1

# 만약 mkfs.xfs 오류 발생 시 패키지 설치 후 재시작
$ sudo yum -y install xfsprogs

# RAID 구성 패키지 설치
$ sudo yum -y install mdadm

# EBS 2개 장착된 스토리지 볼륨을 RAID 0 설정
$ sudo mdadm --create --verbose /dev/md0 --level=0 --name=postgresql_raid --raid-devices=2 /dev/nvme1n1 /dev/nvme2n1

# RAID 1 구성 시 --level=1 로 변경하고 나머지는 동일
$ sudo mdadm --create --verbose /dev/md0 --level=1 --name=mysql_raid --raid-devices=2 /dev/nvme1n1 /dev/nvme2n1

# RAID 파일시스템 포맷(xfs 파일시스템 일 경우)
$ sudo mkfs.xfs -L /dev/md0

# 파티션 조회
$ lsblk 
NAME          MAJ:MIN RM   SIZE RO TYPE  MOUNTPOINT
nvme1n1       259:0    0   500G  0 disk
└─md127         9:127  0 999.8G  0 raid0 /data
nvme2n1       259:1    0   500G  0 disk
└─md127         9:127  0 999.8G  0 raid0 /data
nvme0n1       259:2    0    20G  0 disk
├─nvme0n1p1   259:3    0    20G  0 part  /
└─nvme0n1p128 259:4    0     1M  0 part
  - nvme1n1, nvme2n1 은 raid0으로 1TB를 사용하게 됩니다.(EBS는 2개지만 RAID를 구성하여 2개를 묶음)
  - nvme0n1 은 swapmemory 및 기본 루트볼륨으로 사용할 계획이므로 20G 할당하였습니다.

# RAID 볼륨(md127) UUID 조회
$ sudo blkid /dev/md127
/dev/md127: UUID="f59959c6-adda-4e82-ae94-707d70435af2" TYPE="xfs"

# /etc/fstab 영구 마운트 등록
$ sudo vi /etc/fstab
UUID=f59959c6-adda-4e82-ae94-707d70435af2     /data       xfs    defaults,noatime,discard,nobarrier     0       0

# 마운트
$ sudo mount -a

• Postgresql 12 DB PATH 변경(Master, Slave 동일)

# 기본 DB Path 확인
postgres=# show data_directory;
      data_directory
--------------------------
 /var/lib/psgql/data
(1 row)

$ sudo systemctl stop postgresql-12.service

# 설정파일 수정
$ sudo vi /var/lib/pgsql/data/postmaster.opts
#/usr/bin/postgres "-D" "/var/lib/pgsql/data" <-- 기존내용 주석 처리
/usr/bin/postgres "-D" "/data/postgres/data"  <-- 변경

$ sudo vi /usr/lib/systemd/system/postgresql.service
#Environment=PGDATA=/usr/bin/postgres    <-- 기존내용 주석 처리
Environment=PGDATA=/data/postgres/data/  <-- 변경

# 기존 DB 복제
$ sudo mkdir /data/postgres
$ cp -a /var/lib/pgsql/data /data/postgres/

# 서비스 재설정 및 시작
$ sudo systemctl daemon-reload
$ sudo systemctl start postgresql-12.service

# 변경 된 DB Path 확인
postgres=# show data_directory;
      data_directory
--------------------------
 /data/postgres/data
(1 row)

• Postgresql 12 Streaming Replication Configuration(Master, Slave 구분)

# Master 서버 설정
# replication user 생성
postgres=# create user replication replication password 'pwd';
CREATE ROLE

postgres=# \du
List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 kimdubi    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl      | Replication                                                | {}
 testuser  | No inheritance                                            +| {}
           | Password valid until infinity                              |

# postgresql.conf 수정
$ vi /var/lib/pgsql/12/data/postgresql.conf
wal_level = hot_standby
max_wal_senders = 2
max_replication_slots = 2

# replication slot 생성
$ sudo systemctl restart postgresql-12
$ sudo su postgres
$ psql
postgres=# SELECT * FROM pg_create_physical_replication_slot('repl_slot_01');
\  slot_name   | lsn
--------------+-----
 repl_slot_01 |
(1 row)

# pg_hba.conf 수정
$ vi /var/lib/pgsql/12/data/pg_hba.conf
host    replication    replication    (slave IP)    md5
$ sudo systemctl restart postgresql-12

# Slave 서버 설정
$ sudo su postgres
$ rm -rf /var/lib/pgsql/12/data
$ pg_basebackup -h "Master Private IP" -D /data/postgres/data -U replication -Fp -Xs -P -R
Password:pwd
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
transaction log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
30431/30431 kB (100%), 1/1 tablespace                                         
transaction log end point: 0/2000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
...
$ ls -l /data/postgres/data
drwx------ 20 postgres postgres 4096 12월 24 05:25 data

# Postgresql.conf 설정
$ vi /var/lib/pgsql/12/data/postgresql.conf
hot_standby = on
hot_standby_feedback = on

# Master, Slave 시스템 재부팅
$ sudo systemctl restart postgresql-12

# Master 서버 접속 및 Slave 작동 확인
# psql 접속 후 select 확인
$ sudo su 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
 |          reply_time
------+----------+-------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+-----------
-+-------------------------------
 3672 |    16384 | replication | walreceiver      | 3.35.131.162 |                 |       40176 | 2020-12-24 05:21:23.747091+00 |              | streaming | 0/3000148 | 0/3000148 | 0/3000148 | 0/3000148  |           |           |            |             0 | async
 | 2020-12-24 05:36:21.230106+00
(1개 행)

# postgres login 후 커맨드 확인(Master)
bash-4.2$ psql -x -c "select * from pg_stat_replication"
-[ RECORD 1 ]----+------------------------------
pid              | 3871
usesysid         | 16384
usename          | replication
application_name | walreceiver
client_addr      | xxx.xx.xxx.xx
client_hostname  |
client_port      | 40190
backend_start    | 2020-12-24 05:43:52.33422+00
backend_xmin     | 491
state            | streaming
sent_lsn         | 0/301B3C0
write_lsn        | 0/301B3C0
flush_lsn        | 0/301B3C0
replay_lsn       | 0/301B3C0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2020-12-24 05:56:57.417934+00

# psql 접속 후 recovery 모드 확인
# master 는 "f"
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
 f
(1 row)

# slave 는 "t"
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
 t
(1 row)

• Swap Memory 구성

# Swap file로 Swap Memory 생성
$ sudo dd if=/dev/zero of=/swapfile bs=1M count=10240
$ sudo chmod 600 /swapfile

# Swap 파일 설정
$ sudo mkswap /swapfile

# Swap 파일타입 확인
$ sudo file /swapfile

# Swap 활성화
$ sudo swapon /swapfile

# Swap 파일 자동마운트 등록

$ sudo vi /etc/fstab
/swapfile swap swap defaults 0 0

# Swap 파일 비활성화 후 자동마운트 확인
$ sudo swapoff /swapfile
$ sudo swapon -s
$ sudo swapon -a
$ sudo swapon -s
Filename                                Type            Size    Used    Priority
/swapfile                               file            5242876 0       -2

• Master 서버 Postgresql.conf 설정내역
  

# DISCLAIMER - Software and the resulting config files are provided "AS IS" - IN NO EVENT SHALL
# BE THE CREATOR LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL
# DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION.

# Connectivity
max_connections = 3000
superuser_reserved_connections = 3

# Memory Settings
shared_buffers = '4096 MB'
work_mem = '128 MB'
maintenance_work_mem = '420 MB'
huge_pages = off
effective_cache_size = '11 GB'
effective_io_concurrency = 200   # concurrent IO only really activated if OS supports posix_fadvise function
random_page_cost = 1.25 # speed of random disk access relative to sequential access (1.0)

# Monitoring
shared_preload_libraries = 'pg_stat_statements'    # per statement resource usage stats
track_io_timing=on        # measure exact block IO times
track_functions=pl        # track execution times of pl-language procedures if any

# Replication
wal_level = replica             # consider using at least 'replica'
max_wal_senders = 0
synchronous_commit = on

# Checkpointing:
checkpoint_timeout  = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = '10240 MB'
min_wal_size = '5120 MB'

# WAL writing
wal_compression = on
wal_buffers = -1    # auto-tuned by Postgres till maximum of segment size (16MB by default)
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB

# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0

# Parallel queries:
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 4
parallel_leader_participation = on

# Advanced features
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
jit = on

# General notes:
# Consider using read-only replicas to scale reading
# Note that not all settings are automatically tuned.
#   Consider contacting experts at
#   https://www.cybertec-postgresql.com
#   for more professional expertise.
listen_addresses = '*'