Postgresql DB복제

윈도우 환경에서 데이터베이스 설치, 복제 설정

Postgresql 15.6 database replication

• master, slave 서버 구성 후 master입력이 slave로 복제되는지 확인
• slave를 readonly 설정 후 master 장애시 slave에 접속해서 서비스
• download https://www.enterprisedb.com/download-postgresql-binaries
• doc, include, pgAdmin4, symbols 디렉터리 삭제, lib에 있는 *.lib등을 삭제하면 용량을 줄일 수 있다.
• master: 5432, slave:5433

postgre master install

cd x:\home\postgres
mkdir x:\home\postgres\master

# 영문으로 나오게 설정
set LC_MESSAGES=C
set POSTGRES_VERSION=postgresql-15.6-1-windows-x64-min.zip

x:\home\postgres>tar -xvf %POSTGRES_VERSION% -C x:\home\postgres\master
x:\home\postgres>master\pgsql\bin\initdb -D x:\home\postgres\master\data --username=postgres --locale C --encoding UTF8

# 서비스 등록전 사용, 모든 설정이 정상이면 서비스 등록
# x:\home\postgres>master\pgsql\bin\pg_ctl.exe register -N postgresmaster -S auto -D x:\home\postgres\master\data
# x:\home\postgres>master\pgsql\bin\pg_ctl.exe unregister -N postgresmaster
# net start/stop postgresmaster

x:\home\postgres>master\pgsql\bin\pg_ctl -D "x:\home\postgres\master\data" -l \tmp\masterlogfile start
x:\home\postgres>start powershell Get-Content \tmp\masterlogfile -Wait -Tail 10 

# master postgres user 생성
x:\home\postgres>master\pgsql\bin\createuser.exe -s -U postgres
x:\home\postgres>master\pgsql\bin\psql.exe -p 5432 -U postgres -c "alter user postgres password 'postgres';"
x:\home\postgres>master\pgsql\bin\psql.exe -p 5432 -U postgres -c "select version();"

postgre master pg_hba.conf

• md5 id/password 접속, trust 비밀번호 없이 접속
• super user 'postgres' 생성 후 변경

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

postgre master 복제를 위한 사용자 생성

• 위에서 pg_hba.conf 수정 후 재시작
• id: repl, pass: replpassword

x:\home\postgres\master\pgsql\bin\pg_ctl -D "x:\home\postgres\master\data" -l \tmp\masterlogfile stop
x:\home\postgres\master\pgsql\bin\pg_ctl -D "x:\home\postgres\master\data" -l \tmp\masterlogfile start

# 복제전용 사용자 repl 권한설정
X:\home\postgres>master\pgsql\bin\psql -p 5432 -U postgres
postgres=# CREATE ROLE repl WITH REPLICATION PASSWORD 'replpassword' LOGIN;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 USER      | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl      | Replication                                                | {}                 

postgre master postgresql.conf

listen_addresses = '*'

# 복제설정
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# 로그 설정
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr, jsonlog,
log_directory = 'log'                   # directory where log files are written,
#log_filename = 'master-%Y-%m-%d.log'   # log file name pattern,
log_min_messages = debug1
log_min_error_statement = debug1
log_min_duration_statement = 1000 # 1초 이상쿼리 로깅

log_timezone = 'Asia/Seoul'  # 로그 시간대: UTC+9 (한국 표준시)
timezone = 'Asia/Seoul'      # 시스템 시간대: UTC+9 (한국 표준시)

postgre slave install

• DB 엔진만 설치하고 master db를 복제한 후 사용한다.

cd x:\home\postgres
mkdir x:\home\postgres\slave
rem wget https://sco-goldimage.s3.ap-northeast-2.amazonaws.com/jetcafe/client/postgresql-15.6-1-windows-x64-min.zip
set LC_MESSAGES=C
set POSTGRES_VERSION=postgresql-15.6-1-windows-x64-min.zip

