postgresクラスターを構築してみた。(pgpool)

Last updated at 2026-08-05Posted at 2026-01-16

はじめに

以前、以下の記事で手順を出していましたが、表題について
汎用的に利用できると思い、切り出しました。

構成概要

構築ホスト

pg1 : PostgreSQL Primary + pgpool
pg2 : PostgreSQL Standby + pgpool
pg3 : PostgreSQL Standby + pgpool
pg-vip : PostgreSQL 仮想IP（pgpool制御）

AlmaLinux9を使用する。

HA 方針

コンポーネント	HA方式
PostgreSQL	Streaming Replication
Primary 切替	pgpool
postgresql接続先	VIP (pgpool)

前提 IP（例）

192.168.11.37 pg1
192.168.11.38 pg2
192.168.11.39 pg3
192.168.11.40 pg-vip

※適宜読み替えてください

1. PostgreSQL HA 構築

1.1. 全台共通（pg1 / pg2 / pg3）

1.1.1. 基本設定

sudo dnf -y update
sudo dnf -y install chrony vim wget
sudo systemctl enable --now chronyd

1.1.2. PostgreSQL PGDG リポジトリ

sudo dnf -y install dnf-plugins-core
sudo dnf config-manager --set-enabled crb
sudo dnf -qy module disable postgresql
sudo dnf -y install \
  https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

1.1.3. PostgreSQL + Pgpool インストール（全台）

sudo dnf -y install pgpool-II pgpool-II-pcp
sudo dnf -y install postgresql16-server

1.2 PostgreSQL：Streaming Replication 構築

1.2.1. 3台共通：初期化・起動

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

1.2.2. pg1: postgresql.conf

sudo -u postgres sed -i \
  -e "s/^#listen_addresses.*/listen_addresses = '*'/" \
  /var/lib/pgsql/16/data/postgresql.conf

sudo -u postgres tee -a /var/lib/pgsql/16/data/postgresql.conf >/dev/null <<'EOF'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
wal_log_hints = on
EOF

1.2.3. pg1: DBユーザー作成（pgpool / repl）

sudo -u postgres psql <<'SQL'
SET password_encryption = 'scram-sha-256';

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='pgpool') THEN
    CREATE ROLE pgpool LOGIN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='repl') THEN
    CREATE ROLE repl REPLICATION LOGIN;
  END IF;
END $$;

GRANT pg_monitor TO pgpool;
SQL

sudo -u postgres psql -c "\password pgpool"

sudo -u postgres psql -c "\password repl"

sudo -u postgres psql -c "\password postgres"

→それぞれのDBパスワードを定め、パスワードを控える

1.2.4. pg1: pg_hba.conf

sudo -u postgres tee -a /var/lib/pgsql/16/data/pg_hba.conf >/dev/null <<'EOF'
host    all             all             192.168.11.0/24         scram-sha-256
host    replication     all             192.168.11.0/24         scram-sha-256
EOF

sudo systemctl restart postgresql-16

1.3. pg2/pg3 を Standby にする（basebackup）

1.3.1. pg2 / pg3: データ作り直し

sudo systemctl stop postgresql-16
sudo mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.old.$(date +%F-%H%M%S)
sudo install -d -o postgres -g postgres -m 700 /var/lib/pgsql/16/data
sudo restorecon -RFv /var/lib/pgsql/16/data || true

1.3.2. pg2/pg3: pg_basebackup（pg1から）

sudo -u postgres /usr/pgsql-16/bin/pg_basebackup \
  -h pg1 -p 5432 -U repl \
  -D /var/lib/pgsql/16/data \
  -R -X stream -C -S "$(hostname)" \
  --write-recovery-conf

→replのPW入力が出ます

1.3.3. pg2/pg3: 起動

sudo systemctl start postgresql-16

1.3.4. pg1: 確認

sudo -u postgres psql -c "select client_addr,state,sync_state from pg_stat_replication;"

→sync_stateが全てasyncとなること。

1.4 Pgpool-II：3台 Watchdog + VIP 構築

1.4.1. 3台共通：pgpool_node_id

pg1

echo 0 | sudo tee /etc/pgpool-II/pgpool_node_id

pg2

echo 1 | sudo tee /etc/pgpool-II/pgpool_node_id

pg3

echo 2 | sudo tee /etc/pgpool-II/pgpool_node_id

1.4.2. pg1：pgpool.conf(仮置き)

sudo cp -p /etc/pgpool-II/pgpool.conf.sample /etc/pgpool-II/pgpool.conf
sudo chown root:root /etc/pgpool-II/pgpool.conf
sudo chmod 644 /etc/pgpool-II/pgpool.conf

sudo tee -a /etc/pgpool-II/pgpool.conf >/dev/null <<'EOF'

# ===== minimal settings for pg_enc / SR mode =====
listen_addresses = '*'
port = 9999
pcp_port = 9898

backend_clustering_mode = 'streaming_replication'

backend_hostname0 = 'pg1'
backend_port0 = 5432
backend_data_directory0 = '/var/lib/pgsql/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'pg2'
backend_port1 = 5432
backend_data_directory1 = '/var/lib/pgsql/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'pg3'
backend_port2 = 5432
backend_data_directory2 = '/var/lib/pgsql/16/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# pool_passwd を使う前提
health_check_user = 'pgpool'
health_check_password = ''
sr_check_user = 'pgpool'
sr_check_password = ''
EOF

1.4.3. 全台：pool_passwd

sudo -u postgres bash -lc '
set -e
umask 077
openssl rand -base64 32 | tr -d "\n" > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
ls -l ~/.pgpoolkey
wc -c ~/.pgpoolkey
'

sudo install -o postgres -g postgres -m 600 /dev/null /etc/pgpool-II/pool_passwd
sudo restorecon -v /etc/pgpool-II/pool_passwd 2>/dev/null || true
sudo -u postgres bash -lc 'pg_enc -m -k ~/.pgpoolkey -u pgpool -p'

→pgpoolのPW入力をすること。

sudo cat /etc/pgpool-II/pool_passwd

1.4.4. 3台共通：PCPユーザー（pgpool）

sudo chown -R postgres:postgres /etc/pgpool-II
sudo -u postgres touch /etc/pgpool-II/pcp.conf
sudo pg_md5 -u pgpool -p

→pgpoolパスワードをここで新しく設定し、帰ってきた値を控える

