DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

一般的なPostgreSQL接続エラーのトラブルシューティング

PostgreSQLの接続エラーをトラブルシューティングするには、サービスの状態、リッスンアドレス、pg_hba.conf、認証情報、DNS、SSL、ログを確認します。

一般的な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コマンドラインツール、Webアプリケーション、デスクトップクライアント）はPostgreSQLサーバープロセスに接続しようとします。このプロセスは通常、特定のネットワークインターフェースとポート（デフォルトは5432）で着信接続をリッスンします。

接続の受け入れと認証を管理する2つの主要な設定ファイルがあります：

  • postgresql.conf：リッスンするネットワークインターフェース（listen_addresses）やポート（port）など、サーバーの一般的な動作を制御します。
  • pg_hba.conf：（ホストベース認証）誰が どこから どのデータベースに、どの認証方法で接続できるかを規定します。このファイルはセキュリティとアクセス制御にとって重要です。

これらのファイルの役割とクライアントサーバー間の相互作用を理解することは、効果的なトラブルシューティングの基本です。

一般的な接続エラーとその解決策

遭遇する可能性が高い最も頻繁な接続エラーとそれぞれの解決策を見ていきましょう。

エラー1：FATAL: database "..." does not exist

このエラーは、クライアントが指定したデータベース名がPostgreSQLサーバーに存在しないことを意味します。

説明： クライアントアプリケーションまたはpsqlコマンドが、作成されていないか名前が間違っているデータベースに接続しようとしています。

解決策：

  1. データベース名を確認する： 接続文字列またはpsqlコマンド内のデータベース名が正しいことを確認します。
  2. 既存のデータベースを一覧表示する： デフォルトデータベース（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

これは、接続に指定されたユーザー名（ロール）が存在しないことを示します。

説明： データベースエラーと同様に、接続しようとしているユーザーアカウントが存在しないか、名前が間違っています。

解決策：

  1. ユーザー名を確認する： 接続文字列内のユーザー名を確認します。
  2. 既存のロールを一覧表示する： スーパーユーザーアカウント（例：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ユーザー（ロール）に保存されているものと一致しません。

解決策：

  1. アプリケーションの設定を確認する： アプリケーションの接続文字列または環境変数を確認し、パスワードが正しいことを確認します。

  2. パスワードをリセットする（スーパーユーザーアクセス権がある場合）：

    # 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、ユーザー、データベース、認証方法）に一致するルールが含まれていません。

解決策：

  1. pg_hba.confを見つける： 場所はOSやインストール方法によって異なります（例：Debian/Ubuntuでは/etc/postgresql/14/main/pg_hba.conf、またはpsqlでSHOW hba_file;で指定）。

  2. 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を狭くすると、間違いを見つけやすくなります。

  3. 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が期待されるネットワークインターフェースまたはポートでリッスンしていない。
  • ファイアウォールが接続をブロックしている。

解決策：

  1. PostgreSQLは実行されていますか？

    • サービスの状態を確認する：
      sudo systemctl status postgresql
# または、古いシステム/その他の設定の場合：
# sudo service postgresql status
      実行されていない場合は、起動します：
      sudo systemctl start postgresql
    • ログを確認する： PostgreSQLログ（例：/var/log/postgresql/）で起動エラーを確認します。

  2. 正しいアドレス/ポートでリッスンしていますか？

    • 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でのみリッスンしているか、まったくリッスンしていません。

  3. ファイアウォールが接続をブロックしていますか？

    • サーバー側のファイアウォール： 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とは異なり、タイムアウトは接続試行がサーバーに到達しなかったか、サーバーが応答しなかったことを意味します。これは多くの場合、ネットワーク接続の問題またはサーバーの過負荷を示しています。

解決策：

  1. ネットワーク接続：
    • サーバーにpingを実行する： ping server_ip_address。pingが失敗した場合、根本的なネットワーク問題（ケーブル、ルーター、サーバーオフライン）があります。
    • トレースルート/MTR： traceroute server_ip_address（Linux/macOS）またはtracert server_ip_address（Windows）は、ネットワークパスに沿ってどこで接続が失敗しているかを特定するのに役立ちます。
  2. サーバーのlisten_addressesとファイアウォール： エラー5の解決策を再確認します。listen_addressesの設定ミスやファイアウォールも、サーバーに到達できない場合にタイムアウトを引き起こす可能性があります。
  3. サーバー負荷： サーバーが極端な負荷（高いCPU、低メモリ、過剰なディスクI/O）下にある場合、新しい接続を迅速に受け入れるのに忙しすぎて、タイムアウトが発生する可能性があります。システムリソースの使用状況を確認します。

エラー7：SSL Required, SSL Disabled, or Certificate Verification Failed

PostgreSQLは、サーバー設定とpg_hba.confルールに応じて、暗号化された接続と暗号化されていない接続の両方を受け入れることができます。クライアントは、SSLがオフ、SSLが必要、または証明書の検証に失敗したというメッセージで失敗する場合があります。

3つの場所を確認します：

# サーバーで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：Too Many Clients Already

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を使用する可能性があるため、メモリプレッシャーも増加します。ほとんどのWebアプリケーションでは、すべてのアプリケーションプロセスが多数の直接PostgreSQLセッションを保持するよりも、トランザクションプーリングモードのPgBouncerの方が長期的には優れた修正策です。

迅速なトリアージフロー

誰かが「データベースがダウンしている」と言った場合、設定に触れる前に短いパスを使用します：

# 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にあります。

一般的なトラブルシューティング手順

永続的な接続の問題に直面した場合は、次の一般的な手順に従って体系的な診断を行います：

  1. PostgreSQLログを確認する： ログファイルは最良の味方です。起動の問題、エラー、拒否された接続試行に関する詳細情報が含まれています。場所は通常、postgresql.confのlog_directoryで指定されます（例：Debian/Ubuntuでは/var/log/postgresql/、データディレクトリ内のpg_log）。

    # 最近のログを確認する例
sudo tail -f /var/log/postgresql/postgresql-14-main.log

  2. 設定ファイルを確認する： postgresql.confとpg_hba.confに構文エラー、タイプミス、または誤った値がないか再確認します。1文字でも間違っていると、サーバーの起動や接続の受け入れが妨げられる可能性があります。

  3. PostgreSQLを再起動する（設定変更の最終手段として）： pg_hba.confや一部のpostgresql.confパラメータにはリロードで十分な場合が多いですが、特定の重要な変更（listen_addressesなど）には完全な再起動が必要です。

    sudo systemctl restart postgresql

  4. サーバー上でローカルにテストする： リモートマシンからの接続が失敗する場合は、サーバー自体で直接接続してみてください。これにより、問題がサーバー側かネットワーク関連かを判断するのに役立ちます。

    # 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、またはファイアウォールです。

  5. クライアント設定を確認する： アプリケーションの接続文字列（例：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は、接続が許可されているかどうかを示します。アプリケーション設定は、同じ値が実際に本番環境で使用されているかどうかを示します。