Linux 7.9 에 PostgreSQL 15 이중화(HA) 설치 가이드

본 문서는 Streaming Replication 방식으로 PostgreSQL 이중화 구성(설치)에 대한 내용입니다.

 

설치 환경

 

1. OS : CentOS Linux Release 7.9 2009

2. DB : PostgreSQL 15.3

3. IP & Hostname

Primary Server IP & Hostname	Standby Server IP & Hostname
192.168.0.148            ispg1	192.168.0.158             ispg2

 

* 이중화 구성 위해 Primary와 Standby Server에 설치될 Postgresql 버전은 반드시 동일해야 합니다.

# -- root 계정에서 수행

$ -- postgres 계정에서 수행

 

 

 

PostgreSQL HA 설치(구성) 방법

※   Primary  는 1번 노드에서 수행,  Standby  는 2번 노드에서 수행, 둘 다 있으면 두 노드에서 작업 수행하시면 됩니다.

 

 

/etc/hosts 설정  Primary   Standby 

# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

## Primary PG IP
192.168.0.148 ispg1

## Standby PG IP
192.168.0.158 ispg2

 

 

필수 패키지 설치  Primary   Standby 

# yum -y install gcc gcc-c++ make autoconf readline readline-devel zlib zlib-devel openssl openssl-devel gettext-devel python python-devel perl

 

 

방화벽 비활성화  Primary   Standby 

# systemctl stop firewalld
# systemctl disable firewalld

 

 

Selinux disable  Primary   Standby 

# vi /etc/selinux/config
..(생략)..
SELINUX=disabled
..(생략)..

 

 

Resource Limit 설정  Primary   Standby 

# vi /etc/security/limits.conf
..(생략)..
postgres soft nofile 1024
postgres hard nofile 65536
# End of file

 

 

커널 파라미터 값 설정, 적용  Primary   Standby 

# vi /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

# sysctl -p

 

 

재부팅  Primary   Standby 

# reboot

 

그룹 및 계정 생성  Primary   Standby 

# groupadd dba
# useradd -g dba -G dba postgres
# passwd postgres

 

 

설치할 디렉토리 경로 생성 및 권한 부여  Primary   Standby 

# mkdir -p /postgres/app/postgres/pgsql15
# chown -R postgres:dba /postgres
# chmod -R 755 /postgres

 

 

postgres 계정 환경변수 설정  Primary   Standby 

# su - postgres
$ vi ~/.bash_profile
export PG_HOME=/postgres/app/postgres/pgsql15
export PGDATA=$PG_HOME/data
export PGLIB=$PG_HOME/lib
export PATH=$PG_HOME/bin:$PATH
export LD_LIBRARY_PATH=/postgres/app/postgres/lib

$ source ~/.bash_profile

 

 

PostgreSQL 15 설치  Primary   Standby 

** 설치파일은 Postgresql 공식홈페이지에서 source file로 다운로드 후 각 서버에 업로드**

설치파일 다운로드 링크 => https://www.postgresql.org/ftp/source/v15.3/

$ cd /home/postgres/
$ tar -zxvf postgresql-15.3.tar.gz
$ cd postgresql-15.3/
$ ./configure --prefix=/postgres/app/postgres/pgsql15 --enable-depend --enable-nls=utf-8 --with-python
$ make
$ make check
$ make install

 

 

Database 생성  Primary   Standby 

$ cd /postgres/app/postgres/pgsql15/bin
$ initdb -E utf-8 --locale=en_US.UTF-8 -D /postgres/app/postgres/pgsql15/data

 

 

Archive WAL File 저장 디렉토리, DB Log 디렉토리 경로 생성  Primary   Standby 

$ cd $PG_HOME
$ mkdir pg_log
$ mkdir pg_arch

 

 

postgresql DB 파라미터 설정  Primary 

* postgresql.conf 파일 내용 맨 하단에 작성

$ cd $PGDATA
$ vi postgresql.conf
..(생략)..
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
cluster_name = 'primary_db'
listen_addresses = '192.168.0.148'
port = 5001
max_connections = 100              
superuser_reserved_connections = 10
shared_buffers = 4GB
temp_buffers = 8MB
work_mem = 8MB
maintenance_work_mem = 1GB
wal_buffers = 120MB
effective_cache_size = 1GB
default_statistics_target = 100
autovacuum_vacuum_scale_factor = 0.0
autovacuum_vacuum_threshold = 20000
wal_level = replica
min_wal_size = 1GB
max_wal_size = 1GB
wal_log_hints = on
archive_mode = on
archive_command = 'cp %p /postgres/app/postgres/pgsql15/pg_arch/%f'
archive_timeout = 120
max_wal_senders = 10
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = '/postgres/app/postgres/pgsql15/pg_log/'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 43200
log_rotation_size = 0
password_encryption = 'scram-sha-256'

