PostgreSQL 사용법과 명령어: 설치, psql, 권한, 백업, 모니터링

sudo apt update
sudo apt install postgresql-16          # 서버 + contrib 모듈 포함
sudo apt install postgresql-client-16   # 클라이언트만 필요할 때

sudo locale-gen ko_KR.UTF-8
sudo LANG=ko_KR.UTF-8 apt install postgresql-16

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install postgresql-17          # 원하는 메이저 버전

pg_lsclusters
psql --version
sudo systemctl status postgresql@18-main
sudo -u postgres psql -c "SELECT version();"

sudo systemctl start|stop|restart|reload|status postgresql@18-main
sudo systemctl enable postgresql        # 부팅 시 모든 클러스터 시작
sudo systemctl status postgresql        # 전체 클러스터 상위 유닛

pg_lsclusters                              # 클러스터 목록, 포트, 상태
sudo pg_ctlcluster 18 main start|stop|restart|reload|status
sudo pg_createcluster 18 second -p 5433    # 새 클러스터 (포트 지정)
sudo pg_createcluster --locale ko_KR.UTF-8 18 main
sudo pg_createcluster 18 main -- --locale-provider=icu --icu-locale=ko-KR   # initdb 옵션 전달
sudo pg_dropcluster --stop 18 second       # 클러스터 삭제 (데이터 소멸)
sudo pg_upgradecluster 18 main             # 다음 메이저 버전으로 업그레이드
sudo pg_renamecluster 18 main primary

sudo -u postgres psql                       # 로컬 슈퍼유저 (peer 인증)
psql -U app -d appdb -h localhost -p 5432 -W  # 비밀번호 인증
psql "postgresql://app:secret@localhost:5432/appdb"   # URI
psql "host=localhost dbname=appdb user=app sslmode=require"
psql -d appdb -c "SELECT now();"            # 단일 명령
psql -d appdb -f script.sql                 # 파일 실행
psql -d appdb -At -c "SELECT id FROM t;"    # 정렬 없이 값만 (스크립트용)
psql -d appdb -X -q -v ON_ERROR_STOP=1 -f migrate.sql   # 오류 시 중단

# ~/.pgpass  (권한 600 필수)
# hostname:port:database:username:password
localhost:5432:*:app:secret
chmod 600 ~/.pgpass

-- 역할
CREATE ROLE app WITH LOGIN PASSWORD 'secret';
CREATE ROLE readonly NOLOGIN;
CREATE USER admin WITH SUPERUSER PASSWORD 'x';   -- CREATE USER = CREATE ROLE ... LOGIN
ALTER ROLE app WITH PASSWORD 'new';
ALTER ROLE app CONNECTION LIMIT 20;
ALTER ROLE app SET search_path = app, public;
GRANT readonly TO app;
DROP ROLE app;

-- 데이터베이스
CREATE DATABASE appdb OWNER app ENCODING 'UTF8';
CREATE DATABASE appdb2 TEMPLATE template0 LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8';
ALTER DATABASE appdb OWNER TO app;
DROP DATABASE appdb;                              -- 접속 중이면 실패
DROP DATABASE appdb WITH (FORCE);                 -- 접속 강제 종료 후 삭제

-- 스키마
CREATE SCHEMA app AUTHORIZATION app;

-- 권한
GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;  -- 앞으로 만들 테이블에도
GRANT ALL PRIVILEGES ON DATABASE appdb TO app;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

sudo -u postgres createuser --pwprompt app
sudo -u postgres createdb -O app appdb
sudo -u postgres dropdb appdb
sudo -u postgres dropuser app

-- 그룹 역할
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;

-- 계층별 권한
GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT CREATE ON SCHEMA public TO app_rw;          -- 15 이후 앱이 테이블을 만들려면 필요

-- 앞으로 만들어질 객체에도 자동 적용 (객체를 만드는 역할 기준)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

-- 실제 로그인 사용자
CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE ROLE analyst LOGIN PASSWORD 'secret';
GRANT app_rw TO app_user;
GRANT app_ro TO analyst;

GRANT pg_read_all_data TO analyst;    -- 모든 DB의 모든 테이블 읽기
GRANT pg_write_all_data TO etl;       -- 쓰기
GRANT pg_monitor TO grafana;          -- 통계 뷰·설정 조회
GRANT pg_read_server_files TO loader; -- 서버 파일 COPY FROM
GRANT pg_checkpoint TO ops;           -- CHECKPOINT 실행 (15 이후)