sudo -u postgres vi /etc/pgpool-II/pcp.conf

→以下の記載を入れる。

pgpool:<先ほど控えた値>

sudo chmod 600 /etc/pgpool-II/pcp.conf

1.4.5. 3台共通：sudoers（VIP操作）

sudo visudo -f /etc/sudoers.d/pgpool_vip

中身：

postgres ALL=NOPASSWD: /usr/sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
Defaults:postgres !requiretty

1.4.6. 3台共通：postgres SSH鍵（フェールオーバースクリプト用）

SSHフルメッシュ交換(postgresSSH鍵)

sudo -u postgres bash -lc 'test -f ~/.ssh/id_rsa_pgpool || ssh-keygen -t ed25519 -N "" -f ~/.ssh/id_rsa_pgpool'
sudo -u postgres cat /var/lib/pgsql/.ssh/id_rsa_pgpool.pub

それぞれの公開鍵を控える。

sudo -u postgres touch /var/lib/pgsql/.ssh/authorized_keys
sudo -u postgres chmod 600 /var/lib/pgsql/.ssh/authorized_keys
sudo -u postgres vi /var/lib/pgsql/.ssh/authorized_keys

控えた3台の公開鍵を書き込む。

sudo -u postgres ssh -i /var/lib/pgsql/.ssh/id_rsa_pgpool postgres@pg1 'hostname'
sudo -u postgres ssh -i /var/lib/pgsql/.ssh/id_rsa_pgpool postgres@pg2 'hostname'
sudo -u postgres ssh -i /var/lib/pgsql/.ssh/id_rsa_pgpool postgres@pg3 'hostname'

→yesを入力し、以下の回答が出てくること。(2回やったほうが結果はわかりやすい)

pg1
pg2
pg3

1.4.7. 3台共通：.pgpass（repl / postgres）

REPL_PASSWORDとPOSTGRES_PASSWORDとPGPOOL_PASSWORDは実値に置換する。

sudo -u postgres tee /var/lib/pgsql/.pgpass >/dev/null <<'EOF'
pg1:5432:replication:repl:REPL_PASSWORD
pg2:5432:replication:repl:REPL_PASSWORD
pg3:5432:replication:repl:REPL_PASSWORD
pg1:5432:postgres:postgres:POSTGRES_PASSWORD
pg2:5432:postgres:postgres:POSTGRES_PASSWORD
pg3:5432:postgres:postgres:POSTGRES_PASSWORD
pg1:5432:postgres:pgpool:PGPOOL_PASSWORD
pg2:5432:postgres:pgpool:PGPOOL_PASSWORD
pg3:5432:postgres:pgpool:PGPOOL_PASSWORD
EOF

sudo -u postgres chmod 600 /var/lib/pgsql/.pgpass

1.5 pgpool.conf/pool_hba.conf

1.5.1. pg1/pg2/pg3共通で設定

sudo tee /etc/pgpool-II/pgpool.conf >/dev/null <<'EOF'
########## 基本 ##########
listen_addresses = '*'
port = 9999
pcp_port = 9898

backend_clustering_mode = 'streaming_replication'

########## Backend ##########
backend_hostname0 = 'pg1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'pg2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'pg3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/16/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

########## health / sr check ##########
health_check_period = 5
health_check_timeout = 30
health_check_max_retries = 3
health_check_user = 'pgpool'
health_check_password = 'pgpoolpass'   # pgpool_passwdを使う
health_check_database = 'postgres'

sr_check_period = 5
sr_check_user = 'pgpool'
sr_check_password = 'pgpoolpass'       # pgpool_passwdを使う

########## フェイルオーバ / 追従 ##########
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %m %H'

########## watchdog + VIP ##########
use_watchdog = on
delegate_ip = '192.168.11.40'

# VIP scripts（ip/arping は実パスに統一）
if_up_cmd   = '/usr/bin/sudo /usr/sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /usr/sbin/ip addr del $_IP_$/24 dev eth0'
arping_cmd  = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'

# lifcheck: heartbeat（※定義がないと起動時に FATAL になります）
wd_lifecheck_method = 'heartbeat'
wd_interval = 10

heartbeat_hostname0 = 'pg1'
heartbeat_port0 = 9694

heartbeat_hostname1 = 'pg2'
heartbeat_port1 = 9694

heartbeat_hostname2 = 'pg3'
heartbeat_port2 = 9694

# watchdog peers
hostname0 = 'pg1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'pg2'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'pg3'
wd_port2 = 9000
pgpool_port2 = 9999

wd_escalation_command = '/etc/pgpool-II/escalation.sh'

########## pool_hba ##########
enable_pool_hba = on
pool_passwd = '/etc/pgpool-II/pool_passwd'
pool_key = '/var/lib/pgsql/.pgpoolkey'

########## ファイル出力先（Permission denied 回避のため必須） ##########
logdir = '/var/log/pgpool-II'
backend_status_file = '/var/log/pgpool-II/pgpool_status'
wd_ipc_socket_dir = '/var/run/pgpool'
pid_file_name = '/var/run/pgpool/pgpool.pid'

# ---- auto failback ----
auto_failback = on
EOF

sudo tee /etc/pgpool-II/pool_hba.conf <<EOF
# ローカル運用
local   all         all                             trust
host    all         all         127.0.0.1/32         scram-sha-256
host    all         all         ::1/128              scram-sha-256

host    all         all         192.168.11.0/24      scram-sha-256

EOF

1.6. サンプルスクリプト有効化（PG16向けに修正）

1.6.1. 3台共通：配置

failover.sh(フェイルオーバーシェル)

以下の動作を行います。

スタンバイ障害なら「レプリケーションスロットを落として終わり」
プライマリ障害なら「新しいメイン(=昇格対象)を promote して終わり」

sudo tee /etc/pgpool-II/failover.sh >/dev/null <<'EOF'
#!/bin/bash
# This script is run by failover_command.
set -o xtrace

FAILED_NODE_ID="$1"
FAILED_NODE_HOST="$2"
FAILED_NODE_PORT="$3"
FAILED_NODE_PGDATA="$4"
NEW_MAIN_NODE_ID="$5"
NEW_MAIN_NODE_HOST="$6"
OLD_MAIN_NODE_ID="$7"
OLD_PRIMARY_NODE_ID="$8"
NEW_MAIN_NODE_PORT="$9"
NEW_MAIN_NODE_PGDATA="${10}"
OLD_PRIMARY_NODE_HOST="${11}"
OLD_PRIMARY_NODE_PORT="${12}"

