코드노예

PostgreSQL Replication 서버 세팅 가이드

PostgreSQL의 Streaming Replication을 이용해 Primary-Standby 구성을 세팅하는 방법을 정리했다. 읽기 부하 분산, 장애 대비(failover), 백업 용도로 활용할 수 있다.

Replication 방식 비교

이 글에서는 가장 일반적인 Streaming Replication을 다룬다.

환경 구성

PostgreSQL 16 기준이며, 15 이상이면 동일하게 적용된다.

1. Primary 서버 설정

1-1. Replication 전용 계정 생성

-- Primary에서 실행
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_password';

1-2. postgresql.conf 수정

# 외부 접속 허용
listen_addresses = '*'

# WAL 레벨 (replica 이상 필요)
wal_level = replica

# 동시 replication 연결 수
max_wal_senders = 5

# Standby가 따라잡기 위해 보관할 WAL 세그먼트
wal_keep_size = 1GB

# Replication 슬롯 수 (선택, 권장)
max_replication_slots = 5

1-3. pg_hba.conf에 Standby 접속 허용

# TYPE  DATABASE        USER          ADDRESS           METHOD
host    replication     replicator    10.0.0.2/32       scram-sha-256

1-4. Replication Slot 생성 (권장)

Slot을 사용하면 Standby가 꺼져 있는 동안에도 필요한 WAL이 삭제되지 않는다.

SELECT pg_create_physical_replication_slot('standby_slot');

주의: Standby가 장기간 꺼져 있으면 WAL이 무한히 쌓여 디스크가 가득 찬다. 운영 환경에서는 모니터링 필수.

1-5. Primary 재시작

sudo systemctl restart postgresql

2. Standby 서버 설정

2-1. 기존 데이터 디렉토리 비우기

# Standby에서 실행
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

2-2. pg_basebackup으로 Primary 복제

Primary의 전체 데이터를 Standby로 복사한다.

sudo -u postgres pg_basebackup \
    -h 10.0.0.1 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R \
    -S standby_slot

옵션 설명:

-R 옵션이 자동으로 생성하는 파일들:

# standby.signal (빈 파일, 존재 자체가 Standby 모드를 의미)

# postgresql.auto.conf에 추가됨
primary_conninfo = 'host=10.0.0.1 user=replicator password=repl_password'
primary_slot_name = 'standby_slot'

2-3. Standby 전용 설정 (postgresql.conf)

# Standby에서 읽기 쿼리 허용
hot_standby = on

# 읽기 쿼리와 복제 충돌 시 대기 시간 (기본 30s)
max_standby_streaming_delay = 30s

2-4. Standby 시작

sudo systemctl start postgresql

3. Replication 상태 확인

Primary에서 확인:

-- 연결된 Standby 목록
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Standby에서 확인:

-- Standby 수신 상태
SELECT status, received_lsn, latest_end_lsn,
       latest_end_time, sender_host
FROM pg_stat_wal_receiver;

-- 복제 지연 시간 (PostgreSQL 10+)
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Slot 상태 확인:

SELECT slot_name, active, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

4. Synchronous Replication (선택)

기본 Streaming Replication은 비동기(async)다. Primary가 커밋 후 Standby에 전파하므로, Primary 장애 시 최근 트랜잭션이 유실될 수 있다.

데이터 유실이 허용되지 않는 경우 동기 모드를 사용한다.

Primary의 postgresql.conf:

# Standby의 application_name과 매칭
synchronous_standby_names = 'FIRST 1 (standby1)'

# 동기 커밋 레벨
synchronous_commit = on

Standby의 primary_conninfo에 application_name 추가:

primary_conninfo = 'host=10.0.0.1 user=replicator password=repl_password application_name=standby1'

동기 모드 레벨:

주의: 동기 모드에서 Standby가 죽으면 Primary의 모든 쓰기가 멈춘다. Standby를 2대 이상 두거나 FIRST 1 (standby1, standby2)처럼 설정해야 안전하다.

5. Failover (수동 승격)

Primary에 장애가 발생하면 Standby를 Primary로 승격한다.

# Standby에서 실행
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# 또는 SQL로
SELECT pg_promote();

승격 후 standby.signal 파일이 자동 삭제되고, 쓰기가 가능해진다.

승격 후 해야 할 일:

# 1. 애플리케이션의 DB 연결을 새 Primary(10.0.0.2)로 변경

# 2. 기존 Primary 복구 후 Standby로 전환
#    (pg_basebackup으로 새 Primary에서 다시 복제)

