백엔드 데이터 인프라 45편 — 데이터베이스 관리 생성·복제·드롭

이 글은 백엔드 데이터 인프라 시리즈 70편 중 45편이에요. 5편 CREATE DATABASE 의 "기본" 위에 — 운영 깊이.

CREATE DATABASE 운영 옵션

CREATE DATABASE myappdb
    WITH
        OWNER       = app_user
        TEMPLATE    = template0
        ENCODING    = 'UTF8'
        LC_COLLATE  = 'ko_KR.UTF-8'
        LC_CTYPE    = 'ko_KR.UTF-8'
        TABLESPACE  = pg_default
        CONNECTION LIMIT = 100
        IS_TEMPLATE = FALSE
        ALLOW_CONNECTIONS = TRUE;

추가 운영 옵션: - TABLESPACE — 별도 디스크 영역 (대용량·SSD 분리) - IS_TEMPLATE — 다른 DB 가 이걸 템플릿으로 복제 가능 - ALLOW_CONNECTIONS — 접속 차단 (유지보수·복구 중)

TEMPLATE 시스템

PG 의 "DB 만들기" 메커니즘 = 기존 DB 복사.

CREATE DATABASE new_db TEMPLATE original_db;
-- original_db 의 모든 객체·데이터 복사

조건: - 다른 세션이 original_db 에 접속해 있으면 안 됨 - 같은 인코딩·로케일

활용: - 테스트 환경 = 운영 마스킹된 DB → 빠른 복제 - 개발 환경 = 작은 시드 DB → 자주 복제

template0 vs template1

TEMPLATE template1   -- 기본. 다른 사람이 박은 객체 자동 복제 (위험)
TEMPLATE template0   -- 깨끗한 빈 템플릿 (안전, 권장)

운영 = template0 무조건.

DB 복제 — pg_dump·pg_restore

큰 DB 의 복제는 — 다른 도구.

# 덤프
pg_dump -h source.example.com -U postgres -Fc mydb > mydb.dump

# 복원
createdb -h target.example.com -U postgres newdb
pg_restore -h target.example.com -U postgres -d newdb mydb.dump

-Fc = custom format (압축 + 병렬 복원 가능). 46편 백업에서 깊이.

DB 이동·이름 변경

-- 이름 변경 (다른 세션 없어야)
ALTER DATABASE old_name RENAME TO new_name;

-- TABLESPACE 변경
ALTER DATABASE mydb SET TABLESPACE fast_ssd;

-- OWNER 변경
ALTER DATABASE mydb OWNER TO new_owner;

-- CONNECTION LIMIT
ALTER DATABASE mydb CONNECTION LIMIT 200;

ALTER DATABASE SET — DB 단위 설정

-- 이 DB 에 접속하면 자동 적용
ALTER DATABASE mydb SET timezone = 'Asia/Seoul';
ALTER DATABASE mydb SET search_path = app, public;
ALTER DATABASE mydb SET work_mem = '64MB';

DB 별 설정 — 시스템 전체 변경 없이.

다른 사용자 차단

-- 유지보수·복구 중
ALTER DATABASE mydb ALLOW_CONNECTIONS = FALSE;
-- 작업 후
ALTER DATABASE mydb ALLOW_CONNECTIONS = TRUE;

또는 기존 접속 끊기:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

TABLESPACE — 디스크 분리

CREATE TABLESPACE fast_ssd LOCATION '/mnt/nvme/pgdata';

CREATE DATABASE hot_data TABLESPACE fast_ssd;
-- 또는 특정 테이블만
ALTER TABLE huge_table SET TABLESPACE fast_ssd;

활용: - 자주 쓰는 데이터 = SSD - 옛 로그·아카이브 = HDD (저렴) - 인덱스만 별도 SSD

대규모 운영 표준. 소규모 = pg_default 한 곳.

데이터베이스 크기·통계

-- 모든 DB 크기
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 특정 DB 안 테이블 크기
SELECT
    schemaname || '.' || tablename AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total,
    pg_size_pretty(pg_relation_size(relid)) AS data,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

운영 정기 점검 항목.

활성 연결 모니터

SELECT
    datname,
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    LEFT(query, 60) AS query
FROM pg_stat_activity
WHERE datname IS NOT NULL
ORDER BY query_start;

state = active·idle·idle in transaction. idle in transaction 오래 = 트랜잭션 안 열어 둠 → bloat 위험.

