데이터베이스 기초 (8) - Operations Basics

데이터베이스 기초 시리즈의 8편입니다. 전체 목차는 0편에 있습니다.

백업 전략

PostgreSQL 편에서 본 pg_dump는 실행한 시점의 데이터를 SQL 문장으로 저장하는 논리 백업입니다. 논리 백업의 한계는 시점입니다. 매일 새벽 3시에 pg_dump를 실행하는 전략이라면, 오후 2시에 disk 장애가 났을 때 새벽 3시 이후의 변경은 모두 사라집니다. 덤프 파일에는 덤프 시점 이후의 변경이 들어 있지 않기 때문입니다.

PostgreSQL에는 이 한계를 보완하는 기록이 이미 있습니다. 모든 변경은 데이터 파일에 반영되기 전에 WAL(Write-Ahead Log)이라는 로그 파일에 먼저 기록됩니다. server가 도중에 죽어도 재시작할 때 WAL을 다시 적용해서 commit된 변경을 복구하며, 6편에서 본 ACID의 지속성(durability)이 이 장치로 구현됩니다.

WAL에는 모든 변경이 순서대로 남으므로 백업에도 쓸 수 있습니다. 특정 시점의 데이터 파일 복사본(base backup)을 주기적으로 만들고 그 이후의 WAL 파일을 계속 보관하면, base backup에 WAL을 원하는 지점까지 적용해서 임의의 시점으로 복구할 수 있습니다. 이것이 PITR(Point-In-Time Recovery)이고, 실수로 실행한 DELETE 직전 시점으로 되돌리는 것이 대표적인 사용 예입니다. base backup과 WAL 보관을 직접 구성하는 실습은 이 시리즈의 범위 밖이며, AWS RDS 같은 관리형 서비스는 이 구성을 자동화해서 복구 시점 지정 기능으로 제공합니다.

복원 리허설 원칙은 PostgreSQL 편에서 강조한 그대로입니다. 백업 파일이 존재한다는 사실과 그 파일로 복원이 된다는 사실은 다르므로, 어떤 전략을 쓰든 주기적으로 새 database에 실제로 복원해서 확인해야 합니다.

권한

애플리케이션 계정에는 필요한 권한만 줍니다. MySQL 편에서 CREATE USER와 GRANT로 SELECT, INSERT, UPDATE, DELETE만 가진 서비스 계정을 만들었는데, PostgreSQL도 문법이 거의 같습니다. superuser인 postgres 계정을 애플리케이션이 그대로 쓰면 버그나 7편에서 본 SQL injection이 DROP TABLE까지 실행할 수 있으므로, 계정을 분리해서 사고가 나도 피해 범위를 권한 안으로 제한합니다.

통계 조회처럼 읽기만 하는 용도에는 읽기 전용 계정을 만듭니다.

CREATE USER readonly_user WITH PASSWORD 'reader-secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

GRANT는 실행한 database에만 적용됩니다. 이 계정으로 접속할 때 -U만 바꾸면 psql이 계정명과 같은 이름의 database를 찾다가 실패하므로, -d로 posts table이 있는 database를 지정합니다.

docker exec -it pg-tutorial psql -U readonly_user -d board

접속하면 SELECT는 실행되지만 INSERT를 시도하면 permission denied for table posts 오류가 납니다. 한 가지 주의할 점은 GRANT SELECT ON ALL TABLES IN SCHEMA public이 지금 존재하는 table에만 적용된다는 것입니다. 앞으로 만들 table에도 자동으로 적용하려면 ALTER DEFAULT PRIVILEGES 설정을 추가해야 합니다.

잘못 준 권한은 REVOKE로 회수합니다. 아래는 실수로 INSERT 권한을 줬다가 회수하는 예입니다. postgres 세션으로 돌아와 실행합니다.

GRANT INSERT ON posts TO readonly_user;
REVOKE INSERT ON posts FROM readonly_user;

지금 무슨 일이 벌어지는지 보기

pg_stat_activity는 현재 접속한 session과 각 session이 실행 중인 쿼리를 보여주는 view입니다. view는 table처럼 SELECT할 수 있는 조회 전용 객체입니다. 오래 도는 쿼리를 찾을 때는 실행 시간 순으로 정렬합니다.

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS elapsed, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY elapsed DESC;

이 쿼리 자신도 active 상태로 함께 나옵니다. state가 active면 쿼리 실행 중이고, idle in transaction이면 transaction을 열어둔 채 아무것도 하지 않는 상태입니다. idle in transaction이 오래 유지되는 session은 6편에서 본 lock을 잡은 채 다른 쿼리를 막고 있을 수 있으므로 확인 대상입니다. wait_event_type이 Lock이면 그 쿼리는 다른 transaction의 lock을 기다리는 중입니다. elapsed가 비정상적으로 큰 쿼리를 찾았다면 SELECT pg_cancel_backend(pid);로 그 쿼리만 중단시킬 수 있습니다.

pg_stat_activity는 지금 이 순간의 상태만 보여줍니다. 시간에 걸친 통계는 pg_stat_statements 확장이 담당합니다. 확장(extension)은 기본 설치에 포함되지 않은 기능을 필요할 때 켜는 추가 모듈입니다. 켜 두면 쿼리별 누적 실행 횟수와 누적 실행 시간이 쌓여서 느린 쿼리 후보를 찾는 출발점이 됩니다. server 시작 시점에 로드되어야 하는 확장이라 설정 후 재시작이 필요합니다. ALTER SYSTEM은 설정 파일을 직접 고치지 않고 SQL로 server 설정을 바꿔 저장하는 명령이고, 바꾼 값은 재시작 후에도 유지됩니다.

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

