PostgreSQLで「database is being accessed by other users」が出たら？強制削除の前にセッション台帳を作る

1. 解消を試す前に、拒否された状態を保存する

完全なSQLSTATE、対象データベース、試した操作、UTC時刻、関連するデプロイまたは復元の手順、PostgreSQLが報告した接続数を記録します。同じインシデント記録にすぐ終了クエリを貼り付けて、証拠がそろったとは判断しません。プールが再接続したりバックエンドが消えたりすると、所有者をたどる情報が変わります。

今回確認したStack Overflowの事例では、revokeとterminateを試した後も24セッションが報告されていました。別のDBA.StackExchangeの事例では、保守用データベースに接続していたスクリプトが自分自身の接続を終了していました。原因は異なりますが、どちらも「すべてを終了して再試行」すると、ブロッカーを説明する文脈を失うことを示します。

復元や環境の置き換えに伴う作業なら、バックアップの証拠と変更チケットは別に保存します。この台帳だけでは、バックアップが復元可能であること、データベースを破棄してよいこと、アプリケーション所有者が停止を承認したことは証明できません。

2. コレクターが接続しているサーバーとデータベースを証明する

postgres、または対象ではない別のデータベースへ新しい保守セッションを開きます。PostgreSQLのDROP DATABASE文書は、対象以外のデータベースから実行するよう求めています。同じ原則により、証拠コレクター自身が説明対象のセッションになることも防げます。

current_database()、current_user、session_user、pg_backend_pid()、inet_server_addr()、inet_server_port()、server_version、server_version_num、pg_is_in_recovery()を記録します。エンドポイントをアプリケーション設定および変更チケットと照合します。プロキシやマネージドエンドポイントのため正確なクラスタを証明できない場合は、見慣れたホスト名だけで決めつけず、「クラスタ識別未解決」と記録します。

最初のクエリでは、current_database()が対象データベースと等しいかも判定します。真なら停止し、別の保守用データベースへ接続し直します。pg_databaseに対象がない場合も停止します。名前の誤り、別クラスタへの接続、すでに完了した変更は、それぞれ別の事象です。

3. 境界の明確な読み取り専用スナップショットを開く

1回の収集にはBEGIN ISOLATION LEVEL REPEATABLE READ READ ONLYを使用します。対象データベースのOID、所有者、datallowconn、接続上限、コレクターにCONNECT権限があるかを記録し、pg_read_all_statsへの所属とtrack_activitiesの設定も保存します。

PostgreSQLでは、現在のクエリ情報はトランザクション内で最初に要求された時点のスナップショットとなり、そのトランザクション中は一貫します。複数ビューを結び付けるには有用ですが、同じトランザクション内で待機しても後で必要な新しい比較にはなりません。1回目の読み取り専用処理をコミットしてから、2回目には新しいトランザクションを開始します。

一般ロールでも他のセッションの存在と一般的な属性は見えますが、別ロールが所有するセッションの多くの列はnullになることがあります。pg_read_all_statsがなければ、台帳に「可視性不完全」と明記します。nullは「証拠なし」という意味ではなく、状態変更を認める根拠にもなりません。

4. SQL本文を持ち出さずにセッションを棚卸しする

datnameが対象と一致する行について、pid、leader_pid、usename、application_name、client_addr、client_port、backend_type、state、backend_start、xact_start、query_start、state_change、wait_event_type、wait_eventを記録します。バックエンド種別、ロール、アプリケーション、クライアント、PIDで並べ、同じ接続プールのメンバーを見つけやすくします。

query列は既定で出力しません。クエリ本文には顧客データ、リテラル、認証情報が含まれる可能性があり、接続の所有者を特定するために必須ではありません。対象行にコレクターのPIDが含まれるかも記録します。コレクターが正しい保守用データベースへ接続していれば、対象行にそのPIDは現れないはずです。

application_nameとclient_addrは手掛かりであり、最終的な本人確認ではありません。空のapplication_nameでも実在するクライアントの場合があります。client_addrがnullならUnixソケットまたは内部プロセスかもしれません。ロール、エンドポイント、デプロイ状況、時刻が一致するときにだけ、特定のアプリケーション所有者へ帰属させます。

5. セッション一覧だけでは見落とすブロッカーを確認する

