PostgreSQL M/R 구성 셋팅

설치환경

• OS : Ubuntu20.04
• PostgreSQL v14

---

환경셋팅

# 공통 설정 (Master, Replicas)

# 커널 설정 (/etc/sysctl.conf)
vm.overcommit_memory=2
vm.swappiness=10
vm.overcommit_ratio=90

net.core.netdev_max_backlog=30000
net.ipv4.tcp_max_syn_backlog=30000

net.ipv4.tcp_syn_retries=2
net.ipv4.tcp_retries1=2

net.core.rmem_max=268435456
net.core.wmem_max=268435456
net.core.rmem_default=10485760
net.core.wmem_default=10485760
net.ipv4.tcp_rmem=4096 87380 134217728
net.ipv4.tcp_wmem=4096 87380 134217728

net.core.somaxconn=65535
net.ipv4.tcp_fin_timeout=12
vm.max_map_count=262144
net.ipv4.ip_local_port_range=1024 61000
net.ipv4.tcp_max_tw_buckets=540000


# Limits 설정 (/etc/security/limits.conf)
*         hard    nofile      1000000
*         soft    nofile      1000000
root      hard    nofile      1000000
root      soft    nofile      1000000


# DNS 설정 (/etc/netplan/50-cloud-init.yaml or /etc/netplan/02-bondings.yaml)
....
            nameservers:
                addresses: [네임서버IP]
                search: [ 네임서버HOST ]
....

sudo netplan --debug try

# Hostname 설정 (master, replica) - 선택사항
sudo hostnamectl set-hostname psql-master
sudo hostnamectl set-hostname psql-replica1
sudo hostnamectl set-hostname psql-replica2

# Data Directory Volume Mount (master, replica)
sudo lsblk
ls -l /dev/dataVG/dataLV
sudo mkdir /var/lib/postgresql
sudo mount /dev/dm-1 /var/lib/postgresql/
df -Th
/dev/mapper/dataVG-dataLV         xfs        14T  100G   14T   1% /var/lib/postgresql

# PostgreSQL 14 패키지 설치 (default 15버전으로 변경됨)
sudo apt install software-properties-common apt-transport-https wget -y
sudo wget -O- https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list
sudo apt-get update
sudo apt install postgresql-client postgresql-14 -y

# postgresql 15 설치
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main 15" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt-get install postgresql
sudo systemctl enable postgresql --now

systemctl status postgresql
sudo systemctl enable postgresql --now

---

Master 설정 (설정값은 서버의 스펙마다 다름)

# Master 설정
# Postgresql.conf 설정 (/etc/postgresql/14/main/postgresql.conf)

listen_addresses = '*'

shared_buffers = 38GB
work_mem = 128MB
maintenance_work_mem = 2GB
effective_io_concurrency = 300
max_worker_processes = 12
max_parallel_workers_per_gather = 8
max_parallel_workers = 12
max_wal_size = 12GB
min_wal_size = 4GB

max_wal_senders = 3         # replica 숫자보다 1 크게 설정

random_page_cost = 1.5
cpu_tuple_cost = 0.03
cpu_index_tuple_cost = 0.001
cpu_operator_cost = 0.0005
parallel_setup_cost = 0
parallel_tuple_cost = 0
min_parallel_table_scan_size = 0
min_parallel_index_scan_size = 0
effective_cache_size = 64GB

cursor_tuple_fraction = 0.9

max_locks_per_transaction = 64000


# pg_hba.conf 설정 (/etc/postgresql/14/main/pg_hba.conf)

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256  #추가
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     replication     10.10.0.0/16            trust     #추가
host    all             all             0.0.0.0/0               md5       #추가

# Master Replication User 생성
$ sudo -i -u postgres
$ psql
$ CREATE USER replication WITH PASSWORD 'replication' replication;                                  # replication DB 유저 생성

# DB postgres 사용자 패스워드 설정
$ alter user postgres with password 'postgres';

# 재시작
sudo systemctl status postgresql

---

Replica 설정

# Postgresql 설치 진행
# Replica 설정
sudo systemctl stop postgresql                # db 중지
cd /var/lib/postgresql/14
sudo mv main main_backup                      # 기존 data 백업
sudo pg_basebackup -h IP -U replication --checkpoint=fast -D /var/lib/postgresql/14/main/ -R --slot=repl_slot_01 -C          # master 데이터 디렉토리 복사
sudo chown -R postgres:postgres main

$ vi /etc/postgresql/14/main/postgresql.conf
(master 서버와 동일하게 설정 후 아래내용 추가 설정)

max_standby_archive_delay = 900s
max_standby_streaming_delay = 900s

hot_standby_feedback = on


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

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256       # 추가
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     replication     10.10.0.0/16            trust     # 추가
host    all             all             0.0.0.0/0               md5       # 추가


$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql --now

---

마스터 서버에서 확인

sudo -i -u postgres
psql
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           
--------+----------+-------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 448648 |    16384 | replication | 14/main          | IP |                 |       58970 | 2023-03-08 08:16:42.940233+00 |              | streaming | 0/30002E0 | 0/30002E0 | 0/30002E0 | 0/30002E0  |           |           |            |             0 | async      | 2023-03-08 08:40:54.868154+00
(1 row)