Injae's Laboratory
PostgreSQL 자주 사용 명령 레퍼런스
# PostgreSQL 자주 사용 명령 레퍼런스
PostgreSQL 16 기준 | macOS / Linux
---
## 1. psql 접속
```bash
psql postgres # 기본 DB에 현재 OS 유저로 접속
psql -U postgres # postgres 유저로 접속
psql -U <user> -d <db> # 특정 유저 + 특정 DB 접속
psql -h <host> -p 5432 -U <user> -d <db> # 원격 서버 접속
psql -h localhost -U <user> -d <db> # 로컬 TCP 접속 (소켓 아님)
\q # psql 종료
```
---
## 2. psql 메타 명령 (\backslash)
```
\l 데이터베이스 목록
\c <db> 데이터베이스 전환
\dt 현재 DB 테이블 목록
\dt *.* 모든 스키마 테이블 목록
\d <table> 테이블 구조 (컬럼, 인덱스, 제약)
\di 인덱스 목록
\dv 뷰 목록
\df 함수 목록
\dn 스키마 목록
\du 유저/롤 목록
\dp <table> 테이블 권한 목록
\timing 쿼리 실행 시간 표시 토글
\x 확장 출력 모드 토글 (세로 출력)
\i <file.sql> SQL 파일 실행
\o <file> 출력 결과를 파일로 저장
\! <cmd> 쉘 명령 실행 (예: \! ls)
\? 메타 명령 전체 도움말
\h <SQL> SQL 문법 도움말 (예: \h CREATE TABLE)
```
---
## 3. 데이터베이스 & 유저 관리
```sql
-- 데이터베이스
CREATE DATABASE mydb;
CREATE DATABASE mydb OWNER myuser;
DROP DATABASE mydb;
ALTER DATABASE mydb RENAME TO newdb;
-- 유저
CREATE USER myuser WITH PASSWORD 'pw';
ALTER USER myuser WITH PASSWORD 'newpw';
ALTER USER myuser WITH SUPERUSER;
DROP USER myuser;
-- 권한
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT ALL ON ALL TABLES IN SCHEMA public TO myuser;
```
---
## 4. 스키마 & 테이블
```sql
-- 스키마
CREATE SCHEMA myschema;
SET search_path TO myschema;
-- 테이블 생성
CREATE TABLE t (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT
);
-- 테이블 수정
ALTER TABLE t ADD COLUMN email TEXT;
ALTER TABLE t DROP COLUMN age;
ALTER TABLE t RENAME COLUMN old_name TO new_name;
ALTER TABLE t ALTER COLUMN name TYPE VARCHAR(100);
-- 테이블 삭제
DROP TABLE t;
TRUNCATE TABLE t; -- 데이터 전체 삭제 (빠름)
TRUNCATE TABLE t RESTART IDENTITY; -- 데이터 삭제 + 시퀀스 초기화
```
---
## 5. 기본 CRUD
```sql
-- 조회
SELECT * FROM t;
SELECT * FROM t WHERE id = 1;
SELECT * FROM t ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT COUNT(*), AVG(age) FROM t;
-- 삽입
INSERT INTO t (name) VALUES ('홍길동');
INSERT INTO t (name) VALUES ('홍길동') RETURNING id; -- 삽입 후 ID 반환
-- 수정
UPDATE t SET name = '임꺽정' WHERE id = 1;
-- 삭제
DELETE FROM t WHERE id = 1;
```
---
## 6. 인덱스
```sql
CREATE INDEX idx_name ON t(name); -- 일반 인덱스
CREATE UNIQUE INDEX idx_email ON t(email); -- 유니크 인덱스
CREATE INDEX CONCURRENTLY idx ON t(col); -- 서비스 중단 없이 생성
DROP INDEX idx_name;
-- 실행 계획
EXPLAIN SELECT * FROM t WHERE name = 'a';
EXPLAIN ANALYZE SELECT * FROM t WHERE name = 'a'; -- 실제 실행 + 통계 포함
```
---
## 7. 트랜잭션
```sql
BEGIN;
UPDATE t SET name = 'a' WHERE id = 1;
SAVEPOINT sp1;
DELETE FROM t WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1; -- sp1 이후만 롤백
COMMIT;
ROLLBACK; -- 전체 롤백
```
---
## 8. 백업 & 복원 (터미널)
```bash
# 덤프
pg_dump -U user -d mydb > backup.sql # SQL 형식
pg_dump -U user -Fc -d mydb > backup.dump # 압축 커스텀 형식 (권장)
pg_dump -U user -Fc -d mydb -t mytable > table.dump # 특정 테이블만
pg_dumpall -U postgres > all.sql # 전체 DB + 유저
# 복원
psql -U user -d mydb < backup.sql # SQL 파일 복원
pg_restore -U user -d mydb backup.dump # 커스텀 형식 복원
pg_restore -U user -d mydb -t mytable backup.dump # 특정 테이블만 복원
pg_restore -U user -d mydb --clean backup.dump # 복원 전 기존 객체 DROP
```
> ※ pg_dump는 서비스 중에도 실행 가능 (일관성 스냅샷 보장)
> ※ -Fc 덤프는 pg_restore 전용. psql로는 복원 불가
---
## 9. 유지보수
```sql
VACUUM t; -- 불필요한 버전 정리 (공간 회수 안됨)
VACUUM FULL t; -- 완전 정리 + 디스크 공간 회수 (테이블 락 발생)
VACUUM ANALYZE t; -- 정리 + 통계 업데이트
ANALYZE t; -- 쿼리 플래너용 통계만 업데이트
REINDEX TABLE t; -- 테이블 인덱스 재구성
REINDEX DATABASE mydb; -- DB 전체 인덱스 재구성
```
---
## 10. 시스템 정보 & 모니터링
```sql
-- 버전/설정
SHOW server_version;
SHOW data_directory;
SHOW max_connections;
-- 크기
SELECT pg_size_pretty(pg_database_size('mydb')); -- DB 크기
SELECT pg_size_pretty(pg_total_relation_size('t')); -- 테이블 크기
SELECT pg_size_pretty(pg_indexes_size('t')); -- 인덱스 크기
-- 활성 커넥션/쿼리
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';
-- 슬로우 쿼리 (pg_stat_statements 확장 필요)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
-- 락
SELECT * FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;
-- 테이블 통계
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```
> ※ pg_stat_statements 사용하려면 postgresql.conf에서
> shared_preload_libraries = 'pg_stat_statements' 설정 후 재시작 필요
---
## 11. Homebrew 서비스 관리 (macOS)
```bash
brew services start postgresql@16 # 시작 + 자동시작 등록
brew services stop postgresql@16 # 중지 + 자동시작 해제
brew services restart postgresql@16 # 재시작
brew services run postgresql@16 # 1회 시작 (자동시작 등록 안됨)
brew services list # 서비스 상태 전체 확인
```
---
## 12. pgloader (MS SQL → PostgreSQL 마이그레이션)
```bash
brew install pgloader # macOS 설치
pgloader migrate.load # 설정 파일로 실행
pgloader --verbose migrate.load # 상세 로그와 함께 실행
```
### migrate.load 기본 예시
```
LOAD DATABASE
FROM mssql://sa:password@localhost/SourceDB
INTO pgsql://postgres@localhost/TargetDB
WITH include drop,
create tables,
create indexes,
reset sequences;
```
### 특정 테이블만 마이그레이션
```
LOAD DATABASE
FROM mssql://sa:password@localhost/SourceDB
INTO pgsql://postgres@localhost/TargetDB
INCLUDING ONLY TABLE NAMES MATCHING 'users', 'orders'
WITH create tables, reset sequences;
```
---
PostgreSQL 16 기준 | macOS / Linux
---
## 1. psql 접속
```bash
psql postgres # 기본 DB에 현재 OS 유저로 접속
psql -U postgres # postgres 유저로 접속
psql -U <user> -d <db> # 특정 유저 + 특정 DB 접속
psql -h <host> -p 5432 -U <user> -d <db> # 원격 서버 접속
psql -h localhost -U <user> -d <db> # 로컬 TCP 접속 (소켓 아님)
\q # psql 종료
```
---
## 2. psql 메타 명령 (\backslash)
```
\l 데이터베이스 목록
\c <db> 데이터베이스 전환
\dt 현재 DB 테이블 목록
\dt *.* 모든 스키마 테이블 목록
\d <table> 테이블 구조 (컬럼, 인덱스, 제약)
\di 인덱스 목록
\dv 뷰 목록
\df 함수 목록
\dn 스키마 목록
\du 유저/롤 목록
\dp <table> 테이블 권한 목록
\timing 쿼리 실행 시간 표시 토글
\x 확장 출력 모드 토글 (세로 출력)
\i <file.sql> SQL 파일 실행
\o <file> 출력 결과를 파일로 저장
\! <cmd> 쉘 명령 실행 (예: \! ls)
\? 메타 명령 전체 도움말
\h <SQL> SQL 문법 도움말 (예: \h CREATE TABLE)
```
---
## 3. 데이터베이스 & 유저 관리
```sql
-- 데이터베이스
CREATE DATABASE mydb;
CREATE DATABASE mydb OWNER myuser;
DROP DATABASE mydb;
ALTER DATABASE mydb RENAME TO newdb;
-- 유저
CREATE USER myuser WITH PASSWORD 'pw';
ALTER USER myuser WITH PASSWORD 'newpw';
ALTER USER myuser WITH SUPERUSER;
DROP USER myuser;
-- 권한
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT ALL ON ALL TABLES IN SCHEMA public TO myuser;
```
---
## 4. 스키마 & 테이블
```sql
-- 스키마
CREATE SCHEMA myschema;
SET search_path TO myschema;
-- 테이블 생성
CREATE TABLE t (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT
);
-- 테이블 수정
ALTER TABLE t ADD COLUMN email TEXT;
ALTER TABLE t DROP COLUMN age;
ALTER TABLE t RENAME COLUMN old_name TO new_name;
ALTER TABLE t ALTER COLUMN name TYPE VARCHAR(100);
-- 테이블 삭제
DROP TABLE t;
TRUNCATE TABLE t; -- 데이터 전체 삭제 (빠름)
TRUNCATE TABLE t RESTART IDENTITY; -- 데이터 삭제 + 시퀀스 초기화
```
---
## 5. 기본 CRUD
```sql
-- 조회
SELECT * FROM t;
SELECT * FROM t WHERE id = 1;
SELECT * FROM t ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT COUNT(*), AVG(age) FROM t;
-- 삽입
INSERT INTO t (name) VALUES ('홍길동');
INSERT INTO t (name) VALUES ('홍길동') RETURNING id; -- 삽입 후 ID 반환
-- 수정
UPDATE t SET name = '임꺽정' WHERE id = 1;
-- 삭제
DELETE FROM t WHERE id = 1;
```
---
## 6. 인덱스
```sql
CREATE INDEX idx_name ON t(name); -- 일반 인덱스
CREATE UNIQUE INDEX idx_email ON t(email); -- 유니크 인덱스
CREATE INDEX CONCURRENTLY idx ON t(col); -- 서비스 중단 없이 생성
DROP INDEX idx_name;
-- 실행 계획
EXPLAIN SELECT * FROM t WHERE name = 'a';
EXPLAIN ANALYZE SELECT * FROM t WHERE name = 'a'; -- 실제 실행 + 통계 포함
```
---
## 7. 트랜잭션
```sql
BEGIN;
UPDATE t SET name = 'a' WHERE id = 1;
SAVEPOINT sp1;
DELETE FROM t WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1; -- sp1 이후만 롤백
COMMIT;
ROLLBACK; -- 전체 롤백
```
---
## 8. 백업 & 복원 (터미널)
```bash
# 덤프
pg_dump -U user -d mydb > backup.sql # SQL 형식
pg_dump -U user -Fc -d mydb > backup.dump # 압축 커스텀 형식 (권장)
pg_dump -U user -Fc -d mydb -t mytable > table.dump # 특정 테이블만
pg_dumpall -U postgres > all.sql # 전체 DB + 유저
# 복원
psql -U user -d mydb < backup.sql # SQL 파일 복원
pg_restore -U user -d mydb backup.dump # 커스텀 형식 복원
pg_restore -U user -d mydb -t mytable backup.dump # 특정 테이블만 복원
pg_restore -U user -d mydb --clean backup.dump # 복원 전 기존 객체 DROP
```
> ※ pg_dump는 서비스 중에도 실행 가능 (일관성 스냅샷 보장)
> ※ -Fc 덤프는 pg_restore 전용. psql로는 복원 불가
---
## 9. 유지보수
```sql
VACUUM t; -- 불필요한 버전 정리 (공간 회수 안됨)
VACUUM FULL t; -- 완전 정리 + 디스크 공간 회수 (테이블 락 발생)
VACUUM ANALYZE t; -- 정리 + 통계 업데이트
ANALYZE t; -- 쿼리 플래너용 통계만 업데이트
REINDEX TABLE t; -- 테이블 인덱스 재구성
REINDEX DATABASE mydb; -- DB 전체 인덱스 재구성
```
---
## 10. 시스템 정보 & 모니터링
```sql
-- 버전/설정
SHOW server_version;
SHOW data_directory;
SHOW max_connections;
-- 크기
SELECT pg_size_pretty(pg_database_size('mydb')); -- DB 크기
SELECT pg_size_pretty(pg_total_relation_size('t')); -- 테이블 크기
SELECT pg_size_pretty(pg_indexes_size('t')); -- 인덱스 크기
-- 활성 커넥션/쿼리
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';
-- 슬로우 쿼리 (pg_stat_statements 확장 필요)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
-- 락
SELECT * FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;
-- 테이블 통계
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```
> ※ pg_stat_statements 사용하려면 postgresql.conf에서
> shared_preload_libraries = 'pg_stat_statements' 설정 후 재시작 필요
---
## 11. Homebrew 서비스 관리 (macOS)
```bash
brew services start postgresql@16 # 시작 + 자동시작 등록
brew services stop postgresql@16 # 중지 + 자동시작 해제
brew services restart postgresql@16 # 재시작
brew services run postgresql@16 # 1회 시작 (자동시작 등록 안됨)
brew services list # 서비스 상태 전체 확인
```
---
## 12. pgloader (MS SQL → PostgreSQL 마이그레이션)
```bash
brew install pgloader # macOS 설치
pgloader migrate.load # 설정 파일로 실행
pgloader --verbose migrate.load # 상세 로그와 함께 실행
```
### migrate.load 기본 예시
```
LOAD DATABASE
FROM mssql://sa:password@localhost/SourceDB
INTO pgsql://postgres@localhost/TargetDB
WITH include drop,
create tables,
create indexes,
reset sequences;
```
### 특정 테이블만 마이그레이션
```
LOAD DATABASE
FROM mssql://sa:password@localhost/SourceDB
INTO pgsql://postgres@localhost/TargetDB
INCLUDING ONLY TABLE NAMES MATCHING 'users', 'orders'
WITH create tables, reset sequences;
```
---