postgres

PostgreSQL の運用

このスキルでは、データベースエンジンとしての PostgreSQL を安全に運用します。ワークロードに照らして設定を見直し、インデックスやクエリプランの問題を特定し、VACUUM と肥大化を管理し、WAL アーカイブとポイントインタイムリカバリを設定して検証し、妥当性を説明できるフェイルオーバープランを備えたレプリケーションを運用し、拡張機能に対処し、メジャーバージョンアップグレードを計画し、証拠に基づいてインシデントを診断します。これは、特定のツール（PostgreSQL）を対象としたツールスキルです。データベースの方法論 — エンジンをまたいだバックアップ戦略、移行パターン、SQL による分析パターン — は data-engineering にあります。アプリケーションレベルのデータアクセスパターンは backend-engineering に、スキーマとデータモデリングは data-architect および data-engineering にあります。Supabase プラットフォームの管理については、supabase を参照してください。

運用上の契約

1. 変更を加える前に、読み取り専用で調査します。 まず、設定、カタログ統計、ログを確認します。同梱の pgdiag スクリプトは読み取り専用の証拠を収集し、すべてのセッションを default_transaction_read_only=on にして開始します。
2. 実行前に、対象、範囲、ロールバック手段を確認します。 読み取り専用の調査は確認なしで進められます。変更操作 — pg_ctl による停止、昇格、拡張機能のインストール、pg_upgrade の実行 — には、対象のインスタンスを指定した人間からの明示的な指示が必要です。
3. バックアップは、復旧できる証拠にはなりません。 スケジュールに沿ってスクラッチインスタンスでリストアを検証してください。バックアップログだけを根拠に、復旧可能だと主張してはいけません。
4. 証拠の提示範囲を限定します。 カタログクエリとログの抜粋は要約してください。完全なログ、postgresql.conf、パスワードを含む接続文字列をチャットに出力してはいけません。
5. 引き渡し時点で検証します。 SELECT 1 の応答で確認できるのは接続性であり、健全性ではありません。pg_basebackup をリプレイして確認できるのは復旧可能性であり、今日の WAL がアーカイブされていることではありません。

pgdiag スクリプト

scripts/pgdiag は、エージェント向けの読み取り専用診断コレクターです。psql を呼び出し、すべてのセッションを default_transaction_read_only=on にして開始し、範囲を限定した JSON を出力します。--help はサーバーも psql もインストールされていない環境で動作します。

scripts/pgdiag --help                      # no cluster needed
scripts/pgdiag --json                      # all checks, machine-readable
scripts/pgdiag --host db1 --dbname app --json
scripts/pgdiag --check identity --check wal_archive --json
scripts/pgdiag --plan-for "SELECT * FROM orders WHERE id = 42" --json

終了コード：0 は正常、1 は実行時または収集時のエラー、2 は使用法のエラー、127 は psql バイナリが見つからない場合、124 はタイムアウトです。--check は指定したサブセットを実行し、--plan-for は読み取り専用のステートメント 1 つに対する EXPLAIN (FORMAT JSON) プランを追加します。このスクリプトはデータを変更するステートメントを発行しません。また、実行されてしまったステートメントも、サーバー側の読み取り専用セッション設定によって拒否されます。

運用の流れ

1. インスタンスを特定します：バージョン、リカバリ状態、設定ファイルの場所、接続文字列の形式、プライマリかスタンバイかを確認します。
2. 証拠を収集します：pgdiag --json で、設定、接続、インデックスの使用状況、肥大化の兆候、WAL アーカイブ、リカバリ、レプリケーション、拡張機能、データベースサイズを確認します。
3. 症状に基づいて優先度を付けます：報告された問題を証拠に照らし合わせます（クエリが遅い → プランとインデックスの使用状況、バックアップが停止している → アーカイバー、同期ずれ → レプリケーションの遅延）。
4. 確認を得て実行します：人間からの指示を受けた後、範囲を限定して変更を行います。最初にロールバック手段を明示します。
5. 検証します：関連するチェックを再実行し、引き渡し時点で確認できる状態を確かめます。

設定