PGHOME=/usr/pgsql-16
REPL_SLOT_NAME=$(echo "${FAILED_NODE_HOST,,}" | tr -- -. _)
POSTGRESQL_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
SSH_OPTIONS="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE}"

echo failover.sh: start: failed_node_id=$FAILED_NODE_ID failed_host=$FAILED_NODE_HOST \
    old_primary_node_id=$OLD_PRIMARY_NODE_ID new_main_node_id=$NEW_MAIN_NODE_ID new_main_host=$NEW_MAIN_NODE_HOST

# If there's no main node anymore, skip failover.
if [ "$NEW_MAIN_NODE_ID" -lt 0 ]; then
    echo failover.sh: All nodes are down. Skipping failover.
    exit 0
fi

# Test passwordless SSH
ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} ls /tmp > /dev/null
if [ $? -ne 0 ]; then
    echo failover.sh: passwordless SSH to ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} failed.
    exit 1
fi

# If Standby node is down (not old primary), drop replication slot and exit.
if [ "$OLD_PRIMARY_NODE_ID" != "-1" -a "$FAILED_NODE_ID" != "$OLD_PRIMARY_NODE_ID" ]; then
    ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \
        -c "SELECT pg_drop_replication_slot('${REPL_SLOT_NAME}');"  >/dev/null 2>&1 || true
    echo failover.sh: end: standby node is down. Skipping failover.
    exit 0
fi

# Promote Standby node.
echo failover.sh: primary node is down, promote new_main_node_id=$NEW_MAIN_NODE_ID on ${NEW_MAIN_NODE_HOST}.
ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} \
  ${PGHOME}/bin/pg_ctl -D ${NEW_MAIN_NODE_PGDATA} -w promote

if [ $? -ne 0 ]; then
    echo ERROR: failover.sh: promote failed
    exit 1
fi

echo failover.sh: end: new_main_node_id=$NEW_MAIN_NODE_ID on ${NEW_MAIN_NODE_HOST} was successfully promoted to primary
exit 0
EOF

sudo chown postgres:postgres /etc/pgpool-II/failover.sh
sudo chmod 700 /etc/pgpool-II/failover.sh

escalation.sh(VIP取り扱いシェル)

自身がリーダーになった(VIP付与対象になった)際、他のホストのVIPの取り外しを実施します。

sudo tee /etc/pgpool-II/escalation.sh >/dev/null <<'EOF'
#!/bin/bash
# Bring down the virtual IP on other pgpool nodes before bringing it up on this node.
set -o xtrace

POSTGRESQL_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
SSH_OPTIONS="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE}"
SSH_TIMEOUT=5

PGPOOLS=(pg1 pg2 pg3)

VIP=192.168.11.40
DEVICE=eth0
CIDR_NETMASK=24
IP=/usr/sbin/ip

for pgpool in "${PGPOOLS[@]}"; do
  [ "$HOSTNAME" = "${pgpool}" ] && continue

  timeout ${SSH_TIMEOUT} ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@${pgpool} "
    ${IP} addr show dev ${DEVICE} | grep -F ${VIP} > /dev/null 2>&1
  "

  if [ $? -eq 0 ]; then
    timeout ${SSH_TIMEOUT} ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@${pgpool} "
      /usr/bin/sudo ${IP} addr del ${VIP}/${CIDR_NETMASK} dev ${DEVICE}
    " || echo "ERROR: escalation.sh: failed to release VIP on ${pgpool}."
  fi
done
exit 0
EOF

sudo chown postgres:postgres /etc/pgpool-II/escalation.sh
sudo chmod 700 /etc/pgpool-II/escalation.sh

follow_primary.sh(スタンバイ追従シェル)

スタンバイとするノードに対し、以下の操作を実施します。

postgresql停止
pg_rewind/basebackupによるスタンバイ化
postgresql開始
pgpoolへのアタッチ

sudo tee /etc/pgpool-II/follow_primary.sh >/dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail
set -o xtrace

NODE_ID="${1:?}"
NODE_HOST="${2:?}"
NEW_PRIMARY_NODE_ID="${3:?}"
NEW_PRIMARY_HOST="${4:?}"

PGDATA="/var/lib/pgsql/16/data"
PRIMARY_PORT="5432"
REPLUSER="repl"

PGHOME="/usr/pgsql-16"
PGBASEBACKUP="${PGHOME}/bin/pg_basebackup"
PGREWIND="${PGHOME}/bin/pg_rewind"
PSQL="${PGHOME}/bin/psql"

# pg_rewind 用（確実に行くなら postgres。必要に応じて変更）
REWIND_USER="postgres"
REWIND_DB="postgres"

POSTGRESQL_STARTUP_USER="postgres"

SSH_KEY_FILE="id_rsa_pgpool"
SSH_BASE_OPTS="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE} \
-o ConnectTimeout=5 -o ServerAliveInterval=5 -o ServerAliveCountMax=3 -o BatchMode=yes"

SSH_CMD_TIMEOUT="60"
BASEBACKUP_TIMEOUT="1800"
REWIND_TIMEOUT="600"
START_WAIT="60"

REPL_SLOT_NAME="$(echo "${NODE_HOST,,}" | tr -- -. _)"
BACKUP_TS="$(date +%F_%H%M%S)"
BACKUP_DIR="${PGDATA}.bak_${BACKUP_TS}"

log(){ echo "follow_primary.sh: $*"; }

run_ssh() {
  local host="$1"; shift
  timeout "${SSH_CMD_TIMEOUT}" ssh -T ${SSH_BASE_OPTS} "${POSTGRESQL_STARTUP_USER}@${host}" "$@"
}

run_ssh_long() {
  local host="$1"; shift
  timeout "${BASEBACKUP_TIMEOUT}" ssh -T ${SSH_BASE_OPTS} "${POSTGRESQL_STARTUP_USER}@${host}" "$@"
}

run_ssh_rewind() {
  local host="$1"; shift
  timeout "${REWIND_TIMEOUT}" ssh -T ${SSH_BASE_OPTS} "${POSTGRESQL_STARTUP_USER}@${host}" "$@"
}

