PostgreSQL 9.6의 마스터-슬레이브 복제를 위한 설정 방법 (Ubuntu 16.04)

PostgreSQL 또는 Postgres는 15년 이상의 활발한 개발을 가진 오픈 소스 객체-관계형 데이터베이스 관리 시스템(ORDBMS)입니다. 강력한 데이터베이스 서버로서 높은 작업 부하를 처리할 수 있습니다. PostgreSQL은 Linux, Unix, BSD 및 Windows 서버에서 사용할 수 있습니다.

마스터/슬레이브 데이터베이스 복제는 한 서버(마스터)의 데이터베이스에서 다른 서버(슬레이브)의 데이터베이스로 데이터를 복사(동기화)하는 과정입니다. 이 과정의 주요 이점은 데이터베이스를 여러 머신에 분산시켜 마스터 서버에 문제가 발생할 경우 동일한 데이터를 가진 백업 머신이 요청을 중단 없이 처리할 수 있도록 하는 것입니다.

PostgreSQL은 데이터베이스를 복제하는 여러 가지 방법을 제공합니다. 백업 목적으로 사용되며 고가용성 데이터베이스 서버를 제공할 수 있습니다. 이 튜토리얼에서는 Ubuntu 16.04 서버에서 PostgreSQL 9.6 마스터-슬레이브 복제를 설치하고 구성하는 방법을 보여줍니다. 우리는 핫 스탠바이 모드를 사용할 것이며, 이는 PostgreSQL을 깊이 있게 배우기 위한 매우 좋은 출발점입니다.

우리가 할 일:

PostgreSQL 9.6 설치.
UFW 방화벽 구성.
MASTER 서버 구성.
SLAVE 서버 구성.
MASTER에서 SLAVE로 PostgreSQL 데이터 복사.
테스트.

전제 조건:

1 Ubuntu 16.04 서버 - MASTER - 읽기 및 쓰기 허용 - IP: 10.0.15.10
1 Ubuntu 16.04 서버 - SLAVE - 읽기 전용 - IP: 10.0.15.11
루트 권한

1단계 - PostgreSQL 9.6 설치

이 튜토리얼에서는 PostgreSQL 9.6의 최신 버전을 설치합니다. 공식 Ubuntu 저장소에서는 PostgreSQL 9.5만 제공하므로 PostgreSQL 저장소에서 직접 최신 버전을 설치해야 합니다.

postgreSQL 9.6 저장소를 sources.list.d 디렉토리에 추가합니다.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

그리고 PostgreSQL 서명 키를 시스템에 가져옵니다.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

이제 apt 명령으로 시스템 저장소를 업데이트합니다.

apt-get update

다음으로 아래의 apt 명령으로 PosgreSQL 9.6 패키지를 설치합니다.

apt-get install -y postgresql-9.6 postgresql-contrib-9.6

설치가 완료되면 부팅 시 자동으로 시작되도록 추가합니다.

systemctl enable postgresql

기본적으로 PostgreSQL은 Ubuntu에서 localhost (127.0.0.1) IP 주소의 포트 5432에서 실행되고 있으며, netstat 명령으로 확인할 수 있습니다.

netstat -plntu

Postgres가 실행 중인지 확인

PostgreSQL 9.6이 시스템에서 실행되고 있습니다. 다음 단계에서는 postgres 사용자에 대한 비밀번호를 구성해야 합니다.

루트 계정에서 su 명령으로 postgres 사용자로 로그인한 다음 postgres 프론트엔드 터미널 psql에 접근합니다.

su - postgres  
psql

postgres 사용자에 대한 비밀번호를 변경하고 아래의 postgres 쿼리로 연결 정보를 확인합니다.

\password postgres  
\conninfo

Postgres 비밀번호 변경

PostgreSQL 9.6이 시스템에 설치되었으며, 오류 없이 실행되고 있고 postgres 사용자에 대한 비밀번호가 업데이트되었습니다.

2단계 - UFW 방화벽 구성

UFW 또는 Uncomplicated Firewall은 Ubuntu에서 iptables 기반 방화벽을 관리하는 애플리케이션입니다. UFW는 Ubuntu Linux의 기본 방화벽 구성 도구이며 방화벽을 구성하는 사용자 친화적인 방법을 제공합니다.

apt 명령으로 메인 Ubuntu 저장소에서 ufw를 설치할 수 있습니다.

apt-get install -y ufw

UFW 방화벽에 새로운 서비스를 추가합니다: 아래 명령으로 SSH 및 PostgreSQL 서비스를 추가합니다.

ufw allow ssh  
ufw allow postgresql

UFW 방화벽을 활성화하고 상태를 확인합니다.

ufw enable  
ufw status

UFW 방화벽 설치 및 확인

UFW 방화벽이 설치되었고 PostgreSQL 서비스가 추가되었습니다.

참고:

1단계와 2단계를 MASTER 및 SLAVE 서버에서 실행합니다.

3단계 - PostgreSQL MASTER 서버 구성

마스터 서버의 IP 주소는 10.0.15.10이며, postgres 서비스는 해당 IP에서 기본 포트로 실행됩니다. 마스터 서버는 데이터베이스에 대한 읽기 및 쓰기 권한을 가지며 슬레이브 서버로 스트리밍 복제를 수행합니다.

postgres 구성 디렉토리 ‘/etc/postgresql/9.6/main‘로 이동하여 vim으로 postgresql.conf 파일을 편집합니다.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

‘listen_addresses’ 줄의 주석을 제거하고 값을 마스터 서버 IP 주소 ‘10.0.15.10‘으로 변경합니다.

listen_addresses = '10.0.15.10'

‘wal_level’ 줄의 주석을 제거하고 값을 ‘hot_standby‘로 변경합니다.

wal_level = hot_standby

동기화 수준을 위해 로컬 동기화를 사용할 것입니다. 주석을 제거하고 아래와 같이 값을 변경합니다.

synchronous_commit = local

아카이빙 모드를 활성화하고 archive_command 옵션을 ‘cp %p /var/lib/postgresql/9.6/main/archive/%f’ 명령으로 변경합니다.

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'

‘복제‘ 설정의 경우, 이 튜토리얼에서는 2개의 서버만 사용하므로 ‘wal_sender’ 줄의 주석을 제거하고 값을 2로 변경하며, ‘wal_keep_segments’ 값은 10으로 설정합니다.

max_wal_senders = 2  
wal_keep_segments = 10

애플리케이션 이름을 위해 ‘synchronous_standby_names’ 줄의 주석을 제거하고 값을 ‘pgslave001‘로 변경합니다.

synchronous_standby_names = 'pgslave001'

파일을 저장하고 편집기를 종료합니다.

postgresql.conf 파일에서 아카이브 모드가 활성화되었으므로 아카이브를 위한 새 디렉토리를 생성해야 합니다. 새 아카이브 디렉토리를 생성하고 권한을 변경하며 postgres 사용자로 소유자를 변경합니다.

mkdir -p /var/lib/postgresql/9.6/main/archive/  
chmod 700 /var/lib/postgresql/9.6/main/archive/  
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

다음으로 인증 구성을 위해 pg_hba.conf 파일을 편집합니다.

vim pg_hba.conf

아래 구성을 줄 끝에 붙여넣습니다.

# 로컬호스트  
host    replication     replica          127.0.0.1/32            md5  
   
# PostgreSQL 마스터 IP 주소  
host    replication     replica          10.0.15.10/32            md5  
   
# PostgreSQL 슬레이브 IP 주소  
host    replication     replica          10.0.15.11/32            md5

저장하고 종료한 후 PostgreSQL을 재시작합니다.

systemctl restart postgresql

PostgreSQL은 IP 주소 10.0.15.10에서 실행되고 있으며, netstat 명령으로 확인합니다.

netstat -plntu

Postgres 복제 구성

다음으로 복제를 위한 새 사용자를 생성합니다. ‘ replica ‘라는 이름의 새 사용자를 비밀번호 ‘ aqwe123@ ‘로 생성할 것입니다. 이 설정을 위해 안전한 비밀번호를 선택하세요! postgres 사용자로 로그인하고 postgres 프론트엔드 터미널 psql에 접근합니다.

su - postgres  
psql

아래의 postgres 쿼리로 비밀번호 ‘ aqwe123@ ‘로 새 ‘ replica ‘ 사용자를 생성합니다.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

이제 아래의 ‘ du ‘ 쿼리로 새 사용자를 확인하면 복제 권한이 있는 replica 사용자를 볼 수 있습니다.

\du

Postgres 복제 사용자 생성

MASTER 서버 구성이 완료되었습니다.

4단계 - SLAVE 서버 구성

SLAVE 서버의 IP 주소는 10.0.15.11입니다. 이 서버는 데이터베이스에 대한 읽기 권한만 가집니다. Postgres 데이터베이스 서버는 서버의 IP 주소에서 실행되며, localhost IP가 아닙니다.

아래의 systemctl 명령으로 슬레이브 서버에서 postgres 서비스를 중지합니다.

systemctl stop postgresql

Postgres 구성 디렉토리 ‘/etc/postgresql/9.6/main‘로 이동한 다음 구성 파일 ‘postgresql.conf‘를 편집합니다.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

listen_addresses 줄의 주석을 제거하고 값을 슬레이브 서버 IP 주소 10.0.15.11로 변경합니다.

listen_addresses = '10.0.15.11'

‘wal_level’ 줄의 주석을 제거하고 값을 ‘hot_standby‘로 변경합니다.

wal_level = hot_standby

동기화 수준을 위해 synchronous_commit 줄의 주석을 제거하고 아래와 같이 값을 변경합니다.