対象データベースのpg_prepared_xactsを調べ、トランザクションID、GID、prepare時刻、所有者、データベースを記録します。プリペアドトランザクションは永続的な二相コミットの状態であり、アイドル状態のクライアントセッションではありません。このガイドではcommitもrollbackも行いません。GIDを所有するアプリケーションまたはトランザクションコーディネーターへ渡します。

対象データベースに関連する論理pg_replication_slotsを調べ、スロット名、プラグイン、種類、temporary、active、active_pid、保持しているトランザクション識別子、LSN位置を記録します。activeがfalseでも、スロットは所有者のいるレプリケーションオブジェクトです。非アクティブという理由だけで削除してはいけません。

対象データベースのOIDを使ってpg_subscriptionを調べ、サブスクリプション名、所有者、有効状態、スロット名、publication名だけを記録します。subconninfoは選択しません。PostgreSQLは平文パスワードを含み得ると警告し、一般利用者からその列へのアクセスを取り上げています。サブスクリプションはレプリケーション設計の一部で、一般的な掃除対象ではありません。

これらの確認は付け足しではありません。PostgreSQLは、FORCEでも対象データベースのプリペアドトランザクション、アクティブな論理レプリケーションスロット、サブスクリプションを解消しないと明示しています。いずれかが見つかったら停止し、そのシステムの所有者から処理計画を得ます。

6. 新しい2回目のスナップショットで再接続競合を見つける

1回目の読み取り専用トランザクションをコミットし、UTC時刻とともに出力を保存します。アプリケーションの接続プール周期に応じた短い間隔を置き、新しい読み取り専用トランザクションで同じクエリを再実行します。1回目のトランザクションにキャッシュされた結果を2回目として使ってはいけません。

行順ではなく安定キーを比較します。セッションはPIDとbackend_start、プリペアドトランザクションはGID、レプリケーションスロットはスロット名、サブスクリプションはOIDまたは名前を使います。同じロール、アプリケーション、クライアント系統で新しいPIDが現れるなら、プールまたはワーカーが再接続している可能性があります。プロバイダー所有のロールが断続的に現れる場合は、削除可能な隙を狙わず、マネージドサービスへエスカレーションします。

2つのスナップショットが違う場合、正しい結論は「再接続競合」です。両方を保存します。所有者がセッションを補充し続けているとき、一瞬だけ件数がゼロになっても静止状態は証明できません。

7. 所有者の分岐を分類し、境界で止まる

アプリケーションプール：複数バックエンドが同じロール、アプリケーション、エンドポイントを共有するか、消えた後に新しいPIDで戻ります。台帳をサービス所有者へ渡します。管理ツール：クライアント名またはローカルソケットがpgAdmin、psql、IDE、移行ツールを示します。所有者の通常手順で閉じます。バックグラウンドワーカー：backend_typeやアプリケーション情報から判別し、匿名利用者として扱わずコンポーネント所有者へ連絡します。

プリペアドトランザクション：pg_prepared_xactsに対象行があり、判断はトランザクションコーディネーターが所有します。レプリケーションオブジェクト：論理スロットまたはサブスクリプションが対象を示し、そのライフサイクルはレプリケーション所有者が決めます。マネージドサービス：プロバイダーまたはスーパーユーザーロールは見えるものの利用者側に操作権限がなければ、2つのスナップショットを添えてプロバイダーへ問い合わせます。

可視性不完全：コレクターにpg_read_all_statsがない、必要な列が伏せられている、サーバーエンドポイントが曖昧、または必須のカタログ照会が拒否された状態です。この分岐では必ず停止します。権限のあるデータベース管理者またはプロバイダーに同じ台帳を再実行してもらい、権限エラーを「存在しない」と解釈しません。

8. 掃除スクリプトではなく、秘匿化した証拠一式を保存する

インシデント時刻、対象名とOID、保守用データベース、エンドポイントとバージョン、コレクターのロールとPID、pg_read_all_statsとtrack_activitiesの結果、2つのスナップショット、分類、未解決項目、次の判断を担当する所有者またはチームを保存します。サポート経路でクライアントアドレスが不要なら秘匿化します。

完全なSQL本文、パスワード、接続文字列、subconninfoは含めません。証拠をチーム間で渡すならハッシュを計算し、収集者を記録します。別の権限保持者が「見えるセッションなし」「ブロッカーなし」「コレクターにはブロッカーが見えない」を区別できる成果物にします。