오래된 트랜잭션 끊기

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - state_change > INTERVAL '10 minutes';

운영 cron 으로 자동 정리.

데이터베이스 통계

SELECT
    datname,
    numbackends AS connections,
    xact_commit, xact_rollback,
    blks_hit, blks_read,
    ROUND(blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
    tup_returned, tup_fetched,
    tup_inserted, tup_updated, tup_deleted,
    conflicts, temp_files, temp_bytes,
    deadlocks
FROM pg_stat_database
WHERE datname IS NOT NULL;

전체 DB 통계. cache_hit_pct < 95% = 메모리 부족 신호.

RDS·Aurora 의 데이터베이스 관리

RDS = 일반 PG 와 거의 같음. 단: - 슈퍼유저 → rds_superuser (제한) - 일부 확장만 활성화 가능 - 파라미터는 Parameter Group 으로

Aurora = 추가 도구: - 자동 Read Replica - Backtrack (시간 되돌리기) - Database cloning (빠른 복제 — Copy-on-Write)

운영 자동화 — IaC

# Terraform
resource "aws_db_instance" "main" {
  identifier     = "prod-pg"
  engine         = "postgres"
  engine_version = "18"
  instance_class = "db.t4g.medium"
  allocated_storage = 100
  storage_type      = "gp3"

  db_name  = "appdb"
  username = "app_admin"
  password = data.aws_secretsmanager_secret_version.db.secret_string

  multi_az               = true
  backup_retention_period = 7
  performance_insights_enabled = true

  parameter_group_name = aws_db_parameter_group.main.name
}

운영 = IaC 무조건. 설정 코드화·재현 가능.

함정 5가지

(1) template1 오염

template1 에 박은 객체 = 모든 새 DB 에 복제. template0 사용.

(2) 인코딩·로케일 잘못

운영 가동 후 변경 = DB 재생성 필요. 처음부터 UTF8 + ko_KR.UTF-8.

(3) CONNECTION LIMIT 없음

특정 DB 가 모든 연결 차지 → 다른 DB 영향. 적정 한도.

(4) idle in transaction 누적

트랜잭션 안 열어 두고 잊음 = bloat. 자동 정리.

(5) TABLESPACE 잘못 사용

소규모에 TABLESPACE 분리 = 관리 부담. 큰 시스템부터.

한 줄 정리 — CREATE DATABASE 운영 옵션 + TEMPLATE 시스템 + TABLESPACE 분리. pg_stat_activity·pg_stat_database 모니터링. idle in transaction 정리. ALTER DATABASE SET 으로 DB 별 설정. RDS = Parameter Group + IaC.

시험 직전 한 번 더 — DB 관리 입문자가 매번 헷갈리는 것

• CREATE DATABASE = TEMPLATE template0 + UTF8 + ko_KR
• template0 vs template1 = template0 깨끗
• IS_TEMPLATE = 다른 DB 가 복제 가능
• ALLOW_CONNECTIONS = 차단·허용
• TABLESPACE = 디스크 영역 분리
• CONNECTION LIMIT 적정
• ALTER DATABASE SET = DB 별 설정
• timezone·search_path·work_mem 등
• pg_dump -Fc = 운영 백업
• pg_restore = 복원
• ALTER DATABASE RENAME
• SET TABLESPACE
• pg_stat_activity = 활성 연결
• state = active·idle·idle in transaction
• idle in transaction 오래 = bloat 위험
• pg_terminate_backend(pid)
• pg_stat_database = DB 전체 통계
• cache_hit_pct < 95% = 메모리 부족
• pg_database_size = DB 크기
• pg_total_relation_size = 테이블 + 인덱스
• pg_indexes_size = 인덱스만
• RDS = rds_superuser
• Aurora = Backtrack·Database cloning
• IaC = Terraform·Pulumi
• multi_az = 운영 무조건
• backup_retention_period = 7~30일
• performance_insights = RDS 표준
• 한국 운영 = template0 + UTF8 + IaC

시리즈 다른 편

• Part 3 운영: 42편 설치 · 43편 설정 · 44편 ROLE · 45편 (현재 글)

시리즈 다음 글

다음 글(46편)에서는 백업·복구 — pg_dump·pg_basebackup·PITR.

공식 문서: PostgreSQL 18 — Managing Databases에서 더 자세한 사양을 확인할 수 있어요.