\du                          -- 역할과 속성, 소속 그룹
\l                           -- DB 권한 (Access privileges 열)
\dn+                         -- 스키마 권한
\dp users                    -- 테이블·컬럼 권한
\ddp                         -- 기본 권한 설정
SELECT has_table_privilege('analyst', 'users', 'SELECT');
SELECT has_schema_privilege('analyst', 'public', 'USAGE');
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants WHERE grantee = 'analyst';
SELECT rolname FROM pg_roles WHERE pg_has_role('app_user', oid, 'member');   -- 소속 그룹

REVOKE INSERT ON users FROM app_rw;
REVOKE app_rw FROM app_user;
REVOKE ALL ON SCHEMA public FROM PUBLIC;     -- 모든 역할에 암묵 부여된 권한 회수
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC; -- 기본적으로 모든 역할이 CONNECT 가능하므로

REASSIGN OWNED BY old_user TO postgres;   -- 소유 객체를 넘기고 (접속한 DB 기준)
DROP OWNED BY old_user;                   -- 남은 권한을 지운 뒤
DROP ROLE old_user;                       -- 여러 DB에 객체가 있으면 각 DB에서 반복

listen_addresses = 'localhost'      # '*' 또는 '0.0.0.0,::' 로 외부 허용
port = 5432
max_connections = 100
shared_buffers = 128MB              # RAM 25% 권장
work_mem = 4MB
maintenance_work_mem = 64MB
effective_cache_size = 4GB
wal_level = replica                 # 논리 복제는 logical
max_wal_size = 1GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1              # SSD
log_min_duration_statement = 1000   # 1초 넘는 쿼리 로깅 (ms)
log_line_prefix = '%m [%p] %u@%d '
timezone = 'Asia/Seoul'
shared_preload_libraries = 'pg_stat_statements'   # 변경 시 재시작 필요

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
max_connections = 100

SHOW shared_buffers;
SHOW ALL;
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE 'log%';
-- context가 postmaster면 재시작, sighup이면 reload, user면 세션 단위

ALTER SYSTEM SET work_mem = '16MB';      -- postgresql.auto.conf에 기록
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();                 -- reload
SET work_mem = '64MB';                   -- 현재 세션만

sudo systemctl reload postgresql@18-main    # sighup 항목
sudo systemctl restart postgresql@18-main   # postmaster 항목

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
# 내부망 허용 예시
host    appdb           app             192.168.0.0/24          scram-sha-256
# 도커 브리지 네트워크 허용 예시
host    appdb           app             172.16.0.0/12           scram-sha-256
# SSL 강제
hostssl all             all             0.0.0.0/0               scram-sha-256

CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    name        text NOT NULL,
    profile     jsonb DEFAULT '{}'::jsonb,
    tags        text[] DEFAULT '{}',
    is_active   boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX users_created_at_idx ON users (created_at DESC);
CREATE INDEX users_profile_gin ON users USING gin (profile);
CREATE INDEX users_lower_email ON users (lower(email));
CREATE INDEX CONCURRENTLY users_active_idx ON users (id) WHERE is_active;

INSERT INTO users (email, name, profile) VALUES ('a@x.com', 'Kim', '{"age": 30}') RETURNING id;

-- UPSERT
INSERT INTO users (email, name) VALUES ('a@x.com', 'Kim2')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- JSONB
SELECT profile->>'age' FROM users WHERE profile @> '{"age": 30}';
UPDATE users SET profile = profile || '{"city": "Seoul"}' WHERE id = 1;

-- 배열
SELECT * FROM users WHERE 'vip' = ANY(tags);
UPDATE users SET tags = array_append(tags, 'vip') WHERE id = 1;

-- 윈도우, CTE
WITH ranked AS (
    SELECT id, name, row_number() OVER (ORDER BY created_at DESC) AS rn FROM users
)
SELECT * FROM ranked WHERE rn <= 10;

-- 트랜잭션
BEGIN;
UPDATE users SET is_active = false WHERE id = 1;
SAVEPOINT sp1;
DELETE FROM users WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- 실행 계획
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'a@x.com';

-- 컬럼 단위 한국어 정렬 (C.UTF-8 클러스터에서)
CREATE COLLATION ko (provider = icu, locale = 'ko-KR');
SELECT name FROM users ORDER BY name COLLATE "ko";