die_remote() {
  local host="$1"; shift
  log "ERROR: $*"
  run_ssh "${host}" "sudo systemctl --no-pager -l status postgresql-16 || true; sudo journalctl -u postgresql-16 -n 80 --no-pager || true" || true
  exit 1
}

# --- PCP attach (best-effort) ---
PCP_HOST="127.0.0.1"
PCP_PORT="9898"
PCP_ATTACH_NODE="$(command -v pcp_attach_node || true)"
PCP_NODE_INFO="$(command -v pcp_node_info || true)"
PCP_TIMEOUT="3"

pcp_attach_best_effort() {
  local nid="$1"
  [ -n "$PCP_ATTACH_NODE" ] || { log "pcp_attach_node not found, skip"; return 0; }
  if [ -n "$PCP_NODE_INFO" ]; then
    timeout "$PCP_TIMEOUT" "$PCP_NODE_INFO" -h "$PCP_HOST" -p "$PCP_PORT" -U pgpool -n "$nid" >/dev/null 2>&1 || {
      log "PCP not reachable on ${PCP_HOST}:${PCP_PORT}, skip attach"
      return 0
    }
  fi
  log "try pcp_attach_node nid=${nid} (best-effort)"
  timeout "$PCP_TIMEOUT" "$PCP_ATTACH_NODE" -h "$PCP_HOST" -p "$PCP_PORT" -U pgpool -n "$nid" >/dev/null 2>&1 || true
}

# --- standby config helper (pg_basebackup -R 相当を自前で) ---
configure_as_standby() {
  local host="$1"
  log "configure standby.signal + primary_conninfo on ${host}"

  run_ssh "${host}" "
    set -euo pipefail
    sudo -u postgres bash -ceu '
      : \"\${PGDATA:=${PGDATA}}\"

      # standby.signal（PG16はこれでstandby）
      touch \"${PGDATA}/standby.signal\"
      chown postgres:postgres \"${PGDATA}/standby.signal\"

      # primary_conninfo / primary_slot_name を postgresql.auto.conf に入れる
      # 既存行がある場合は置換（無ければ追記）
      AUTO=\"${PGDATA}/postgresql.auto.conf\"
      touch \"\$AUTO\"
      chown postgres:postgres \"\$AUTO\"

      # primary_conninfo を整形（必要に応じて sslmode など追記）
      PC=\"host=${NEW_PRIMARY_HOST} port=${PRIMARY_PORT} user=${REPLUSER} application_name=${NODE_HOST}\"

      # 行置換 or 追記
      grep -q \"^primary_conninfo\" \"\$AUTO\" && sed -i \"s|^primary_conninfo.*|primary_conninfo = '\\''\${PC}'\\''|\" \"\$AUTO\" || echo \"primary_conninfo = '\\''\${PC}'\\''\" >> \"\$AUTO\"
      grep -q \"^primary_slot_name\" \"\$AUTO\" && sed -i \"s|^primary_slot_name.*|primary_slot_name = '\\''${REPL_SLOT_NAME}'\\''|\" \"\$AUTO\" || echo \"primary_slot_name = '\\''${REPL_SLOT_NAME}'\\''\" >> \"\$AUTO\"
    '
  " || die_remote "${host}" "failed to write standby settings"
}

# --- optional: create replication slot on new primary (best-effort) ---
create_slot_best_effort() {
  local host="$1"
  log "best-effort create slot on new primary: ${REPL_SLOT_NAME}"
  # ここは「新プライマリ側」に対して実行する（postgresで実行できる前提）
  run_ssh "${host}" "
    set -euo pipefail
    sudo -u postgres ${PSQL} -h '${NEW_PRIMARY_HOST}' -p '${PRIMARY_PORT}' -d '${REWIND_DB}' -Atc \"
      DO \\\$\\\$BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name='${REPL_SLOT_NAME}') THEN
          PERFORM pg_create_physical_replication_slot('${REPL_SLOT_NAME}');
        END IF;
      END\\\$\\\$;
    \" >/dev/null 2>&1 || true
  " || true
}

# --- try pg_rewind. return 0 if success, 1 if not ---
try_pg_rewind() {
  local host="$1"

  log "try pg_rewind on ${host} (if prerequisites ok)"
  # PGDATA が無い/空なら無理なので即失敗扱い
  run_ssh "${host}" "test -d '${PGDATA}' -a -f '${PGDATA}/PG_VERSION'" || {
    log "pg_rewind skipped: PGDATA missing or not initialized on ${host}"
    return 1
  }

  # pg_rewind 実行（失敗してもここでは die しない）
  set +e
  run_ssh_rewind "${host}" "
    set -euo pipefail
    sudo -u postgres '${PGREWIND}' -D '${PGDATA}' \
      --source-server=\"host=${NEW_PRIMARY_HOST} port=${PRIMARY_PORT} user=${REWIND_USER} dbname=${REWIND_DB}\" \
      --progress
  "
  local rc=$?
  set -e

  if [ $rc -eq 0 ]; then
    log "pg_rewind SUCCESS on ${host}"
    return 0
  fi

  log "pg_rewind FAILED on ${host} (rc=${rc}) -> fallback to basebackup"
  return 1
}

log "start node=${NODE_ID} host=${NODE_HOST} new_primary=${NEW_PRIMARY_NODE_ID}(${NEW_PRIMARY_HOST}:${PRIMARY_PORT})"

if [ "${NODE_ID}" = "${NEW_PRIMARY_NODE_ID}" ]; then
  log "this node is new primary -> skip"
  exit 0
fi

run_ssh "${NODE_HOST}" "hostname; id" || { log "ssh failed to ${NODE_HOST}"; exit 1; }
run_ssh "${NODE_HOST}" "sudo -n true" || die_remote "${NODE_HOST}" "sudo requires password (NOPASSWD missing)"

