[Database] PostgreSQL

Bzeromo·2025년 5월 15일

Database PostgreSQL

DB

목록 보기

9/10

⚡ PostgreSQL

📌 사용 전적이 있었지만...

😓 정리를 해놓지 않아 활용 경험을 자세히 적어두지 못했다...
기회가 닿아 이곳에 새로 기록을 남겨볼 생각이다.

📌 Postgres?

⭐ 1,2,3위 모두 지고 있지만 혼자 떠오르고 있는 PostgreSQL

🔷 PostgreSQL

확장성과 SQL 표준 준수를 강조하는 무료 오픈 소스 관계형 데이터베이스 관리 시스템
원자성, 일관성, 고립성, 지속성(ACID) 특성을 갖춘 트랜잭션, 자동으로 갱신되는 뷰, 머터리얼라이즈드 뷰, 트리거, 외래 키, 저장 프로시저 등의 기능 제공
윈도우, 리눅스, macOS, FreeBSD, OpenBSD 등 모든 주요 운영체제 지원
단일 머신에서 데이터 웨어하우스, 데이터 레이크, 또는 다수의 동시 사용자가 접속하는 웹 서비스에 이르기까지 다양한 작업 부하 처리 가능
원래 캘리포니아 대학교 버클리에서 개발된 Ingres 데이터베이스의 후속작임을 의미하는 POSTGRES라는 이름으로 시작
1996년, SQL 지원을 반영해 프로젝트 이름이 PostgreSQL로 변경되었으며, 2007년 검토를 거쳐 개발팀은 PostgreSQL이라는 이름과 별칭인 Postgres를 모두 유지

📌 PostgreSQL 설치

🖥 설치 시 사용할 운영체제: Ubuntu 22.04 LTS

🖥 설치할 PostgreSQL 버전: 15 (EOL: 2027-11)

다운로드 링크

설치 후 관리자 계정 암호 변경
데이터베이스/사용자 생성

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y curl ca-certificates

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
--fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

sudo apt update && sudo apt -y install postgresql-15
sudo systemctl status postgresql

sudo adduser clouds2025

sudo -u postgres psql postgres
CREATE USER clouds2025 WITH PASSWORD 'clouds2025';
CREATE DATABASE clouds2025 OWNER clouds2025;

psql -c "\du"
psql -c "\l"

# 사용자 전환 후 접속 테스트
psql -U clouds2025 -d clouds2025

항목	`sudo -i -u postgres`	`sudo -u postgres psql postgres`
목적	postgres 사용자 셸로 전환	postgres 사용자로 psql 실행
셸 전환	O (bash로 변경됨)	X (현재 셸 유지, 명령만 실행)
환경 파일 로딩	O (`.bashrc`, `.profile` 등)	X
psql 자동 실행	X (수동으로 실행 필요)	O
접속 대상 DB	없음 (셸만 바뀜)	`postgres` DB로 바로 접속
사용 예	여러 명령을 실행할 때	바로 `psql` 접속할 때 편리

📌 PostgreSQL 메타 명령

🔷 psql 커맨드라인 환경에서 데이터베이스를 효과적으로 탐색하고
관리할 수 있게 해주는 명령어들

구분	메타 명령 (psql 전용)	SQL 명령 (표준 SQL)
예시	`\dt`, `\du`, `\h`	`SELECT * FROM users;`
사용 위치	`psql` 안에서만 사용 가능	대부분의 DB 툴에서 사용 가능
목적	탐색, 도움말, 관리 등	데이터 조작, 질의 등
표준 여부	PostgreSQL 전용	ANSI SQL 표준 기반

🔷 주요 명령 모음

명령어	설명
`\?`	모든 메타 명령어 목록 출력 (도움말)
`\h`	SQL 문법 도움말 (예: `\h SELECT`)
`\l`	데이터베이스 목록 보기
`\c dbname`	다른 데이터베이스로 접속
`\dt`	테이블 목록 보기
`\d 테이블명`	테이블 구조 보기
`\du`	사용자 목록 보기
`\dn`	스키마 목록 보기
`\q`	psql 종료
`\x`	세로 출력모드 토글 (많은 컬럼 있을 때 유용)
`\timing`	쿼리 실행 시간 측정 on/off

🔷 명령어 분류표

분류	명령어 목록
데이터베이스	`\l`, `\c`, `\conninfo`
테이블/뷰	`\dt`, `\dv`, `\d`, `\d+`
사용자	`\du`, `\password`
성능/툴	`\timing`, `\x`, `\watch`, `\!`
도움말	`\?`, `\h`, `\set`, `\echo`

📌 예제용 데이터베이스 생성

dvdrental 데이터베이스는 DVD 대여 상점의 비즈니스 프로세스를 표현한 것
15개의 테이블, 1개의 트리거, 7개의 뷰, 8개의 함수, 1개의 도메인, 13개의 시퀀스로 구성

# postgres 계정으로 작업
sudo -i -u postgres
psql

CREATE DATABASE dvdrental OWNER cloud2025;
CREATE DATABASE hr OWNER cloud2025;
\l

\q

wget https://buly.kr/uU2ACd
mv uU2ACd dvdrental.zip
unzip dvdrental.zip

# pg_restore는 tar 파일 안의 restore.sql을 실행해서 스키마를 만들고 
# *.dat 데이터 파일들을 읽어 데이터를 테이블에 적재
pg_restore -U postgres -d dvdrental dvdrental.tar

psql
\c dvdrental
\dt
\q