PostgreSQL 18 運用設計入門：全9回実機検証ロードマップ

3フェーズ学習体系の構成

• Phase 1：アーキテクチャと環境構築（第01回〜第03回）
  PostgreSQLのメモリ構造（共有バッファとプロセス個別メモリ）やプロセス構成を理解し、AlmaLinux 10への適切なインストール、ネットワーク接続制御（postgresql.conf / pg_hba.conf）を確立します。
• Phase 2：堅牢な運用とデータ保全（第04回〜第06回）
  最小権限の原則に基づくロール設計、カスタム形式（-Fc）を活用した柔軟なバックアップ・リストア、MVCCアーキテクチャに起因するテーブル肥大化を防ぐVACUUM/ANALYZEのチューニングを習得します。
• Phase 3：ライフサイクルと高可用化（第07回〜第09回）
  計画停止と迅速な切り戻しを担保するマイナーバージョンアップ、ハードリンク（–link）を用いたダウンタイム最小化メジャー更新、WAL物理転送による2ノードストリーミングレプリケーションの構築を完結させます。

第01回：PostgreSQLアーキテクチャ入門：プロセス構成とメモリの仕組み

• 学習テーマ：Postmaster主プロセスと子プロセス群の役割分担、共有メモリとローカルメモリの分離構造。
• 現場の重要知見：クライアントが接続するたびにフォークされるバックエンドプロセスのメモリ消費（work_mem）と、接続急増時のOOM Killer発動リスク。
• 主要実機確認コマンド：

  # プロセス親子関係の確認
  ps -ef --forest | grep -E 'postgres|UID'
  
  # 共有メモリ使用量と設定値の確認
  psql -c "SHOW shared_buffers;" -c "SHOW work_mem;"

• 習得成果：ps -ef --forest コマンドでプロセスツリーを追跡し、共有バッファ（shared_buffers）とWALバッファのデータ保護メカニズムを説明できるようになります。

第02回：PostgreSQLインストール比較：AlmaLinux 10でのAppStreamとPGDG

• 学習テーマ：AlmaLinux 10における2大導入手法の構造比較と選定基準。
• 現場の重要知見：AppStream版の単一バージョン制約と、将来のメジャーバージョンアップ（pg_upgrade）を見据えたPGDG版のパス分離構造（/usr/pgsql-XX/）。
• 主要実機確認コマンド：

  # PGDGリポジトリ登録とインストール
  dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  dnf install -y postgresql18-server
  
  # データベース初期化とサービス起動
  /usr/pgsql-18/bin/postgresql-18-setup initdb
  systemctl enable --now postgresql-18

• 習得成果：dnfコマンドによるリポジトリ追加から、systemdサービス管理（postgresql-XX.service）、initdbオプション設計までを自力で完結できるようになります。

第03回：postgresql.conf基本設定とpg_hba.confアクセス制御の設計

• 学習テーマ：外部接続を許可する基本通信設定と、ホストベース認証（pg_hba.conf）によるセキュリティ強化。
• 現場の重要知見：listen_addressesのデフォルト設定（localhost）による接続拒否、pg_hba.confの評価順序（ファーストマッチ方式）による認証設定ミス、SCRAM-SHA-256パスワード暗号化の適用。
• 主要実機確認コマンド：

  # 設定変更後のリロード実行と反映確認
  psql -c "SELECT pg_reload_conf();"
  psql -c "SELECT name, setting, context FROM pg_settings WHERE name IN ('listen_addresses', 'password_encryption');"
  
  # 接続元IP別の認証ルール確認（pg_hba.conf）
  grep -vE '^#|^$' /var/lib/pgsql/18/data/pg_hba.conf

• 習得成果：設定変更時のreload（pg_ctl reload）とrestart（サービス再起動）の適切な使い分け、およびpg_settingsシステムビューを活用した動的反映確認が可能になります。

第04回：PostgreSQLのロールと権限管理：最小権限で安全に運用する設計

• 学習テーマ：ユーザーとグループを包括するロール概念、データベース・スキーマ・テーブルの3階層アクセス権限モデル。
• 現場の重要知見：スーパーユーザー（postgres）の常時利用がもたらす重大リスク、スキーマのUSAGE権限とテーブルのSELECT権限の混同、テーブル追加時に権限付与漏れを防ぐALTER DEFAULT PRIVILEGESの必須性。
• 主要実機確認コマンド：

  # アプリ用ロールと参照専用ロールの作成
  CREATE ROLE app_user WITH LOGIN PASSWORD 'AppUserSecurePass2026';
  CREATE ROLE readonly_user WITH LOGIN PASSWORD 'ReadOnlyPass2026';
  
  # 3階層権限の付与と将来テーブルへのデフォルト権限定義
  GRANT USAGE ON SCHEMA app_schema TO app_user, readonly_user;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_user;
  GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO readonly_user;
  ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
      GRANT SELECT ON TABLES TO readonly_user;