• 実行時の信頼できる情報源はファイルではなくpg_settingsです。SHOW／current_setting()は、再読み込みや上書き（ALTER SYSTEM、コマンドラインの-c、環境変数PGOPTIONS）を反映します。pgdiagのconfigチェックには、運用上重要な値が一覧表示されます。
• 再読み込み（pg_ctl reload／SELECT pg_reload_conf()）で済む変更と、再起動が必要な変更を把握してください。メモリ関連（shared_buffers、max_connections、wal_level、max_wal_senders）は再起動が必要ですが、チューニングやログ関連のパラメーターのほとんどは再読み込みで反映されます。
• 遅いクエリの証跡が必要になる前に、log_destination、logging_collector、log_min_duration_statementを確認してください。track_io_timing=onにすると、pg_stat_databaseのI/O時間が意味のある値になります。
• 接続の逼迫状況を把握するには、pg_stat_activityの状態別件数をmax_connectionsと比較してください。接続プーラーの導入はアプリケーションアーキテクチャ上の判断であり、backend-engineeringの対象です。
• GUCの根拠、再読み込みと再起動の比較表、パラメーター変更のレビュー方法については、references/01-configuration.mdを参照してください。

インデックスとクエリプラン

• まず証跡を確認してください。pg_stat_user_indexesにはidx_scan／idx_tup_read／idx_tup_fetchが表示されます。フィルターがあるのにテーブルがシーケンシャルスキャンされ、seq_tup_readが大きい場合は、インデックス不足の可能性があります。
• 実際のワークロードのクエリに対するEXPLAIN (ANALYZE, BUFFERS)は、推測よりも確実です。推定行数と実際の行数を比較してください。大きな不一致は、プランナー統計の古さ（pg_statisticの鮮度の問題）または不適切なパラメーター（random_page_cost、effective_cache_size）を示しています。
• 長期間にわたってidx_scan = 0の未使用インデックスは、書き込みとメンテナンスの負担になります。無効なインデックス（pg_index.indisvalid = false）は次回のVACUUMで削除されるため、意図的に修復または削除してください。
• インデックスの選択（BRINとbtreeの比較、部分インデックス、カバリングインデックス）はスキーマ設計に関するものであり、data-architectの対象です。このスキルでは、既存のインデックスの測定と運用を担当します。
• クエリプランの読み方、インデックス使用状況を確認するSQL、プランレビューのチェックリストについては、references/02-indexes-and-query-plans.mdを参照してください。

VACUUMと肥大化

• VACUUMはデッドタプルを回収し、プランナー統計を更新します。自動VACUUMがこれを自動で行うはずです。実際に動作していることを確認してください。autovacuum=on、ワーカー数、テーブルごとのrelfrozenxid／n_dead_tupの推移を確認します。
• 肥大化とは、テーブルファイルのサイズと有効なデータの間にある差です。VACUUMの実行よりも速くpg_stat_user_tables.n_dead_tupが増加している場合は、最も有力な兆候です。VACUUMの失敗やスキップによるヒープの肥大化は、有効なタプルが少ないのにrelpagesが大きい状態として現れます。
• 自動VACUUMが遅れている場合は、対象を絞り、実施を確認したメンテナンス時間帯を設けてください（対象テーブルを指定したVACUUMに限り、無差別に実行しないでください）。そのうえで、自動VACUUMが遅れた原因（長時間トランザクション、接続の飽和、ワーカー不足）を確認します。
• VACUUM FULLを定例作業として扱わないでください。テーブルを書き換え、ロックを取得するため、メンテナンス時間帯と、検証済みのバックアップ手段が必要です。
• 肥大化の測定方法と自動VACUUMのチューニング方法については、references/03-vacuum-and-bloat.mdを参照してください。

バックアップ：WAL アーカイブとポイントインタイムリカバリ

• リカバリモデル：ベースバックアップと継続的な WAL アーカイブを組み合わせると、ポイントインタイムリカバリ（PITR）が可能になります。ベースバックアップを復元し、目標時刻までアーカイブ済み WAL を適用します。
• リカバリターゲットには 2 つの種類があります。時刻ベース（pitr|point.in.time はこの種類の略記で、「昨日の 02:00」のような実時間の目標）と、位置ベース（特定の LSN またはタイムラインマーカー）です。どちらも有効な recovery_target の入力値です。
• WAL アーカイブを使用するには、archive_mode=on に設定し、archive_command が機能していて、アーカイバーが正常である必要があります。pg_stat_archiver で archived_count が増加し、failed_count が安定し、last_failed_wal が空か古い値であることを確認してください。
• アーカイブとストリーミングレプリケーションの両方で、wal_level を replica 以上にする必要があります。この設定の変更には再起動が必要です。
• pg_basebackup（または専用ツール）を使って、WAL と整合性の取れたバックアップを取得します。各バックアップにラベルを付け、pg_stop_backup() の LSN／タイムラインを記録し、信頼する前にアーカイブを使って復元テストを行ってください。
• PITR の手順、recovery_target のオプション、指定した時点への復元手順、RPO／RTO の考え方（方法論は data-engineering を参照）：references/04-backups-wal-pitr.md。

