PostgreSQL 08003 오류 원인과 해결 방법 완벽 가이드

#postgres #database #dba #sql

08003 connection does not exist 는?

PostgreSQL 에러 코드 08003은 클라이언트가 이미 존재하지 않는 데이터베이스 연결을 통해 쿼리나 트랜잭션 명령을 실행하려 할 때 발생합니다. 쉽게 말해, 연결이 서버 측에서 이미 종료되었거나 끊어진 상태인데 클라이언트는 그 사실을 인지하지 못한 채 해당 연결을 계속 사용하려는 상황입니다. 주로 커넥션 풀 환경, 장시간 유휴 상태의 연결, 또는 PREPARE TRANSACTION과 관련된 2단계 커밋(Two-Phase Commit) 흐름에서 자주 목격되는 에러입니다.

주요 발생 원인

1. 커넥션 풀에서 이미 끊어진 유휴 연결 재사용

PgBouncer, HikariCP, pg_pool 등 커넥션 풀러는 내부적으로 연결을 캐싱해두고 재사용합니다. PostgreSQL 서버 측에서 idle_in_transaction_session_timeout 또는 tcp_keepalives_idle 설정에 의해 해당 연결이 강제로 끊겼더라도, 풀러는 연결이 살아있다고 착각하고 애플리케이션에 해당 연결을 넘겨줄 수 있습니다. 이때 클라이언트가 쿼리를 실행하려 하면 08003 에러가 즉시 발생합니다.

2. 2단계 커밋(Two-Phase Commit)에서 잘못된 트랜잭션 ID 참조

PREPARE TRANSACTION으로 준비된 트랜잭션은 고유한 식별자(GID)를 가지며, 이후 별도의 세션에서 COMMIT PREPARED 또는 ROLLBACK PREPARED를 통해 완료해야 합니다. 이미 커밋되거나 롤백된 prepared 트랜잭션 GID를 다시 참조하거나, 해당 세션 자체가 사라진 뒤 연결을 통해 명령을 보내면 서버는 대응할 연결 컨텍스트가 없기 때문에 08003을 반환합니다. 분산 트랜잭션 처리 코드에서 예외 처리 미흡 시 특히 자주 발생합니다.

3. 네트워크 장애 또는 서버 재시작 후 연결 상태 불일치

방화벽 타임아웃, 네트워크 순단(intermittent failure), PostgreSQL 서버 재시작 등으로 인해 TCP 연결이 물리적으로 끊어졌음에도 클라이언트 소켓은 여전히 연결된 것처럼 보이는 "half-open" 상태가 될 수 있습니다. 이 상태에서 클라이언트가 명령을 전송하면, 서버는 해당 연결 슬롯이 더 이상 존재하지 않으므로 08003을 반환하거나 연결 자체가 즉시 drop됩니다.

해결 방법

원인 1 해결: 커넥션 풀 유효성 검사(Validation Query) 설정

커넥션 풀에서 연결을 재사용하기 전에 반드시 연결 유효성을 검사하도록 설정합니다.

-- 연결 유효성 검사용 경량 쿼리 (validation query로 사용)
SELECT 1;

-- 현재 유휴 연결 상태 확인
SELECT pid, state, query_start, state_change, query
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes';

-- 오래된 유휴 연결 강제 종료 (DBA 수동 처리)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '30 minutes'
  AND pid <> pg_backend_pid();

HikariCP를 사용하는 경우 connectionTestQuery=SELECT 1과 keepaliveTime=30000(30초)을 설정하고, PgBouncer를 사용하는 경우 server_check_query=select 1 및 server_idle_timeout 값을 PostgreSQL의 idle_in_transaction_session_timeout보다 짧게 설정해야 합니다.

-- PostgreSQL 서버의 유휴 세션 타임아웃 확인 및 설정 (postgresql.conf 또는 ALTER SYSTEM)
SHOW idle_in_transaction_session_timeout;

-- 10분 이상 유휴 트랜잭션 자동 종료 설정
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

-- 특정 데이터베이스 또는 사용자에게만 적용
ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE myapp_user SET idle_in_transaction_session_timeout = '5min';

원인 2 해결: Prepared Transaction 상태 점검 및 정리

2단계 커밋 사용 시 항상 pg_prepared_xacts 뷰를 통해 미완료 트랜잭션을 모니터링하고 정리합니다.

-- 현재 준비된(prepared) 트랜잭션 목록 확인
SELECT gid, prepared, owner, database, transaction
FROM pg_prepared_xacts
ORDER BY prepared;