# 3. 기존 Replication Slot 정리 (새 Primary에서)
SELECT pg_drop_replication_slot('standby_slot');

6. 자동 Failover (Patroni)

수동 failover는 운영에서 한계가 있다. 자동 failover가 필요하면 Patroni를 사용한다.

# 아키텍처
┌─────────┐     ┌─────────┐     ┌─────────┐
│ Patroni │     │ Patroni │     │  etcd    │
│ + PG    │────▶│ + PG    │────▶│ cluster  │
│ Primary │     │ Standby │     │ (DCS)    │
└─────────┘     └─────────┘     └─────────┘
      │               │
      └───────┬───────┘
              ▼
        ┌───────────┐
        │ HAProxy / │
        │ PgBouncer │
        └───────────┘

Patroni 최소 설정 (patroni.yml):

scope: pg-cluster
name: pg-node1

etcd3:
  hosts: 10.0.0.10:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 5
        max_replication_slots: 5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.0.1:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    superuser:
      username: postgres
      password: postgres_pass
    replication:
      username: replicator
      password: repl_password

Patroni가 리더 선출, 자동 failover, pg_rewind를 통한 기존 Primary 재합류를 모두 처리한다.

7. Logical Replication (테이블 단위)

특정 테이블만 복제하거나, 다른 버전의 PostgreSQL 간 복제가 필요할 때 사용한다.

Primary (Publisher):

# postgresql.conf
wal_level = logical

-- Publication 생성
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- 또는 전체 테이블
CREATE PUBLICATION my_pub FOR ALL TABLES;

Standby (Subscriber):

-- 테이블 스키마는 미리 생성되어 있어야 함

-- Subscription 생성
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=10.0.0.1 dbname=myapp user=replicator password=repl_password'
    PUBLICATION my_pub;

Streaming vs Logical 비교:

8. 운영 모니터링 쿼리 모음

-- 1. 복제 지연 (Primary에서)
SELECT client_addr,
       state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_pretty
FROM pg_stat_replication;

-- 2. 복제 지연 시간 (Standby에서)
SELECT CASE
    WHEN pg_last_wal_replay_lsn() = pg_last_wal_receive_lsn() THEN 0
    ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS lag_seconds;

-- 3. Slot의 WAL 보관량 (디스크 사용량 감시)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- 4. 현재 서버가 Primary인지 Standby인지 확인
SELECT pg_is_in_recovery();  -- true = Standby, false = Primary

정리: 단계별 체크리스트

HA가 필요하면 Streaming Replication + Patroni 조합이 가장 검증된 구성이다. 테이블 단위 선택적 복제나 버전 간 마이그레이션이 필요한 경우에만 Logical Replication을 사용한다.

PostgreSQL은 기본 설정이 매우 보수적이다. 서버 사양에 맞게 튜닝하지 않으면 하드웨어의 10%도 활용하지 못한다. 실무에서 효과가 큰 순서대로 정리했다.

1. 메모리 설정

가장 효과가 큰 튜닝 영역이다. 대부분의 성능 문제는 여기서 해결된다.

shared_buffers

PostgreSQL이 데이터를 캐시하는 공유 메모리 영역이다. 기본값은 128MB로 너무 작다.

# 권장: 전체 RAM의 25%
# 16GB RAM 서버 기준
shared_buffers = 4GB

effective_cache_size

OS 파일 캐시를 포함하여 PostgreSQL이 사용할 수 있는 총 캐시 크기의 추정치다. 쿼리 플래너가 인덱스 스캔 여부를 결정할 때 참고한다. 실제 메모리를 할당하지는 않는다.

# 권장: 전체 RAM의 50~75%
effective_cache_size = 12GB

work_mem

정렬, 해시 조인 등 쿼리 내부 연산에 사용하는 메모리다. 연결 x 쿼리 x 연산 수만큼 곱해지므로 너무 크게 잡으면 OOM이 발생한다.

# 공식: RAM / max_connections / 4 (대략적 기준)
# 16GB RAM, 200 connections 기준
work_mem = 20MB

maintenance_work_mem

VACUUM, CREATE INDEX, ALTER TABLE 등 유지보수 작업에 사용하는 메모리다.

# 권장: 512MB ~ 2GB
maintenance_work_mem = 1GB

16GB RAM 서버 기준 요약:

2. WAL (Write-Ahead Log) 설정

쓰기 성능에 직접적인 영향을 주는 설정이다.

wal_buffers

# shared_buffers의 1/32, 최소 64MB
wal_buffers = 128MB

