postgres primary/replica

docker를 이용하여 postgresql의 primary replica 설정할 수 있다.

 

---

 

docker-compose.yml 설정

services:
  postgres-primary:
    image: postgres:16.1
    container_name: postgres-primary
    restart: always
    ports:
      - 15432:5432
    environment:
      - POSTGRES_USER=아이디 입력
      - POSTGRES_PASSWORD=비밀번호 입력
      - TZ=Asia/Seoul
    volumes:
      - ./data/primary:/var/lib/postgresql/data

  postgres-replica:
    image: postgres:16.1
    container_name: postgres-replica
    restart: always
    ports:
      - 15433:5432
    environment:
      - POSTGRES_USER=repluser
      - POSTGRES_PASSWORD=비밀번호 입력
      - TZ=Asia/Seoul
    depends_on:
      - postgres-primary
    volumes:
      - ./data/replica:/var/lib/postgresql/data

 

---

 

primary 설정

 

1. replication 권한을 가지는 사용자 생성 (primary 컨테이너 안에서 SQL 실행)

psql -U postgres

CREATE USER repluser WITH LOGIN REPLICATION PASSWORD '비밀번호 입력';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repluser;

 

2. physical replication slot 추가 (primary 컨테이너 안에서 SQL 실행)

SELECT pg_create_physical_replication_slot('repl_slot_0');

 

3. pg_hba.conf 수정 (host 서버에서 vi pg_hba.conf)

host    replication    repluser    all    trust

 

4. postgresql.conf 설정 (host 서버에서 vi postgresql.conf)

max_connections = 500
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/data/pg_archive/%f'

 

2.5. primary 컨테이너 재시작

docker restart postgres-primary

 

---

 

replica 설정

 

1. replica 컨테이너 /bin/bash로 접속 후 서비스 종료

su postgres
pg_ctl stop

 

2. replica 컨테이너에서 /var/lib/postgres/data 디렉토리 삭제 또는 host에 마운트된 data 디렉토리 삭제

cd /var/lib/postgres/data
rm -rf ./*

 

3. pg_basebackup 실행 (replica 컨테이너에서)

pg_basebackup -h {PRIMARY_IP} -p {PRIMARY_PORT} -D /var/lib/postgresql/data -U repluser -P -v -X stream -S repl_slot_0 -R

 

4. replica 컨테이너 재시작

docker restart postgres-replica

 

---

 

끝.