pigsty는 Ansible로 온프레미스에 HA·백업·모니터링까지 갖춘 PostgreSQL을 RDS처럼 구축해주는 오픈소스 배포판이다. PGPOOL-II+Watchdog로 HA를 구성하고 pg_dump로 백업하는 현 시스템을 대체할 수 있는 PaaS급 Postgre인 것이다.

Patroni+etcd+HAProxy 기반 HA, pgBackRest 기반 PITR 백업, Prometheus+Grafana 기반 모니터링을 Ansible 플레이북으로 한 번에 구성해주고 확장 플러그인을 포함해서 제공한다는 것이 장점이다. 전부 설치되어있고 CREATE EXTENSION XXX로 켜서 사용만 하면 된다.

 

공식 문서는 아래 URL에서 확인 가능하다.

https://pigsty.io/docs/concept/

 

Concepts

Understand Pigsty's core concepts, architecture design, learn how high availability, backup recovery, iac, security works

pigsty.io

 

아키텍처를 더 자세히 살펴보도록 하겠다. 우선 모듈별로 의존성이 있기 때문에 INFRA -> NODE -> ETCD -> PGSQL 모듈순으로 설치를 해야한다. Active-Standby 서버로 구성할 시 총 3대의 서버가 필요하고 추가적으로 중앙서버가 하나 필요하다. 그래서 총 4대의 서버가 필요하다. 중앙 서버는 Ansible을 통해서 나머지 모든 서버들을 원격으로 제어하고 배포하는 구조이다. 관리 노드인 중앙서버에는 Infra 노드만 깔고 나머지 PGSQL 노드들에 NODE -> ETCD -> PGSQL를 설치하면 된다.

 

구조를 하나씩 뜯어보자.

 

먼저 가장 중요한 HA 이중화를 가능하게 하는 것은 haproxy, etcd 때문이다.

 

pgbouncer는 연결 풀링에 최적화된 도구로써 클라이언트 연결할때 미리 준비해 놓은 연결 풀링을 이용함으로써 클라이언트가 접속할때 마다 생성하는 OS 프로세스를 미리 준비해두어 속도를 향상시킨다. 그래서 접속할 때는 5432가 아니라 미들웨어인 pgbouncer의 Port로 접속해야한다.

 

patroni는 노드가 죽었을 때 자동 failover부터 죽은 노드를 복구시키는 online recovery까지 전부 자동으로 관리해주는 에이전트이다. postgresql 자체는 고가용성이 없기 때문에 파이썬 코드로 되어있고 이 코드가 돌면서 고가용성을 책임진다.

 

etcd는 patroni가 모여서 정보를 공유하고 합의하는 분산 저장소이다. 현재 primary가 누구인지, 노드 응답이 없어 etcd에 주기적으로 신호를 보내지 못하면 patroni가 승격을 하는 것이다.

 

 

그 다음 백업을 위해서 존재하는 것은 pgBackRest와 Minio다.

 

pgBackRest의 가장 큰 장점은 증분 백업이 가능하다는 것이다. pg_dump는 전체 백업만 가능하여 시간이 오래 걸리지만 증분 백업이 가능하기에 백업 시간과 저장 공간을 줄일 수 있다. 그리고 PITR (Point-in-Time Recovery)이 가능해서 특정 시간대의 DB로 되돌릴 수 있다. 아키텍처 그림에서 patroni와 협업하는 방식은 Patroni는 Primary 노드가 죽으면 Replica를 Primary로 승격시키는데, 이때 승격된 새 Primary는 pgBackRest와 연동되어 데이터 동기화(복구)를 수행할 수 있다. Streaming Replication가 끊겨있거나 WAL 로그 갭이 심해서 따라갈 수 없을때 진행한다. 

 

 

다음은 네트워크 계층을 살펴보겠다.

 

VIP-Manager는 네트워크 L2 계층으로써 VIP를 실제로 옮기고 서버가 하나의 IP를 바라볼 수 있도록 묶어주는 역할을 한다.

HAProxy는 L4 계층으로써 누가 현재 Primary인지 판단하여 하나의 VIP로 요청이 오더라도 실제 Primary 서버로 라우팅해주는 역할을 하고 포트를 보고 replica로 Select를 보내고 primary로는 Insert, Update, Delete를 보낸다.

 

 

