전체 치트시트데이터베이스

postgresql

PostgreSQL 14+

PostgreSQL 빠른 참조: psql 클라이언트 명령, DDL/DML 문, joins/aggregates, 인덱스, 트랜잭션, 백업/복원 — 자주 쓰는 옵션과 실전 예시까지 정리했습니다.

명령어 36개

연결

psql -h <host> -p <port> -U <user> -d <db>

원격 또는 로컬 DB 에 대해 인터랙티브 psql 세션을 엽니다.

-h host; -p 5432 기본; -U user; -d dbname; -W 패스워드 강제; -f file 스크립트 실행; -c 한 명령

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

DB 에 대해 SQL 스크립트를 실행, 첫 오류에서 중단.

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql 메타 명령

\l[+] \c[onnect] [<db>]

DB 목록; 그 중 하나로 연결.

\l+ 크기/tblsp 정보 포함; \c db user 대상 변경

\l+; \c app_dev

\dt[+] [<pattern>]

현재 스키마에서 패턴에 매치되는 테이블 목록(기본: public).

\dt+ 크기+설명; \dt *.* 모든 스키마; \d table 테이블 설명

\dt+ public.*

\d <table|view|index|seq|matview>

객체 구조 설명: 컬럼, 인덱스, FK, 코멘트.

\d orders

\dn / \du / \dv / \di

각각 스키마 / 역할 / 뷰 / 인덱스 목록.

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

쿼리 타이밍 토글; 확장 표시; 도움말 표시; 세션 종료.

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

파일에서 데이터 벌크 로드(서버 측이지만 클라이언트 파일 시스템을 사용).

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

데이터베이스 & 스키마

CREATE DATABASE <db>

템플릿 DB 옵션으로 새 데이터베이스를 만듭니다.

OWNER role; TEMPLATE template0; ENCODING 'UTF8'; LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

DB 삭제(사용 중이 아니어야 함; 종종 IF EXISTS + force 와 함께 사용).

DROP DATABASE WITH (FORCE); postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

테이블/뷰의 네임스페이스; 기본 search_path 의 일부.

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

테이블 & DDL

CREATE TABLE

새 테이블을 정의: 컬럼, 타입, 기본값, 제약.

PRIMARY KEY; FOREIGN KEY ... REFERENCES; UNIQUE; CHECK; INHERITS; PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

컬럼 추가/삭제/이름변경, 타입 변경, 제약 추가, 스토리지 파라미터 설정.

ADD COLUMN; DROP COLUMN; RENAME TO; ADD CONSTRAINT; ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

테이블 제거(FK 참조 시 CASCADE); TRUNCATE 는 빠르게 비웁니다.

DROP TABLE ... CASCADE; TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

DB 객체에 주석 추가 — \d+ 와 IDE 툴팁에 노출됩니다.

COMMENT ON COLUMN orders.total IS 'Subtotal before tax'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

한 개/여러 행 삽입; RETURNING 은 삽입된 행의 컬럼을 보여줍니다.

INSERT ... SELECT; ON CONFLICT DO NOTHING / DO UPDATE (upsert); RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

매치되는 행 수정; 의도가 아닌 경우 항상 WHERE 로 범위를 한정하세요.

UPDATE ... FROM t2 WHERE ...; CTE (WITH ...) + UPDATE; ... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

매치되는 행 삭제; 전체 테이블을 비울 때는 TRUNCATE 가 더 빠릅니다.

DELETE ... USING t2 WHERE ...; RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

쿼리 & 필터

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

필터, 정렬, 페이지 매김으로 행을 읽습니다.

WHERE col op ANY/ALL(array); LIMIT n OFFSET n; FETCH FIRST n ROWS ONLY; FOR UPDATE/SHARE 잠금

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

중복 제거, 컬럼 기준 집계, 그 결과에 대한 필터.

GROUP BY ROLLUP(a,b); GROUPING SETS; HAVING 집계 필터; FILTER (WHERE ...) NULL 유지

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

조인

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

여러 테이블의 행을 결합; LEFT JOIN 은 매치되지 않는 왼쪽 행을 유지합니다.

JOIN LATERAL; NATURAL JOIN; USING(col) (ON 대신); CROSS JOIN 곱집합

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

서브쿼리를 CTE 로 구체화; 계층/그래프에는 재귀 CTE.

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

집계

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

내장 집계 함수; FILTER 절이나 GROUP BY 와 함께 잘 어울립니다.

count/sum 내부의 DISTINCT; array_agg(string_agg) 안의 ORDER BY

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

인덱스 & 뷰

CREATE INDEX ...

WHERE/ORDER BY 를 가속; 부분 인덱스는 핫한 부분집합을 대상으로 할 수 있습니다.

CONCURRENTLY (배타 잠금 없음); INCLUDE (a,b) 커버링; USING btree|gin|gist|hash; partial WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

쿼리 플랜 표시; ANALYZE 는 실행해 실제 시간과 행 수를 보고합니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE); FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

스토리지 & 통계 유지: 죽은 튜플 재사용, 플래너 통계 새로 고침, 인덱스 재구축.

VACUUM (FULL) 테이블 재기록; ANALYZE 통계 갱신; REINDEX 재구축; autovacuum 이 보통 처리

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

저장된 쿼리; materialized view 는 실제로 행을 캐시(리프레시 필요).

CREATE OR REPLACE VIEW; MATERIALIZED VIEW ... WITH NO DATA; REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

트랜잭션

