# PostgreSQL Streaming Replication 구성

이전 글(# PostgreSQL Streaming Replication 개념)에서 PostgreSQL Replication에 대한 개념을 살펴봤고 이제 실제 구성을 해보자.

 

Step 1. PostgreSQL11 설치

$ rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

$ yum install -y postgresql11-server postgresql11-contrib

Step 2. PostgreSQL 실행

# init db
$cd /usr/pgsql-11/bin
$ ./postgresql-11-setup initdb

# postgresql 실행
$ systemctl start postgresql-11
$ systemctl enable postgresql-11

# postgresql db 접속
$ su - postgres        # postgresql 폴더의 사용자가 postgres로 되어있다. 특별한 이유가 없다면
$ psql                 # postgres 사용자 계정으로 postgresql을 이용하자

# postgresql 비밀번호 설정
$ \password postgres
$ Enter new password:

# 사용자 생성
$ CREATE USER brown PASSWORD 'brown' SUPERUSER;

# database 생성
$ CREATE DATABASE dwarf OWNER brown;

Step3. Master Server 설정

$ vi /var/lib/pgsql/11/data/postgresql.conf
> listen_addresses = '*'
> wal_level = hot_standby
> synchronous_commit = local
> archive_mode = on
> archive_command = 'cp %p /var/lib/pgsql/11/archive/%f'
> max_wal_senders = 2
> wal_keep_segments = 10
> synchronous_standby_names = 'master02'
 
# 생략 가능? Archive 설정시 사용?
$ mkdir -p /var/lib/pgsql/11/archive/
$ chmod 700 /var/lib/pgsql/11/archive/
$ chown -R postgres:postgres /var/lib/pgsql/11/archive/

$ vi /var/lib/pgsql/11/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host     all             all             192.168.10.0/24         trust
host     all             all             ::1/128                 trust
host    replication     replication          127.0.0.1/32            md5        # Localhost
host    replication     replication          192.168.10.7/32            md5     # PostgreSQL Master IP address
host    replication     replication          192.168.10.8/32            md5     # PostgreSQL SLave IP address

# postgresql 재시작
$ systemctl restart postgresql-11

# postgresql 접속
$ psql -h localhost -U postgres -d postgres

 

Step4. Replication User 및 Slot 생성

# Replication User 생성
$ CREATE ROLE replication WITH REPLICATION PASSWORD 'replication' LOGIN;

# Replication Slot 생성
$ SELECT * FROM pg_create_physical_replication_slot('repl_slot_01');

Step5. Master Server -> Standby Server 설정 복사

$ systemctl stop postgresql-11

$ cd /var/lib/pgsql/11/
$ mv data data-backup

$ mkdir -p data/
$ chmod 700 data/
$ chown -R postgres:postgres data/

$ su - postgres
$ pg_basebackup -h 192.168.10.7 -U replication -D /var/lib/pgsql/11/data -v -X stream
$ Password:

 

Step6. Standby Server 설정

$ vi /var/lib/pgsql/11/data/postgresql.conf
> listen_addresses = '*'
> hot_standby = on

# recovery.conf 만들기 (중요)
$ vi /var/lib/pgsql/11/data/recovery.conf
> standby_mode = 'on'
> primary_conninfo = 'host=192.168.10.7 port=5432 user=replication password=replication application_name=master02'
> primary_slot_name='repl_slot_01'
> trigger_file = '/var/lib/pgsql/11/data/failover_trigger'

$ chmod 600 recovery.conf
$ chown postgres:postgres recovery.conf

$ systemctl start postgresql-11

# Master Server에서 replication 확인
postgres=# SELECT * FROM pg_stat_replication;

# 로그 확인 
$ tail -f /var/lib/pgsql/11/data/log/postgresql-Mon.log

 

Step7. Replication Streaming 동작 Test

# Master Server에서 replication 확인
$ su - postgres
$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
$ psql -x -c "select * from pg_stat_replication;"

# database 변경
$ \c dwarf brown

# table 생성
CREATE TABLE moon (
id integer NOT NULL,
name character varying(255),
class character varying(32),
age integer,
radius integer,
lum integer,
magnt integer,
time timestamp,
CONSTRAINT moon_pk PRIMARY KEY (id)
);

# moon table 데이터 insert
$ INSERT INTO public.moon(id, name, class, age, radius, lum, time, magnt)  VALUES(2, 'Sat', 'L22V', 5500, 1231, 7,  current_timestamp ,2);

# Standby Server에서 moon 테이블 확인
=> Master Server에 Insert한 데이터가 있어야된다. 

 

 

<참조 사이트>

https://www.howtoforge.com/tutorial/how-to-install-and-configure-master-slave-replication-with-postgresql-96-on-centos-7/

https://browndwarf.tistory.com/4?category=803646