PostgreSQL 명령어 치트시트

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

명령어 35개

연결

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'

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;

치트시트 버전 1.0.0

PostgreSQL 14+ 대응