# 1) 停止
log "stop postgresql-16 on ${NODE_HOST}"
run_ssh "${NODE_HOST}" "
  set -euo pipefail
  sudo systemctl stop postgresql-16 || true

  if [ -f '${PGDATA}/postmaster.pid' ]; then
    PID=\$(head -n 1 '${PGDATA}/postmaster.pid' 2>/dev/null || true)
    if [ -n \"\${PID}\" ] && kill -0 \"\${PID}\" 2>/dev/null; then
      echo \"[remote] postgres still running pid=\${PID}, killing...\"
      sudo kill -TERM \"\${PID}\" || true
      sleep 1
      sudo kill -KILL \"\${PID}\" || true
    fi
  fi
  ss -lntp | grep ':5432' || true
" || die_remote "${NODE_HOST}" "failed to stop postgres"

# 2) まず pg_rewind を試す（成功なら basebackup スキップ）
if try_pg_rewind "${NODE_HOST}"; then
  # standby 設定（-R 相当）
  create_slot_best_effort "${NODE_HOST}"
  configure_as_standby "${NODE_HOST}"

  # 起動
  log "start postgresql-16 on ${NODE_HOST} (after pg_rewind)"
  run_ssh "${NODE_HOST}" "
    set -euo pipefail
    sudo systemctl start postgresql-16
  " || die_remote "${NODE_HOST}" "systemctl start failed after pg_rewind"

else
  # 3) rewind失敗時は従来通り：PGDATA退避＆空作成 → basebackup
  log "rotate PGDATA on ${NODE_HOST} (for basebackup)"
  run_ssh "${NODE_HOST}" "
    set -euo pipefail
    if [ -d '${PGDATA}' ]; then
      sudo mv '${PGDATA}' '${BACKUP_DIR}'
    fi
    sudo -u postgres mkdir -p '${PGDATA}'
    sudo chown -R postgres:postgres '${PGDATA}'
    sudo chmod 700 '${PGDATA}'
  " || die_remote "${NODE_HOST}" "failed to rotate PGDATA"

  log "pg_basebackup to ${NODE_HOST} from ${NEW_PRIMARY_HOST} slot=${REPL_SLOT_NAME}"
  run_ssh_long "${NODE_HOST}" "
    set -euo pipefail
    '${PGBASEBACKUP}' -h '${NEW_PRIMARY_HOST}' -p '${PRIMARY_PORT}' -U '${REPLUSER}' \
      -D '${PGDATA}' -R -X stream -C -S '${REPL_SLOT_NAME}' --no-password
  " || die_remote "${NODE_HOST}" "pg_basebackup failed (auth/pg_hba/network/slot?)"

  log "start postgresql-16 on ${NODE_HOST} (after basebackup)"
  run_ssh "${NODE_HOST}" "
    set -euo pipefail
    sudo systemctl start postgresql-16
  " || die_remote "${NODE_HOST}" "systemctl start failed after basebackup"
fi

# 4) 起動待ち＆standby確認
log "wait up to ${START_WAIT}s for postgres and check recovery on ${NODE_HOST}"
run_ssh "${NODE_HOST}" "
  set -euo pipefail
  for i in \$(seq 1 ${START_WAIT}); do
    if sudo systemctl is-active --quiet postgresql-16; then
      break
    fi
    sleep 1
  done
  sudo systemctl --no-pager -l status postgresql-16 | sed -n '1,25p' || true
  psql -U postgres -d postgres -tAc 'select pg_is_in_recovery();'
" | tail -n 1 | grep -qx 't' || die_remote "${NODE_HOST}" "postgres is not standby after recovery"

pcp_attach_best_effort "${NODE_ID}"

log "done node=${NODE_ID} host=${NODE_HOST}"
exit 0
EOF

sudo chmod 755 /etc/pgpool-II/follow_primary.sh
sudo chown postgres:postgres /etc/pgpool-II/follow_primary.sh
sudo restorecon -v /etc/pgpool-II/follow_primary.sh 2>/dev/null || true

postgresユーザーでのsudo操作許可(follow_primary.shで必要)

sudo tee /etc/sudoers.d/postgres_tmp_all >/dev/null <<'EOF'
Defaults:postgres !requiretty
postgres ALL=(ALL) NOPASSWD: ALL
EOF

sudo chmod 440 /etc/sudoers.d/postgres_tmp_all
sudo visudo -cf /etc/sudoers.d/postgres_tmp_all

postgres ユーザー用に pgpool-II の PCP コマンドのパスワードファイル作成(follow_primary.shで必要)

sudo -u postgres bash -lc 'umask 077; cat > ~/.pcppass <<EOF
127.0.0.1:9898:pgpool:pgpoolpass
EOF
chmod 600 ~/.pcppass
ls -l ~/.pcppass
'

1.7. pgpool 起動(全台)

1.7.1. pid/書き込み先置き場所作成

sudo install -d -m 755 -o postgres -g postgres /var/run/pgpool /var/log/pgpool-II
sudo restorecon -RFv /var/run/pgpool /var/log/pgpool-II 2>/dev/null || true

sudo tee /etc/tmpfiles.d/pgpool.conf >/dev/null <<'EOF'
d /var/run/pgpool 0755 postgres postgres -
EOF

sudo systemd-tmpfiles --create /etc/tmpfiles.d/pgpool.conf

1.7.2. 起動

sudo systemctl enable --now pgpool-II
sudo systemctl status pgpool-II

1.8. 確認（VIP / watchdog / pool_nodes）

1.8.1. VIPがどこにいるか（3台どれかで）

ip -br addr | grep 192.168.11.40 || true

→3台のどれかでVIPが出ればよい。

1.8.2. Pgpool経由で backend 状態（どこからでも）

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

→backendのステータスが見れること。

1.8.3. watchdog 状態

pcp_watchdog_info -h 127.0.0.1 -p 9898 -U pgpool

→pgpoolのパスワードを入れる。
3台のステータスが見れること。

1.9. アクセス制限更新(pg_hba.conf,pool_hba.conf)

pg1/pg2/pg3で実施

sudo tee /var/lib/pgsql/16/data/pg_hba.conf >/dev/null <<'EOF'
# ===== local =====
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# ===== Pgpool health/sr_check user (pgpool) =====
# pgpool は同居でも別ノード経由でも backend に接続し得るので、3台のIPを明示許可
host    all             pgpool           192.168.11.37/32       scram-sha-256
host    all             pgpool           192.168.11.38/32       scram-sha-256
host    all             pgpool           192.168.11.39/32       scram-sha-256

# ===== Streaming Replication (repl) =====
# standby が primary に replication 接続するため
host    replication     repl             192.168.11.37/32       scram-sha-256
host    replication     repl             192.168.11.38/32       scram-sha-256
host    replication     repl             192.168.11.39/32       scram-sha-256

# follow_primary動作のため
host    all     postgres   192.168.11.37/32   scram-sha-256
host    all     postgres   192.168.11.38/32   scram-sha-256
host    all     postgres   192.168.11.39/32   scram-sha-256

EOF

sudo tee /etc/pgpool-II/pool_hba.conf <<EOF
# ローカル運用
local   all         all                              scram-sha-256
host    all         all         127.0.0.1/32         scram-sha-256
host    all         all         ::1/128              scram-sha-256

# pg1/pg2/pg3からのアクセス許可
host    all         all         192.168.11.37/32      scram-sha-256
host    all         all         192.168.11.38/32      scram-sha-256
host    all         all         192.168.11.39/32      scram-sha-256

EOF

sudo systemctl reload postgresql-16
sudo systemctl reload pgpool-II

1.10. pgpool認証ファイルコピー

1.10.1.【pg1】/tmp に一時コピーを作る（root）

sudo cp /etc/pgpool-II/pool_passwd /tmp/pool_passwd
sudo cp /var/lib/pgsql/.pgpoolkey /tmp/.pgpoolkey

sudo chown postgres:postgres /tmp/pool_passwd /tmp/.pgpoolkey
sudo chmod 600 /tmp/pool_passwd /tmp/.pgpoolkey

1.10.2.【pg1】postgres SSH で配布

sudo -u postgres scp -i /var/lib/pgsql/.ssh/id_rsa_pgpool \
  /tmp/pool_passwd /tmp/.pgpoolkey \
  postgres@pg2:/tmp/

sudo -u postgres scp -i /var/lib/pgsql/.ssh/id_rsa_pgpool \
  /tmp/pool_passwd /tmp/.pgpoolkey \
  postgres@pg3:/tmp/

1.10.3.【pg2 / pg3】正規配置（root）

sudo mv /tmp/pool_passwd /etc/pgpool-II/pool_passwd
sudo chown postgres:postgres /etc/pgpool-II/pool_passwd
sudo chmod 600 /etc/pgpool-II/pool_passwd

sudo mv /tmp/.pgpoolkey /var/lib/pgsql/.pgpoolkey
sudo chown postgres:postgres /var/lib/pgsql/.pgpoolkey
sudo chmod 600 /var/lib/pgsql/.pgpoolkey

1.10.4. Pgpool 反映（全ノード）

sudo systemctl restart pgpool-II

1.10.5.【後始末】pg1 の /tmp を掃除

sudo rm -f /tmp/pool_passwd /tmp/.pgpoolkey

1.11 postgresql自動起動停止

pgpoolにてpostgresqlの制御を実施させるため、postgresqlの自動起動を停止する。

sudo systemctl disable postgresql-16

2. ユーザーDB構築

2.1. ユーザーDB作成

下記のDB名、DBユーザ名、パスワード、クライアントIPを環境に合わせ作成してください。

例：
DB名:hive_metastore
DBユーザー名:hive
パスワード:HivePasswordHere
クライアントIP:192.168.11.54,192.168.11.55

pg1で実施。

psql -h pg1 -U postgres -d postgres <<'SQL'

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'hive') THEN
    CREATE ROLE hive LOGIN PASSWORD 'HivePasswordHere';
  END IF;
