PostgreSQL 실습
0. 실습 환경 준비 (Ubuntu)
1) PostgreSQL 설치 (Ubuntu 22.04/24.04 공통)
sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql상태 확인:
systemctl status postgresql --no-pager2) 실습 DB/사용자 생성
- PostgreSQL 기본 계정
postgres로 접속:
sudo -iu postgres psql
- psql에서 실행:
CREATE DATABASE bootcamp_shop;
CREATE USER bootcamp WITH PASSWORD '1234';
GRANT ALL PRIVILEGES ON DATABASE bootcamp_shop TO bootcamp;
\q
- 권한부여(스키마 권한까지 안정적으로):
sudo -u postgres psql -d bootcamp_shop -c "GRANT ALL ON SCHEMA public TO bootcamp;"
sudo -u postgres psql -d bootcamp_shop -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO bootcamp;"
sudo -u postgres psql -d bootcamp_shop -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO bootcamp;"3) 접속 테스트
psql "host=localhost dbname=bootcamp_shop user=bootcamp password=1234"4) PostgreSQL 외부 접속 설정
PostgreSQL은 “네트워크 수신”과 “인증/접근 제어”를 분리해서 관리
→ 파일 2개를 반드시 설정해야 함
4-1. 설정 파일 위치
① 서버 수신 설정
/etc/postgresql/*/main/postgresql.conf
② 접속 허용/인증 규칙
/etc/postgresql/*/main/pg_hba.conf
4-2. 서버수신 설정 : listen_addresses 설정 (postgresql.conf)
기본값:
#listen_addresses = 'localhost'
외부 접속 허용:
listen_addresses = '*'
또는
listen_addresses = '0.0.0.0'
의미
'*': 모든 IPv4/IPv6 인터페이스'0.0.0.0': 모든 IPv4 인터페이스
4-3. 접속 허용 규칙 (pg_hba.conf)
형식
TYPE DATABASE USER ADDRESS METHOD
실습용(모든 IP 허용 ❌ 운영 비권장)
host all all 0.0.0.0/0 scram-sha-256
운영 권장 예시(특정 DB + 사용자 + 대역)
host all all 127.0.0.1/32 scram-sha-256
// 로컬 윈도우 주소
host bootcamp_shop bootcamp 192.168.80.1/32 scram-sha-256
PostgreSQL은 여기서 허용하지 않으면 절대 접속 불가
→
listen_addresses만 바꾸고 안 되는 이유의 90%
4-4. 서비스 재시작
sudo systemctl restart postgresql1. 스키마/샘플 데이터 적재
1-1) SQL 파일로 저장 후 실행
예: bootcamp_shop_pg.sql 생성:
cat > bootcamp_shop_pg.sql <<'SQL'
-- =========================================================
-- bootcamp_shop (PostgreSQL) Schema
-- =========================================================
-- (옵션) 실습 반복을 위한 초기화
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
-- =========================================================
-- 1. customers
-- =========================================================
CREATE TABLE customers (
customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'SUSPENDED')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- =========================================================
-- 2. products
-- =========================================================
CREATE TABLE products (
product_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
category TEXT NOT NULL,
price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
stock INTEGER NOT NULL CHECK (stock >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- =========================================================
-- 3. orders (order header)
-- =========================================================
CREATE TABLE orders (
order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
status TEXT NOT NULL DEFAULT 'CREATED'
CHECK (status IN ('CREATED', 'PAID', 'CANCELLED', 'REFUNDED')),
ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
total_amount NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0)
);
-- 인덱스(조회 빈도가 높은 컬럼)
CREATE INDEX idx_orders_customer_id_ordered_at
ON orders(customer_id, ordered_at DESC);
-- =========================================================
-- 4. order_items (order lines)
-- =========================================================
CREATE TABLE order_items (
order_item_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
-- PostgreSQL: 생성 컬럼(Generated Column)로 라인 금액 자동 계산 (MySQL과 문법/지원 차이 주의)
line_amount NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
-- 한 주문에서 같은 상품이 중복 라인으로 들어가는 것을 방지(정책에 따라 선택)
CONSTRAINT uq_order_items_order_product UNIQUE (order_id, product_id)
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- =========================================================
-- 5. payments
-- =========================================================
CREATE TABLE payments (
payment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
status TEXT NOT NULL DEFAULT 'PENDING'
CHECK (status IN ('PENDING', 'PAID', 'FAILED', 'REFUNDED')),
method TEXT NOT NULL DEFAULT 'CARD'
CHECK (method IN ('CARD', 'TRANSFER', 'VIRTUAL')),
amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
paid_at TIMESTAMPTZ NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- 일반적으로 "주문 1건당 결제 1건" 정책이라면 UNIQUE
CONSTRAINT uq_payments_order UNIQUE (order_id)
);
CREATE INDEX idx_payments_status_created_at ON payments(status, created_at DESC);
-- =========================================================
-- bootcamp_shop Sample Data (PostgreSQL)
-- =========================================================
-- 1) customers
INSERT INTO customers (customer_id, email, name, status, created_at) VALUES
(1, 'minsu@example.com', '김민수', 'ACTIVE', NOW() - INTERVAL '40 days'),
(2, 'jiyoon@example.com', '박지윤', 'ACTIVE', NOW() - INTERVAL '25 days'),
(3, 'seojun@example.com', '이서준', 'ACTIVE', NOW() - INTERVAL '10 days'),
(4, 'yuna@example.com', '최유나', 'SUSPENDED', NOW() - INTERVAL '5 days'),
(5, 'dohyuk@example.com', '정도혁', 'ACTIVE', NOW() - INTERVAL '2 days');
-- 2) products
INSERT INTO products (product_id, sku, name, category, price, stock, is_active, created_at) VALUES
(1, 'SKU-KEY-001', 'Mechanical Keyboard', 'INPUT', 119000.00, 50, TRUE, NOW() - INTERVAL '60 days'),
(2, 'SKU-MOU-002', 'Gaming Mouse', 'INPUT', 59000.00, 80, TRUE, NOW() - INTERVAL '60 days'),
(3, 'SKU-MON-003', '27inch Monitor', 'DISPLAY', 279000.00, 20, TRUE, NOW() - INTERVAL '45 days'),
(4, 'SKU-HDS-004', 'External SSD 1TB', 'STORAGE', 149000.00, 30, TRUE, NOW() - INTERVAL '30 days'),
(5, 'SKU-USB-005', 'USB-C Hub', 'ACCESS', 39000.00, 100, TRUE, NOW() - INTERVAL '20 days'),
(6, 'SKU-CAM-006', 'Webcam', 'ACCESS', 69000.00, 35, TRUE, NOW() - INTERVAL '15 days'),
(7, 'SKU-CHA-007', 'Office Chair', 'FURN', 189000.00, 10, TRUE, NOW() - INTERVAL '90 days'),
(8, 'SKU-MIC-008', 'USB Microphone', 'AUDIO', 99000.00, 25, TRUE, NOW() - INTERVAL '12 days');
-- 3) orders (총액은 일단 0으로 넣고, 아래에서 order_items 기준으로 갱신)
INSERT INTO orders (order_id, customer_id, status, ordered_at, total_amount) VALUES
(1001, 3, 'CREATED', NOW() - INTERVAL '1 day', 0),
(1002, 2, 'PAID', NOW() - INTERVAL '3 days', 0),
(1003, 1, 'PAID', NOW() - INTERVAL '8 days', 0),
(1004, 3, 'CANCELLED',NOW() - INTERVAL '15 days',0),
(1005, 5, 'CREATED', NOW() - INTERVAL '2 hours',0);
-- 4) order_items (unit_price는 “주문 시점 가격”으로 저장하는 패턴)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1001, 1, 1, 119000.00),
(1001, 5, 2, 39000.00),
(1002, 2, 1, 59000.00),
(1002, 4, 1, 149000.00),
(1003, 3, 1, 279000.00),
(1003, 6, 1, 69000.00),
(1003, 8, 1, 99000.00),
(1004, 5, 1, 39000.00),
(1005, 2, 2, 59000.00),
(1005, 1, 1, 119000.00);
-- 5) orders.total_amount 갱신 (order_items의 line_amount 합계)
UPDATE orders o
SET total_amount = t.sum_amount
FROM (
SELECT order_id, SUM(line_amount) AS sum_amount
FROM order_items
GROUP BY order_id
) t
WHERE o.order_id = t.order_id;
-- 6) payments (PAID 상태인 주문만 결제 생성)
INSERT INTO payments (payment_id, order_id, status, method, amount, paid_at, created_at) VALUES
(5001, 1002, 'PAID', 'CARD', (SELECT total_amount FROM orders WHERE order_id = 1002), NOW() - INTERVAL '3 days', NOW() - INTERVAL '3 days'),
(5002, 1003, 'PAID', 'TRANSFER', (SELECT total_amount FROM orders WHERE order_id = 1003), NOW() - INTERVAL '8 days', NOW() - INTERVAL '8 days');
-- 7) 재고 차감(샘플) - “PAID 주문만” 반영했다고 가정
UPDATE products p
SET stock = stock - x.qty
FROM (
SELECT oi.product_id, SUM(oi.quantity) AS qty
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY oi.product_id
) x
WHERE p.product_id = x.product_id;
-- 검증용 조회
SELECT * FROM customers ORDER BY customer_id;
SELECT * FROM products ORDER BY product_id;
SELECT * FROM orders ORDER BY order_id;
SELECT order_id, product_id, quantity, unit_price, line_amount
FROM order_items
ORDER BY order_id, product_id;
SELECT * FROM payments ORDER BY payment_id;
SQL
실행:
psql "host=localhost dbname=bootcamp_shop user=bootcamp password=1234" -f bootcamp_shop_pg.sql
1-2) 데이터 검증
psql 접속 후:
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;
SELECT COUNT(*) FROM payments;
SELECT * FROM orders ORDER BY order_id;
2. 데이터 모델 이해 실습 (ERD 관점)
핵심 관계
- customers(1) ── (N) orders
- orders(1) ── (N) order_items
- products(1) ── (N) order_items
- orders(1) ── (0..1) payments (UNIQUE(order_id))
2-1) FK 제약 확인
SELECT
tc.table_name, kcu.column_name, ccu.table_name AS ref_table, ccu.column_name AS ref_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, kcu.column_name;
실습문제(3단계)
- (하)
orders테이블의 status 허용값을 확인하라. - (중)
order_items의 유니크 제약(한 주문에서 같은 상품 중복 방지)을 조회로 확인하라. - (상)
payments가 “주문 1건당 결제 1건”을 어떻게 보장하는지 제약조건 기반으로 설명하라.
힌트:
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass;
Front-end Developer, Cloud Engineer