# 커스텀 포맷 (압축, 선택 복원 가능, 권장)
sudo -u postgres pg_dump -Fc appdb -f appdb.dump
# 평문 SQL
sudo -u postgres pg_dump appdb > appdb.sql
# 스키마만 / 데이터만 / 특정 테이블
sudo -u postgres pg_dump -s appdb > schema.sql
sudo -u postgres pg_dump -a -t users appdb > users_data.sql
# 병렬 디렉터리 포맷
sudo -u postgres pg_dump -Fd -j 4 appdb -f appdb_dir/
# 전체 클러스터 (역할, 테이블스페이스 포함)
sudo -u postgres pg_dumpall > all.sql
sudo -u postgres pg_dumpall --globals-only > globals.sql   # 역할만

sudo -u postgres createdb appdb_restored
sudo -u postgres pg_restore -d appdb_restored appdb.dump
sudo -u postgres pg_restore -d appdb --clean --if-exists -j 4 appdb.dump
sudo -u postgres pg_restore -l appdb.dump              # 내용 목록
sudo -u postgres pg_restore -d appdb -t users appdb.dump  # 특정 테이블만
sudo -u postgres psql -d appdb -f appdb.sql            # 평문 SQL
sudo -u postgres psql -f all.sql postgres              # pg_dumpall 복원

sudo -u postgres pg_basebackup -D /backup/base -Fp -Xs -P -c fast
sudo -u postgres pg_basebackup -D /backup/base.tar -Ft -z -Xs -P   # tar.gz
# 17 이상 증분
sudo -u postgres pg_basebackup -D /backup/incr1 --incremental=/backup/base/backup_manifest

# /etc/cron.d/pg-backup
0 3 * * * postgres pg_dump -Fc appdb -f /backup/pg/appdb_$(date +\%F).dump && find /backup/pg -name '*.dump' -mtime +14 -delete

VACUUM;                          -- 전체 DB 죽은 튜플 회수
VACUUM (VERBOSE, ANALYZE) users; -- 특정 테이블 + 통계
VACUUM FULL users;               -- 파일 재작성으로 공간 반환. 배타 락, 오래 걸림
ANALYZE;                         -- 플래너 통계 갱신
REINDEX INDEX CONCURRENTLY users_email_key;
REINDEX TABLE CONCURRENTLY users;
CLUSTER users USING users_created_at_idx;   -- 인덱스 순서로 물리 재정렬

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 1000);

sudo -u postgres vacuumdb --all --analyze-in-stages   # 업그레이드 후 통계 재생성
sudo -u postgres reindexdb appdb

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY runtime DESC;

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

SELECT pg_cancel_backend(12345);     -- 쿼리만 취소
SELECT pg_terminate_backend(12345);  -- 접속 종료

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks WHERE NOT granted;

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY 2 DESC;

SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid)) AS table_only
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;

# postgresql.conf 에 shared_preload_libraries = 'pg_stat_statements' 추가 후 재시작

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

SELECT pg_stat_statements_reset();

SELECT relname, idx_scan, seq_scan, n_live_tup
FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;   -- seq_scan 많으면 인덱스 검토

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;  -- 안 쓰는 인덱스

SELECT round(sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

SELECT client_addr, state, sent_lsn, replay_lsn, replay_lag FROM pg_stat_replication;  -- primary
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn();                                  -- standby
SELECT slot_name, active, wal_status FROM pg_replication_slots;

sudo tail -f /var/log/postgresql/postgresql-18-main.log
sudo journalctl -u postgresql@18-main -n 100

SELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER EXTENSION pg_trgm UPDATE;
DROP EXTENSION pg_trgm;
\dx

sudo apt install postgresql-18-postgis-3 postgresql-18-pgvector postgresql-18-cron

services:
  db:
    image: postgres:18
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: change_me
      POSTGRES_DB: appdb
      POSTGRES_INITDB_ARGS: "--locale-provider=icu --icu-locale=ko-KR"
    ports:
      - "5433:5432"          # 호스트 5432는 네이티브용
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 10s
volumes:
  pgdata:

docker compose exec db psql -U app -d appdb
docker compose exec db pg_dump -U app -Fc appdb > appdb.dump

sudo apt install pgloader
pgloader mysql://user:pass@localhost/srcdb postgresql://app:secret@localhost/appdb