DB 인사이드 | PostgreSQL Replication - 구성

 

 

시작하며

본 문서에서는 앞서 설명한 다양한 Replication 방식의 설정에 대해 알아보도록 하겠습니다.

이 중 가장 많이 언급되는 Log Shipping, Streaming, Logical Replication방식으로 환경 구성을 진행하며, 그 과정에서 직접적인 연관이 없거나 발생 가능한 이슈에 대해서는 별도 문서인 PostgreSQL Replication - Trouble Shooting을 통해 다루도록 하겠습니다.

구성을 위한 환경은 다음과 같으며, Replication Slot 관련 설정은 포함되지 않습니다.

Case	Replication 방법	Main Server (10.10.45.230)	Standby Server (10.10.45.231)
1	Log Shipping	CentOS 7.9 / PostgreSQL 14	CentOS 7.9 / PostgreSQL 14
2	Streaming	CentOS 7.9 / PostgreSQL 14	CentOS 7.9 / PostgreSQL 14
3	Logical	CentOS 7.9 / PostgreSQL 14	CentOS 7.9 / PostgreSQL 14

📢 본 문서에서는 PostgreSQL 14 버전을 기준으로 작성되었습니다.
PostgreSQL 12 버전부터 Configuration 파일 구성에 변화가 있었습니다. 14 버전을 기준으로 postgresql.conf 파일을 사용하지만, 12 이전 버전을 사용 중이라면 recovery.conf 파일도 함께 사용해야 합니다. 자세한 설명은 PostgreSQL Replication - Trouble Shooting을 참조하세요.

📢 용어의 통일
PostgreSQL Replication을 설명할 때 여러 단어를 혼용하여 사용하고 있습니다. 본 문서에서는 아래와 같이 단어를 사용합니다.
- Main Server : 운영서버, Primary Server, Base Server, Master Server, Active Server
- Standby Server : 복제서버, Secondary Server, Replication Server, Slave Server

 

Case 1. File-based Log Shipping Replication 설정

Step 1. [Main Server] SSH-KEY 생성 및 Standby Server SSH-KEY 등록

Main Server의 WAL 파일을 SCP를 통해 Standby Server로 전송하기 위해 Main Server에 SSH-KEY를 생성하여 WAL 파일 전송 시 패스워드를 묻지 않도록 합니다.

## Main Server
[postgres@main ~] ssh-keygen
[postgres@main ~] ls -l .ssh
total 8
-rw-------. 1 postgres postgres 1679 Feb  3 16:37 id_rsa
-rw-r--r--. 1 postgres postgres  395 Feb  3 16:37 id_rsa.pub

## Standby Server
[postgres@standby ~] vi .ssh/authorized_keys

📢 ssh-keygen 설정에 대한 내용은 PostgreSQL Replication - Trouble Shooting을 참조하세요.

 

Step 2. [Main Server] postgresql.conf 설정

[postgres@main ~] vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'       # Default = 'localhost'
port = 5432                  # Default = 5432
wal_level = replica          # Default = replica (PostgreSQL 9.6 버전까지는 minimal)
archive_mode = on            # Default = off
archive_command = 'scp %p postgres@10.10.45.231:/var/lib/pgsql/Archive/%f'
                             # WAL File Archive 시 수행할 명령
archive_timeout = 30         # Default = 0 (단위:sec / WAL File(16MB)이 가득 차지 않아도 30초마다 WAL File을 Switching) 
max_wal_senders = 10         # Default = 10 (2이상으로 설정. Standby Server에 전송하기 위한 Sender 1개, pg_basebackup을 위한 1개)

 

Step 3. [Main Server] PostgreSQL Server 재기동

[root@main ~] systemctl restart postgresql-14

 

Step 4. [Standby Server] PostgreSQL Server 종료

[root@standby ~] systemctl stop postgresql-14

 

Step 5. [Standby Server] PostgreSQL Data Directory 삭제

Redhat 계열의 Linux처럼 PostgreSQL 설치 시 initdb 단계를 수행하지 않은 상태라면 Data Directory 삭제하는 작업은 건너뛸 수 있습니다. 하지만 Debian 계열(ex. Ubuntu)의 경우 설치 시 initdb가 자동으로 수행되기 때문에 PostgreSQL Data Directory를 삭제해야 합니다.