終了条件は意図的に厳格です。見えるすべてのセッションとクラスタオブジェクトに所有者があり、2つのスナップショットが安定し、コレクターに十分な可視性があり、対象、エンドポイント、保守用データベースが証明され、バックアップと停止の判断が別の承認済み変更に存在すること。それまでは「証拠不足」であり、終了、権限変更、再起動、名前変更、削除の許可ではありません。

9. 埋め込みの読み取り専用ワークシートを同じバイト列で2回実行する

target_db に正確なデータベース名を設定し、postgres または別のメンテナンス用データベースから次のワークシートを実行する。スクリプトは UTF-8 で 2,365 バイト、SHA-256 は e787aced94fd049e0e6335d61a51bca6ef0d659f88264e0e2153276f273fc196 である。repeatable-read の読み取り専用トランザクションを開始し、識別情報、データベース、セッション、プリペアドトランザクション、論理レプリケーションスロット、サブスクリプションの証拠を取得するが、元のクエリ本文やサブスクリプション接続文字列は取得しない。

```sql \set ON_ERROR_STOP on \pset pager off \if :{?target_db} \else \echo 'Set target_db before running, for example: \\set target_db my_database' \quit 2 \endif BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY; SELECT clock_timestamp() AT TIME ZONE 'UTC' AS captured_at_utc, current_database() AS maintenance_database, :'target_db' AS target_database, current_database() = :'target_db' AS collector_is_on_target, current_user, session_user, pg_backend_pid() AS collector_pid, inet_server_addr()::text AS server_address, inet_server_port() AS server_port, current_setting('server_version') AS server_version, current_setting('server_version_num') AS server_version_num, pg_is_in_recovery() AS server_in_recovery, current_setting('track_activities') AS track_activities, pg_has_role(current_user, 'pg_read_all_stats', 'member') AS has_pg_read_all_stats; SELECT d.oid AS target_oid, d.datname AS target_database, pg_get_userbyid(d.datdba) AS database_owner, d.datallowconn AS connections_allowed, d.datconnlimit AS connection_limit, has_database_privilege(current_user, d.oid, 'CONNECT') AS collector_has_connect FROM pg_database AS d WHERE d.datname = :'target_db'; SELECT a.pid, a.leader_pid, a.usename, a.application_name, a.client_addr::text AS client_address, a.client_port, a.backend_type, a.state, a.backend_start, a.xact_start, a.query_start, a.state_change, a.wait_event_type, a.wait_event FROM pg_stat_activity AS a WHERE a.datname = :'target_db' ORDER BY a.backend_type, a.usename, a.application_name, a.client_addr, a.pid; SELECT x.transaction, x.gid, x.prepared, x.owner, x.database FROM pg_prepared_xacts AS x WHERE x.database = :'target_db' ORDER BY x.prepared, x.gid; SELECT s.slot_name, s.plugin, s.slot_type, s.database, s.temporary, s.active, s.active_pid, s.xmin, s.catalog_xmin, s.restart_lsn, s.confirmed_flush_lsn FROM pg_replication_slots AS s WHERE s.database = :'target_db' ORDER BY s.slot_name; SELECT s.oid AS subscription_oid, s.subname AS subscription_name, pg_get_userbyid(s.subowner) AS subscription_owner, s.subenabled AS enabled, s.subslotname AS slot_name, s.subpublications AS publications FROM pg_subscription AS s JOIN pg_database AS d ON d.oid = s.subdbid WHERE d.datname = :'target_db' ORDER BY s.subname; COMMIT; ```

同一バイト列について、読み取り専用トランザクション境界、対象パラメータの一貫性、5種類のカタログ、元の query 本文と subconninfo を含まないこと、状態変更SQL文がないことを静的に検査した。この検査はテキストの内容だけを証明し、PostgreSQLの実機統合テストではない。

運用前に、権限のある管理者が同じメジャーバージョンとマネージドサービス制限を持つ非本番クラスタで実行し、各結果セットを確認し、スクリプトハッシュをスナップショット1と保存する。コミットして接続プール1周期を待ち、同じバイト列をスナップショット2として再実行する。カタログ拒否、null値、版の相違は未解決境界であり、台帳を完全に見せるため失敗した検査を削除してはならない。