✍️ 필사 모드: 데이터베이스: PostgreSQL 18 업그레이드 및 튜닝 2026

-- 현재 인증 방식 확인
SELECT usename, passwd IS NOT NULL AS has_password,
       CASE
           WHEN passwd LIKE 'md5%' THEN 'MD5'
           WHEN passwd LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
           ELSE 'unknown'
       END AS auth_method
FROM pg_shadow
WHERE passwd IS NOT NULL;

-- SCRAM-SHA-256으로 마이그레이션
SET password_encryption = 'scram-sha-256';
ALTER USER myapp_user PASSWORD 'new_secure_password';

-- pg_hba.conf에서 md5 -> scram-sha-256 변경
-- host all all 10.0.0.0/8 scram-sha-256

# 현재 설치된 확장 목록과 PG 18 호환 여부 확인
psql -U postgres -d mydb -c "
SELECT e.extname, e.extversion,
       a.default_version AS available_version
FROM pg_extension e
LEFT JOIN pg_available_extensions a ON a.name = e.extname
ORDER BY e.extname;
"

# 주요 확장 호환성 (2026년 3월 기준)
# pg_stat_statements: 1.11 -> 호환
# PostGIS: 3.5+ -> 호환
# pgvector: 0.8+ -> 호환
# pg_cron: 1.6+ -> 호환
# hypopg: 1.4+ -> 호환
# timescaledb: 2.17+ -> PG 18 지원 확인 필요

# PG 18 바이너리 설치 (Ubuntu/Debian)
sudo apt-get install postgresql-18

# 디렉토리 구조 확인
# /usr/lib/postgresql/17/bin/  <- 기존 PG 17
# /usr/lib/postgresql/18/bin/  <- 신규 PG 18
# /var/lib/postgresql/17/main/ <- 기존 데이터
# /var/lib/postgresql/18/main/ <- 신규 데이터 (pg_upgrade가 생성)

# PG 17 정상 종료
sudo systemctl stop postgresql@17-main

# 신규 PG 18 클러스터 초기화
sudo -u postgres /usr/lib/postgresql/18/bin/initdb \
  -D /var/lib/postgresql/18/main \
  --encoding=UTF8 \
  --locale=ko_KR.UTF-8 \
  --data-checksums

# 반드시 --check 모드로 먼저 실행
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --check

# 정상 출력 예시:
# Performing Consistency Checks
# -----------------------------
# Checking cluster versions                         ok
# Checking database user is the install user         ok
# Checking database connection settings              ok
# Checking for prepared transactions                 ok
# Checking for system-defined composite types        ok
# Checking for reg* data types in user tables        ok
# Checking for contrib/isn with bigint-passing mismatch ok
# Checking for user-defined encoding conversions     ok
# Checking for user-defined postfix operators         ok
# Checking for incompatible polymorphic functions     ok
# *Clusters are compatible*

# --link 옵션으로 하드링크 사용 (데이터 복사 없이 수초 내 완료)
# 주의: --link 사용 시 이전 클러스터로 롤백 불가
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --link \
  --jobs=4

# 출력 예시:
# Performing Upgrade
# ------------------
# ...
# Setting next OID for new cluster                   ok
# Sync data directory to disk                        ok
# Creating script to delete old cluster              ok
# Checking for extension updates                     ok
#
# Upgrade Complete
# ----------------
# Optimizer statistics are carried over.              <- PG 18 신기능!

# PG 18 시작
sudo systemctl start postgresql@18-main

# 버전 확인
psql -U postgres -c "SELECT version();"
# PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc ...

# 확장 업데이트
psql -U postgres -d mydb -c "ALTER EXTENSION pg_stat_statements UPDATE;"
psql -U postgres -d mydb -c "ALTER EXTENSION postgis UPDATE;"

# 통계 상태 확인 (PG 18: 이미 존재해야 함)
psql -U postgres -d mydb -c "
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;
"

-- PG 18: VIRTUAL이 기본값 (STORED는 명시 필요)
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    base_price numeric(10,2) NOT NULL,
    tax_rate numeric(4,3) NOT NULL DEFAULT 0.1,
    -- VIRTUAL generated column: 디스크 공간 0, 조회 시 계산
    total_price numeric(10,2) GENERATED ALWAYS AS (
        base_price * (1 + tax_rate)
    ) VIRTUAL,
    -- STORED generated column: 디스크 저장, INSERT/UPDATE 시 계산
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('korean', name)
    ) STORED
);

-- VIRTUAL vs STORED 비교
-- VIRTUAL: 디스크 0, 읽기 시 CPU 사용, 인덱스 생성 불가
-- STORED: 디스크 사용, 읽기 시 즉시 반환, 인덱스 생성 가능

-- VIRTUAL column에는 직접 인덱스를 만들 수 없으므로
-- 인덱스가 필요한 경우 expression index 사용
CREATE INDEX idx_products_total ON products ((base_price * (1 + tax_rate)));

