일반적인 PostgreSQL 연결 오류 문제 해결

PostgreSQL 연결 오류는 정확한 메시지를 자세히 읽지 않으면 비슷해 보입니다. Connection refused는 일반적으로 클라이언트가 호스트에 도달했지만 해당 주소와 포트에서 TCP 연결을 수락하는 대상이 없음을 의미합니다. Connection timed out은 패킷이 유용한 응답을 받지 못했음을 의미합니다. password authentication failed는 서버에 도달했지만 자격 증명이 거부되었음을 의미합니다. no pg_hba.conf entry는 PostgreSQL이 일치하는 액세스 규칙을 찾지 못했음을 의미합니다.

이 순서가 중요합니다. 외부에서 내부로 시작하세요: 호스트, 포트, 서비스, 리스너, 방화벽, pg_hba.conf, 사용자, 데이터베이스, 비밀번호, SSL, 그 다음 애플리케이션 풀링. 서비스가 수신 대기 중이 아닐 때 비밀번호를 추측하는 것은 시간 낭비입니다. DNS가 잘못된 호스트를 가리킬 때 pg_hba.conf를 편집하는 것도 시간 낭비입니다.

PostgreSQL 연결 기본 사항 이해

특정 오류를 살펴보기 전에 PostgreSQL이 연결을 처리하는 방식을 이해하는 것이 중요합니다. PostgreSQL은 클라이언트-서버 모델로 작동합니다. 클라이언트(예: psql 명령줄 도구, 웹 애플리케이션 또는 데스크톱 클라이언트)가 PostgreSQL 서버 프로세스에 연결을 시도합니다. 이 프로세스는 일반적으로 특정 네트워크 인터페이스와 포트(기본값 5432)에서 들어오는 연결을 수신 대기합니다.

연결 수락 및 인증 방식을 결정하는 두 가지 주요 구성 파일이 있습니다:

postgresql.conf: 수신 대기할 네트워크 인터페이스(listen_addresses)와 포트(port)를 포함한 일반 서버 동작을 제어합니다.
pg_hba.conf: (호스트 기반 인증) 누가 어디서 어떤 데이터베이스에 어떤 인증 방법을 사용하여 연결할 수 있는지를 규정합니다. 이 파일은 보안 및 액세스 제어에 중요합니다.

이러한 파일의 역할과 클라이언트-서버 상호 작용을 이해하는 것은 효과적인 문제 해결의 기본입니다.

일반적인 연결 오류 및 해결 방법

발생할 수 있는 가장 빈번한 연결 오류와 각각의 해결 방법을 살펴보겠습니다.

오류 1: `FATAL: database "..." does not exist`

이 오류는 클라이언트가 지정한 데이터베이스 이름이 PostgreSQL 서버에 존재하지 않음을 의미합니다.

설명: 클라이언트 애플리케이션 또는 psql 명령이 생성되지 않았거나 이름이 잘못 입력된 데이터베이스에 연결을 시도하고 있습니다.

해결 방법:

데이터베이스 이름 확인: 연결 문자열 또는 psql 명령의 데이터베이스 이름이 올바른지 확인합니다.
기존 데이터베이스 나열: 기본 데이터베이스(예: postgres 또는 template1)에 연결하고 \l(또는 \list)을 사용하여 사용 가능한 모든 데이터베이스를 나열합니다.

# 기본 'postgres' 데이터베이스에 연결 시도
psql -U your_username -h your_host -d postgres

# 연결되면 모든 데이터베이스 나열
\l

# 누락된 데이터베이스 생성 예시
CREATE DATABASE my_app_db;

오류 2: `FATAL: role "..." does not exist`

이는 연결에 지정된 사용자 이름(역할)이 존재하지 않음을 나타냅니다.

설명: 데이터베이스 오류와 유사하게 연결을 시도하는 사용자 계정이 없거나 이름이 잘못 입력되었습니다.

해결 방법:

사용자 이름 확인: 연결 문자열의 사용자 이름을 확인합니다.
기존 역할 나열: 슈퍼유저 계정(예: postgres 사용자)으로 연결하고 \du를 사용하여 모든 역할을 나열합니다.

# 기본 'postgres' 슈퍼유저로 연결
psql -U postgres -h your_host -d postgres

# 모든 역할(사용자) 나열
\du

# 누락된 역할 생성 예시
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'my_strong_password';

오류 3: `FATAL: password authentication failed for user "..."`

이는 일반적인 오류로, 지정된 사용자에 대해 잘못된 비밀번호가 제공되었음을 나타냅니다.

설명: 클라이언트가 제공한 비밀번호가 PostgreSQL 사용자(역할)에 저장된 비밀번호와 일치하지 않습니다.

해결 방법:

애플리케이션 구성 확인: 애플리케이션의 연결 문자열 또는 환경 변수를 검토하여 비밀번호가 올바른지 확인합니다.
비밀번호 재설정(슈퍼유저 액세스 권한이 있는 경우):
```
# postgres 슈퍼유저로 연결
psql -U postgres -h your_host -d postgres

# 문제가 있는 사용자의 비밀번호 변경
ALTER USER my_app_user WITH PASSWORD 'new_strong_password';
```
팁: 비밀번호를 사용하려는 경우 pg_hba.conf의 사용자 항목이 trust 또는 ident가 아닌 비밀번호 기반 인증 방법(예: md5, scram-sha-256)을 지정하는지 확인합니다.

오류 4: `FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on`

이 오류는 pg_hba.conf 구성 문제로, 서버가 액세스 규칙에 따라 연결을 명시적으로 거부했음을 의미합니다.

설명: pg_hba.conf 파일에 들어오는 연결의 매개변수(클라이언트 IP, 사용자, 데이터베이스 및 인증 방법)와 일치하는 규칙이 없습니다.

해결 방법:

pg_hba.conf 찾기: 위치는 OS 및 설치 방법에 따라 다릅니다(예: Debian/Ubuntu의 경우 /etc/postgresql/14/main/pg_hba.conf 또는 psql에서 SHOW hba_file;로 지정).
pg_hba.conf 편집: 연결을 허용하는 항목을 추가하거나 수정합니다. 비밀번호 인증을 사용하여 모든 곳에서 연결을 허용하는 일반적인 항목은 다음과 같습니다:
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    my_app_db       my_app_user     203.0.113.25/32         scram-sha-256
```
- TYPE: TCP/IP 연결의 경우 host.
- DATABASE: all(또는 특정 데이터베이스 이름).
- USER: all(또는 특정 사용자 이름).
- ADDRESS: 클라이언트의 IP 주소 범위(예: 192.168.1.0/24, 로컬의 경우 127.0.0.1/32 또는 단일 공용 클라이언트 IP).
- METHOD: 인증 방법. 최신 PostgreSQL 배포에서 클라이언트가 지원하는 경우 비밀번호 인증에는 scram-sha-256을 선호합니다.
경고: 앞에 매우 신중한 네트워크 제어가 없는 한 host all all 0.0.0.0/0 ...을 피하십시오. 좁은 데이터베이스, 역할 및 CIDR은 실수를 더 쉽게 발견할 수 있게 합니다.
PostgreSQL 다시 로드: pg_hba.conf를 편집한 후 변경 사항을 적용하려면 PostgreSQL 구성을 반드시 다시 로드해야 합니다.
```
# systemd 기반 시스템
```

sudo systemctl reload postgresql

# 또는 pg_ctl 사용(데이터 디렉토리 지정 필요)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

오류 5: `could not connect to server: Connection refused (0x0000274D/10061)`

이는 클라이언트가 PostgreSQL 서버에 연결을 설정할 수 없음을 나타내는 일반적인 오류입니다. 서버가 연결 시도를 적극적으로 거부했으며, 종종 대상 IP/포트에서 수신 대기하는 항목이 없기 때문입니다.

설명: 이는 일반적으로 다음 중 하나를 가리킵니다:

PostgreSQL 서비스가 실행되고 있지 않습니다.
PostgreSQL이 예상된 네트워크 인터페이스 또는 포트에서 수신 대기하고 있지 않습니다.
방화벽이 연결을 차단하고 있습니다.

해결 방법:

PostgreSQL이 실행 중입니까?
- 서비스 상태 확인:
```
sudo systemctl status postgresql
# 또는 이전 시스템/다른 설정의 경우:
# sudo service postgresql status
```
  실행 중이 아니면 시작합니다:
```
sudo systemctl start postgresql
```
- 로그 확인: 시작 오류가 있는지 PostgreSQL 로그(예: /var/log/postgresql/)를 검토합니다.
올바른 주소/포트에서 수신 대기하고 있습니까?
- postgresql.conf 확인: listen_addresses가 올바르게 구성되었는지 확인합니다. 다른 호스트의 연결을 위해서는 localhost(127.0.0.1)가 아닌 * 또는 서버 네트워크 인터페이스의 특정 IP 주소여야 합니다.
```
# postgresql.conf에서
listen_addresses = '*'    # 사용 가능한 모든 네트워크 인터페이스에서 수신 대기
port = 5432               # 기본 포트
```
  listen_addresses를 변경한 후에는 PostgreSQL을 반드시 다시 시작해야 합니다(다시 로드만으로는 충분하지 않음).
```
sudo systemctl restart postgresql
```
- 수신 포트 확인: netstat 또는 ss를 사용하여 PostgreSQL이 실제로 포트 5432(또는 구성된 포트)에서 수신 대기하고 있는지 확인합니다.
```
sudo ss -ltnp | grep 5432
# 예상 출력 예시:
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
```
  0.0.0.0:5432 또는 your_server_ip:5432가 표시되지 않으면 PostgreSQL이 127.0.0.1:5432에서만 수신 대기 중이거나 전혀 수신 대기하지 않을 가능성이 높습니다.
방화벽이 연결을 차단하고 있습니까?
- 서버 측 방화벽: ufw(Ubuntu/Debian), firewalld(CentOS/RHEL) 또는 iptables를 확인하여 클라이언트의 IP 주소에서 들어오는 연결에 대해 포트 5432가 열려 있는지 확인합니다.
```
# UFW 예시
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# firewalld 예시
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
```
- 클라이언트 측 방화벽: 덜 일반적이지만 클라이언트의 방화벽이 포트 5432에서 서버로의 아웃바운드 연결을 차단하고 있지 않은지 확인합니다.

오류 6: `timeout expired` 또는 `connection timed out`

이 오류는 클라이언트가 연결을 시도했지만 지정된 시간 내에 서버로부터 응답을 받지 못했음을 나타냅니다.

설명: 능동적 거부인 Connection refused와 달리 시간 초과는 연결 시도가 서버에 도달하지 못했거나 서버가 응답하지 않았음을 의미합니다. 이는 종종 네트워크 연결 문제나 심각하게 과부하된 서버를 가리킵니다.

해결 방법:

네트워크 연결:
- 서버 핑: ping server_ip_address. 핑이 실패하면 근본적인 네트워크 문제(케이블, 라우터, 서버 오프라인)가 있는 것입니다.
- Traceroute/MTR: traceroute server_ip_address(Linux/macOS) 또는 tracert server_ip_address(Windows)는 네트워크 경로를 따라 연결이 실패하는 위치를 식별하는 데 도움이 될 수 있습니다.
서버 listen_addresses 및 방화벽: 오류 5에 대한 해결 방법을 다시 검토하십시오. 잘못 구성된 listen_addresses 또는 방화벽도 서버에 연결할 수 없는 경우 시간 초과를 유발할 수 있습니다.
서버 부하: 서버가 극심한 부하(높은 CPU, 낮은 메모리, 과도한 디스크 I/O) 상태인 경우 새 연결을 신속하게 수락하기에 너무 바빠 시간 초과가 발생할 수 있습니다. 시스템 리소스 사용률을 확인합니다.

오류 7: SSL 필요, SSL 비활성화 또는 인증서 확인 실패

PostgreSQL은 서버 설정 및 pg_hba.conf 규칙에 따라 암호화된 연결과 암호화되지 않은 연결을 모두 수락할 수 있습니다. 클라이언트는 SSL이 꺼져 있거나, SSL이 필요하거나, 인증서 확인에 실패했다는 메시지와 함께 실패할 수 있습니다.

세 곳을 확인하십시오:

# 서버에 SSL이 활성화되어 있는지 확인
psql -U postgres -d postgres -c "SHOW ssl;"

# 클라이언트에서 암호화된 연결 시도
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# 인증서 검증이 예상되는 경우 verify-full 및 신뢰할 수 있는 루트 인증서 사용
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

sslmode=require는 연결을 암호화하지만 verify-full과 같은 방식으로 서버 ID를 확인하지는 않습니다. 내부 개발의 경우 충분할 수 있습니다. 신뢰할 수 없는 네트워크를 통한 프로덕션 트래픽의 경우 인증서 확인을 사용하고 연결 문자열의 호스트 이름이 인증서와 일치하는지 확인하십시오.

또한 pg_hba.conf가 hostssl 또는 hostnossl을 사용하는지 확인하십시오. hostssl 규칙은 비SSL 연결과 일치하지 않으며 hostnossl 규칙은 SSL 연결과 일치하지 않습니다.

오류 8: 클라이언트가 너무 많음

PostgreSQL이 FATAL: sorry, too many clients already를 반환하면 연결 경로는 작동 중입니다. max_connections에 도달했거나 예약된 슈퍼유저 슬롯만 남아 있기 때문에 서버가 새 세션을 거부하고 있습니다.

먼저 연결된 내용을 확인하십시오:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

그런 다음 패턴을 찾으십시오. 수백 개의 idle 세션은 종종 너무 크게 구성된 애플리케이션 풀, 작업자 프로세스 누수 또는 각각 자체 풀을 여는 여러 애플리케이션 복제본을 가리킵니다. max_connections를 늘리면 시간을 벌 수 있지만 각 백엔드에 오버헤드가 있고 work_mem을 사용할 수 있기 때문에 메모리 압력도 증가합니다. 대부분의 웹 애플리케이션에서 트랜잭션 풀링 모드의 PgBouncer는 모든 앱 프로세스가 많은 직접 PostgreSQL 세션을 유지하도록 하는 것보다 더 나은 장기적인 수정 방법입니다.

빠른 분류 흐름

누군가 "데이터베이스가 다운되었습니다"라고 말하면 구성 건드리기 전에 짧은 경로를 사용합니다:

# 1. 이름이 예상한 호스트로 확인되고 있습니까?
getent hosts db.example.com

# 2. 이 클라이언트에서 TCP 포트에 연결할 수 있습니까?
nc -vz db.example.com 5432

# 3. psql이 동일한 호스트, 포트, 사용자 및 데이터베이스로 연결할 수 있습니까?
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. PostgreSQL은 실패한 시도에 대해 무엇을 기록합니까?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

nc가 실패하면 네트워크 및 리스너 영역에 머무르십시오. nc는 성공하지만 psql이 FATAL로 실패하면 PostgreSQL에 연결할 수 있으며 답은 일반적으로 인증, 데이터베이스 이름, 역할 이름, SSL 모드 또는 pg_hba.conf에 있습니다.

일반적인 문제 해결 단계

지속적인 연결 문제에 직면했을 때는 체계적인 진단을 위해 다음 일반 단계를 따르십시오:

PostgreSQL 로그 확인: 로그 파일은 가장 좋은 친구입니다. 여기에는 시작 문제, 오류 및 거부된 연결 시도에 대한 자세한 정보가 포함되어 있습니다. 위치는 일반적으로 postgresql.conf의 log_directory에 지정됩니다(예: Debian/Ubuntu의 경우 /var/log/postgresql/ 또는 데이터 디렉토리 내의 pg_log).
```
# 최근 로그 확인 예시
sudo tail -f /var/log/postgresql/postgresql-14-main.log
```
구성 파일 확인: postgresql.conf 및 pg_hba.conf에 구문 오류, 오타 또는 잘못된 값이 있는지 다시 확인하십시오. 잘못된 문자 하나라도 서버가 시작되거나 연결을 수락하지 못하게 할 수 있습니다.
PostgreSQL 다시 시작(구성 변경의 마지막 수단): pg_hba.conf 및 일부 postgresql.conf 매개변수의 경우 reload로 충분하지만 특정 중요한 변경 사항(예: listen_addresses)은 전체 다시 시작이 필요합니다.
```
sudo systemctl restart postgresql
```
서버에서 로컬로 테스트: 원격 시스템에서 연결이 실패하면 서버 자체에서 직접 연결을 시도하십시오. 이렇게 하면 문제가 서버 측인지 네트워크 관련인지 확인하는 데 도움이 됩니다.
```
# Unix 도메인 소켓 사용(사용 가능한 경우)
psql -U your_username -d your_database