# Add settings for extensions here

 

 

 

DB 기동  Primary 

$ /postgres/app/postgres/pgsql15/bin/pg_ctl -D /postgres/app/postgres/pgsql15/data/ start

 

 

postgres 관리자 계정 초기 비밀번호 설정  Primary 

$ psql -p 5001
postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:

 

 

이중화 구성용 계정 생성  Primary 

postgres=# create user hauser replication login encrypted password 'password0' connection limit -1;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 hauser    | Replication                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

 

 

pg_hba.conf 설정  Primary 

$ vi pg_hba.conf
..(생략)..
# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
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     postgres                                trust
host    replication     hauser          192.168.0.148/0         trust
host    replication     hauser          192.168.0.158/0         trust

 

 

DB reload  Primary 

$ /postgres/app/postgres/pgsql15/bin/pg_ctl -D /postgres/app/postgres/pgsql15/data/ reload

 

 

Standby 서버 기존 data 디렉토리 백업, 새 data 디렉토리 생성  Standby 

$ cd $PG_HOME
$ mv data/ data_old
$ mkdir data
$ chmod -R 750 data/

 

 

Standby 서버 DB 복제  Standby 

$ cd $PG_HOME/bin/
$ ./pg_basebackup -h 192.168.0.148 -p 5001 -D /postgres/app/postgres/pgsql15/data -U hauser -v -P -R -X stream
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_44245"
22355/22355 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/7000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

 

 

Standby DB 복제 확인  Standby 

standby.signal <-- DB 서버에 Standby DB라는 신호를 보내주는 파일

$ cd $PGDATA
$ ls
PG_VERSION        global         pg_logical    pg_snapshots  pg_twophase           standby.signal
backup_label      pg_commit_ts   pg_multixact  pg_stat       pg_wal
backup_manifest   pg_dynshmem    pg_notify     pg_stat_tmp   pg_xact
base              pg_hba.conf    pg_replslot   pg_subtrans   postgresql.auto.conf
current_logfiles  pg_ident.conf  pg_serial     pg_tblspc     postgresql.conf

$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=hauser passfile=''/home/postgres/.pgpass'' channel_binding=disable host=192.168.0.148 port=5001 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

 

 

Standby DB postgresql.conf 파일 수정  Standby 

---

cluster_name :: primary_db ===> standby_db 변경

listen_addresses :: 192.168.0.148 ===> 192.168.0.158 변경 