• 習得成果：アプリケーション用（Read/Write）および監視・分析用（Read-Only）のロールを最小権限の原則に基づいて分離構築し、\dp メタコマンドの権限フラグを正確に解読できるようになります。

第05回：pg_dumpとpg_restore実務入門：カスタム形式と障害復旧

• 学習テーマ：論理バックアップツールpg_dumpの運用プラクティスとpg_restoreによる柔軟なデータ復元。
• 現場の重要知見：プレーンテキストSQL形式（-Fp）の復旧柔軟性の限界と、カスタム形式（-Fc）が実務標準とされる理由（圧縮、並列復元、特定オブジェクトのみの抽出リストア）。
• 主要実機確認コマンド：

  # カスタム形式による論理バックアップ取得
  pg_dump -Fc -U postgres -d production_db -f /backup/prod_$(date +%Y%m%d).dump
  
  # 特定テーブル（orders）のみを対象としたピンポイント復旧（--cleanで既存テーブル再作成）
  pg_restore -U postgres -d production_db -t orders --clean --if-exists /backup/prod_20260909.dump

• 習得成果：-t オプションを用いた特定テーブルのピンポイント復旧、--clean --if-exists オプションを活用した安全なテーブル再作成、およびグローバルオブジェクト（ロール・表領域）を保護するpg_dumpallの併用手順を確立できます。

第06回：PostgreSQLのVACUUMとANALYZE：肥大化防止と性能維持

• 学習テーマ：MVCC（多版型同時実行制御）の内部メカニズム、不要行（Dead Tuple）の発生原因、テーブル統計情報の収集。
• 現場の重要知見：VACUUM FULLが本番環境で原則厳禁とされる理由（排他ロックによる全クエリ遮断とディスク容量2倍消費）、autovacuumの自動起動閾値の計算式、ロングトランザクションによるVACUUM停止リスク。
• 主要実機確認コマンド：

  # 不要タプル（Dead Tuple）蓄積状況の監視
  SELECT relname, n_live_tup, n_dead_tup, 
         round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
         last_vacuum, last_autovacuum
  FROM pg_stat_user_tables
  ORDER BY n_dead_tup DESC;
  
  # 特定テーブルに対するautovacuum個別チューニング
  ALTER TABLE high_update_table SET (
      autovacuum_vacuum_scale_factor = 0.05,
      autovacuum_vacuum_threshold = 100
  );

• 習得成果：pg_stat_user_tablesビューを用いた不要タプル率の監視、autovacuumパラメータのテーブル個別チューニング、および統計情報の更新による実行計画劣化の予防を実践できます。

第07回：PostgreSQLのマイナーバージョンアップ実務：更新と切り戻し

• 学習テーマ：同一メジャーバージョン内でのバグ修正・セキュリティパッチ適用（例：18.4から18.6への更新）。
• 現場の重要知見：データディレクトリの再構築が不要でバイナリ更新のみで済む理由（ディスク上データ互換性の維持）、サービス計画停止手順、パッケージ更新失敗時のダウングレード切り戻し手順の策定。
• 主要実機確認コマンド：

  # 更新対象パッケージの事前確認とサービス計画停止
  dnf check-update postgresql18*
  systemctl stop postgresql-18
  
  # パッケージ更新の実行と起動確認
  dnf update -y postgresql18-server postgresql18
  systemctl start postgresql-18
  psql -c "SELECT version();"
  
  # 万一の障害時のダウングレード切り戻し手順
  systemctl stop postgresql-18
  dnf downgrade -y postgresql18-server-18.4 postgresql18-18.4
  systemctl start postgresql-18

• 習得成果：dnf check-updateによる更新対象確認、事前論理バックアップ取得、dnf downgradeによる迅速なフェイルバックを実機で実証できるようになります。

第08回：PostgreSQLのメジャー更新手順：pg_upgrade実務攻略