빠르게 설치해보기

./deploy.yml을 입력하면 전체 모듈이 한번에 순서대로 설치된다. 그래서 특정 노드만 설치하고 싶다면 ansible-playbook infra.yml 이런식으로 특정 모듈의 yml을 지정해서 설치해야한다.

curl -fsSL https://repo.pigsty.io/get | bash  # Install Pigsty and dependencies
cd ~/pigsty; ./configure -g                   # Generate config (with 1-node template, -g generates random passwords)
./deploy.yml                                  # Execute deployment playbook

'DB > Postgresql' 카테고리의 다른 글

🐘 PostgreSQL Pgpool-II + watchdog를 사용하여 DB 이중화하기  (0) 2026.07.01
🐘 Postgresql DB 백업 및 복구하여 다른 서버로 마이그레이션  (0) 2026.01.10

PostgreSQL은 우선 Active- Active로 HA 구성이 불가능하며, Active- Active 운영 난이도가 매우 높아서 보통 Active-Stanby로 HA를 구성한다.
 
Pgpool-II -+ watchdog는 PostgreSQL을 고가용성(HA)으로 운영하기 위한 구성이다.

 

Pgpool-II은 PostgreSQL 앞단에 놓이는 미들웨어로, DB 연결 관리·로드밸런싱·실시간 복제 감지를 담당한다. Pgpool-II에 내장된  watchdog는 pgpool 노드끼리 서로 생존 여부를 감시하고, 장애 발생 시 자동 Failover와 VIP 전환을 수행한다.

Pgpool-II의 주요 기능

읽기/쓰기 분리(Read/Write Splitting): SQL을 파싱하여 쓰기 작업은 Primary로, 읽기 작업은 Replica로 자동 라우팅한다.

SQL를 스스로 까보고 알아서 Select는 Replica로 그 외 Insert, Delete, Update는 Primary로 보낸다. 다만 이 과정에서 SQL을 직접 파싱하기에 오버헤드가 발생할 수 있다.

 

쿼리 캐시(Query Cache): 동일한 쿼리의 결과를 메모리에 캐싱하여 응답 속도를 높인다.

 

고가용성(HA) 지원: Health check 기반의 자동 Failover(Standby 승격)와, Watchdog 기반의 가상 IP(VIP)/active pgpool 관리를 통해 HA를 구성한다.

 

Streaming Replication

Primary(DB 메인)가 생성한 WAL 로그를 Standby(DB 대기)가 실시간으로 받아 적용해 데이터를 계속 동일하게 유지하는 복제 방식이다. 이것은 Streaming replication은 Postgresql 자체 기능으로써, Pgpool-II 미들웨어를 사용하지 않아도 가능하다.

Pgpool-II 미들웨어는 Standby 서버가 여러대일 경우 복제 지연 상태를 감지해서 어느 Standby 서버로 보낼지 라우팅한다.
 

Failover

Primary 서버가 장애로 죽었을 때, Standby를 자동으로 Primary로 승격해서 서비스가 계속되도록 하는 동작이다.
Failover는 pgpool + watchdog이 수행한다. watchdog은 pgpool 미들웨어 자체의 장애 감지하고 리더를 선출해서 Active pgpool로 VIP를 이동시킨다. pgpool Health check가 Primary 장애를 감지하면, pgpool은 failover_command에 등록된 스크립트를 실행해 특정 Standby를 Primary로 승격시키고, 내부 backend status 테이블을 갱신한다. failover_command는 한번 작성해두면 자동화 가능하다.

 

Online Recovery

죽었던 Active DB 노드를 최신 데이터로 재동기화해 Standby로 복귀시키는 작업으로 수동으로 스크립트( recovery_1st_stage_command, pgpool_remote_start)를 실행해주어야한다.

pgpool의 health check가 죽었던 Active DB 노드가 살아났다는 것은 감지할 수 있지만, 결국 관리자가 pcp_recovery_node 명령으로 복구 스크립트를 직접 실행하여 Standby로 복구시키고 죽었던 동안 격차가 벌어진 데이터를 동기화해줘야한다. 즉 온라인 복구는 자동화가 불가능하다.

 
 
