PostgreSQL 설치와 기본 설정

sudo apt install -y postgresql postgresql-contrib

# 최신 버전 (PostgreSQL 공식 APT)
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update && sudo apt install -y postgresql-16

sudo systemctl enable --now postgresql

sudo -u postgres psql

# psql 내 명령어
\l          -- 데이터베이스 목록
\c mydb     -- 데이터베이스 전환
\dt         -- 테이블 목록
\d users    -- 테이블 구조
\du         -- 사용자 목록
\q          -- 종료

CREATE USER myapp WITH PASSWORD 'strongpassword123!';
CREATE DATABASE myappdb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myapp;
GRANT ALL ON SCHEMA public TO myapp;
GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO myapp;

listen_addresses = '*'          # 원격 접속 허용
max_connections = 100
shared_buffers = 256MB          # RAM의 25% 권장
work_mem = 4MB
log_slow_query = 1000           # 1초 이상 쿼리 로그

local   all   postgres    peer
local   all   all         md5
host    myappdb   myapp   10.0.0.0/24   scram-sha-256

sudo systemctl reload postgresql

pg_dump -U myapp -h localhost myappdb > backup.sql
pg_dump -U myapp -h localhost -Fc myappdb > backup.dump

sudo -u postgres pg_dumpall > all_databases.sql

psql -U myapp -h localhost myappdb < backup.sql
pg_restore -U myapp -h localhost -d myappdb backup.dump

-- 현재 연결 수
SELECT count(*) FROM pg_stat_activity;

-- 실행 중인 쿼리
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity WHERE state = 'active';

-- 테이블 크기
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;