レプリケーションとフェイルオーバー

• ストリーミングレプリケーションでは、スタンバイがレプリケーションスロットを使って接続し、WAL を継続的に受信します。pg_stat_replication（state=streaming、追従する replay_lsn、小さい replay_lag）とスタンバイのリカバリ状態で確認してください。
• 同期と非同期のどちらにするかを慎重に決めてください。同期（synchronous_standby_names）では、コミット遅延と引き換えに耐久性が保証されます。非同期では、フェイルオーバー時に直近のコミットが失われるリスクがあります。
• フェイルオーバー計画は、単に pg_ctl promote を実行するだけではありません。誰が昇格させるのか、クライアントをどうリダイレクトするのか、旧プライマリが復帰した際にどうするのか、データをどう確認するのか（昇格時の遅延、タイムラインの分岐）を定めます。
• 昇格は変更操作です。まず対象と範囲を確認してください。レプリケーションマネージャーツール（Patroni、repmgr）を使用している場合は、手動で昇格するのではなく、そのスイッチオーバーコマンドを使います。旧プライマリはスタンバイとして再参加させ、2 つのプライマリが書き込みを行う状態を決して作らないでください。
• ストリーミングのセットアップ、スロット管理、遅延の測定、フェイルオーバー手順書：references/05-replication-and-failover.md。

拡張機能

• まず一覧を確認します。pg_extension（インストール済み）と pg_available_extensions（利用可能）で、存在する拡張機能とディスク上のバージョンを確認できます。pgdiag の extensions チェックでも同じ確認ができます。
• 拡張機能のインストールは共有カタログを変更し、拡張機能によっては元に戻すのが難しい形でデータベースを変更します。インストールはロールバック手順を伴う変更操作であり、反射的に CREATE EXTENSION を実行してはいけません。
• メジャーバージョンのアップグレードでは通常、新しいバイナリ上で拡張機能（PostGIS、pgvector など）の再インストールまたは再ビルドが必要です。pg_upgrade の前に、各拡張機能のアップグレードに関する注意事項を確認してください。
• 信頼済みの拡張機能は、スーパーユーザーでなくても自身のデータベースにインストールできます。拡張機能のポリシーと共有ライブラリの利用可否は、platform-engineering におけるインフラストラクチャ上の判断事項です。
• よく使われる拡張機能、ライフサイクル、バージョンアップグレード時の注意点：references/06-extensions.md。

アップグレード

• マイナーアップグレードではインプレースでバイナリを差し替え（再起動）ます。メジャーアップグレード（例：15 → 16）ではディスク上の形式が変わるため、pg_upgrade または論理ダンプ／リストアが必要です。
• まずアップグレード経路を計画します。対象となるバージョン全体のリリースノートとアップグレードガイドを読み、拡張機能とサポートされていない機能を確認し、方法（リンクモードを使う pg_upgrade または論理方式）を選び、実際のデータ構成を使ってスクラッチ環境でリハーサルします。
• pg_upgrade は、ダウンタイムと検証済みバックアップを必要とする変更操作です。書き込みを停止し、--old／--new バイナリを指定してアップグレードを実行し、新しいクラスターで analyze を実行します。古いクラスターを廃止する前に、アプリケーション側で検証します。
• 論理レプリケーション（パブリッシャー／サブスクライバー）は、ほぼゼロダウンタイムでのメジャーアップグレード経路として利用できます。また、移行パターンでもあり、その方法論については data-engineering を参照してください。
• バージョンマトリックス、アップグレード手順書、ロールバックの判断については references/07-upgrades.md を参照してください。

根拠に基づく診断

根拠の確認順に診断します：識別情報／バージョン → 設定 → 接続 → インデックスの使用状況と実行計画 → バキューム／肥大化 → WAL アーカイブ → レプリケーション → 拡張機能。