-- PG 18: 네이티브 UUIDv7 생성
SELECT uuidv7();
-- 결과: 019576a0-6c00-7def-8000-1a2b3c4d5e6f
-- 앞 48비트가 Unix timestamp (밀리초)

-- UUIDv7을 Primary Key로 사용
CREATE TABLE distributed_events (
    id uuid DEFAULT uuidv7() PRIMARY KEY,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- UUIDv7 vs UUIDv4 인덱스 성능 비교 실험
-- 100만 건 INSERT 후 인덱스 크기와 페이지 분할 비교
CREATE TABLE bench_uuid4 (id uuid DEFAULT gen_random_uuid() PRIMARY KEY, val int);
CREATE TABLE bench_uuid7 (id uuid DEFAULT uuidv7() PRIMARY KEY, val int);

INSERT INTO bench_uuid4 (val) SELECT i FROM generate_series(1, 1000000) i;
INSERT INTO bench_uuid7 (val) SELECT i FROM generate_series(1, 1000000) i;

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relname IN ('bench_uuid4', 'bench_uuid7');

-- 예상 결과:
-- bench_uuid4 | table: 42MB | index: 34MB (랜덤 분산으로 페이지 분할 많음)
-- bench_uuid7 | table: 42MB | index: 21MB (순차 삽입으로 효율적 패킹)

-- UPDATE에서 변경 전/후 값을 함께 조회
UPDATE products
SET base_price = base_price * 1.05  -- 5% 인상
WHERE id BETWEEN 1 AND 100
RETURNING
    old.id,
    old.base_price AS old_price,
    new.base_price AS new_price,
    new.base_price - old.base_price AS price_diff;

-- 감사 로그 테이블 자동 생성 패턴
CREATE TABLE price_audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY,
    product_id bigint,
    old_price numeric(10,2),
    new_price numeric(10,2),
    changed_at timestamptz DEFAULT now()
);

-- UPDATE + RETURNING + INSERT를 CTE로 결합
WITH price_changes AS (
    UPDATE products
    SET base_price = base_price * 0.9  -- 10% 할인
    WHERE tax_rate > 0.15
    RETURNING old.id, old.base_price AS old_price, new.base_price AS new_price
)
INSERT INTO price_audit_log (product_id, old_price, new_price)
SELECT id, old_price, new_price FROM price_changes;

-- AIO 관련 설정 확인
SHOW io_method;              -- 'io_uring' 또는 'worker' 또는 'sync'
SHOW io_max_concurrency;     -- 동시 비동기 I/O 요청 수

-- NVMe SSD 환경 권장 설정
ALTER SYSTEM SET io_method = 'io_uring';        -- Linux 5.1+ 필요
ALTER SYSTEM SET io_max_concurrency = 256;
ALTER SYSTEM SET effective_io_concurrency = 256; -- AIO와 연동
SELECT pg_reload_conf();

Checking for reg* data types in user tables     FAILED

Your installation contains one of the reg* data types in user tables.
These data types reference system OIDs that are not preserved by
pg_upgrade.

-- 문제가 되는 컬럼 찾기
SELECT n.nspname, c.relname, a.attname, t.typname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname IN ('regproc','regprocedure','regoper','regoperator',
                    'regclass','regtype','regconfig','regdictionary')
  AND c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog','information_schema');

-- 해결: 해당 컬럼을 text로 변환
ALTER TABLE my_table ALTER COLUMN proc_col TYPE text;

# pg_upgrade가 생성하는 삭제 스크립트 확인
cat ./delete_old_cluster.sh

# 내용 예시:
#!/bin/sh
rm -rf '/var/lib/postgresql/17/main'

# 주의: --link 사용 후에는 이전 클러스터를 절대 시작하면 안 된다
# 하드링크로 데이터 파일을 공유하므로 양쪽에서 동시 접근하면 손상됨

# 업그레이드 전 확장 제거, 업그레이드 후 재설치
psql -U postgres -d mydb -c "DROP EXTENSION IF EXISTS old_extension CASCADE;"

# pg_upgrade 실행

# 업그레이드 후 최신 버전 확장 설치
psql -U postgres -d mydb -c "CREATE EXTENSION old_extension VERSION '2.0';"

-- 업그레이드 전에 핵심 쿼리 플랜 스냅샷 저장
\o /tmp/critical_query_plans_pg17.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- 핵심 쿼리 1
SELECT ...;
-- 핵심 쿼리 2
SELECT ...;
\o

-- 업그레이드 후 동일 쿼리 플랜 비교
\o /tmp/critical_query_plans_pg18.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- 동일 쿼리 실행
\o

-- 플랜이 달라진 경우 cost 파라미터 조정
-- PG 18에서 변경된 기본값 확인
SHOW random_page_cost;    -- AIO 도입으로 변경되었을 수 있음
SHOW cpu_tuple_cost;
SHOW parallel_tuple_cost;