END $$;

CREATE DATABASE hive_metastore OWNER postgres;

\connect hive_metastore

GRANT CONNECT, TEMP ON DATABASE hive_metastore TO hive;

ALTER SCHEMA public OWNER TO hive;
GRANT USAGE, CREATE ON SCHEMA public TO hive;

GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public TO hive;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO hive;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO hive;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES    TO hive;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO hive;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO hive;

SQL

2.2. HiveDBアクセス設定

pg1/pg2/pg3で実施

sudo tee -a /var/lib/pgsql/16/data/pg_hba.conf >/dev/null <<'EOF'
# ------------------------------------------------------------
# 3) Hive Metastore
# ------------------------------------------------------------
host    hive_metastore  hive        192.168.11.37/32            scram-sha-256
host    hive_metastore  hive        192.168.11.38/32            scram-sha-256
host    hive_metastore  hive        192.168.11.39/32            scram-sha-256

EOF

sudo tee -a /etc/pgpool-II/pool_hba.conf <<EOF
# ------------------------------------------------------------
# Hive Metastore -> Pgpool VIP
# ------------------------------------------------------------
host    hive_metastore  hive    192.168.11.54/32    scram-sha-256
host    hive_metastore  hive    192.168.11.55/32    scram-sha-256

EOF

sudo systemctl reload postgresql-16
sudo systemctl reload pgpool-II

2.3. HiveDBアクセス認証情報配布

【pg1】認証情報作成

sudo -u postgres bash -lc 'pg_enc -m -k ~/.pgpoolkey -u hive -p'
→HivePasswordHereを入れる

【pg1】/tmp に一時コピーを作る（root）

sudo cp /etc/pgpool-II/pool_passwd /tmp/pool_passwd
sudo cp /var/lib/pgsql/.pgpoolkey /tmp/.pgpoolkey

sudo chown postgres:postgres /tmp/pool_passwd /tmp/.pgpoolkey
sudo chmod 600 /tmp/pool_passwd /tmp/.pgpoolkey

【pg1】postgres SSH で配布

sudo -u postgres scp -i /var/lib/pgsql/.ssh/id_rsa_pgpool \
  /tmp/pool_passwd /tmp/.pgpoolkey \
  postgres@pg2:/tmp/

sudo -u postgres scp -i /var/lib/pgsql/.ssh/id_rsa_pgpool \
  /tmp/pool_passwd /tmp/.pgpoolkey \
  postgres@pg3:/tmp/

【pg2 / pg3】正規配置（root）

sudo mv /tmp/pool_passwd /etc/pgpool-II/pool_passwd
sudo chown postgres:postgres /etc/pgpool-II/pool_passwd
sudo chmod 600 /etc/pgpool-II/pool_passwd

sudo mv /tmp/.pgpoolkey /var/lib/pgsql/.pgpoolkey
sudo chown postgres:postgres /var/lib/pgsql/.pgpoolkey
sudo chmod 600 /var/lib/pgsql/.pgpoolkey

Pgpool 反映（全ノード）

sudo systemctl restart pgpool-II

【後始末】pg1 の /tmp を掃除