checkpoint 관련

체크포인트가 너무 자주 발생하면 I/O 스파이크가 생긴다.

# 체크포인트 간격 늘리기 (기본 5min)
checkpoint_timeout = 15min

# WAL 최대 크기 (기본 1GB)
max_wal_size = 4GB
min_wal_size = 1GB

# 체크포인트 I/O를 분산 (기본 0.9)
checkpoint_completion_target = 0.9

synchronous_commit

트랜잭션 커밋 시 WAL이 디스크에 기록될 때까지 기다릴지 결정한다.

# 쓰기 성능 향상 (약간의 데이터 손실 가능성 감수)
synchronous_commit = off

주의: off로 설정하면 크래시 시 최근 수백 ms의 트랜잭션이 유실될 수 있다. 로그성 데이터에는 괜찮지만, 결제 등 중요 데이터에는 on을 유지해야 한다.

3. 커넥션 관리

max_connections

PostgreSQL은 커넥션당 프로세스를 fork한다. 커넥션이 많을수록 컨텍스트 스위칭 비용이 증가한다.

# 직접 연결: 실제 필요한 수 + 여유분
max_connections = 200

Connection Pooler 사용 (권장)

애플리케이션이 수백~수천 커넥션을 사용한다면 PostgreSQL에 직접 연결하지 말고 PgBouncer를 사용한다.

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # 트랜잭션 단위로 커넥션 재사용
max_client_conn = 1000     # 클라이언트 최대 연결
default_pool_size = 50     # DB 실제 연결 수

이렇게 하면 PostgreSQL의 max_connections는 100 이하로 낮추고, 애플리케이션은 PgBouncer에 1000개까지 연결할 수 있다.

4. VACUUM과 Autovacuum

PostgreSQL은 MVCC 구조상 UPDATE/DELETE 후 죽은 튜플(dead tuple)이 쌓인다. VACUUM이 이를 정리한다. Autovacuum이 꺼져 있거나 너무 느리면 테이블 bloat과 성능 저하가 발생한다.

# autovacuum 기본 활성화 (절대 끄지 말 것)
autovacuum = on

# 워커 수 (대규모 DB에서 증가)
autovacuum_max_workers = 5

# 더 자주, 더 빠르게 실행
autovacuum_vacuum_cost_delay = 2ms      # 기본 2ms (이전 버전은 20ms)
autovacuum_vacuum_cost_limit = 1000     # 기본 -1 (vacuum_cost_limit 사용)

# 트리거 임계값 조정
autovacuum_vacuum_threshold = 50        # 기본 50
autovacuum_vacuum_scale_factor = 0.05   # 기본 0.2 (5%로 낮추면 더 자주 실행)

큰 테이블에 대한 개별 설정:

-- 1억 행 테이블은 scale_factor 0.01 (1%)로 설정
ALTER TABLE huge_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

dead tuple 확인:

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

5. 쿼리 분석 (EXPLAIN ANALYZE)

튜닝의 핵심은 느린 쿼리를 찾아서 고치는 것이다.

느린 쿼리 로깅 활성화:

# 200ms 이상 걸리는 쿼리 로깅
log_min_duration_statement = 200ms

EXPLAIN ANALYZE 사용법:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 12345
  AND created_at > '2025-01-01';

실행 계획에서 주의할 키워드:

6. 인덱스 전략

기본 B-tree 인덱스:

-- 단일 컬럼
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 복합 인덱스 (선택도 높은 컬럼을 앞에)
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

Partial Index (조건부 인덱스):

특정 조건의 행만 인덱싱하여 크기를 줄이고 성능을 높인다.

-- 활성 주문만 인덱싱
CREATE INDEX idx_orders_active ON orders (created_at)
    WHERE status = 'active';

-- NULL이 아닌 행만
CREATE INDEX idx_users_email ON users (email)
    WHERE email IS NOT NULL;

Covering Index (INCLUDE):

인덱스만으로 쿼리를 완료하여 테이블 접근을 제거한다 (Index-Only Scan).

-- user_id로 검색하고 name, email을 반환하는 쿼리에 최적
CREATE INDEX idx_users_covering ON users (user_id) INCLUDE (name, email);

사용하지 않는 인덱스 찾기:

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

사용되지 않는 인덱스는 쓰기 성능만 떨어뜨린다. 과감히 삭제한다.

7. 디스크 I/O 설정

# SSD 사용 시 (random I/O가 빠르므로 낮게)
random_page_cost = 1.1        # 기본 4.0 (HDD 기준)
seq_page_cost = 1.0