# 또는 TCP/IP를 통해 localhost에 연결
psql -U your_username -h 127.0.0.1 -p 5432 -d your_database
```
로컬 연결은 작동하지만 원격 연결이 작동하지 않으면 문제는 listen_addresses, pg_hba.conf 또는 방화벽일 가능성이 높습니다.
클라이언트 구성 확인: 애플리케이션의 연결 문자열(예: PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE 환경 변수 또는 libpq 연결 문자열)이 서버 설정과 일치하도록 올바르게 구성되었는지 확인합니다.

팁 및 모범 사례

최소 권한 원칙: 일상적인 애플리케이션 연결에 postgres 슈퍼유저를 사용하지 마십시오. 필요한 권한만 있는 특정 역할을 만드십시오.
강력한 비밀번호: 데이터베이스 역할에 항상 강력하고 고유한 비밀번호를 사용하십시오.
pg_hba.conf 제한: 0.0.0.0/0 대신 pg_hba.conf에 정확한 클라이언트 IP 주소 또는 좁은 CIDR 범위를 지정하여 보안을 강화하십시오.
정기적으로 로그 모니터링: PostgreSQL 로그를 검토하는 루틴을 만드십시오. 로그 항목을 관찰하여 많은 문제를 조기에 발견할 수 있습니다.
구성 문서화: 특히 프로덕션 환경의 경우 postgresql.conf 및 pg_hba.conf 설정에 대한 명확한 기록을 유지하십시오.

가장 빠른 수정은 일반적으로 오류를 생성한 계층과 일치시키는 데서 비롯됩니다. 네트워크 도구는 호스트와 포트에 연결할 수 있는지 알려줍니다. PostgreSQL 로그는 서버가 세션을 거부한 이유를 알려줍니다. pg_hba.conf는 연결이 허용되는지 여부를 알려줍니다. 애플리케이션 구성은 프로덕션에서 실제로 동일한 값이 사용되고 있는지 알려줍니다.

일반적인 PostgreSQL 연결 오류 문제 해결

PostgreSQL 연결 기본 사항 이해

일반적인 연결 오류 및 해결 방법

오류 1: FATAL: database "..." does not exist

오류 2: FATAL: role "..." does not exist

오류 3: FATAL: password authentication failed for user "..."

오류 4: FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on

오류 5: could not connect to server: Connection refused (0x0000274D/10061)

오류 6: timeout expired 또는 connection timed out

오류 7: SSL 필요, SSL 비활성화 또는 인증서 확인 실패

오류 8: 클라이언트가 너무 많음

빠른 분류 흐름

일반적인 문제 해결 단계

팁 및 모범 사례

오류 1: `FATAL: database "..." does not exist`

오류 2: `FATAL: role "..." does not exist`

오류 3: `FATAL: password authentication failed for user "..."`

오류 4: `FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on`

오류 5: `could not connect to server: Connection refused (0x0000274D/10061)`

오류 6: `timeout expired` 또는 `connection timed out`