• 学習テーマ：メジャーバージョンアップ（例：PostgreSQL 17から18への移行）における内部データフォーマット変更とpg_upgradeの活用。
• 現場の重要知見：論理ダンプ・リストア方式による膨大なダウンタイム発生問題、ハードリンク（–link）方式によるファイル再配置なしの瞬時アップグレード、同一ファイルシステム制約と旧クラスタ破壊リスク。
• 主要実機確認コマンド：

  # 事前整合性チェック（非破壊ドライラン）
  /usr/pgsql-18/bin/pg_upgrade \
    -b /usr/pgsql-17/bin -B /usr/pgsql-18/bin \
    -d /var/lib/pgsql/17/data -D /var/lib/pgsql/18/data \
    --link --check
  
  # 実アップグレードの実行（数秒で完了）
  /usr/pgsql-18/bin/pg_upgrade \
    -b /usr/pgsql-17/bin -B /usr/pgsql-18/bin \
    -d /var/lib/pgsql/17/data -D /var/lib/pgsql/18/data \
    --link
  
  # 新クラスタ起動とオプティマイザ統計再収集
  systemctl start postgresql-18
  /usr/pgsql-18/bin/vacuumdb -U postgres --all --analyze-in-stages

• 習得成果：事前整合性チェック（–check）、pg_upgrade_internal.logの診断、更新後の新クラスタ統計収集（vacuumdb –analyze-in-stages）、およびクリーンアップスクリプト（delete_old_cluster.sh）の安全な実行手順を修得できます。

第09回：PostgreSQLの冗長化入門：レプリケーション構築手順

• 学習テーマ：ストリーミングレプリケーションによるPrimaryノード（alma-db01）からStandbyノード（alma-db02）への物理WALリアルタイム転送。
• 現場の重要知見：論理レプリケーションとのアーキテクチャ上の差異、pg_basebackup -Rによるスタンバイ初期化、レプリケーションスロットによる未転送WAL自動保持とディスク溢れリスクのトレードオフ。
• 主要実機確認コマンド：

  # Primary側でレプリケーション用ロールとスロットを作成
  psql -c "CREATE ROLE repuser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'rep_secret_pass';"
  psql -c "SELECT pg_create_physical_replication_slot('standby_slot_01');"
  
  # Standby側でベースバックアップ取得と同期接続自動生成
  pg_basebackup -h 192.168.2.132 -U repuser -p 5432 \
    -D /var/lib/pgsql/18/data -Fp -Xs -c fast -P -R -S standby_slot_01
  
  # Primary側でのレプリケーション状態と遅延バイト数の監視
  SELECT client_addr, state, sync_state,
         pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag_bytes,
         pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
  FROM pg_stat_replication;

• 習得成果：Primary側pg_stat_replicationビューによる転送遅延バイト数の監視、Standby側リードレプリカ（Read-Onlyクエリ参照）の運用、およびスタンバイ昇格（pg_ctl promote）の手動フェイルオーバー手順を理解できます。

重点監視メトリクス一覧

• アクティブ接続数（pg_stat_activity）
  SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
  閾値目安：max_connections の80%を超えた場合に警告（Alert）を発報。接続のスタックや接続リークの有無を即座に特定します。
• 長時間トランザクション（Long-Running Queries）
  SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' AND now() - xact_start > interval '5 minutes';
  閾値目安：5分以上継続しているトランザクションを検知。autovacuumの進行阻害やテーブルロック待ちの発生を未然に防止します。
• 不要タプル率（Dead Tuple Ratio）
  SELECT relname, n_dead_tup * 100 / nullif(n_live_tup + n_dead_tup, 0) AS ratio FROM pg_stat_user_tables;
  閾値目安：特定テーブルでDead Tuple率が20%を超えて継続上昇している場合、autovacuumパラメータの個別見直しを行います。
• レプリケーション遅延バイト数（pg_stat_replication）
  SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;
  閾値目安：遅延量が100MB（または時間換算で30秒）を超過した場合に監視通知。ネットワーク帯域逼迫やスタンバイノードの過負荷を調査します。
• WALディレクトリ（pg_wal）のディスク使用率
  レプリケーションスロットの停止やアーカイブログ転送詰まりにより、急激にディスクが枯渇する事故を検知するため、ファイルシステム監視で80%警告、90%緊急を設定します。

連載第01回はこちら

第01回：PostgreSQLアーキテクチャ入門：プロセス構成とメモリの仕組み

クライアント接続とバックエンドプロセスの関係、共有メモリ（shared_buffers / WAL）と個別メモリ（work_mem / maintenance_work_mem）の境界線を図解と実機ログで論理的に解説します。