synchronous_commit = local

복제 설정을 위해 max_wal_senders 줄의 주석을 제거하고 값을 ‘2‘로 변경합니다. wal_keep_segments의 값도 ‘10‘으로 변경합니다.

max_wal_senders = 2  
wal_keep_segments = 10

애플리케이션 이름을 위해 ‘synchronous_standby_names’ 줄의 주석을 제거하고 값을 ‘pgslave001‘로 변경합니다.

synchronous_standby_names = 'pgslave001'

슬레이브 서버에 대해 hot_standby를 활성화하려면 아래 줄의 주석을 제거하고 값을 ‘on‘으로 변경합니다.

hot_standby = on

파일을 저장하고 편집기를 종료합니다.

5단계 - MASTER에서 SLAVE로 PostgreSQL 데이터 복사

다음으로, ‘ SLAVE ‘ 서버의 postgres 메인 디렉토리를 ‘ MASTER ‘ 서버의 메인 데이터 디렉토리로 교체하고자 합니다.

SLAVE 서버에 로그인하고 postgres 사용자로 접근합니다.

su - postgres

postgres 데이터 디렉토리 ‘main‘로 이동하여 백업을 위해 디렉토리 이름을 변경합니다.

cd 9.6/  
mv main main-bekup

새 ‘main‘ 디렉토리를 ‘ postgres ‘ 사용자로 생성하고 main-bekup 디렉토리와 같은 권한을 부여합니다.

mkdir main/  
chmod 700 main/

다음으로 pg_basebackup 명령을 사용하여 MASTER 서버에서 SLAVE 서버로 메인 디렉토리를 복사합니다. 우리는 replica 사용자를 사용하여 이 데이터 복사를 수행할 것입니다.

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog  
Password:

데이터 전송이 완료되면 메인 데이터 디렉토리로 이동하여 새 recovery.conf 파일을 생성합니다.

cd /var/lib/postgresql/9.6/main/  
vim recovery.conf

아래 구성을 붙여넣습니다:

standby_mode = 'on'  
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'  
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'  
trigger_file = '/tmp/postgresql.trigger.5432'

저장하고 종료한 후 chmod로 파일의 권한을 600으로 변경합니다.

chmod 600 recovery.conf

Postgres 슬레이브 서버 설정

이제 SLAVE 서버에서 PostgreSQL 9.6을 시작하고 netstat로 postgres 서비스가 IP 주소 10.0.15.11에서 실행되고 있는지 확인합니다.

systemctl start postgresql  
netstat -plntu

SLAVE 서버에 대한 데이터 전송 및 구성이 완료되었습니다.

6단계 - 테스트

테스트를 위해 PostgreSQL 9.6의 복제 상태를 확인하고 MASTER 서버에서 새 테이블을 생성한 다음 SLAVE 서버에서 모든 데이터를 확인하여 복제를 확인합니다.

MASTER 서버에 로그인하고 postgres 사용자로 로그인합니다.

su - postgres

아래의 psql 명령을 실행하여 복제 상태를 확인합니다.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  
psql -x -c "select * from pg_stat_replication;"

아래와 같은 결과를 얻을 수 있습니다.

Postgres 복제 상태 확인

다음으로 MASTER 서버에서 새 테이블을 생성해 보겠습니다. 마스터 서버에서 postgres 사용자로 로그인합니다.

su - postgres  
psql

그리고 아래의 postgres 쿼리를 실행하여 ‘ replica_test ‘라는 이름의 새 테이블을 생성하고 데이터베이스에 데이터를 삽입합니다.

CREATE TABLE replica_test (hakase varchar(100));  
INSERT INTO replica_test VALUES ('howtoforge.com');  
INSERT INTO replica_test VALUES ('This is from Master');  
INSERT INTO replica_test VALUES ('pg replication by hakase-labs');

테스트 데이터 추가

다음으로 SLAVE 서버에서 postgres 사용자로 로그인하고 psql 터미널에 접근합니다.

su - postgres  
psql

아래의 postgres 쿼리로 ‘ replica_test ‘ 테이블의 데이터를 확인합니다.

select * from replica_test;

그러면 MASTER 서버와 동일한 데이터를 얻을 수 있으며, 이는 MASTER 서버에서 SLAVE 서버로 복제된 것입니다.

데이터 복제 확인

추가 테스트:

SLAVE 서버에서 아래 쿼리로 쓰기를 테스트합니다.

INSERT INTO replica_test VALUES ('this is SLAVE');

그리고 SLAVE 서버에서 ‘ Cannot execute INSERT ‘ 쿼리에 대한 오류 메시지를 받게 됩니다.

Ubuntu 16.04 Xenial Xerus에서 PostgreSQL 9.6의 설치 및 마스터-슬레이브 복제가 성공적으로 완료되었습니다.

참고

https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/