## Redhat 기본 Data Directory 삭제
[root@standby ~] rm -rf /var/lib/pgsql/14/data/*

📢 Ubuntu 기본 Data Directory는 /var/lib/postgresql/{MAJOR_VERSION}/main 입니다.

 

Step 6. [Standby Server] Main Server Data Directory 복사

Main Server의 Data Directory를 Standby Server로 복사합니다. pg_basebackup 응용프로그램을 통한 복사도 가능하지만, 해당 방식은 Streaming Replication에서 설명할 예정이므로, 본 Case에서는 OS 명령어를 통한 복사방법을 알아보겠습니다.

방법 1) Main Server 종료 상태에서 Data Directory 복사(Cold Backup)

Main Server에서 사용 중인 Data Directory를 압축한 후 Standby Server로 전송합니다. Database 크기에 따라 압축 및 전송 시간이 달라질 수 있습니다. Data Directory를 압축할 때에는 반드시 Database가 중지되어 있어야 합니다.

## Main Server 중지
[root@main ~] systemctl stop postgresql-14

## Main Server Data Directory 압축
[postgres@main 14] cd /var/lib/pgsql/14/
[postgres@main 14] tar -cvf Data.tar ./data

## Main Server 기동
[root@main ~] systemctl start postgresql-14

## Standby Server로 압축파일 전송
## Step 1.에서 SSH-KEYGEN을 하였기 때문에 scp 사용시 비밀번호 필요 없음.
[postgres@main 14] scp /var/lib/pgsql/14/Data.tar postgres@10.10.45.231:/var/lib/pgsql/14/

## Standby Server - 압축 해제
[postgres@standby 14] cd /var/lib/pgsql/14
[postgres@standby 14] tar -xvf Data.tar

 

방법 2) Main Server 기동 상태에서 Data Directory 복사(Hot backup)

Main Server가 기동 상태에서 Data Directory를 복사하려면 pg_start_backup과 pg_stop_backup함수를 사용해야 합니다. pg_start_backup 수행 시 생성되는 backup_label 파일을 참조하면 Standby Server에서 WAL 파일을 이용한 복구시점을 알 수 있습니다.

pg_start_backup과 pg_stop_backup 사이에 발생한 변경내역들을 온전히 Standby Server에 적용하기 위해서는 WAL 파일의 유실에 주의해야 합니다. 본 Case는 Step 2. 에서 설정한 archive_command를 통해 Main Server에서 생성된 WAL 파일이 Standby Server로 전송되게 설정하였으므로 WAL 파일의 유실이 없습니다.

-- Main Server pg_start_backup() 수행
SELECT pg_start_backup('backup');
 pg_start_backup
-----------------
 0/34000028
(1 row)

## Main Server Data Directory 압축 및 Standby Server로 전송
[postgres@main 14] cd /var/lib/pgsql/14
[postgres@main 14] tar -zcvf data.tar data
[postgres@main 14] scp /var/lib/pgsql/14/data.tar postgres@10.10.45.231:/var/lib/pgsql/14

-- Main Server pg_stop_backup() 수행
SELECT pg_stop_backup();
NOTICE:  all required WAL segments have been archived
 pg_stop_backup
----------------
 0/36000088
(1 row)

## Standby Server Data Directory 압축해제
[postgres@standby 14] cd /var/lib/pgsql/14
[postgres@standby 14] tar -xvf data.tar

📢 Online 상태에서 Data Directory 백업에 대한 설명은 PostgreSQL Replication - Trouble Shooting에서 상세 내용을 확인할 수 있습니다.

 

Step 7. [Standby Server] signal 파일 생성

Data Directory 복사가 완료된 후 Standby Server로 사용하기 위하여 PostgreSQL Data Directory에 standby.signal 파일을 생성합니다.

[postgres@standby ~] touch /var/lib/pgsql/14/data/standby.signal

 

Step 8. [Standby Server] postgresql.conf 설정

Main Server에서 전달받은 WAL 파일을 복원하기 위해 추가적으로 파라미터를 설정합니다. 파라미터 위치는 PostgreSQL 버전에 따라 recovery.conf 또는 postgresql.conf 일수 있습니다.

📢 recovery.conf 파일에 대한 설명은 PostgreSQL Replication - Trouble Shooting에서 파라미터 내용을 확인할 수 있습니다.

postgreSQL 11 버전 이하(recovery.conf)

## PostgreSQL 11 버전 이하 recovery.conf 설정
[postgres@standby ~] vi /var/lib/pgsql/14/data/postgresql.conf
restore_command = 'cp /var/lib/pgsql/Archive/%f %p'
standby_mode = on   ## Standby Mode로 운영 할 것인지 여부

 

PostgreSQL 12 버전 이상(postgresql.conf)

## PostgreSQL 12 버전 이상 postgresql.conf 설정
[postgres@standby ~] vi /var/lib/pgsql/14/data/postgresql.conf
restore_command = 'cp /var/lib/pgsql/Archive/%f %p'
hot_standby = on    ## Standby Mode 운영 시 Database 접속을 허용할 것인지 여부

 

Step 9. [Standby Server] Archive Directory 생성

Main Server에서 전달받은 WAL 파일을 보관하기 위한 Directory를 생성합니다.

[postgres@standby ~] mkdir /var/lib/pgsql/Archive

 

Step 10. [Standby Server] PostgreSQL Server 기동

[root@standby ~] systemctl start postgresql-14

---

 

Case 2. Streaming Replication 설정

Step 1. [Main Server] postgresql.conf 수정

## postgresql.conf 수정
[postgres@main ~] vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'       # Default = 'localhost'
port = 5432                  # Default = 5432
wal_level = replica          # Default = replica (PostgreSQL 9.6 버전까지는 minimal)

 

Step 2. [Main Server] Replication User 생성

CREATE USER replicauser REPLICATION ;

📢 CREATE ROLE로 Role(User) 생성 시 Cannot login옵션이 부여되기 때문에, "CREATE ROLE username REPLICATION LOGIN"로 생성하거나, Role 생성 후 LOGIN 권한을 추가적으로 부여해야 합니다. 보다 자세한 내용은 PostgreSQL Replication - Trouble Shooting에서 확인할 수 있습니다.

 

Step 3. [Main Server] pg_hba.conf 수정

## 10.10.45.231에서 replicauser가 로그인하고 WAL Recode를 Streaming 할 수 있도록 허용.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicauser     10.10.45.231/24         trust

📢 pg_hba.conf를 수정하지 않으면 Streaming Replication 구성을 할 수 없습니다. 보다 자세한 내용은 PostgreSQL Replication - Trouble Shooting에서 확인할 수 있습니다.

 

Step 4. [Main Server] PostgreSQL Server 재기동

[root@main ~] systemctl restart postgresql-14

 

Step 5. [Standby Server] PostgreSQL Server 종료

[root@standby ~] systemctl stop postgresql-14

 

Step 6. [Standby Server] PostgreSQL Data Directory 삭제

Redhat 계열의 Linux처럼 PostgreSQL 설치 시 initdb단계를 수행하지 않은 상태라면 Data Directory 삭제하는 작업은 건너뛸 수 있습니다. 하지만 Debian 계열(ex. Ubuntu)의 경우 설치 시 initdb가 자동으로 수행되기 때문에 PostgreSQL Data Directory를 삭제해야 합니다.

## Redhat 기본 Data Directory 삭제
[root@standby ~] rm -rf /var/lib/pgsql/14/data/*

📢 Ubuntu 기본 Data Directory는 /var/lib/postgresql/{MAJOR_VERSION}/main입니다.

 

Step 7. [Standby Server] Main Server Data Directory 복사

Main Sever의 Data Directory를 복사합니다. 이때, Case 1에서 사용한 OS 명령어를 통한 복사가 아닌 pg_basebackup 응용프로그램을 사용합니다.

-R 옵션 사용 시 postgresql.auto.conf에 primary_conninfo 파라미터와 standby.signal 파일이 자동으로 생성됩니다.

[postgres@standby ~] /usr/pgsql-14/bin/pg_basebackup \
 --host=10.10.45.230 --username=replicauser --checkpoint=fast \
 --pgdata=/var/lib/pgsql/14/data/ -R

[postgres@standby ~] cat /var/lib/pgsql/14/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicauser passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=10.10.45.230 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

Option	설명
-h (--host)	Main Server IP
-U (--username)	Replication DB User
--checkpoint=fast	빠른 Checkpoint 수행
-D (--pgdata)	Standby Server에서 Data Directory로 사용할 경로 지정
-R (--write-recovery-conf)	Standby Server 설정을 자동으로 수행(standby.signal 파일 생성)
-S (--slot)	Replication에 사용할 Slot 이름 지정

 

Step 8. [Standby Server] PostgreSQL Server 기동

[root@standby ~] systemctl start postgresql-14

 

📢 Log Shipping(Case 1)과 Streaming(Case 2)을 혼합한 Replication 구성도 가능합니다.
Log Shipping Replication에서 Data Lag가 발생하는 단점을 Streaming 방식을 통해 보완할 수 있으며, WAL 파일이 삭제되어 Streaming 할 수 없을 때 Replication이 종료되는 문제점을 Log Shipping 방식을 사용함으로써 상호 보완할 수 있습니다.

---

 

CASE 3. Logical Replication 설정

Step 1. [Main Server] postgresql.conf 설정

[root@main ~] vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'       # Default = 'localhost'
port = 5432                  # Default = 5432
wal_level = logical          # Default = replica (PostgreSQL 9.6 버전까지는 minimal)

📢 wal_level을 logical로 설정하면, Replica 레벨에서 수집하는 정보에 추가적으로 Logical Decoding을 지원하기 위한 정보까지 추가됩니다. 즉, logical 설정 시 저장하는 WAL 파일이 많이 생성되므로 디스크 여유공간 확인이 필요합니다.

 

Step 2. [Main Server] Replication 전용 DB User 생성

CREATE ROLE repluser REPLICATION LOGIN PASSWORD 'repluser' ;

 

Step 3. [Main Server] pg_hba.conf 설정

[postgres@main ~] vi /var/lib/pgsql/14/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             repluser        10.10.45.231/24         trust

 

Step 4. [Main Server] PostgreSQL 재기동

[root@main ~] systemctl restart postgresql-14

 

Step 5. 게시(Publication), 구독(Subscription) 설정

여기까지 Main Server에서 설정하는 것으로 Logical Replication 준비는 완료되었다고 할 수 있습니다. 이제 게시자(Publisher, Main)는 Replication 대상들을 게시물(Publication)로 생성하고 구독자(Subscriber, Standby)는 구독물(Subscription)을 생성하여 Replication을 구성할 수 있습니다. 각 Server에서는 아래의 절차를 수행해야 하며, 예시를 통하여 Replication 구성을 해보도록 하겠습니다.

Main Server (Publisher, Publication)	Standby Server (Subscriber, Subscription)
1. Replication 대상 선정	1. Replication 대상 Table 생성
2. Publication 생성	2. Subsctiption 생성
3. DB User 권한부여

 

[Main Server] Replication 대상 선정

Replication 대상 Table은 하나 이상이 될 수 있습니다. 이미 존재하는 Table을 선정할 수도 있고, 새로 생성되는 Table을 선정할 수도 있습니다. 예시에서는 Replication 대상 테이블을 새로 생성하여 진행합니다.

CREATE TABLE replication_table_01 ( id INTEGER PRIMARY KEY , string VARCHAR ) ;
CREATE TABLE replication_table_02 ( id INTEGER PRIMARY KEY , string VARCHAR ) ;

 

[Main Server] Publication 생성

Replication 대상으로 선정된 Table을 포함하도록 Publication을 생성합니다. Publication 생성 시 INSERT, UPDATE, DELETE, TRUNCATE 중 특정 Operation에 대해서만 Replication이 동작하도록 지정할 수 있습니다. 예시에서는 위 4가지 케이스에 대해 모두 Replication이 동작하도록 지정합니다.

CREATE PUBLICATION my_publication FOR TABLE replication_table_01 , replication_table_02 ;

📢 INSERT만 포함하는 Publication 생성예시
CREATE PUBLICATION my_publication FOR TABLE table01 WITH ( publish = 'insert' )

 

[Main Server] DB User 권한 부여

GRANT SELECT ON TABLE replication_table_01 TO repluser ;
GRANT SELECT ON TABLE replication_table_02 TO repluser ;

 

[Standby Server] Replication 대상 Table 생성

Logical Replication에서 DDL은 복제가 되지 않기 때문에 Main Server의 Replication 대상 Table을 미리 생성해야 합니다. Table 생성 시 게시물(Publication) 보다 많은 Column의 정의는 가능하지만, Replication 되는 Column의 Type과 이름은 동일하게 지정해야 합니다. 예시에서는 string2 Column을 추가로 정의하였습니다.

CREATE TABLE replication_table_01 ( id INTEGER PRIMARY KEY , string VARCHAR , string2 VARCHAR ) ;
CREATE TABLE replication_table_02 ( id INTEGER PRIMARY KEY , string VARCHAR , string2 VARCHAR ) ;

📢 pg_dump를 통하여 Standby Server에 Table 구조를 생성할 수 있습니다. 또한, 테이블에 관한 인덱스도 같이 생성됩니다. pg_dump -s -t replication_table_01 -t replication_table_02 | psql -h 10.10.45.231

 

[Standby Server] Subscription 생성

CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=postgres host=10.10.45.230 port=5432 user=repluser password=repluser' PUBLICATION my_publication ;
NOTICE:  created replication slot "my_subscription" on publisher

📢 Replication Slot을 따로 생성하지 않은 경우 Subscription이 생성되면서 Main Server에 Subscription 이름으로 Replication Slot이 생성됩니다. 기본 Output 옵션은 pgoutput입니다.

 

 

 

기획 및 글 | DB기술기획팀