Split View: 데이터베이스: PostgreSQL 18 업그레이드 및 튜닝 2026

-- Check current authentication methods
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;

-- Migrate to SCRAM-SHA-256
SET password_encryption = 'scram-sha-256';
ALTER USER myapp_user PASSWORD 'new_secure_password';

-- Change md5 -> scram-sha-256 in pg_hba.conf
-- host all all 10.0.0.0/8 scram-sha-256

# Check installed extensions and PG 18 compatibility
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;
"

# Key extension compatibility (as of March 2026)
# pg_stat_statements: 1.11 -> compatible
# PostGIS: 3.5+ -> compatible
# pgvector: 0.8+ -> compatible
# pg_cron: 1.6+ -> compatible
# hypopg: 1.4+ -> compatible
# timescaledb: 2.17+ -> PG 18 support needs verification

# Install PG 18 binaries (Ubuntu/Debian)
sudo apt-get install postgresql-18

# Directory structure
# /usr/lib/postgresql/17/bin/  <- existing PG 17
# /usr/lib/postgresql/18/bin/  <- new PG 18
# /var/lib/postgresql/17/main/ <- existing data
# /var/lib/postgresql/18/main/ <- new data (created by pg_upgrade)

# Gracefully stop PG 17
sudo systemctl stop postgresql@17-main

# Initialize new PG 18 cluster
sudo -u postgres /usr/lib/postgresql/18/bin/initdb \
  -D /var/lib/postgresql/18/main \
  --encoding=UTF8 \
  --locale=ko_KR.UTF-8 \
  --data-checksums

# Always run in --check mode first
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

# Expected successful output:
# 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*

# Use --link option for hard links (completes in seconds without data copying)
# Note: rollback to the previous cluster is not possible when using --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

# Output example:
# 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 new feature!

# Start PG 18
sudo systemctl start postgresql@18-main

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

# Update extensions
psql -U postgres -d mydb -c "ALTER EXTENSION pg_stat_statements UPDATE;"
psql -U postgres -d mydb -c "ALTER EXTENSION postgis UPDATE;"

# Verify statistics status (PG 18: should already exist)
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 is the default (STORED must be explicitly specified)
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 disk space, computed at query time
    total_price numeric(10,2) GENERATED ALWAYS AS (
        base_price * (1 + tax_rate)
    ) VIRTUAL,
    -- STORED generated column: stored on disk, computed at INSERT/UPDATE
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('korean', name)
    ) STORED
);

-- VIRTUAL vs STORED comparison
-- VIRTUAL: 0 disk usage, CPU used on read, cannot create index directly
-- STORED: uses disk, returns immediately on read, index creation possible

-- Since indexes cannot be created directly on VIRTUAL columns,
-- use expression indexes when indexing is needed
CREATE INDEX idx_products_total ON products ((base_price * (1 + tax_rate)));

-- PG 18: Native UUIDv7 generation
SELECT uuidv7();
-- Result: 019576a0-6c00-7def-8000-1a2b3c4d5e6f
-- First 48 bits are Unix timestamp (milliseconds)

-- Using UUIDv7 as 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 index performance comparison experiment
-- Compare index size and page splits after 1 million INSERTs
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');

-- Expected results:
-- bench_uuid4 | table: 42MB | index: 34MB (many page splits due to random distribution)
-- bench_uuid7 | table: 42MB | index: 21MB (efficient packing due to sequential insertion)

-- Retrieve both before and after values in UPDATE
UPDATE products
SET base_price = base_price * 1.05  -- 5% increase
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;

-- Automatic audit log table pattern
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()
);

-- Combine UPDATE + RETURNING + INSERT with CTE
WITH price_changes AS (
    UPDATE products
    SET base_price = base_price * 0.9  -- 10% discount
    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;

-- Check AIO-related settings
SHOW io_method;              -- 'io_uring' or 'worker' or 'sync'
SHOW io_max_concurrency;     -- Number of concurrent async I/O requests

-- Recommended settings for NVMe SSD environments
ALTER SYSTEM SET io_method = 'io_uring';        -- Requires Linux 5.1+
ALTER SYSTEM SET io_max_concurrency = 256;
ALTER SYSTEM SET effective_io_concurrency = 256; -- Works with 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.

-- Find the problematic columns
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');

-- Solution: Convert the column to text
ALTER TABLE my_table ALTER COLUMN proc_col TYPE text;

# Check the deletion script generated by pg_upgrade
cat ./delete_old_cluster.sh

# Example content:
#!/bin/sh
rm -rf '/var/lib/postgresql/17/main'

# Warning: Never start the old cluster after using --link
# Since data files are shared via hard links, concurrent access from both sides causes corruption

# Remove extension before upgrade, reinstall after upgrade
psql -U postgres -d mydb -c "DROP EXTENSION IF EXISTS old_extension CASCADE;"

# Run pg_upgrade

# Install latest version of extension after upgrade
psql -U postgres -d mydb -c "CREATE EXTENSION old_extension VERSION '2.0';"

-- Save critical query plan snapshots before upgrade
\o /tmp/critical_query_plans_pg17.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- Critical query 1
SELECT ...;
-- Critical query 2
SELECT ...;
\o

-- Compare with the same query plans after upgrade
\o /tmp/critical_query_plans_pg18.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- Run the same queries
\o

-- Adjust cost parameters if plans have changed
-- Check default values changed in PG 18
SHOW random_page_cost;    -- May have changed with AIO introduction
SHOW cpu_tuple_cost;
SHOW parallel_tuple_cost;