# 동시 I/O 워커
effective_io_concurrency = 200   # SSD: 200, HDD: 2

random_page_cost를 낮추면 플래너가 인덱스 스캔을 더 적극적으로 선택한다. SSD를 사용하는데 기본값(4.0)을 두면 불필요한 Seq Scan이 발생할 수 있다.

8. pg_stat_statements로 모니터링

실제 운영에서 느린 쿼리를 찾는 가장 효과적인 방법이다.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# DB에서 확장 활성화
CREATE EXTENSION pg_stat_statements;

총 실행 시간 기준 상위 쿼리:

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

I/O가 많은 쿼리:

SELECT query,
       shared_blks_read + shared_blks_hit AS total_blks,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

hit_pct가 낮은 쿼리는 캐시 미스가 잦다는 뜻이므로 인덱스나 쿼리 최적화가 필요하다.

9. 전체 설정 요약 (postgresql.conf)

16GB RAM, SSD, 200 커넥션 기준 템플릿이다.

# Memory
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 20MB
maintenance_work_mem = 1GB

# WAL
wal_buffers = 128MB
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Disk I/O (SSD)
random_page_cost = 1.1
effective_io_concurrency = 200

# Connections
max_connections = 200

# Autovacuum
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.05

# Logging
log_min_duration_statement = 200ms
shared_preload_libraries = 'pg_stat_statements'

정리: 튜닝 우선순위

설정을 변경하기 전에 pg_stat_statements로 현재 병목을 먼저 파악하는 것이 가장 중요하다. 병목을 모르고 설정만 바꾸는 것은 효과가 제한적이다.

PostgreSQL 계정 및 권한 관리 가이드

PostgreSQL에서 DB 생성부터 계정 생성, 권한 부여/삭제, 스키마 변경 시 권한 자동 유지까지 실무에서 자주 쓰는 명령어를 정리했습니다.

1. 데이터베이스 생성

-- 기본 생성
CREATE DATABASE myapp;

-- 인코딩, 소유자 지정
CREATE DATABASE myapp
    OWNER myuser
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8';

2. 계정(Role) 생성

PostgreSQL에서는 사용자와 그룹 모두 ROLE을 사용합니다.

-- 일반 사용자
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass';

-- DB 생성 권한 포함
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass' CREATEDB;

-- Superuser
CREATE ROLE myadmin WITH LOGIN PASSWORD 'adminpass' SUPERUSER;

주요 옵션 정리:

3. 권한 부여 (GRANT)

DB 접속 권한:

GRANT CONNECT ON DATABASE myapp TO myuser;

스키마 사용 권한:

GRANT USAGE ON SCHEMA public TO myuser;

-- 스키마 내 객체 생성까지 허용
GRANT USAGE, CREATE ON SCHEMA public TO myuser;

테이블 권한:

-- 특정 테이블
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO myuser;

-- 스키마 내 모든 기존 테이블
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

-- 읽기 전용
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

시퀀스 권한 (INSERT 시 serial/identity 컬럼 사용에 필요):

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

4. 스키마 변경 시 권한 자동 유지 (DEFAULT PRIVILEGES)

GRANT ON ALL TABLES는 실행 시점의 기존 테이블에만 적용됩니다. 이후 새로 생성되는 테이블에는 권한이 없습니다. 이 문제를 해결하는 것이 ALTER DEFAULT PRIVILEGES입니다.

-- 앞으로 생성되는 테이블에 자동 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

-- 앞으로 생성되는 시퀀스에 자동 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO myuser;

-- 읽기 전용 계정용
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_user;

주의: ALTER DEFAULT PRIVILEGES는 실행한 역할(Role)이 생성하는 객체에만 적용된다. 예를 들어, postgres로 실행했으면 postgres가 만드는 테이블에만 적용된다. 다른 역할이 만드는 객체에도 적용하려면 FOR ROLE을 지정해야 한다.

-- deployer 역할이 생성하는 테이블에도 적용
ALTER DEFAULT PRIVILEGES FOR ROLE deployer IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

5. 실전: 완전한 권한 설정 스크립트

새 DB와 계정을 만들고, 기존 + 미래 테이블 모두에 권한을 부여하는 전체 흐름입니다.

-- 1) 계정 생성
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass';

-- 2) DB 생성
CREATE DATABASE myapp OWNER postgres;

-- 3) DB 접속 (psql -d myapp 또는 \c myapp)

-- 4) 스키마 권한
GRANT CONNECT ON DATABASE myapp TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;

