Postgres 마이그레이션이 멈췄을 때 확인할 것

Postgres 마이그레이션을 실행했는데 진행이 안 되거나 타임아웃이 난다. 이럴 땐 보통 테이블 lock, 연결 풀 부족, 또는 제약 조건 때문이다.

마이그레이션 중단은 다른 쿼리들도 영향을 받으므로 빨리 진단해야 한다.

첫 번째: 현재 실행 중인 쿼리를 본다

-- 현재 실행 중인 쿼리와 pid 보기
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';

-- 또는 더 자세히
SELECT
  pid, usename, query,
  EXTRACT(EPOCH FROM (now() - query_start))::INT AS seconds
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

마이그레이션 PID와 그 외 오래 실행 중인 쿼리가 있는지 본다. 특히 몇 초 이상 걸리는 쿼리가 있으면 그것이 lock을 잡고 있을 가능성이 높다.

두 번째: 테이블 lock 상태를 확인한다

-- Lock을 잡고 있는 프로세스
SELECT * FROM pg_locks
WHERE NOT granted;

-- 또는 어느 프로세스가 어떤 테이블을 lock했는지
SELECT
  l.pid, p.usename, p.query,
  t.relname AS table_name,
  l.mode AS lock_mode
FROM pg_locks l
JOIN pg_stat_activity p ON l.pid = p.pid
JOIN pg_class t ON l.relation = t.oid
WHERE t.relkind = 'r';

Access Exclusive Lock(ALTER TABLE 등에서 생김) 때문에 마이그레이션이 대기 중일 수 있다. 어느 연결이 lock을 잡고 있는지 확인하고, 필요하면 그 연결을 끊는다.

세 번째: 연결 풀 상태를 본다

# 만약 pgBouncer를 쓴다면
psql -h localhost -p 6432 -U pgbouncer -d pgbouncer

# 풀의 현재 상태
SHOW POOLS;

# 또는 직접 Postgres에
SELECT count(*) FROM pg_stat_activity;

max_connections에 도달했거나, 연결 풀이 가득 찼으면 새로운 연결을 못 만든다. pgBouncer나 다른 풀 매니저의 상태를 확인하자.

네 번째: 마이그레이션 중단 후 확인하는 방법

마이그레이션이 멈춰있다면, Ctrl+C로 중단하고 상태를 본다.

# Prisma를 쓴다면
npx prisma migrate status

# 직접 확인
SELECT * FROM "_prisma_migrations"
ORDER BY finished_at DESC
LIMIT 5;

부분적으로 적용된 마이그레이션이 있는지, 또는 완전히 롤백되었는지 확인한다.

다섯 번째: 안전하게 lock을 해제한다

-- 문제 있는 프로세스 ID를 알았다면
SELECT pg_terminate_backend(12345); -- pid = 12345

-- 또는 해당 사용자의 모든 연결 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user';

주의: 프로덕션 환경에서는 신중하게. 불필요한 연결을 종료하면 앱이 영향을 받을 수 있다.

체크리스트

1. pg_stat_activity에서 오래 실행 중인 쿼리를 본다
2. pg_locks에서 대기 중인 lock을 확인한다
3. 마이그레이션 프로세스의 상태를 본다
4. 연결 풀 상태를 확인한다(idle 연결이 많은지)
5. 필요하면 문제 프로세스를 종료한다
6. 마이그레이션을 다시 실행한다

Postgres 마이그레이션은 장시간 테이블 lock을 잡으므로, 프로덕션 환경에서는 피크 시간을 피해 실행하는 게 좋다.