⭐ HA 용어 정의

용어 역할 목적
Streaming Replication 데이터 실시간 복제 Standby를 최신 상태로 유지
Failover Standby → Primary 승격 장애 시 서비스 지속
Online Recovery 복구된 노드 재동기화, 재편입 클러스터 구조 복원

 
⭐ 기능별 수행 주체

기능 실제 수행 주체
Streaming Replication PostgreSQL 자체 기능
Failover pgpool (스크립트 실행 - 자동화 가능)
Online Recovery pgpool (스크립트 실행 - 수동 실행)
pgpool 미들웨어 생존 감시 및 VIP 이동 watchdog

 

⭐ 감시 판단 주체

감시 대상 판단 주체 메커니즘
PostgreSQL DB 노드 생존 pgpool (health check) 다중 pgpool일 경우 watchdog 통신망으로 합의(quorum) 가능
pgpool 미들웨어 자체 생존 watchdog watchdog 간 heartbeat

 

내가 설치와 설정을 하면서 참고한 사이트이다.

 

추후에 pigsty라는 local aws rds postgre 오픈소스를 알게되었는데 이걸로 구성하면 Online Recovery를 포함하여 모든 설정을 자동화 할 수 있다. 그건 나중에 블로그에서 더 자세히 설명하고자한다.

Streaming Replication 설정 및 Failover 스크립트 생성 및 테스트

Online Recovery 부분에 대해서는 나와있지 않다.

https://tech.osci.kr/postgresql-ha-%EA%B5%AC%EC%84%B1-pgpool-ii%EB%A1%9C-%EC%9E%90%EB%8F%99-failover%EA%B9%8C%EC%A7%80-%EA%B5%AC%ED%98%84%ED%95%98%EB%8A%94-%EC%8B%A4%EC%A0%84-%EA%B0%80%EC%9D%B4%EB%93%9C/

 

해당 블로그에 들어가면 모든 부분을 확인할 수 있다. 아래 블로그에서 postresql 이중화 #1~#6번까지 보는걸 추천한다.

나는 Streaming Replication랑 Failover는 위 사이트 참고해서 하고 Online Recovery 부분만 아래 사이트를 참고했는데 그냥 처음부터 아래 블로그를 참고하는걸 추천한다.

https://berasix.tistory.com/entry/PostgreSQL-%EC%9D%B4%EC%A4%91%ED%99%94-6-PostgreSQL-Pgpool2Watchdog-%EC%9D%B4%EC%A4%91%ED%99%94-Failover%EC%99%80-Failback?category=1056014

 

[PostgreSQL 이중화] #6. PostgreSQL Pgpool2+Watchdog 이중화 Failover와 Failback