-- 오래된 prepared 트랜잭션 탐지 (30분 이상 미완료)
SELECT gid, prepared, owner, database,
       EXTRACT(EPOCH FROM (NOW() - prepared)) / 60 AS minutes_pending
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '30 minutes';

-- 고아(orphan) prepared 트랜잭션 롤백 처리
ROLLBACK PREPARED 'my_distributed_txn_id_001';

-- 정상적인 2PC 흐름 예시
BEGIN;
  INSERT INTO orders(customer_id, amount) VALUES (42, 9900);
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 7;
PREPARE TRANSACTION 'order_txn_20240101_001';

-- 별도 세션 또는 코디네이터에서 최종 커밋
COMMIT PREPARED 'order_txn_20240101_001';
-- 또는 실패 시 롤백
-- ROLLBACK PREPARED 'order_txn_20240101_001';

애플리케이션 코드에서 PREPARE TRANSACTION 이후 예외가 발생했을 때 반드시 ROLLBACK PREPARED를 호출하는 예외 처리 로직을 구현해야 하며, 주기적으로 pg_prepared_xacts를 스캔하는 모니터링 잡(cron job)을 운영하는 것을 강력히 권장합니다.

원인 3 해결: TCP Keepalive 및 재연결 로직 구현

네트워크 단절로 인한 half-open 연결을 방지하기 위해 TCP keepalive를 활성화합니다.

-- PostgreSQL 서버의 TCP keepalive 설정 확인
SHOW tcp_keepalives_idle;    -- 첫 keepalive 프로브까지 유휴 시간 (초)
SHOW tcp_keepalives_interval; -- 프로브 간격 (초)
SHOW tcp_keepalives_count;   -- 재시도 횟수

-- 운영 환경 권장 설정 (postgresql.conf 또는 ALTER SYSTEM)
ALTER SYSTEM SET tcp_keepalives_idle = 60;      -- 60초 유휴 후 keepalive 시작
ALTER SYSTEM SET tcp_keepalives_interval = 10;  -- 10초 간격으로 프로브
ALTER SYSTEM SET tcp_keepalives_count = 5;      -- 5회 실패 시 연결 종료
SELECT pg_reload_conf();

-- 현재 연결별 keepalive 설정 확인 (세션 레벨)
SHOW tcp_keepalives_idle;

-- 에러 발생 후 연결 상태 진단
SELECT pid, usename, application_name, client_addr,
       backend_start, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

애플리케이션 레벨에서는 08003 에러 감지 시 자동으로 연결을 재생성하는 retry 로직을 구현해야 합니다. 단순 재시도(retry)만으로는 부족하며, exponential backoff와 함께 최대 재시도 횟수를 제한하는 것이 실무 베스트 프랙티스입니다.

예방 방법

1. 커넥션 풀 + 서버 타임아웃 설정의 계층적 정합성 유지

커넥션 풀의 유휴 연결 유지 시간(idle timeout)은 반드시 PostgreSQL 서버의 idle_in_transaction_session_timeout 및 방화벽/로드밸런서의 TCP 세션 타임아웃보다 짧게 설정해야 합니다. 예를 들어 방화벽 TCP 타임아웃이 5분이라면, 커넥션 풀의 idleTimeout은 4분, PostgreSQL의 세션 타임아웃은 10분 순으로 계층을 구성하여 풀러가 서버보다 먼저 낡은 연결을 감지하고 교체하도록 만듭니다. 이 설정을 문서화하고 인프라 변경 시 항상 함께 검토하는 프로세스를 팀 내 운영 규칙으로 정착시키세요.

2. pg_prepared_xacts 모니터링 및 알림 자동화

2PC를 사용하는 시스템에서는 Prometheus + postgres_exporter, Zabbix, 또는 단순 cron 스크립트를 활용하여 pg_prepared_xacts에 5분 이상 남아있는 트랜잭션이 감지되면 즉시 알림이 발송되도록 구성합니다. 고아 prepared 트랜잭션은 테이블 bloat, vacuum 방해, 잠금 점유 등 2차 장애로 이어질 수 있으므로 조기 탐지가 매우 중요합니다. 아래 쿼리를 모니터링 스크립트의 기반으로 활용하세요.

-- 모니터링용 임계값 알림 쿼리 (5분 이상 미완료 트랜잭션 카운트)
SELECT COUNT(*) AS stale_prepared_count
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '5 minutes';
-- 결과가 0보다 크면 알림 발송