sudo rm -f /tmp/pool_passwd /tmp/.pgpoolkey

3. 動作確認(PostgreSQL)

3.1 準備

3.1.1 確認用端末

psqlコマンドを使用できるホストを用意する。

psqlコマンドを使用できるようにする。

sudo apt install postgresql-client-16

3.1.2 pg1/pg2/pg3設定

例として以下のIP(192.168.11.71)で登録する。

sudo tee -a /etc/pgpool-II/pool_hba.conf <<EOF
# ope1からのアクセス許可(オプション)
host    all         all         192.168.11.71/32    scram-sha-256 

EOF

sudo systemctl reload pgpool-II

3.1.3 動作確認

ope1にて以下のコマンドを入力して応答が返ることを確認する。

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

3.2. 事前確認（共通）

3.2.1. 現在状態（VIP経由で確認）

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果

pg1 primary
pg2 standby
pg3 standby
status/pg_status: up

3.3. フェールオーバー(postgresqlサービス停止)

3.3.1. 概要

目的：primary を pg1 → pg2 に自動的にフェールオーバーさせる。
障害注入方法：
pg1のpostgresqlサービスを停止する。

期待する結果：
自動的にpg2へDBプライマリが自動的に切り替わり、外からの接続も実施できること。
pg1/pg3について、自動的にpg2のstandbyとなること。
合わせて、pgpoolのattachも自動的に実施される。

3.3.2. primary(pg1) の PostgreSQL を停止（障害注入）

pg1

sudo systemctl stop postgresql-16
sudo systemctl is-active postgresql-16 || true

期待結果

inactive

数十秒待って再度2つめのコマンドを実施して、activeとなること。

3.3.3. フェールオーバー成立確認（VIP経由）

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果（数十秒〜で変化）

pg1 standby
pg2 primary
pg3 standby
status/pg_status: up

3.3.4. 新primary(pg2) 側で “本当にprimaryか” を確認

pg2

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

f（= primary）

3.3.5. レプリケーション確認

pg2

sudo -u postgres psql -c "select client_addr,state,sync_state,application_name from pg_stat_replication;"

期待結果

pg1/pg3 が並ぶ（2行）

3.4. フェールオーバー(primaryホスト停止)

3.4.1. 概要

目的：primary を pg2 → pg1 に自動的にフェールオーバーさせる。
障害注入方法：
pg2ホストを停止する。

期待する結果：
自動的にpg1へDBプライマリが自動的に切り替わり、外からの接続も実施できること。
pg3について、自動的にpg1のstandbyとなること。
合わせて、pgpoolのattachも自動的に実施される。
pg2復旧後、手動操作を行いstandbyとして復旧すること。

3.4.2. primary(pg2) のノードを停止（障害注入）

pg2

sudo shutdown -h now

3.4.3. フェールオーバー成立確認（VIP経由）

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果（数十秒〜で変化）

pg1 primary
pg2 standby,status/pg_status: down
pg3 standby
status/pg_status: up

3.4.4. 新primary(pg1) 側で “本当にprimaryか” を確認

pg1

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

f（= primary）

3.4.5. レプリケーション確認

pg1

sudo -u postgres psql -c "select client_addr,state,sync_state,application_name from pg_stat_replication;"

期待結果

pg3 が並ぶ（1行）

3.4.6. pg2ホスト立ち上げ

落とした元primaryホストを立ち上げる

3.4.7. pg2ホストの再加入

pg2

sudo -u postgres /etc/pgpool-II/follow_primary.sh 1 pg2 0 pg1

3.4.8. フェールオーバー成立確認（VIP経由）

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果（数十秒〜で変化）

pg1 primary
pg2 standby
pg3 standby
status/pg_status: up

3.4.9. pg2で “本当にstandbyか” を確認

pg2

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

t（= standby）

3.4.10. レプリケーション確認

pg1

sudo -u postgres psql -c "select client_addr,state,sync_state,application_name from pg_stat_replication;"

期待結果

pg2/pg3 が並ぶ（1行）

3.5. フェイルバック

3.5.1. 概要

目的：
primary を pg1 → pg2 に戻す

リスク：
pgpool稼働中にpg1を止めると、自動判定が走る可能性(pg2に確実に戻したい。)
pg2のスタンバイとしてpg1/pg3が立ち上がらなくなり、クラスタ状態が壊れる

対策：
フェイルバック作業中だけ pgpoolを全台止める
follow_primaryシェルを利用し、pg1/pg3のstanby化を確実に実施する。

3.5.2. pgpool を全台停止（pg1/pg2/pg3）

各ノードで：

sudo systemctl stop pgpool-II
sudo systemctl is-active pgpool-II || true

期待結果

3台とも pgpool-II inactive

3.5.3 primary-standby追いつき確認

pg1：

sudo -u postgres psql -Atc "select pg_current_wal_lsn();"

pg2/pg3：

sudo -u postgres psql -Atc "select pg_last_wal_replay_lsn();"

上記の結果が全台で一致すること。

3.5.4. 現primary(pg1) を停止

pg1

sudo systemctl stop postgresql-16
sudo systemctl is-active postgresql-16 || true

期待結果

pg2 postgresql-16 inactive

3.5.5. pg2 を primary に昇格

pg2

sudo -u postgres /usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data -w promote
sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

f（pg2 primary）

3.5.6. pg3 を pg2 の standby として作り直し

pg3：follow_primaryシェルで作り直す

sudo systemctl stop postgresql-16

sudo -u postgres /etc/pgpool-II/follow_primary.sh 2 pg3 1 pg2

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

t（pg3 standby）

3.5.7. pg1 を pg2 の standby として作り直す

pg1：follow_primaryシェルで作り直す

sudo -u postgres /etc/pgpool-II/follow_primary.sh 0 pg1 1 pg2

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

t（pg1 standby）

3.5.8. pg2でレプリケーション確認

pg2

sudo -u postgres psql -c "select client_addr,state,sync_state,application_name from pg_stat_replication;"

期待結果

pg1/pg3 が並ぶ（2行）

3.5.9. pgpool を全台起動

sudo systemctl start pgpool-II
sudo systemctl is-active pgpool-II

3.5.10. VIP経由で最終確認

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果

pg1 standby
pg2 primary
pg3 standby
status/pg_status: up

3.6. 全台落としたあとの復帰

3.6.1. 概要