BEGIN / COMMIT / ROLLBACK

여러 문을 하나의 원자적 작업 단위로 묶습니다(ACID).

BEGIN ISOLATION LEVEL SERIALIZABLE; COMMIT/ROLLBACK AND CHAIN; SAVEPOINT 레이블

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; ROLLBACK ON ERROR; COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

트랜잭션이 끝날 때까지 선택 행을 잠금; 동시 업데이트를 방지합니다.

FOR UPDATE / SHARE; NOWAIT 또는 SKIP LOCKED (큐용)

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

백업 & 복원

pg_dump <db> -Fc -f <file.dump>

DB 를 커스텀 포맷 파일로 덤프(병렬 복원, 선택적 복원 가능).

-Fc custom; -Ft tar; -Fp plain SQL; --schema=; --exclude-table=; -Fd 의 -j jobs

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

custom/tar/dir 덤프 복원; 필요한 스키마/데이터/역할만.

--clean 객체 먼저 삭제; --if-exists; --schema=; --table=; --jobs=N; --no-owner; --single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

plain-SQL 덤프(pg_dump -Fp 출력)를 복원합니다.

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

사용자 & 권한

CREATE ROLE / ALTER ROLE

역할은 로그인 가능(LOGIN)하고 객체를 소유; 역할별로 속성 부여.

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

CREATE USER <name>

`CREATE ROLE ... LOGIN`의 축약형 — 로그인 가능한 사용자를 생성(기본 권한 없음).

[SUPERUSER|NOSUPERUSER]; CREATEDB|NOCREATEDB; CREATEROLE; INHERIT|NOINHERIT; PASSWORD '...'; VALID UNTIL 'timestamp'; IN ROLE group_name

CREATE USER app_reader WITH PASSWORD 's3cr3t' NOCREATEDB NOCREATEROLE VALID UNTIL '2026-12-31';

GRANT / REVOKE

테이블, 스키마, 데이터베이스, 함수에 권한 부여; 컬럼 단위도 가능.

GRANT SELECT, INSERT ON TABLE ...; GRANT USAGE ON SCHEMA ...; WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

search_path 와 표시 객체 조정(\dt public.*); pg_hba.conf 가 연결 허용 대상을 제어합니다.

SHOW search_path; ALTER ROLE app IN DATABASE app SET search_path = app, public; pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

상표 및 사용 안내

본 명령줄 도구 치트시트 모음은 오픈 커뮤니티 참고 자료입니다. 본 문서에서 언급되는 도구의 명칭, 로고, 상표(예: Git™, Docker™, Kubernetes®, kubectl, PostgreSQL®, MySQL®, Redis™, MongoDB®, Linux™, PowerShell™, Vim™ 등)는 각 권리자의 자산이며, 본 문서에서는 식별 및 참고 목적のみで 사용됩니다.

본 치트시트의 모든 내용은 독립적으로 작성된 단순화된 자체 완결형 참고 자료이며, 어떠한 공식 문서와도 계열·인정·출처 관계가 없습니다. 명령 목록, 플래그 설명, 예제는 모두 저자의 원본 단순화이며, 권위 있는 정보가 필요한 경우 각 도구의 공식 문서를 참조하시기 바랍니다.

명령 구문 및 옵션 플래그는 도구 버전에 따라 다를 수 있습니다. 표시되는 버전 태그는 안정 릴리스를 반영하며, 다른 버전에서는 동작이 다를 수 있습니다. 본 치트시트는 상표권자의 어떠한 선호나 권장도 의미하지 않습니다.

본 사이트는 사이트 정보의 이용으로 인해 발생하는 어떠한 직접적 또는 간접적 손해에 대해서도 법적 책임을 지지 않습니다.

PostgreSQL 소개

PostgreSQL은 1986년 UC 버클리의 POSTGRES 프로젝트(Michael Stonebraker)에서 시작되어 1996년에 오픈 소스화되고 1997년에 PostgreSQL로 이름을 바꾼 오픈 소스 객체 관계형 데이터베이스 관리 시스템입니다. 현재 안정 버전은 PostgreSQL 16+이며, PostgreSQL License(BSD 계열의宽松한 라이선스) 하에 글로벌 커뮤니티가 매년 릴리스합니다. PostgreSQL은 후에 표준이 되는 많은 기능을 먼저 도입했습니다: 읽기를 차단하지 않는 MVCC, 크래시 안전성을 보장하는 WAL(Write-Ahead Log), 사용자 정의 타입과 함수, 테이블 상속, 풍부한 인덱스(B-tree, hash, GiST, GIN, BRIN, 식 인덱스와 부분 인덱스). SQL:2003을 거의 완전히 지원하며, 윈도우 함수, CTE, 재귀 쿼리, `LATERAL` 조인을 포함합니다. PostGIS, pg_trgm, pgvector, pgcrypto 같은 확장이 지리 공간 검색, 퍼지 텍스트 검색, 벡터 유사도, 암호화를 서버를 포크하지 않고 추가합니다. 트랜잭션 OLTP, JSONB 문서 저장, 지리 공간 앱, 전문 검색 모두 PostgreSQL을 사용하고, 성능 문제는 언제든 `EXPLAIN (ANALYZE, BUFFERS)`부터 시작하세요. PostgreSQL은 로컬 서비스이므로, 연결은 네트워크 내부에 머물고 거의 절대 포트를 인터넷에 노출해서는 안 됩니다.

치트시트 버전 1.0.0