-- 5) 기존 객체 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- 6) 미래 객체 자동 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO myuser;

6. 권한 삭제 (REVOKE)

GRANT의 반대다. 문법도 거의 동일합니다.

-- 테이블 권한 삭제
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM myuser;

-- 스키마 권한 삭제
REVOKE USAGE ON SCHEMA public FROM myuser;

-- DB 접속 권한 삭제
REVOKE CONNECT ON DATABASE myapp FROM myuser;

-- DEFAULT PRIVILEGES 삭제
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE USAGE ON SEQUENCES FROM myuser;

7. 권한 확인

-- 역할 목록 확인
\du

-- 특정 테이블 권한 확인
\dp users

-- DB 권한 확인
\l

-- DEFAULT PRIVILEGES 확인
\ddp

8. 계정 삭제

계정을 삭제하려면 소유 객체와 권한을 먼저 정리해야 합니다.

-- 소유 객체를 다른 역할로 이전
REASSIGN OWNED BY myuser TO postgres;

-- 남은 권한 모두 삭제
DROP OWNED BY myuser;

-- 계정 삭제
DROP ROLE myuser;

정리

핵심은 GRANT ON ALL TABLES와 ALTER DEFAULT PRIVILEGES를 함께 사용해야 기존 + 미래 테이블 모두 권한이 적용된다는 것 입니다.

이번에는 PostgreSQL 데이터베이스를 백업하고 복구하는 방법에 대해서 살펴보겠습니다. database 이름은 testdb 라고 가정하였으며, 백업해서 생성할 파일은 db_backup_file.sql 입니다.

DB 백업하기

DB 백업을 위해서는 pg_dump 명령어를 사용합니다.

백업시에 많이 사용되는 기본 옵션들과 같이 적어 보았습니다.

pg_dump -h <server_host> -p <port> -U <username> -d <db_name> -f <file_name>

만약 DB 서버에서 root 권한으로 백업을 한다면, 아래와 같이 db_name과 file_name만 적어줘도 됩니다.

pg_dump testdb -f db_backup_file.sql

f 옵션으로 파일 이름을 지정하는 대신 redirect를 이용할 수도 있습니다.

pg_dump testdb > db_backup_file.sql

DB 복구하기

위에서 백업한 파일은 sql 구문형태로 저장이 됩니다. 물론 복구시의 최적화를 위해, 제약사항들을 나중에 추가하는 방식으로 기록됩니다. 따라서 데이터 복구를 위해서는 sql 파일을 실행해 주면 됩니다.

psql -h <server_host> -p <port> -U <username> -d <db_name> -f <file_name>

혹은

psql -h <server_host> -p <port> -U <username> -d <db_name> < <file_name>

DB서버에서 root 권한으로 복구를 한다면, db_name과 file_name 만 있으면 됩니다.

psql testdb -f db_backup_file.sql

혹은

psql testdb < db_backup_file.sql

대용량 DB 다루기

1. 대용량 DB를 백업하거나 복구할 때는, gzip / gunzip 을 이용할 수 있습니다.

pg_dump testdb | gzip > db_backup_file.sql.gz

복구시에는 반대로 압축을 풀면서 스트림을 전달해 주면 됩니다.

gunzip -c db_backup_file.sql.gz | psql testdb

아니면 cat 명령어를 이용하여 다음과 같이 사용해도 결과는 같습니다.

cat db_backup_file.sql.gz | gunzip | psql testdb

2. 압축을 선호하지 않는다면, split 명령어를 이용하여 적당한 크기로 분할할 수 있습니다.

pg_dump testdb | split -b 2G - db_backup_file.sql

합치는 것은 cat 명령어를 이용하면 됩니다. 

cat db_backup_file.sql* | psql testdb

3. 커스텀 dump 포맷을 사용하는 경우

=> 이 경우 백업 결과, 압축 포맷으로 저장되기 때문에, pg_restore를 이용해서 복구를 해야 합니다.

pg_dump -Fc testdb > db_backup_file.sql

pg_restore를 이용하여 복구

pg_restore -d testdb db_backup_file.sql

유용한 팁

아래 명령어를 이용하면, src DB 서버에서 백업을 하면서, 동시에 target DB 서버에 restore를 할 수 있습니다.

pg_dump -h <src_db_host> <db_name> | psql -h <target_db_host> <db_name>

PostgreSQL에서는 Role을 정의해서 DB 이용에 대한 권한을 부여합니다. 다른 DB의 user와 비슷한 개념이지만 좀 더 포괄적으로 사용할 수 있습니다.

