PG Insight

운영 중인 PostgreSQL의 느린 쿼리, 락 대기, 블로트, 복제 지연은 여러 도구와 SQL을 오가며 따로 확인해야 합니다. 이를 하나의 서비스로 모으되, 외부인이 남의 DB에 접속하는 공개 서비스인 만큼 테넌트 격리, SSRF 방어, 임의 SQL 제한을 처음부터 갖춰야 했습니다.

수집기가 각 DB에서 3초에서 5분 주기로 메트릭을 모아 TimescaleDB(hypertable 10개)에 기록하고, 실시간 화면은 대상 DB를 직접 조회합니다. 사용자, 대상, 알림, 감사 로그는 Prisma 7로 관리하는 플랫폼 DB(PostgreSQL 16)에 있습니다. NestJS 11의 전역 가드(Throttler → JWT → TargetAccess)가 모든 :targetId 라우트의 소유권을 확인하고, 대상 DB 쿼리는 대상별 pool(최대 5)에서 raw SQL로 실행합니다. 역할은 admin(첫 계정)과 user(본인 대상 최대 3개, 공인 주소만) 두 가지이며, UI는 한국어, 영어, 우즈베크어를 지원합니다.

크게 보기

무엇을 바꿨나

수집 오케스트레이터가 onModuleInit에서 pool 연결 전에 시작되던 것을 onApplicationBootstrap으로 옮기고, 재시작 순서를 검증하는 lifecycle 테스트를 추가했습니다 (6246ce2). 같은 G 단계에서 lock 수집 RENAME 오류, 메트릭 시각, 일시정지 상태, TLS 기본값, /health 500 → 503도 고쳤습니다.

증상

API를 재시작한 뒤 어떤 대상도 모니터링되지 않았습니다. 부하 측정에서 PG Insight 쿼리가 0건으로 나왔습니다.

원인

로그에 Starting collection for 0 active target(s)가 찍혔습니다. 오케스트레이터가 onModuleInit에서 pool이 연결되기 전에 시작됐고, 14:23–17:25 UTC 데이터가 사라졌습니다.

해결

시작 시점을 onApplicationBootstrap으로 옮겼습니다 (6246ce2). 재시작 직후 대상 2개 모두 수집됩니다.

재발 방지

오류를 재현하는 lifecycle 테스트를 추가했고, 일시정지한 대상을 건너뛰는지도 함께 검사합니다. 새 테스트는 이전 코드에서 실패하는 것을 확인했습니다.

테스트 수

237

커버리지

—

CI

GitHub Actions (워크플로 준비, billing 문제로 현재 중단)

테스트 도구

Jest, Supertest, Vitest, 실제 PostgreSQL 16

배포 환경

iwinv VPS, Docker Compose: api(NestJS, non-root), web(Caddy + React 정적 파일, /api/*와 /socket.io/* 프록시), platform-db(PostgreSQL 16), metrics-db(TimescaleDB). DB 포트는 외부에 열지 않습니다. deploy.sh: 충돌 표시 검사 → git merge --ff-only → api와 web을 SHA 태그로 빌드 → health를 45 × 2초 대기 → 성공했을 때만 APP_TAG와 .deploy-history에 기록. API 컨테이너가 시작할 때 prisma migrate deploy를 실행합니다.

백업

서버 공통 cron 백업(매일 03:00, 7일 보관)에 platform-db와 metrics-db가 포함됩니다. 사용자 DB의 pg_dump 백업은 admin만 만들 수 있습니다.

롤백

rollback.sh는 빌드 없이 기존 이미지로 api와 web을 다시 만들고, 먼저 두 이미지가 있는지 확인합니다. 프로덕션에서 양방향(71dceaf ↔ d587ce6)으로 시험해 둘 다 health 200이었습니다.

모니터링

실사용 대상: LedgerCore와 FlashDrop 프로덕션 DB를 등록해 모니터링(같은 서버의 내부 Docker 네트워크). /api/v1/health(DB 장애 시 503), 감사 로그(로그인, 가입, 대상 변경, 모든 EXPLAIN), 가입은 IP당 시간당 5회, 로그인은 분당 10회로 제한. GitHub private vulnerability reporting(SECURITY.md)

• pg-insight-back/src/auth/target-access.guard.ts

  전역 소유권 검사, 남의 대상은 404

• pg-insight-back/src/auth/admin-routes.spec.ts

  보호되지 않은 변경 라우트를 메타데이터로 찾아내는 테스트

• pg-insight-back/src/targets/target-host.policy.ts

  SSRF: BlockList, DNS의 모든 IP 검사, IP 고정 + SNI

• pg-insight-back/src/live/live-query.service.ts

  EXPLAIN 6단계 방어, READ ONLY, statement_timeout / lock_timeout

• pg-insight-back/src/targets/target-pool.manager.ts

  대상 pool, 복호화한 비밀번호로 재연결, paused 상태 유지

• pg-insight-back/src/collector/collector.orchestrator.ts

  onApplicationBootstrap, 오류 시 재연결, 일시정지 대상 건너뛰기

• pg-insight-back/src/real-time/real-time.gateway.ts

  핸드셰이크 JWT, room 구독 시 소유권 확인

• pg-insight-back/docker/init-platform.sql

  hypertable, 압축, retention 정책

• scripts/measure-overhead.sh

  대상 DB 부하 측정 스크립트

• scripts/deploy.sh, scripts/rollback.sh

  충돌 검사, SHA 태그, health 확인 뒤 기록

수집이 대상 DB에 주는 부하는 10분 동안 쿼리 2,430건, 실행 시간 합계 353.4ms로 CPU 1코어의 약 0.06%였습니다. 실제 프로덕션 DB(LedgerCore, FlashDrop)에서 5건, 코드 리뷰와 테스트에서 3건의 버그를 찾았고, 모두 먼저 테스트로 재현한 뒤 고쳤습니다. 다른 사례: 실시간 브로드캐스트 오류가 수집기를 멈추던 문제(b9825e4), lock 수집 쿼리의 RENAME 문법 오류(b9825e4), 연결 메트릭 시각이 세션 시작 시각으로 기록되던 문제(d587ce6, 잘못된 행 891개 삭제), 일시정지한 대상이 재시작 뒤 다시 켜지던 문제(41569c7), 비밀번호 변경 시 SSL 설정이 사라지고 prefer가 실제로는 암호화하지 않던 문제(41569c7, 47287b4), 첫 rollback 훈련에서 이전 이미지가 없어 스크립트가 안전하게 멈춘 일.