포스트

PostgreSQL 해부 - 설치부터 운영 기초까지

Docker로 PostgreSQL 18을 실행하고 psql 접속, table과 index, transaction, 백업과 복원까지 관계형 데이터베이스의 기본기를 실습하는 입문 단편입니다.

Posted
By 강호승
4 min read
PostgreSQL 해부 - 설치부터 운영 기초까지

기준 버전은 PostgreSQL 18(마이너 18.4)입니다. PostgreSQL에는 LTS 개념이 없고 모든 메이저 버전이 릴리스 후 약 5년간 지원되며, 19는 아직 Beta 단계라 이 글은 18을 기준으로 합니다.

PostgreSQL이 푸는 문제

회원과 주문 데이터를 CSV나 JSON 파일로 저장하는 웹 서비스에는 세 가지 한계가 있습니다. 두 프로세스가 같은 파일에 동시에 쓰면 나중에 쓴 쪽이 먼저 쓴 내용을 덮어써서 데이터가 유실됩니다. 파일을 쓰는 도중에 프로세스가 죽으면 반만 쓰인 파일이 남고, 어디까지가 정상 데이터인지 알 수 없습니다. 그리고 데이터가 커질수록 조회가 느려집니다. 회원 한 명을 찾을 때도 파일 전체를 처음부터 읽어야 하기 때문입니다.

관계형 데이터베이스(RDB)는 이 문제들을 풀기 위한 server 소프트웨어입니다. 데이터를 행(row)과 열(column)로 이루어진 table에 저장하고, 동시 쓰기는 잠금으로 조율하고, 쓰다 만 중간 상태는 transaction이라는 실행 단위로 막고, 조회는 index라는 자료구조로 빠르게 만듭니다. 애플리케이션은 파일을 직접 열지 않고 SQL이라는 질의 언어로 database server에 요청을 보냅니다.

PostgreSQL과 MySQL은 가장 널리 쓰이는 두 오픈소스 RDB이고, table과 SQL로 데이터를 다루는 기본 사용법은 거의 같습니다. 그중 PostgreSQL은 표준 SQL 준수와 기능 확장성이 강점이며, MySQL의 구조와 특징은 MySQL 편에서 따로 다룹니다.

설치와 첫 실행

container 실행 자체는 Docker 기초 4편에서 다뤘으므로 여기서는 명령만 적습니다.

1
2
3
4
5

docker run -d --name pg-tutorial \
  -e POSTGRES_PASSWORD=mysecret \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:18

공식 이미지는 POSTGRES_PASSWORD를 반드시 지정해야 하고, 기본 계정은 postgres, 기본 port는 5432입니다. postgres 계정은 superuser로, server의 모든 권한을 가진 관리자 계정입니다. volume을 /var/lib/postgresql/data가 아니라 /var/lib/postgresql에 마운트한 점에 주의합니다. 18부터 실제 데이터 경로(PGDATA)가 /var/lib/postgresql/18/docker처럼 버전별 하위 경로로 바뀌어서, 상위 경로에 마운트해야 데이터가 유지되고 이후 메이저 업그레이드도 수월합니다. 접속은 기본 client(server에 접속해 명령을 보내는 프로그램)인 psql로 합니다. container 안에서는 trust 인증이 적용되어 비밀번호 없이 접속되고, container 밖에서 접속할 때는 비밀번호가 필요합니다.

1

docker exec -it pg-tutorial psql -U postgres

접속 직후에는 명령 네 개로 상태를 확인합니다. SELECT version();이 server 버전, \l이 database 목록, \dt가 현재 database의 table 목록, \q가 종료입니다.

구조

구성요소역할
server접속을 받고 여러 database를 관리하는 PostgreSQL 프로세스
database논리적으로 격리된 데이터 묶음이자 접속의 단위
schemadatabase 안에서 table들을 묶는 이름 공간, 기본값은 public
table, row, column데이터 본체, column마다 타입과 제약이 강제된다
index특정 column의 조회를 빠르게 하는 자료구조, 기본은 정렬 상태를 유지해 검색이 빠른 트리 구조인 B-tree
transaction여러 쓰기를 전부 반영하거나 전부 취소하는 실행 단위

\l, \dt, \d 테이블명, \q 같은 백슬래시 명령은 SQL이 아니라 psql이 제공하는 client 편의 기능입니다. server에 전달되는 것은 SQL 문장뿐이고, 백슬래시 명령은 다른 client에서는 동작하지 않습니다.

최소 예제

주문 도메인으로 기본 SQL을 한 벌 실행해 봅니다. psql에 그대로 붙여넣으면 됩니다. 두 번째 줄의 \c는 접속 database를 mydb로 바꾸는 psql 명령입니다. 전환하지 않으면 table이 접속 중인 postgres database에 만들어집니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE DATABASE mydb;
\c mydb
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,   -- SERIAL은 자동 증가 정수
    name  TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL
);
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity   INTEGER NOT NULL
);
INSERT INTO products (name, price, stock) VALUES
    ('키보드', 45000, 10),
    ('마우스', 23000, 25),
    ('모니터', 210000, 5);