archive_mode, archive_command, archive_timeout 주석(#) 처리 - Standby DB에서 WAL File이 떨어지지 않게끔 설정

$ vi postgresql.conf
..(생략)..
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
cluster_name = 'standby_db'
listen_addresses = '192.168.0.158'
port = 5001
max_connections = 100              
superuser_reserved_connections = 10
shared_buffers = 4GB
temp_buffers = 8MB
work_mem = 8MB
maintenance_work_mem = 1GB
wal_buffers = 120MB
effective_cache_size = 1GB
default_statistics_target = 100
autovacuum_vacuum_scale_factor = 0.0
autovacuum_vacuum_threshold = 20000
wal_level = replica
min_wal_size = 1GB
max_wal_size = 1GB
wal_log_hints = on
# archive_mode = on
# archive_command = 'cp %p /postgres/app/postgres/pgsql15/pg_arch/%f'
# archive_timeout = 120
max_wal_senders = 10
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = '/postgres/app/postgres/pgsql15/pg_log/'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 43200
log_rotation_size = 0
password_encryption = 'scram-sha-256'

# Add settings for extensions here

 

 

Standby DB 기동  Standby 

$ /postgres/app/postgres/pgsql15/bin/pg_ctl -D /postgres/app/postgres/pgsql15/data/ start

 

 

DB Process 확인  Primary 

$ ps -ef | grep postgres
postgres 1798450       1  0 13:58 ?        00:00:05 /postgres/app/postgres/pgsql15/bin/postgres -D /postgres/app/postgres/pgsql15/data
postgres 1766671 1766651  0 13:58 ?        00:00:00 postgres: primary_db: logger
postgres 1766672 1766651  0 13:58 ?        00:00:00 postgres: primary_db: checkpointer
postgres 1766673 1766651  0 13:58 ?        00:00:00 postgres: primary_db: background writer
postgres 1766675 1766651  0 13:58 ?        00:00:00 postgres: primary_db: walwriter
postgres 1766676 1766651  0 13:58 ?        00:00:00 postgres: primary_db: autovacuum launcher
postgres 1766677 1766651  0 13:58 ?        00:00:00 postgres: primary_db: archiver last was 000000090000000000000095
postgres 1766678 1766651  0 13:58 ?        00:00:00 postgres: primary_db: logical replication launcher
postgres 1766679 1766651  0 13:58 ?        00:00:00 postgres: primary_db: walsender hauser 192.168.0.158(55318) streaming 0/96000000
postgres 1768834 1766512  0 17:19 pts/0    00:00:00 grep --color=auto postgres

 

 

DB Process 확인2  Standby 

$ ps -ef | grep postgres
postgres 1226863       1  0 13:59 ?      00:00:05 /postgres/app/postgres/pgsql15/bin/postgres -D /postgres/app/postgres/pgsql15/data
postgres 1226863 1226862  0 13:59 ?      00:00:00 postgres: standby_db: logger
postgres 1226864 1226862  0 13:59 ?      00:00:04 postgres: standby_db: checkpointer
postgres 1226865 1226862  0 13:59 ?      00:00:08 postgres: standby_db: background writer
postgres 1226866 1226862  0 13:59 ?      00:00:15 postgres: standby_db: startup recovering 000000090000000000000095
postgres 1510147 1226862  0 13:59 ?      00:00:10 postgres: standby_db: walreceiver streaming 0/96000000
postgres 1512022 1511875  0 03:19 pts/1    00:00:00 grep --color=auto postgres

 

이중화 구성 확인  Primary 

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 44342
usesysid         | 16388
usename          | hauser
application_name | standby_db
client_addr      | 192.168.0.158
client_hostname  |
client_port      | 45828
backend_start    | 2023-07-26 16:59:55.23635+09
backend_xmin     |
state            | streaming
sent_lsn         | 0/9000000
write_lsn        | 0/9000000
flush_lsn        | 0/9000000
replay_lsn       | 0/9000000
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-07-26 17:00:55.791514+09

 

 

이중화 구성 확인2  Standby 

$ cd $POSTGRES_HOME/data/pg_log
$ vi postgresql-2023-07-26.log
2023-07-26 16:59:55.180 KST [44076] LOG:  starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
2023-07-26 16:59:55.180 KST [44076] LOG:  listening on IPv4 address "192.168.0.158", port 5001
2023-07-26 16:59:55.196 KST [44076] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5001"
2023-07-26 16:59:55.203 KST [44080] LOG:  database system was interrupted; last known up at 2023-07-26 16:51:07 KST
2023-07-26 16:59:55.227 KST [44080] LOG:  entering standby mode
2023-07-26 16:59:55.230 KST [44080] LOG:  redo starts at 0/7000028
2023-07-26 16:59:55.232 KST [44080] LOG:  consistent recovery state reached at 0/7000138
2023-07-26 16:59:55.232 KST [44076] LOG:  database system is ready to accept read-only connections
2023-07-26 16:59:55.267 KST [44081] LOG:  started streaming WAL from primary at 0/8000000 on timeline 1

 

 

데이터 동기화 확인 (테스트용 테이블 생성)  Primary 

$ psql -p 5001
Password for user postgres:
psql (15.3)
Type "help" for help.

postgres=# create table ha_config(id numeric, product_name varchar(10), created_time timestamp);
CREATE TABLE
postgres=# insert into ha_config values (1, 'test1',now());
INSERT 0 1
postgres=# insert into ha_config values (2, 'test2',now());
INSERT 0 1
postgres=# insert into ha_config values (3, 'test3',now());
INSERT 0 1
postgres=# insert into ha_config values (4, 'test4',now());
INSERT 0 1
postgres=# select * from ha_config;
 id | product_name |        created_time
----+--------------+----------------------------
  1 | test1        | 2023-11-22 17:22:22.158605
  2 | test2        | 2023-11-22 17:22:22.161428
  3 | test3        | 2023-11-22 17:22:22.162751
  4 | test4        | 2023-11-22 17:22:22.892752
(4 rows)

 

 

데이터 동기화 확인2 (테스트용 테이블 조회)  Standby 

* Streaming Replication HA 구성 시, StandbyDB는 Read-Only 용 DB로 사용 가능 

postgres=# select * from ha_config;
 id | product_name |        created_time
----+--------------+----------------------------
  1 | test1        | 2023-11-22 17:22:22.158605
  2 | test2        | 2023-11-22 17:22:22.161428
  3 | test3        | 2023-11-22 17:22:22.162751
  4 | test4        | 2023-11-22 17:22:22.892752
(4 rows)

 

PostgreSQL HA 설치 완료

 

 

PostgreSQL HA 수동 Failover , Failback 방법

https://checktech.tistory.com/3

PostgreSQL 수동 Fail-over, Fail-back 방법