tar -xvf %POSTGRES_VERSION% -C x:\home\postgres\slave

# postgre master data 복사, slave data 생성하려면 "Database 복제는 DB삭제 후 진행"으로 이동
# mysql과는 다르게 slave DB를 삭제후 master DB와 동기화 후 사용하므로 정상 설치를 확인하기 위함
#x:\home\postgres\master\pgsql\bin\initdb -D x:\home\postgres\slave\data --username=postgres --locale C --encoding UTF8

# master와 동일하게 서비스 등록전 사용, 모든 설정이 정상이면 서비스로 등록
#x:\home\postgres\slave\pgsql\bin\pg_ctl.exe register -N postgresslave -S auto -D x:\home\postgres\slave\data
# x:\home\postgres\slave\pgsql\bin\pg_ctl.exe unregister -N postgresslave
# net start/stop postgresslave

#x:\home\postgres\slave\pgsql\bin\pg_ctl -D "x:\home\postgres\slave\data" -l \tmp\slavelogfile start

# master postgres user 생성
#x:\home\postgres\slave\pgsql\bin\createuser.exe -s -U postgres
#x:\home\postgres\slave\pgsql\bin\psql.exe -p 5433 -U postgres -c "alter user postgres password 'postgres';"
#x:\home\postgres\slave\pgsql\bin\psql.exe -p 5433 -U postgres -c "select version();"

# Database 복제는 DB삭제 후 진행
X:\home\postgres>rmdir /s slave\data
X:\home\postgres>slave\pgsql\bin\pg_basebackup -h localhost -p 5432 -D X:\home\postgres\slave\data -P -U repl -R
23033/23033 kB (100%), 1/1 tablespace

postgre slave postgresql.conf

• master 정보가 복제되어 있음

port = 5433                             # (change requires restart)
hot_standby = on    #readonly 허용
primary_conninfo = 'host=localhost port=5432 user=repl password=replpassword' # connection string to sending server

slave pg_hba.conf

• master 정보가 복제되어 있음, md5 변경

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

• slave start

x:\home\postgres>slave\pgsql\bin\pg_ctl -D "x:\home\postgres\slave\data" -l \tmp\slavelogfile start

Postgre 복제확인

• master db, table 생성

X:\home\postgres\master\pgsql\bin\psql -p 5432 -U postgres
postgres=# CREATE USER sampleuser WITH PASSWORD 'sampleuser';
CREATE ROLE
postgres=# ALTER ROLE sampleuser SUPERUSER;
ALTER ROLE
postgres=# CREATE DATABASE sampledb WITH ENCODING = 'UTF8' OWNER = sampleuser;
CREATE DATABASE
postgres=# \c sampledb sampleuser
Password for user sampleuser:

You are now connected to database "sampledb" as user "sampleuser".
sampledb=# CREATE TABLE test (id INTEGER, name VARCHAR(100));
CREATE TABLE
sampledb=# insert into test(id, name) values(1, 'Hello');
INSERT 0 1
sampledb=#

• slave에서 확인

X:\home\postgres>X:\home\postgres\slave\pgsql\bin\psql -p 5432 -U postgres
Password for user postgres:

psql (15.6)
Type "help" for help.

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


postgres=#  \c sampledb sampleuser
Password for user sampleuser:

You are now connected to database "sampledb" as user "sampleuser".
sampledb=# select * from test;
 id | name
----+-------
  1 | Hello
(1 row)

Postgre service 등록

• master

x:\home\postgres\master\pgsql\bin\pg_ctl -D "x:\home\postgres\master\data" -l \tmp\masterlogfile stop
x:\home\postgres\master\pgsql\bin\pg_ctl.exe register -N postgresmaster -S auto -D x:\home\postgres\master\data
net start postgresmaster
tail x:\home\postgres\master\data\log\postgresql-2024-09-25.log