SELECT name, price FROM products WHERE price < 100000;
SELECT count(*) FROM products;
UPDATE products SET price = 25000 WHERE name = '마우스';
CREATE INDEX idx_orders_product_id ON orders (product_id);

PRIMARY KEY는 row를 고유하게 식별하는 column이고, REFERENCES는 다른 table의 row를 가리키게 강제하는 제약(foreign key)입니다. count처럼 여러 row를 하나의 값으로 요약하는 함수를 집계 함수라고 합니다. 마지막 줄의 CREATE INDEX는 orders의 product_id 조회를 빠르게 하는 index를 만듭니다. 이어서 주문 생성과 재고 차감처럼 함께 성공해야 하는 쓰기를 transaction으로 묶어 봅니다.

1
2
3
4

BEGIN;
INSERT INTO orders (product_id, quantity) VALUES (1, 2);
UPDATE products SET stock = stock - 2 WHERE id = 1;
COMMIT;

BEGIN과 COMMIT 사이의 문장들은 하나의 transaction으로 묶여 전부 반영되거나 전부 취소됩니다. 중간에 오류가 나거나 ROLLBACK을 실행하면 시작 전 상태로 돌아갑니다. transaction이 없으면 주문 기록만 남고 재고는 줄지 않은 중간 상태가 데이터에 남을 수 있습니다.

UI 훑기

SQL 실행은 psql만으로 충분하지만 table 구조를 훑어볼 때는 GUI가 편합니다. pgAdmin은 PostgreSQL 전용 웹 기반 관리 도구이고, DBeaver는 여러 종류의 database를 함께 다루는 데스크톱 client입니다. 어느 쪽이든 접속 정보(host, port, 계정)를 등록하면 database와 table 목록, 각 table의 column과 index, 데이터 미리보기를 확인할 수 있고, 쿼리 실행 결과도 표로 보여줍니다.

실전 운영

데이터는 반드시 volume에 둡니다. container 내부 파일시스템은 container 삭제와 함께 사라지므로, 위의 실행 명령처럼 named volume을 /var/lib/postgresql에 마운트합니다. volume의 동작은 Docker 기초 7편에서 다뤘습니다.

백업은 pg_dump로 SQL 덤프 파일을 뜹니다. 복원 대상 database는 미리 만들어져 있어야 하고 비어 있어야 하므로, 검증할 때는 새 database를 만들어 복원합니다. 복원까지 한 번 해봐야 그 백업을 믿을 수 있습니다.

1
2
3

docker exec pg-tutorial pg_dump -U postgres mydb > backup.sql
docker exec pg-tutorial createdb -U postgres mydb_restore
cat backup.sql | docker exec -i pg-tutorial psql -U postgres -d mydb_restore

접속 정보는 코드에 적지 않고 환경 변수나 설정 파일로 밖에서 주입합니다. 비밀번호가 코드 저장소에 들어가는 사고를 막고, 개발과 운영 환경에서 다른 database를 쓰기도 쉬워집니다.

PostgreSQL은 접속마다 server 프로세스를 하나씩 만듭니다. 접속 수가 많은 서비스는 server 앞에 PgBouncer 같은 connection pool을 두어 적은 수의 접속을 재사용합니다. 그리고 쿼리가 느리면 EXPLAIN ANALYZE를 씁니다. SELECT 문 앞에 붙여 실행하면 query planner가 고른 실행 계획과 실제 소요 시간이 출력됩니다. 성능 진단은 이 출력을 읽는 데서 시작합니다.

자주 겪는 문제

WHERE 조건이 있는 SELECT가 데이터가 늘수록 느려지는 경우가 있습니다. 조건에 쓰는 column에 index가 없어 table 전체를 읽는 Seq Scan이 일어나는 것이 원인입니다. EXPLAIN ANALYZE 출력에서 Seq Scan을 확인하고 해당 column에 CREATE INDEX를 실행하면 해결됩니다.

애플리케이션 container에서 localhost:5432로 접속하면 connection refused가 납니다. container마다 localhost는 자기 자신을 가리키므로 PostgreSQL container에 닿지 않는 것이 원인입니다. docker network create로 만든 user-defined network에 두 container를 연결하고 host 자리에 container 이름(pg-tutorial)을 쓰면 해결됩니다. 기본 bridge network에서는 container 이름이 해석되지 않습니다.

container를 지우고 다시 만들었더니 database가 비어 있는 경우가 있습니다. volume 없이 실행해서 데이터가 container 내부에만 있었던 것이 원인입니다. 이미 지운 데이터는 백업이 없으면 복구할 수 없으므로, named volume을 /var/lib/postgresql에 마운트해서 실행하는 것이 해결책입니다.

관련 글

  • MySQL 편: 같은 구성을 MySQL 기준으로 실습한 글입니다.
  • Docker 기초 시리즈: container 실행과 volume 등 이 글의 실습 전제를 다룹니다.
Database
PostgreSQL SQL 데이터베이스
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.