PostgreSQL 13.7 Replication 설정 (RockyLinux 9.0)

본 매뉴얼에서는 PostgreSQL 동기화 방식중 마스터 서버 로그를 이용해 동기화 하는 Streaming 방식으로 진행합니다.

이 방식은 단점이 하나 있는데 마스터 서버와 슬레이브 서버간의 네트워크 단절 시간이 길어지면 마스터 서버의 WAL 파일이 덮어 씌워져 (구버전에서는 파일 개수 지정, 신버전에서는 용량 지정 가능) 슬레이브 서버로 데이터를 전송하기도 전에 옛날 데이터는 지워져 버리는 경우가 생길 수 있습니다. 이경우 다시 Replication 을 구성해야 하지만 Streaming 방식은 9.0 이상에서 지원되는 최신 기술이고 속도가 빠른 장점이 있어 본 매뉴얼의 동기화 방식으로 채택하였습니다. 일반적으로 많이 사용하는 방식 이기도 합니다.

추가 정보는 공식 홈페이지 Documents (https://www.postgresql.org/docs/) 를 참고해 주세요.

버전에 따라 옵션 이름이 다른 경우가 있으므로 해당 버전의 Document 를 찾아서 보는것이 좋습니다.

여기에서는 최소한의 옵션을 이용하여 동기화를 해보도록 하겠습니다.

 

 

[ 테스트 환경 ]

- OS : RockyLinux 9.0

- 서버 수량 : 2대 (마스터 192.168.1.2 / 슬레이브 192.168.1.3)

- 사용 버전 : PostgreSQL 13.7

 

* 데이터 안전을 위해 작업전 백업은 필수 입니다.

 

 

1. PostgreSQL 설치

 

(모든 서버에서)

# yum -y update

# yum -y install postgresql postgresql-server

 

부팅시 데몬이 자동 구동 되도록 허용합니다.

# systemctl enable postgresql

 

PostgreSQL 설치시 자동 생성된 postgres 계정으로 데이터베이스를 초기화 합니다.

# sudo -u postgres /usr/bin/initdb -D /var/lib/pgsql/data

 

초기화가 완료 되었으면 데몬을 시작합니다.

# systemctl start postgresql

 

 

2. 테스트 DB 생성 및 데이터 입력

 

(마스터 서버에서)

postgres 계정으로 postgresql 에 접속합니다.

# sudo su - postgres

$ psql
psql (13.7)
Type "help" for help.

postgres=# 

 

테스트를 위해 임의의 데이터베이스, 테이블을 생성하고 데이터를 입력합니다.

postgres=# CREATE DATABASE sysdocudb;

postgres=# CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);

postgres=# INSERT INTO users (name, email) VALUES ('CDH', 'cdh@tistory.com'), ('CJE', 'cje@tistory.com');

 

입력된 데이터를 확인합니다.

postgres=# SELECT * FROM users;

 id | name |      email      
----+------+-----------------
  1 | CDH  | cdh@tistory.com
  2 | CJE  | cje@tistory.com
(2 rows)

 

PostgreSQL 명령행을 빠져나갈 경우 아래와 같이 실행합니다.

postgres=# \q

 

 

3. 계정 생성

 

(마스터 서버에서)

복제를 위해 데이터베이스에 접근 할 수 있는 Replication 전용 계정을 생성합니다.

postgres=# CREATE ROLE repluser WITH REPLICATION PASSWORD '12345678' LOGIN;

 

생성된 계정을 확인합니다.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repluser  | Replication                                                | {}

 

 

4. 마스터 서버 설정

 

PostgreSQL 설정 파일에서 생성한 계정의 권한을 줍니다.

root 사용자로 전환 후 아래 파일에 인증 및 권한 설정을 추가합니다.

# vi /var/lib/pgsql/data/pg_hba.conf

... host    all             all             0.0.0.0/0               trust host    replication    repluser    192.168.1.3/32    mp5

- 첫번째줄 : 모든 IP 에서 원격 접근 허용

- 두번째줄 : 위에서 생성했던 replication 전용 계정과 접근 허용할 슬레이브 서버 IP

 

다른 설정 파일에서 복제 관련 옵션을 아래 값으로 설정합니다.

# vi /var/lib/pgsql/data/postgresql.conf

... listen_addresses = '*' wal_level = replica ...

- listen_addresses : 접속을 허용할 서버 아이피 주소 (client 아이피 아님 주의)

- wal_level : Replication의 레벨을 설정합니다. replica로 설정하면 replication을 위한 모든 WAL 데이터를 생성합니다.

 

설정 적용을 위해 postgresql 데몬을 재시작 합니다.

# systemctl restart postgresql

또는 postgres 계정으로 데몬을 재시작

# sudo su - postgres
$ pg_ctl reload
server signaled

 

 

5. 슬레이브 서버 설정

 

(슬레이브 서버에서)

마스터 서버에 데이터를 한차례 전부 가져와야 합니다.

아래는 명령 수행 전 확인사항입니다.

- 슬레이브 서버의 설정 파일 백업 (postgresql.conf)

   # cp -arp /var/lib/pgsql/data/postgresql.conf /root/

- 슬레이브 서버의 data 디렉토리는 비워 놓기

   # rm -rf /var/lib/pgsql/data/*

- 마스터 서버에서 방화벽 확인 (TCP 5432)

 

모두 확인 되었으면 postgres 계정으로 데이터 복사 명령을 수행합니다.

# sudo -u postgres pg_basebackup -h 192.168.1.2 /var/lib/pgsql/data -U repluser -v -P --wal-method=stream --write-recovery-conf

 

별도로 백업했었던 설정 파일을 복원하고 내용을 수정합니다.

data 디렉토리를 복사해왔기 때문에 conf 파일도 바뀌었기 때문입니다.

# cp -arp /root/postgresql.conf /var/lib/pgsql/data/

# vi /var/lib/pgsql/data/postgresql.conf

... primary_conninfo = 'host=192.168.1.2 port=5432 user=repluser password=12345678' ...

- primary_conninfo : 마스터 서버에 접근하기 위한 replication 계정 정보

 

# systemctl restart postgresql

 

아래 파일이 자동 생성되며 동기화가 시작됩니다.

/var/lib/pgsql/data/standby.signal

 

마스터 서버에서 insert 쿼리를 실행하면 슬레이브 서버에서도 데이터 확인이 가능합니다.

마스터 서버에서 상태값 호출로 확인하는 방법도 있습니다.

# SELECT * FROM pg_stat_activity where usename='repluser';
 datid | datname |  pid   | leader_pid | usesysid | usename  | application_name |  client_addr   | client_hostname | client_port |         backend_start         | xact_start |          query_start       
   |         state_change          | wait_event_type |  wait_event   | state  | backend_xid | backend_xmin |                  query                  | backend_type 
-------+---------+--------+------------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+------------+----------------------------
---+-------------------------------+-----------------+---------------+--------+-------------+--------------+-----------------------------------------+--------------
       |         | 109299 |            |    16393 | repluser | walreceiver      | 192.168.1.3 |                 |       38854 | 2023-03-13 16:55:25.630835+09 |            | 2023-03-13 16:55:25.644428+
09 | 2023-03-13 16:55:25.644449+09 | Activity        | WalSenderMain | active |             |              | START_REPLICATION 0/12000000 TIMELINE 1 | walsender
(1 row)