docker restart pg-tutorial

재시작하면 psql 접속이 끊기므로 다시 접속한 뒤 확장을 활성화하고 통계를 조회합니다.

CREATE EXTENSION pg_stat_statements;

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

calls가 누적 실행 횟수, total_exec_time이 누적 실행 시간(ms)입니다. 누적 시간이 큰 쿼리를 찾았다면 다음 단계는 5편의 EXPLAIN ANALYZE로 실행 계획을 확인하는 것입니다.

느린 쿼리를 로그로 남길 수도 있습니다. log_min_duration_statement에 지정한 시간보다 오래 걸린 쿼리는 실행 시간과 함께 server 로그에 기록됩니다.

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

이 설정은 재시작 없이 pg_reload_conf()로 반영됩니다. Docker 환경에서는 docker logs pg-tutorial로 로그를 확인합니다. 500ms는 예시 값이고 서비스 기준에 맞게 정합니다.

dead tuple과 VACUUM

6편에서 본 MVCC 때문에 UPDATE와 DELETE는 row를 그 자리에서 지우지 않습니다. UPDATE는 새 버전 row를 만들고 DELETE는 삭제 표시만 남기는데, 다른 transaction이 아직 옛 버전을 읽고 있을 수 있기 때문입니다. 어느 transaction도 더 이상 볼 수 없게 된 옛 버전 row를 dead tuple이라고 부르고, 이것을 회수하는 작업이 VACUUM입니다.

VACUUM은 dead tuple이 차지하던 공간을 같은 table 안에서 재사용할 수 있게 만듭니다. autovacuum이라는 백그라운드 프로세스가 변경량이 임계치를 넘은 table을 골라 자동으로 VACUUM을 실행하므로 평소에는 직접 실행할 일이 거의 없습니다.

다만 대량 DELETE 직후에는 확인이 필요합니다. dead tuple이 회수되어도 table 파일 크기 자체는 대부분 줄지 않아서, 실제 row 수에 비해 disk 사용량이 큰 상태가 될 수 있고 이를 bloat라고 부릅니다. table별 dead tuple 수와 마지막 autovacuum 시각은 pg_stat_user_tables에서 확인합니다.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

n_dead_tup이 크게 쌓여 있으면 VACUUM posts;처럼 table을 지정해 직접 실행할 수 있습니다. 파일 크기까지 줄이려면 table을 새로 쓰는 VACUUM FULL이 필요하지만 실행 중에 해당 table이 통째로 잠깁니다. 대부분의 서비스에서는 autovacuum 기본값으로 충분하며, autovacuum을 끄지 않는 것이 원칙입니다.

replication 개요

지금까지의 구성은 server 한 대입니다. 그 한 대가 죽으면 서비스 전체가 멈추고 읽기 부하도 전부 한 대가 받습니다. replication은 같은 데이터를 가진 server를 여러 대 유지하는 기능입니다.

PostgreSQL의 기본 방식은 streaming replication입니다. 쓰기를 받는 primary가 WAL을 replica server로 실시간 전송하고, replica는 받은 WAL을 적용해서 primary와 같은 상태를 유지합니다. 백업 절에서 본 WAL이 여기서도 같은 역할을 합니다. replica는 읽기 전용이므로 SELECT 쿼리를 replica로 분산해서 읽기 부하를 나눌 수 있습니다. 다만 WAL 전송과 적용에는 지연이 있어서, 방금 commit한 변경이 replica에서는 아직 보이지 않을 수 있습니다.

primary에 장애가 나면 replica 하나를 새 primary로 승격(promote)해서 서비스를 이어가는데, 이 전환을 failover라고 부릅니다. 승격 판단과 애플리케이션의 접속 전환을 자동화하는 것이 고가용성 구성의 핵심이고, 관리형 서비스는 이를 기능으로 제공합니다. replication 구성 실습은 이 시리즈의 범위 밖이며, 여기서는 primary, replica, failover라는 개념과 용어를 아는 것이 목표입니다.

MySQL 차이

MySQL에서 논리 백업은 mysqldump가 담당하고, 백업과 replication에서 WAL이 하던 역할은 binlog(binary log)가 맡습니다. crash recovery는 InnoDB의 redo log가 별도로 담당합니다. 주기적 백업에 binlog를 결합하면 PITR이 가능하다는 구조도 같습니다. 느린 쿼리 기록은 slow query log라는 별도 기능이 담당하고 기준 시간은 long_query_time으로 정합니다. InnoDB도 MVCC로 옛 버전을 유지하지만 정리는 VACUUM이 아니라 백그라운드 purge 작업이 담당하므로, 운영자가 VACUUM에 해당하는 명령을 직접 실행할 일은 없습니다. replication은 primary의 binlog를 replica가 받아 적용하는 방식이고, 읽기 분산과 failover 개념은 PostgreSQL과 같습니다. pg_stat_activity에 해당하는 것은 SHOW PROCESSLIST입니다.

다음 편

다음 편 데이터베이스 기초 (9) - MySQL vs PostgreSQL and Next Steps에서는 시리즈 내내 병기해 온 MySQL과 PostgreSQL의 공통점과 차이를 정리하고 선택 기준과 다음 학습 방향을 안내하며 시리즈를 마무리합니다.