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

스마트라이프onLeave a Comment on 백엔드 데이터 인프라 45편 — 데이터베이스 관리 생성·복제·드롭
2026-05-17•백엔드 데이터 인프라

백엔드 데이터 인프라 45편. PostgreSQL 데이터베이스 관리 — 생성·복제·드롭·이동·TEMPLATE 표준 풀어쓴 학습 노트.

📚 백엔드 데이터 인프라 · 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 시스템

PostgreSQL 의 "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 는 일반 PostgreSQL 과 거의 같지만 몇 가지 제약이 있어요. - 슈퍼유저 → 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 를 분리하면 관리 부담만 늘어요. 큰 시스템부터 적용합니다.

🎯 DB 운영 5룰

(1) template0 + UTF8 + ko_KR.UTF-8. (2) CONNECTION LIMIT 적정. (3) pg_stat_activity·pg_stat_database 모니터. (4) idle in transaction 자동 정리. (5) IaC 로 설정 코드화.

한 줄 정리 — 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

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

다음 글:

※ 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

답글 남기기

error: Content is protected !!