■ PostgreSQL 이중화(Pgpool2+Watchdog) Failover와 Failback ▶ 지난 포스팅에 이어 Auto Failover 테스트와 Failback 작업을 진행해 보겠습니다. ▶ pgpool-II version 4.1.4 버전을 사용하였습니다.(Ubuntu 22.04 Repository에

berasix.tistory.com

 

공식문서 참고자료: https://www.pgpool.net/docs/4.1/en/html/example-cluster.html

 

Pgpool-II + Watchdog Setup Example

Specify failover.sh script to be executed after failover in failover_command parameter. If we use 3 PostgreSQL servers, we need to specify follow_master_command to run after failover on the primary node failover. In case of two PostgreSQL servers, follow_m

www.pgpool.net

 

'DB > Postgresql' 카테고리의 다른 글

🐷 PostgreSQL On-premise RDS인 Pigsty 오픈소스 아키텍처 정리  (0) 2026.07.01
🐘 Postgresql DB 백업 및 복구하여 다른 서버로 마이그레이션  (0) 2026.01.10

백업 서버에서 DB 서버에 원격 접속하여 pg_dump로 백업 파일을 생성하는 경우, pg_dump 클라이언트 버전은 대상 PostgreSQL 서버 버전보다 낮을 수 없다.
 
예를 들어, PostgreSQL 16 버전 DB 서버를 백업 서버에 설치된 pg_dump 14 버전 클라이언트로 백업하는 것은 불가능하다.
이 경우 백업 서버에 PostgreSQL 16 버전 클라이언트(pg_dump) 를 추가로 설치한 뒤, 해당 버전의 pg_dump를 사용하여 백업 파일을 생성하고 복구를 수행해야 한다.
 
DB 백업 파일은 전체 백업과 특정 DB의 백업으로 구분된다.
실무에서는 트러블슈팅이 어렵기 때문에  pg_dumpall으로 데이터, 글로벌 객체 모두 백업하지 않는다. 
Postgresql은 pg_dump와 pg_dumpall을 통해서 백업 가능하며, 증분백업이 불가능하다.
 

백업 생성 방법

우선 백업은 postgres 계정으로 진행한다.
 
1. pg_dumpall을 사용하여 cluster 전체의 글로벌 객체(사용자, 권한, tablespace)를 먼저 백업하는 globals-only 백업 필요하다.

pg_dumpall -U postgres -v --roles-only -f "/backup/role.sql"

 
2. 특정 DB을 백업하는 경우 pg_dump 사용
백업파일 용량이 크면 파일 압축을 해서 gz 파일로 백업을 한다.

pg_dump {DB명} | gzip > /backup/backup-ex.gz

 

백업 복원 방법

우선 백업은 postgres 계정으로 진행한다.
 
1. Role 복구

psql -f /data/backup/pgsql_backup/dev/role.sql postgres

# 계정 목록 확인(psql에서 확인)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;

 
2. DB 복구
대상DB만 없다면 대상DB를 생성한 뒤 DB 복구한다.
15GB 데이터 복구 시 20분 정도 소요되며, 14버전의 스키마와 데이터를 16버전으로 복구했을때 특이사항은 없었다.

# DB 생성(psql로 진행)
CREATE DATABASE {DB명} OWNER {DB계정} LC_COLLATE 'C.UTF-8' LC_CTYPE
'C.UTF-8' TEMPLATE template0;

# DB 조회
select * from PG_DATABASE; 

# 백업 복구
gunzip < backup-ex.gz | psql -d {DB명}

 
3. 복구 완료 후 통계정보 갱신
ANALYZE; 
 
 

Troubleshooting

ROLE 복구 시 주의할 점
1)
오류코드: ERROR: role "existing_role" already exists
-> 백업 파일 복구 시 기존 role이 있다면 에러코드가 나오고 복구할 때 role 생성하지 않는다. (기존 role의 권한/속성 자체는 안 바뀜)
but, 아래 ALTER문으로 role의 비밀번호가 바뀔수있다.
ALTER ROLE ex WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'XXXXXXXXXXX';
해결방법:  --no-role-passwords로 globals 다시 뜨거나 OR 수동으로 기존 Role의 비밀번호를 ALTER 문을 없앤다.
 
2)
오류 코드: ERROR: require a VALID UNTIL option
원인: credcheck extension 설정으로 인해 CREATE ROLE 시 에러 발생
해결방법

# 해당 세션에서만 credcheck 비활성화
SET credcheck.password_valid_until = 0;
SET credcheck.password_valid_max = 0;
SELECT pg_reload_conf(); // 재시작 없이 변경사항 저장

# credcheck 확인
SHOW credcheck.password_valid_max;
SHOW credcheck.encrypted_password_allowed;

# 시스템상 영구 적용이기에 추천하지 않는 방법이다.
ALTER SYSTEM SET credcheck.password_valid_until = 0;
ALTER SYSTEM SET credcheck.password_valid_max = 0;
SELECT pg_reload_conf();

# credcheck를 제외 계정 추가
# 보통 시스템 계정이 해당
ALTER SYSTEM SET credcheck.whitelist = '{계정명}';
SELECT pg_reload_conf();

 

'DB > Postgresql' 카테고리의 다른 글

🐷 PostgreSQL On-premise RDS인 Pigsty 오픈소스 아키텍처 정리  (0) 2026.07.01
🐘 PostgreSQL Pgpool-II + watchdog를 사용하여 DB 이중화하기  (0) 2026.07.01

+ Recent posts

티스토리툴바