X:\home\postgres\master\pgsql\bin\psql -p 5432 -U sampleuser -d sampledb
Password for user sampleuser:
sampledb=# SELECT * FROM pg_stat_replication;
  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |        backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
-------+----------+---------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 92928 |    16389 | repl    | walreceiver      | ::1         |                 |        4450 | 2024-09-25 05:02:02.53745+00 |              | streaming | 0/3438310 | 0/3438310 | 0/3438310 | 0/3438310  |           |           |            |             0 | async      | 2024-09-25 05:07:22.215853+00
(1 row)

• slave

x:\home\postgres\slave\pgsql\bin\pg_ctl -D "x:\home\postgres\slave\data" -l \tmp\slavelogfile stop
x:\home\postgres\slave\pgsql\bin\pg_ctl.exe register -N postgresslave -S auto -D x:\home\postgres\slave\data
net start postgresslave
tail x:\home\postgres\slave\data\log\postgresql-2024-09-25.log

X:\home\postgres\slave\pgsql\bin\psql -p 5432 -U sampleuser -d sampledb
sampledb=# SELECT * FROM pg_stat_replication;
  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |        backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
-------+----------+---------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 92928 |    16389 | repl    | walreceiver      | ::1         |                 |        4450 | 2024-09-25 05:02:02.53745+00 |              | streaming | 0/3438310 | 0/3438310 | 0/3438310 | 0/3438310  |           |           |            |             0 | async      | 2024-09-25 05:07:32.282171+00
(1 row)

복제 서버를 메인 DB로 전환하는 절차

• Primary (Master) 서버: 쓰기와 읽기 작업을 수행하는 메인 DB 서버.
• Standby (Slave) 서버: Primary 서버로부터 WAL을 복제해 읽기 작업만 수행하는 서버.
• Promotion: Standby 서버를 Primary 서버로 승격하는 과정.

1. 기존 Primary 서버의 중단 확인

• 복제 서버를 메인 DB로 승격하기 전, 기존 Primary 서버가 정상 종료되었거나 더 이상 접근할 수 없는지 확인
• 만약 기존 Primary가 꺼지지 않은 상태에서 복제 서버를 승격하면 스플릿 브레인(Split Brain) 문제가 발생할 수 있음

# 기존 Primary 서버가 다운되었는지 확인
master\pgsql\bin\pg_ctl status -D X:\home\postgres\master\data

pg_ctl: server is running (PID: 42388)
X:/home/postgres/master/pgsql/bin/postgres.exe "-D" "x:/home/postgres/master/data"

2. Standby 서버를 Primary 서버로 승격

• slave의 data 디렉토리로에 postmaster.pid가 있으면 master를 중지하고 slave가 master로 승격

X:\home\postgres\slave>pgsql\bin\pg_ctl promote -D X:\home\postgres\slave\data
waiting for server to promote.... done
server promoted

3. Standby 서버의 설정 변경 후 재시작

• Standby 서버를 Primary 서버로 승격하면, Standby 서버의 설정을 Primary 서버로 변경해야 함

# Standby 서버의 설정 파일인 postgresql.conf 파일을 열어서 다음 설정을 변경
hot_standby = off

4. Standby 서버 재시작

• Standby 서버를 Primary 서버로 승격하고 설정을 변경했다면, Standby 서버를 재시작

5. 기존 Primary 서버를 Standby 서버로 변경

• slave가 master로 승격하고 DB를 복제

X:\home\postgres\master>pgsql\bin\pg_basebackup -h localhost -p 5433 -D X:\home\post
gres\master\data -P -U repl -R
32213/32213 kB (100%), 1/1 tablespace

• 복제 후 환경설정, slave의 환경이 복제되었으므로 slave 원래 master의 포트정보로 변경

port = 5432                             # (change requires restart)
primary_conninfo = 'host=localhost port=5433 user=repl password=replpassword' # connection string to sending server
hot_standby = on