여기서는 testuser 라는 role을 생성할 예정이고, 해당 role에게 testdb에 접속해서 public schema 에 있는 테이블을 사용하는 권한을 부여하는 방법에 대해서 알아보도록 하겠습니다.

ROLE 생성하기

Role 생성은 CREATE ROLE statement를 사용합니다.

CREATE ROLE testuser;

cf. role 이름은 '소문자'만 허용이 되는 것 같습니다. 생성시에 이름을 대문자로 입력해도 '소문자'로 생성이 되네요..

ROLE 확인하기

생성된 Role은 다음의 statement로 확인이 가능합니다.

SELECT rolname FROM pg_roles;

혹은 psql 에서는 아래 command를 이용할 수도 있습니다.

\du

ROLE 에 속성 추가/변경 하기

위에서 생성된 role은 아무런 권한이 부여되지 않았기 때문에, 아무것도 할 수 없습니다. 로그인 조차 할 수 없기 때문에, 필요한 속성을 추가해 줘야 합니다. ALTER ROLE statement 를 이용하여 지정한 password를 이용해서 로그인을 할 수 있는 권한을 부여합니다.

ALTER ROLE testuser [WITH] LOGIN PASSWORD 'password';

WITH는 LOGIN, PASSWORD 같은 옵션 앞에 붙인다고 되어 있는데, 생략해도 되는 것 같습니다. ( PostgreSQL 16 기준)

추가할 수 있는 속성에 대해서는 PostgreSQL manual 페이지를 참조해 주세요.

https://www.postgresql.org/docs/current/sql-alterrole.html

참고로 PostgreSQL에서, CREATE USER는 CREATE ROLE 의 alias 로 쓰이고 있어서, 아래 구문은 위와 같습니다.

ALTER USER testuser [WITH] LOGIN PASSWORD 'password';

참고로 LOGIN 속성을 제거 하고 싶다면, NOLOGIN 속성을 부여하면 됩니다.

Password 변경은 ALTER ROLE 혹은 ALTER USER statement로 PASSWORD 를 다시 지정해 주면 됩니다.

ALTER ROLE testuser PASSWORD 'new_password';

DB 연결 권한 추가하기

Role은 전체 PostgresSQL 시스템에서 유저를 관리하는 개념이었고, 개별 database에 접근하는 권한은 각 database 별도로 따로 지정해 줘야 합니다. 먼저 DB 연결 권한을 부여합니다.

GRANT CONNECT ON DATABASE testdb TO testuser;

DB 사용 권한 추가하기

다음으로 DB 사용 권한을 부여해 줍니다. DB 사용 권한은 schema 별로 지정해 줘야 합니다. 여기서는 public schema에 대한 권한을 부여해 주겠습니다. DB 사용 권한 부여시에는 해당 DB에 접속한 상태에서 구문을 입력합니다.

GRANT USAGE ON SCHEMA public TO testuser;

이번에는 select 구문 사용 권한을 추가해 봅니다.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO testuser;

select 이외에도 insert, update, delete 등의 권한을 추가해 줄 수 있습니다.

그 밖에 자세한 권한에 대한 정보는 아래 링크를 참고하시면 됩니다.

https://www.postgresql.org/docs/current/sql-grant.html

위의 구문으로 DB 테이블에 대한 권한을 부여하면, 권한을 줄 때 당시에 있던 테이블에 대해서만 권한이 적용되며, 나중에 추가한 테이블에 대한 권한은 자동으로 추가되지 않습니다. 모든 새로 생성된 테이블에 대한 권한을 자동으로 추가해 주려면 아래와 같은 구문을 실행해 줍니다.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO testuser;

DB 사용 권한 제거하기

위에서 생성한 DB 권한은 Revoke statement로 제거할 수 있습니다.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM testuser;

cf.  테이블 별 사용 권한 확인

\dp <tablename>

ROLE 제거하기

위에서 생성했던 Role은 DROP ROLE statement로 제거할 수 있습니다. 하지만 그 전에 부여했던 모든 권한을 DB별로 모두 제거해 줘야 합니다.

권한 제거는 DROP OWNED statement를 이용합니다. 아래와 같이 사용하면 됩니다.

DROP OWNED BY testuser CASCADE;

그리고 나서 Role을 제거합니다.

DROP ROLE testuser;

아래 URL 내용 참고.

https://stackoverflow.com/questions/3023583/how-to-quickly-drop-a-user-with-existing-privileges