目的：
全台落として復帰させる

作業概要：
pg1をプライマリとしてpostgresqlを立ち上げ、
その後standby(pg2/pg3)のDB同期、立ち上げを実施する。

3.6.2. ノードを全台停止（pg1/pg2/pg3）

各ノードで：

sudo shutdown -h now

3.6.3 pg1/pg2/pg3立ち上げ

正常に立ち上がり、SSHでログインできること。

3.6.4. pg1/pg2/pg3 のpgpool-IIを停止する

pg1/pg2/pg3

sudo systemctl stop pgpool-II
sudo systemctl is-active pgpool-II || true

期待結果

3台ともinactiveと出てくること。

3.6.5. pg1 のpostgresqlを立ち上げて、primaryとする

pg1

sudo systemctl start postgresql-16
sudo -u postgres /usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data -w promote
sudo -u postgres psql -c "select pg_is_in_recovery();"

"pg_ctl: cannot promote server; server is not in standby mode"と出る場合は無視してよい。

期待結果

f（pg1 primary）

3.6.6. pg2/pg3 を pg1 の standby として作り直し

pg2/pg3

sudo systemctl stop postgresql-16
sudo mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.old.$(date +%F-%H%M%S)
sudo install -d -o postgres -g postgres -m 700 /var/lib/pgsql/16/data
sudo restorecon -RFv /var/lib/pgsql/16/data || true

sudo -u postgres /usr/pgsql-16/bin/pg_basebackup \
  -h pg1 -p 5432 -U repl \
  -D /var/lib/pgsql/16/data \
  -R -X stream -S "$(hostname)" \
  --write-recovery-conf

sudo systemctl start postgresql-16

sudo -u postgres psql -c "select pg_is_in_recovery();"

期待結果

t（pg2/pg3 standby）

3.6.7. pg1でレプリケーション確認

pg1

sudo -u postgres psql -c "select client_addr,state,sync_state,application_name from pg_stat_replication;"

期待結果

pg2/pg3 が並ぶ（2行）

3.6.8. pgpool を全台起動

sudo systemctl start pgpool-II
sudo systemctl is-active pgpool-II

3.6.9. VIP経由で最終確認

ope1

psql -h 192.168.11.40 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"

期待結果

pg1 primary
pg2 standby
pg3 standby
status/pg_status: up

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

postgresクラスターを構築してみた。(pgpool)

はじめに

構成概要

構築ホスト

HA 方針

前提 IP（例）

1. PostgreSQL HA 構築

1.1. 全台共通（pg1 / pg2 / pg3）

1.1.1. 基本設定

1.1.2. PostgreSQL PGDG リポジトリ

1.1.3. PostgreSQL + Pgpool インストール（全台）

1.2 PostgreSQL：Streaming Replication 構築

1.2.1. 3台共通：初期化・起動

1.2.2. pg1: postgresql.conf

1.2.3. pg1: DBユーザー作成（pgpool / repl）

1.2.4. pg1: pg_hba.conf

1.3. pg2/pg3 を Standby にする（basebackup）

1.3.1. pg2 / pg3: データ作り直し

1.3.2. pg2/pg3: pg_basebackup（pg1から）

1.3.3. pg2/pg3: 起動

1.3.4. pg1: 確認

1.4 Pgpool-II：3台 Watchdog + VIP 構築

1.4.1. 3台共通：pgpool_node_id

1.4.2. pg1：pgpool.conf(仮置き)

1.4.3. 全台：pool_passwd

1.4.4. 3台共通：PCPユーザー（pgpool）

1.4.5. 3台共通：sudoers（VIP操作）

1.4.6. 3台共通：postgres SSH鍵（フェールオーバースクリプト用）

1.4.7. 3台共通：.pgpass（repl / postgres）

1.5 pgpool.conf/pool_hba.conf

1.5.1. pg1/pg2/pg3共通で設定

1.6. サンプルスクリプト有効化（PG16向けに修正）

1.6.1. 3台共通：配置

1.7. pgpool 起動(全台)

1.7.1. pid/書き込み先置き場所作成

1.7.2. 起動

1.8. 確認（VIP / watchdog / pool_nodes）

1.8.1. VIPがどこにいるか（3台どれかで）

1.8.2. Pgpool経由で backend 状態（どこからでも）

1.8.3. watchdog 状態

1.9. アクセス制限更新(pg_hba.conf,pool_hba.conf)

1.10. pgpool認証ファイルコピー

1.10.1.【pg1】/tmp に一時コピーを作る（root）

1.10.2.【pg1】postgres SSH で配布

1.10.3.【pg2 / pg3】正規配置（root）

1.10.4. Pgpool 反映（全ノード）

1.10.5.【後始末】pg1 の /tmp を掃除

1.11 postgresql自動起動停止

2. ユーザーDB構築

2.1. ユーザーDB作成

2.2. HiveDBアクセス設定

2.3. HiveDBアクセス認証情報配布

3. 動作確認(PostgreSQL)

3.1 準備

3.1.1 確認用端末

3.1.2 pg1/pg2/pg3設定

3.1.3 動作確認

3.2. 事前確認（共通）

3.2.1. 現在状態（VIP経由で確認）

3.3. フェールオーバー(postgresqlサービス停止)

3.3.1. 概要

3.3.2. primary(pg1) の PostgreSQL を停止（障害注入）

3.3.3. フェールオーバー成立確認（VIP経由）

3.3.4. 新primary(pg2) 側で “本当にprimaryか” を確認

3.3.5. レプリケーション確認

3.4. フェールオーバー(primaryホスト停止)

3.4.1. 概要

3.4.2. primary(pg2) の ノードを停止（障害注入）

3.4.3. フェールオーバー成立確認（VIP経由）

3.4.4. 新primary(pg1) 側で “本当にprimaryか” を確認

3.4.5. レプリケーション確認

3.4.6. pg2ホスト立ち上げ

3.4.7. pg2ホストの再加入

3.4.8. フェールオーバー成立確認（VIP経由）

3.4.9. pg2で “本当にstandbyか” を確認

3.4.10. レプリケーション確認

3.5. フェイルバック

3.5.1. 概要

3.5.2. pgpool を全台停止（pg1/pg2/pg3）

3.5.3 primary-standby追いつき確認

3.4.2. primary(pg2) のノードを停止（障害注入）