PostgreSQL 고급 관리 및 운영

1️⃣ 백업 및 복구 전략

1. pg_dump 및 pg_restore 사용법

pg_dump와 pg_restore는 PostgreSQL에서 데이터베이스 백업과 복구를 수행하는 주요 도구입니다.

✅ 데이터베이스 백업

pg_dump -U postgres -F c -f backup.dump mydatabase

• -U postgres : postgres 사용자로 실행
• -F c : 압축된(custom) 형식으로 백업
• -f backup.dump : 백업 파일명 지정
• mydatabase : 백업할 데이터베이스명

✅ 데이터베이스 복구

pg_restore -U postgres -d mydatabase backup.dump

• -U postgres : postgres 사용자로 실행
• -d mydatabase : 복구할 데이터베이스 지정
• backup.dump : 복구할 백업 파일

2. PITR (Point-In-Time Recovery) 개념 및 실습

PITR은 특정 시점으로 데이터베이스를 복원하는 방법입니다.

✅ WAL 아카이브 활성화

postgresql.conf에서 다음 설정을 추가합니다.

wal_level = archive
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

✅ 특정 시점 복구 절차

pg_basebackup -U postgres -D /var/lib/postgresql/data -Fp -Xs -P

복구 후 recovery.conf 파일을 생성하여 특정 시점으로 복원합니다.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-03-31 12:00:00'

3. Logical Replication과 Physical Replication

✅ Logical Replication 설정

-- 퍼블리셔에서 설정
CREATE PUBLICATION my_pub FOR TABLE my_table;

-- 구독자에서 설정
CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary_host dbname=mydatabase user=repuser password=mypassword' PUBLICATION my_pub;

✅ Streaming Replication (Physical Replication)

Primary 서버에서 postgresql.conf 설정:

wal_level = replica
max_wal_senders = 5
hot_standby = on

Standby 서버에서 recovery.conf 설정:

standby_mode = 'on'
primary_conninfo = 'host=primary_host user=repuser password=mypassword'

2️⃣ 모니터링 및 로깅

1. pg_stat_activity 및 pg_stat_database

PostgreSQL의 실행 중인 세션과 데이터베이스 상태를 확인할 수 있습니다.

✅ 실행 중인 쿼리 확인

SELECT * FROM pg_stat_activity;

✅ 데이터베이스별 통계 확인

SELECT * FROM pg_stat_database;

2. PostgreSQL 로그 분석 (log_statement, log_min_duration_statement)

postgresql.conf에서 다음과 같이 로깅을 활성화할 수 있습니다.

log_statement = 'all'
log_min_duration_statement = 1000

위 설정을 적용하면 모든 SQL 문을 로깅하고, 1초 이상 실행된 쿼리를 기록합니다.

3. Prometheus + Grafana를 활용한 PostgreSQL 모니터링

Prometheus 및 Grafana를 사용하여 PostgreSQL 성능을 시각적으로 모니터링할 수 있습니다.

✅ PostgreSQL Exporter 설치 및 실행

docker run -d --name postgres-exporter \
  -e DATA_SOURCE_NAME="postgresql://user:password@localhost:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter

✅ Prometheus 설정 예제

prometheus.yml 파일에 다음을 추가합니다.

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']

✅ Grafana 대시보드 설정

Grafana에서 Prometheus를 데이터 소스로 추가한 후 PostgreSQL 모니터링 대시보드를 가져옵니다.