• pgdiag --json は、最初の根拠レイヤーを上限付きの単一ペイロードにまとめて収集します。変更後は、影響を受けたチェックを再実行してください。
• クエリが遅い場合 → EXPLAIN (ANALYZE, BUFFERS) と pg_stat_user_indexes／seq_tup_read を確認します。random_page_cost を変更する前に、プランナー統計が最新か確認してください。
• バックアップが停滞している場合 → pg_stat_archiver の failed_count、last_failed_wal、およびアーカイブ先のディスク／ネットワークを確認します。
• スタンバイが遅れている場合 → pg_stat_replication のラグ列、スロットの保持状況（pg_replication_slots）、プライマリとスタンバイ間のネットワーク飽和状態を確認します。
• 相関関係を原因として示してはいけません。クエリの遅さと高い n_dead_tup は根拠であって、診断ではありません。何を測定し、何が変化し、何を検証したかを明記してください。
• 障害モード別の振り分けと、症状→調査→修正の表については references/08-diagnostics.md を参照してください。

リファレンスの参照先

読み込む状況	リファレンス
チューニング、GUC のレビュー、リロードと再起動の判断	references/01-configuration.md
遅いクエリ、インデックスの使用状況、実行計画のレビュー	references/02-indexes-and-query-plans.md
autovacuum、デッドタプル、肥大化の測定	references/03-vacuum-and-bloat.md
WAL アーカイブ、ベースバックアップ、PITR、リストア訓練	references/04-backups-wal-pitr.md
ストリーミングのセットアップ、スロット、ラグ、フェイルオーバー手順書	references/05-replication-and-failover.md
拡張機能の一覧とアップグレード時の注意点	references/06-extensions.md
マイナー／メジャーアップグレード、pg_upgrade、ロールバック	references/07-upgrades.md
症状から調査対象を特定する診断表	references/08-diagnostics.md
情報源、バージョンに関する観察事項、更新手順	references/00-source-index.md

含まれる成果物

• scripts/pgdiag：読み取り専用の診断情報収集ツール（標準ライブラリのみを使用し、クラスターなしで --json、--check、--plan-for、--help を実行できます）。
• tests/test_pgdiag.py：読み取り専用の仕様を含む、偽の psql スタブに対する決定論的テスト。
• references/：上記の運用トピックを網羅する、日付入りで情報源を索引化した9つのリファレンス。

検証基準

主張	最低限必要な根拠
インスタンスに接続でき、バージョンが確認できる	pgdiag --check identity --json が解析され、バージョンとリカバリ状態を報告する
設定が把握されている	pgdiag の config チェックに、運用上重要な GUC が一覧表示される
アーカイブが正常である	pg_stat_archiver：archived_count が増加し、failed_count が増加せず、last_failed_wal が古い
レプリケーションが最新である	pg_stat_replication：state=streaming で、ラグが合意済みの上限内にある
バックアップから復旧できる	スクラッチインスタンス上で、ベースバックアップをリストアし、指定時点まで WAL を再生する
診断が妥当である	主張の前に根拠を収集し、チェックを再実行して修正を検証している

厳守事項

• 対象を明示し、ロールバック手順を提示する人間からの明確な指示がない限り、変更操作（pg_ctl stop、昇格、pg_upgrade、拡張機能のインストール、メンテナンス目的のVACUUM）は実行しないでください。読み取り専用の調査は自由に進められます。
• 検証していない主張を証拠として示さないでください。何を、いつ、どのように測定したかを明記してください。
• ログ全体、postgresql.confの内容、パスワードを含む接続文字列は決して公開しないでください。
• pgdiagは書き込み可能なセッションで実行しないでください。このツールは設計上、読み取り専用です。

使用しない場合

• アプリケーションレベルのデータアクセスパターン（アプリケーションコードでのコネクションプーリング、ORMの使用、クエリの構築、サービス内のトランザクション）— backend-engineeringの対象です。
• スキーマ設計とデータモデリング（テーブル、キー、正規化、ディメンショナルモデル）— data-architectおよびdata-engineeringの対象です。
• データベースエンジンを横断する方法論（バックアップ戦略、移行パターン、分析用SQL）— data-engineeringの対象です。
• Supabaseプラットフォームの管理（マネージドプロジェクト、CLIスタック、セルフホスト可能なSupabaseスタック）— supabaseの対象です。Supabase固有の規約を使用しない通常のPostgreSQL運用は、こちらの対象です。エージェントのSupabaseタスクの能力を測定するには、このスキルのエージェント評価ハーネスのリファレンスを使用してください。
• その他のデータベースエンジン（Redis、MongoDB、Elasticsearch、ベクトルストア）— これらはdata-engineeringのリファレンスで扱います。このスキルの対象はPostgreSQLのみです。