前提条件
下記がインストールされていること
- keepalived
- docker
- docker-compose
ない場合
sudo apt install docker-ce docker-ce-cli docker-ce-rootless-extras docker-compose-plugin docker composeコマンドがdocker-composeとして利用できること。(docker-composeパッケージは古いためNG)sudo ln -s /usr/libexec/docker/cli-plugins/docker-compose /usr/local/bin/docker-composeディレクトリ構成
[172.31.0.2][root@tx100s3-01 18:32:41 ~]# tree /opt/postgres-ha/ -L 2
/opt/postgres-ha/
├── config
│ ├── haproxy.cfg
│ ├── pg_hba.conf
│ └── postgresql-primary.conf
├── docker-compose-haproxy.yml
├── docker-compose-postgres-primary.yml
├── docker-compose-postgres-standby.yml
├── env
├── postgres_data
│ ├── 18
│ └── data
├── scripts
│ ├── 06-setup-postgres-ha.sh
│ ├── check-role.sh
│ ├── demote-to-standby.sh
│ ├── init-db.sh
│ ├── init-standby.sh
│ └── promote-to-primary.sh
└── templates
├── check-role.sh.template
├── demote-to-standby.sh.template
├── docker-compose-haproxy.yml.template
├── docker-compose-postgres-primary.yml.template
├── docker-compose-postgres-standby.yml.template
├── haproxy.cfg.template
├── init-db.sh.template
├── init-standby.sh.template
└── promote-to-primary.sh.template
6 directories, 22 files4-1. PostgreSQL HAセットアップ
テンプレート作成
mkdir -p /opt/postgres-ha/{scripts,templates,config}
cd /opt/postgres-ha/templates下記templateファイルを作成する
- docker-compose-haproxy.yml.template
- hapxoryのdocker-compose.yml
- docker-compose-postgres-primary.yml.template
- postgresqlのprimary用docker-compose.yml
- docker-compose-postgres-standby.yml.template
- postgresqlのstandby用docker-compose.yml
- haproxy.cfg.template
- haproxy.cfg
- init-db.sh.template
- postgresql-primary初期起動時に叩くスクリプト
- init-standby.sh.template
- postgresql-standby起動時に叩くスクリプト
- promote-to-primary.sh.template
- standbyからprimary昇格スクリプト
demote-to-standby.sh.template- primaryからstandby降格スクリプト
- VRRP外れた直後はpostgresqlを停止することにしたので無し
- primaryからstandby降格スクリプト
- check-role.sh.template
- primary/standbyのrole確認スクリプト
4-2. 展開スクリプト作成と実行
先に準備したtemplatesの各ファイルをenvファイルから読み込み作成し、対向ホストへ同期させます。
cd /opt/postgres-ha/scripts
vi 06-setup-postgres-ha.sh4-3. keepalived.conf
- VRRP設定
- VIP遷移時のスクリプト
- /etc/keepalived/scripts/postgres-vip-handler.sh
- vrrp_instanceの中のnotify_master,notify_backupに記述する
- PostgreSQL/HAProxyヘルスチェック
- /etc/keepalived/scripts/check_postgres_haproxy.sh
- keepalivedや他の障害とは別でpsql/haproxyのチェックを行い障害があれば優先度を下げてファイルオーバーさせる
vrrp_instance VI_1 {
...
...
# 通知スクリプト
notify_master "/etc/keepalived/scripts/postgres-vip-handler.sh MASTER"
notify_backup "/etc/keepalived/scripts/postgres-vip-handler.sh BACKUP"
}
# PostgreSQL/HAProxyヘルスチェック
vrrp_script chk_postgres_haproxy {
script "/etc/keepalived/scripts/check_postgres_haproxy.sh"
interval 5
weight 30 # チェック失敗時の優先度減少量
fall 3
rise 2
timeout 5
user root
}4-3.1 check script
vi /etc/keepalived/scripts/check_postgres_haproxy.sh
chmod +x /etc/keepalived/scripts/check_postgres_haproxy.sh#!/bin/bash
# 1. ネットワークインターフェースの状態チェック
INTERFACE_CHECK=$(ip link show br3 2>/dev/null | grep -c "state UP")
if [ "$INTERFACE_CHECK" -eq 0 ]; then
exit 1
fi
# 2. HAProxyコンテナの状態チェック
HAPROXY_CHECK=$(docker ps --format "table {{.Names}}" 2>/dev/null | grep -c "postgres-haproxy\|haproxy")
if [ "$HAPROXY_CHECK" -eq 0 ]; then
# このホストでHAProxyが実行されていない
echo "HAProxy not running on this host"
exit 1
fi
# 3. PostgreSQLの状態チェック
PG_CHECK=$(docker exec postgres-$(hostname) pg_isready -U k3s 2>/dev/null | grep -c "accepting connections")
if [ "$PG_CHECK" -eq 0 ]; then
echo "Primary PostgreSQL not ready"
exit 1
fi
fi
# すべてのチェックを通過
exit 04-3.2 切り替えスクリプト
vi /etc/keepalived/scripts/postgres-vip-handler.sh
chmod +x /etc/keepalived/scripts/postgres-vip-handler.sh#!/bin/bash
STATE=$1
HOSTNAME=$(hostname)
PG_DIR="/opt/postgres-ha"
LOG_FILE="/var/log/postgres-vip-handler.log"
log() {
echo "$(date): $1" >> "$LOG_FILE"
}
case $STATE in
"MASTER")
log "Becoming MASTER - Promoting to primary"
# 1. このホストをプライマリに昇格
cd "$PG_DIR"
./scripts/promote-to-primary.sh
# 2. HAProxyを起動
docker-compose -p postgres-haproxy -f docker-compose-haproxy.yml up -d
# 3. もう一方のホストにスタンバイになるよう通知
log "Notifying other host to become standby"
if [ "$HOSTNAME" = "tx100s3-01" ]; then
OTHER_HOST="172.31.0.3"
else
OTHER_HOST="172.31.0.2"
fi
ssh "$OTHER_HOST" "cd /opt/postgres-ha && docker-compose -p postgres-primary -f docker-postgres-primary.yml down 2>/dev/null" || \
log "Failed to notify other host"
log "MASTER transition completed"
;;
"BACKUP"|"FAULT")
log "Becoming $STATE - Already standby or will become standby"
# HAProxyを停止
cd "$PG_DIR" && docker-compose -p postgres-haproxy -f docker-compose-haproxy.yml down 2>/dev/null || true
# スタンバイに降格
log "In FAULT state - ensuring standby role"
#cd "$PG_DIR"
# standbyからprimaryになったコンテナの場合もあるのでVIP落ちたらpostgresqlは一旦全停止させる
#./scripts/demote-to-standby.sh
cd "$PG_DIR" && docker-compose -p postgres-primary -f docker-compose-postgres-primary.yml down 2>/dev/null || true
cd "$PG_DIR" && docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml down 2>/dev/null || true
log "$STATE transition completed"
;;
esac 4-4 動作確認
初回起動
1号機
172.31.0.200がVirtualIP
[172.31.0.2][root@tx100s3-01 14:43:50 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 14:43:50 /opt/postgres-ha]# ip -4 a|grep 0.200
[172.31.0.2][root@tx100s3-01 14:43:52 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
[172.31.0.2][root@tx100s3-01 14:43:54 /opt/postgres-ha]# 2号機
root@tx100s3-02:/opt/postgres-ha# ip -4 a|grep 0.200
inet 172.31.0.200/32 scope global br3:0
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES2号機がVRRP持っているので2号機でprimary DB として初回起動させる
root@tx100s3-02:/opt/postgres-ha# docker-compose -p postgres-primary -f docker-compose-postgres-primary.yml up -d
[+] Running 1/1
✔ Container postgres-tx100s3-02 Started 3.0s
root@tx100s3-02:/opt/postgres-ha# docker exec -it postgres-$(hostname) /scripts/init-db.sh
=== PostgreSQL Database Initialization ===
Waiting for PostgreSQL to start...
PostgreSQL is ready
Creating replicator user...
CREATE ROLE
Creating k3s database and user...
Creating extensions...
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
Creating physical replication slots...
pg_create_physical_replication_slot
-------------------------------------
(standby_a_slot,0/1B9F2B8)
(1 row)
pg_create_physical_replication_slot
-------------------------------------
(standby_b_slot,0/1B9F2B8)
(1 row)
Granting database permissions...
GRANT
GRANT
GRANT
=== Database initialization completed ===
root@tx100s3-02:/opt/postgres-ha# /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-02
コンテナ名: postgres-tx100s3-02
コンテナ状態: 稼働中
役割: ✅ プライマリ
=== VIP状態 ===
VIP: ✅ このホストにあります
期待される役割: プライマリ
root@tx100s3-02:/opt/postgres-ha#
1号機でstandbyとして初回起動
[172.31.0.2][root@tx100s3-01 20:55:24 /opt/postgres-ha]# docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml up -d
[+] Running 1/1
✔ Container postgres-tx100s3-01 Started 2.7s
[172.31.0.2][root@tx100s3-01 20:55:29 /opt/postgres-ha]# docker exec -it postgres-$(hostname) /scripts/init-standby.sh
=== PostgreSQL Standby Initialization ===
Primary Host: 172.31.0.3
Standby Name: tx100s3-01
Replicator User: replicator
[2026-01-25 20:55:42] Starting standby initialization...
[2026-01-25 20:55:42] Checking connection to primary server (172.31.0.3)...
172.31.0.3:5432 - accepting connections
[2026-01-25 20:55:42] ✓ Primary server is accepting connections
[2026-01-25 20:55:42] Using existing data directory
[2026-01-25 20:55:42] Configuring as standby...
[2026-01-25 20:55:42] ✓ Configured as standby
[2026-01-25 20:55:42] Setting proper permissions...
[2026-01-25 20:55:42] ✓ Permissions set
[2026-01-25 20:55:42] Performing final checks...
[2026-01-25 20:55:42] ✓ PostgreSQL version: 18
[2026-01-25 20:55:42] ✓ Standby signal file present
[2026-01-25 20:55:42] === Standby initialization completed ===
[2026-01-25 20:55:42] Standby is ready to start
[172.31.0.2][root@tx100s3-01 20:55:42 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: ✅ プライマリ
=== VIP状態 ===
VIP: 📭 別ホストにあります
期待される役割: スタンバイ
初期化しただけではstandbyにならないので、再起動をかかける
[172.31.0.2][root@tx100s3-01 20:55:47 /opt/postgres-ha]# docker restart postgres-tx100s3-01
postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 20:55:54 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: 📭 スタンバイ
primary_connection | replication_lag
--------------------+-----------------
(0 rows)
=== VIP状態 ===
VIP: 📭 別ホストにあります
期待される役割: スタンバイ
[172.31.0.2][root@tx100s3-01 20:55:56 /opt/postgres-ha]# 接続確認
[172.31.0.2][root@tx100s3-01 21:02:39 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.2 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:03:07.357136+09 | 172.31.0.2 | t
(1 行)
[172.31.0.2][root@tx100s3-01 21:03:07 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:03:12.196903+09 | 172.31.0.3 | f
(1 行)
[172.31.0.2][root@tx100s3-01 21:03:12 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.200 -p 6432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:03:22.335002+09 | 127.0.0.1 | f
(1 行)
- pg_is_in_recovery : t
- standbyモード : true
- pg_is_in_recovery : f
- standbyモード : false
- つまりprimary(master)keepalivedで１号機をマスターに
２号機restart
root@tx100s3-02:/opt/postgres-ha# ip -4 a | grep 0.200
inet 172.31.0.200/32 scope global br3:0
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
781287286abb postgres:18.1-alpine3.23 "docker-entrypoint.s…" 7 minutes ago Up 7 minutes postgres-tx100s3-02
a6f9e6efd485 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 3 hours ago Up 3 hours postgres-haproxy
root@tx100s3-02:/opt/postgres-ha# systemctl restart keepalived
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
781287286abb postgres:18.1-alpine3.23 "docker-entrypoint.s…" 9 minutes ago Up 9 minutes postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# ip -4 a | grep 0.200
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES1号機 ip遷移 & docker 確認
[172.31.0.2][root@tx100s3-01 21:03:22 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:04:24 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 8 minutes ago Up 8 minutes (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:04:25 /opt/postgres-ha]# ip -4 a | grep 0.200
[172.31.0.2][root@tx100s3-01 21:04:36 /opt/postgres-ha]# ip -4 a | grep 0.200
inet 172.31.0.200/32 scope global br3:0
[172.31.0.2][root@tx100s3-01 21:04:51 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 9 minutes ago Up 2 seconds (health: starting) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:04:54 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
76a0aaea9fe7 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 46 seconds ago Up 43 seconds postgres-haproxy
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 10 minutes ago Up 51 seconds (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:05:43 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
76a0aaea9fe7 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 48 seconds ago Up 46 seconds postgres-haproxy
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 10 minutes ago Up 54 seconds (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:05:45 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
76a0aaea9fe7 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 56 seconds ago Up 54 seconds postgres-haproxy
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 10 minutes ago Up About a minute (healthy) postgres-tx100s3-01VIP経由でのDB接続確認
[172.31.0.2][root@tx100s3-01 21:07:24 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.200 -p 6432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:07:25.476176+09 | 127.0.0.1 | f
(1 行)
[172.31.0.2][root@tx100s3-01 21:07:25 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
psql: エラー: サーバに接続できませんでした: 接続を拒否されました
サーバはホスト"172.31.0.3"で稼動していますか?
また、ポート5432でTCP/IP接続を受け付けていますか?
[172.31.0.2][root@tx100s3-01 21:07:31 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.2 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:07:38.094484+09 | 172.31.0.2 | f
(1 行)
2号機復旧
root@tx100s3-02:/opt/postgres-ha# docker-compose -p postgres-primary -f docker-compose-postgres-primary.yml down
[+] Running 1/1
✔ Container postgres-tx100s3-02 Removed 0.3s
root@tx100s3-02:/opt/postgres-ha# \rm -r postgres_data/
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
root@tx100s3-02:/opt/postgres-ha# docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml up -d
[+] Running 1/1
✔ Container postgres-tx100s3-02 Started 2.6s
root@tx100s3-02:/opt/postgres-ha# docker exec -it postgres-$(hostname) /scripts/init-standby.sh
=== PostgreSQL Standby Initialization ===
Primary Host: 172.31.0.2
Standby Name: tx100s3-02
Replicator User: replicator
[2026-01-25 21:16:44] Starting standby initialization...
[2026-01-25 21:16:44] Checking connection to primary server (172.31.0.2)...
172.31.0.2:5432 - accepting connections
[2026-01-25 21:16:44] ✓ Primary server is accepting connections
[2026-01-25 21:16:44] Using existing data directory
[2026-01-25 21:16:44] Configuring as standby...
[2026-01-25 21:16:44] ✓ Configured as standby
[2026-01-25 21:16:44] Setting proper permissions...
[2026-01-25 21:16:44] ✓ Permissions set
[2026-01-25 21:16:44] Performing final checks...
[2026-01-25 21:16:44] ✓ PostgreSQL version: 18
[2026-01-25 21:16:44] ✓ Standby signal file present
[2026-01-25 21:16:44] === Standby initialization completed ===
[2026-01-25 21:16:44] Standby is ready to start
root@tx100s3-02:/opt/postgres-ha# docker restart postgres-$(hostname)
postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-02
コンテナ名: postgres-tx100s3-02
コンテナ状態: 稼働中
役割: 📭 スタンバイ
primary_connection | replication_lag
--------------------+-----------------
(0 rows)
=== VIP状態 ===
VIP: 📭 別ホストにあります
期待される役割: スタンバイ
root@tx100s3-02:/opt/postgres-ha#
２号機マスター(failover)確認
1号機ダウン
[172.31.0.2][root@tx100s3-01 21:20:03 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: ✅ プライマリ
=== VIP状態 ===
VIP: ✅ このホストにあります
期待される役割: プライマリ
[172.31.0.2][root@tx100s3-01 21:20:05 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
76a0aaea9fe7 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 15 minutes ago Up 15 minutes postgres-haproxy
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 24 minutes ago Up 15 minutes (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:20:08 /opt/postgres-ha]# ip -4 a | grep 0.200
inet 172.31.0.200/32 scope global br3:0
[172.31.0.2][root@tx100s3-01 21:20:13 /opt/postgres-ha]# systemctl restart keepalived
[172.31.0.2][root@tx100s3-01 21:20:20 /opt/postgres-ha]# ip -4 a | grep 0.200
[172.31.0.2][root@tx100s3-01 21:20:21 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
76a0aaea9fe7 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 15 minutes ago Up 15 minutes postgres-haproxy
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 24 minutes ago Up 15 minutes (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:20:23 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
216f8d1df6a3 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 25 minutes ago Up 15 minutes (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:20:27 /opt/postgres-ha]# haproxy落ちたのを確認
2号機確認
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 3 minutes ago Up 3 minutes (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha#
root@tx100s3-02:/opt/postgres-ha#
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f861dfedc4fd haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 22 seconds ago Up 20 seconds postgres-haproxy
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 4 minutes ago Up 27 seconds (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# ip -4 a | grep 0.200
inet 172.31.0.200/32 scope global br3:0
root@tx100s3-02:/opt/postgres-ha# DB接続確認
[172.31.0.2][root@tx100s3-01 18:27:25 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h $VIP -p 6432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 18:28:18.672326+09 | 127.0.0.1 | f
(1 行)
[172.31.0.2][root@tx100s3-01 18:28:18 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.2 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 18:28:25.828991+09 | 172.31.0.2 | f
(1 行)
[172.31.0.2][root@tx100s3-01 18:28:25 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 18:28:33.864688+09 | 172.31.0.3 | f
(1 行)1号機復旧
[172.31.0.2][root@tx100s3-01 21:24:44 /opt/postgres-ha]# docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml down --remove-orphans
[+] Running 1/1
✔ Container postgres-tx100s3-01 Removed 2.9s
(reverse-i-search)`up -d': docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml ^C -d
[172.31.0.2][root@tx100s3-01 21:26:41 /opt/postgres-ha]# \rm -r postgres_data/
[172.31.0.2][root@tx100s3-01 21:26:43 /opt/postgres-ha]# docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml up -d
[+] Running 1/1
✔ Container postgres-tx100s3-01 Started 3.5s
[172.31.0.2][root@tx100s3-01 21:26:51 /opt/postgres-ha]# docker exec -it postgres-$(hostname) /scripts/init-standby.sh
=== PostgreSQL Standby Initialization ===
Primary Host: 172.31.0.3
Standby Name: tx100s3-01
Replicator User: replicator
[2026-01-25 21:26:55] Starting standby initialization...
[2026-01-25 21:26:55] Checking connection to primary server (172.31.0.3)...
172.31.0.3:5432 - accepting connections
[2026-01-25 21:26:55] ✓ Primary server is accepting connections
[2026-01-25 21:26:55] Using existing data directory
[2026-01-25 21:26:55] Configuring as standby...
[2026-01-25 21:26:55] ✓ Configured as standby
[2026-01-25 21:26:55] Setting proper permissions...
[2026-01-25 21:26:55] ✓ Permissions set
[2026-01-25 21:26:55] Performing final checks...
[2026-01-25 21:26:55] ✓ PostgreSQL version: 18
[2026-01-25 21:26:55] ✓ Standby signal file present
[2026-01-25 21:26:55] === Standby initialization completed ===
[2026-01-25 21:26:55] Standby is ready to start
[172.31.0.2][root@tx100s3-01 21:26:55 /opt/postgres-ha]# docker restart postgres-`hostname`
postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:27:11 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: 📭 スタンバイ
primary_connection | replication_lag
--------------------+-----------------
(0 rows)
=== VIP状態 ===
VIP: 📭 別ホストにあります
期待される役割: スタンバイ
[172.31.0.2][root@tx100s3-01 21:27:13 /opt/postgres-ha]# 2号機落とす
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f861dfedc4fd haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 7 minutes ago Up 7 minutes postgres-haproxy
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 11 minutes ago Up 7 minutes (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha#
root@tx100s3-02:/opt/postgres-ha#
root@tx100s3-02:/opt/postgres-ha#
root@tx100s3-02:/opt/postgres-ha# systemctl restart keepalived
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 11 minutes ago Up 7 minutes (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 11 minutes ago Up 7 minutes (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 11 minutes ago Up 7 minutes (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
01d0f1a925de postgres:18.1-alpine3.23 "docker-entrypoint.s…" 12 minutes ago Up 8 minutes (healthy) postgres-tx100s3-02[172.31.0.2][root@tx100s3-01 21:27:13 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:27:54 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3b6e901b9682 postgres:18.1-alpine3.23 "docker-entrypoint.s…" About a minute ago Up 5 seconds (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:28:24 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: ✅ プライマリ
=== VIP状態 ===
VIP: ✅ このホストにあります
期待される役割: プライマリ
[172.31.0.2][root@tx100s3-01 21:28:34 /opt/postgres-ha]# ip -4 a | grep 0.200
inet 172.31.0.200/32 scope global br3:0
[172.31.0.2][root@tx100s3-01 21:28:43 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e16ca5b21750 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 22 seconds ago Up 17 seconds postgres-haproxy
3b6e901b9682 postgres:18.1-alpine3.23 "docker-entrypoint.s…" About a minute ago Up 26 seconds (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:28:45 /opt/postgres-ha]# 2号機復旧後１号機落とす
[172.31.0.2][root@tx100s3-01 21:37:16 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:38:31.968782+09 | 172.31.0.3 | t
(1 行)
[172.31.0.2][root@tx100s3-01 21:38:31 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bf242253be89 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 6 minutes ago Up About a minute (healthy) postgres-tx100s3-01
e16ca5b21750 haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 10 minutes ago Up 10 minutes postgres-haproxy
[172.31.0.2][root@tx100s3-01 21:38:58 /opt/postgres-ha]# systemctl restart keepalived
[172.31.0.2][root@tx100s3-01 21:39:03 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bf242253be89 postgres:18.1-alpine3.23 "docker-entrypoint.s…" 6 minutes ago Up 2 minutes (healthy) postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:39:06 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:39:09 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
[172.31.0.2][root@tx100s3-01 21:39:09 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:39:11 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:39:11 /opt/postgres-ha]# root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8a8600578b7d postgres:18.1-alpine3.23 "docker-entrypoint.s…" 33 seconds ago Up 7 seconds (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# 2026年 1月 25日 日曜日 21:39:04 JST: Becoming MASTER - Promoting to primary
2026年 1月 25日 日曜日 21:39:15 JST: Notifying other host to become standby
2026年 1月 25日 日曜日 21:39:15 JST: Failed to notify other host
2026年 1月 25日 日曜日 21:39:15 JST: MASTER transition completed
root@tx100s3-02:/opt/postgres-ha# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f6143ddc4a6c haproxy:3.3.1-alpine3.23 "docker-entrypoint.s…" 25 seconds ago Up 22 seconds postgres-haproxy
8a8600578b7d postgres:18.1-alpine3.23 "docker-entrypoint.s…" About a minute ago Up 29 seconds (healthy) postgres-tx100s3-02
root@tx100s3-02:/opt/postgres-ha# jobs
[1]+ 実行中 tail -f /var/log/postgres-vip-handler.log &
root@tx100s3-02:/opt/postgres-ha# fg
tail -f /var/log/postgres-vip-handler.log
^C
root@tx100s3-02:/opt/postgres-ha# /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-02
コンテナ名: postgres-tx100s3-02
コンテナ状態: 稼働中
役割: ✅ プライマリ
=== VIP状態 ===
VIP: ✅ このホストにあります
期待される役割: プライマリ
root@tx100s3-02:/opt/postgres-ha# [172.31.0.2][root@tx100s3-01 21:39:33 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:39:33 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:40:08.247524+09 | 172.31.0.3 | f
(1 行)
[172.31.0.2][root@tx100s3-01 21:40:08 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.200 -p 6432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:40:14.594255+09 | 127.0.0.1 | f
(1 行)
[172.31.0.2][root@tx100s3-01 21:40:14 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.2 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
psql: エラー: サーバに接続できませんでした: 接続を拒否されました
サーバはホスト"172.31.0.2"で稼動していますか?
また、ポート5432でTCP/IP接続を受け付けていますか?
[172.31.0.2][root@tx100s3-01 21:40:20 /opt/postgres-ha]# 1 復旧
[172.31.0.2][root@tx100s3-01 21:43:11 /opt/postgres-ha]# \rm -r postgres_data/
[172.31.0.2][root@tx100s3-01 21:43:17 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:43:17 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:43:18 /opt/postgres-ha]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
[172.31.0.2][root@tx100s3-01 21:43:19 /opt/postgres-ha]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
[172.31.0.2][root@tx100s3-01 21:43:24 /opt/postgres-ha]# lrdd^C
[172.31.0.2][root@tx100s3-01 21:44:39 /opt/postgres-ha]# less scripts/init-standby.sh
[172.31.0.2][root@tx100s3-01 21:45:06 /opt/postgres-ha]# docker ps^C
[172.31.0.2][root@tx100s3-01 21:45:08 /opt/postgres-ha]# docker-compose -p postgres-standby -f docker-compose-postgres-standby.yml up -d
[+] Running 1/1
✔ Container postgres-tx100s3-01 Started 2.9s
[172.31.0.2][root@tx100s3-01 21:45:15 /opt/postgres-ha]# docker exec -it postgres-$(hostname) /scripts/init-standby.sh
=== PostgreSQL Standby Initialization ===
Primary Host: 172.31.0.3
Standby Name: tx100s3-01
Replicator User: replicator
[2026-01-25 21:45:35] Starting standby initialization...
[2026-01-25 21:45:35] Checking connection to primary server (172.31.0.3)...
172.31.0.3:5432 - accepting connections
[2026-01-25 21:45:35] ✓ Primary server is accepting connections
[2026-01-25 21:45:35] Using existing data directory
[2026-01-25 21:45:35] Configuring as standby...
[2026-01-25 21:45:35] ✓ Configured as standby
[2026-01-25 21:45:35] Setting proper permissions...
[2026-01-25 21:45:35] ✓ Permissions set
[2026-01-25 21:45:35] Performing final checks...
[2026-01-25 21:45:35] ✓ PostgreSQL version: 18
[2026-01-25 21:45:35] ✓ Standby signal file present
[2026-01-25 21:45:35] === Standby initialization completed ===
[2026-01-25 21:45:35] Standby is ready to start
[172.31.0.2][root@tx100s3-01 21:45:35 /opt/postgres-ha]# docker restart postgres-`hostname`
postgres-tx100s3-01
[172.31.0.2][root@tx100s3-01 21:46:05 /opt/postgres-ha]# bash /opt/postgres-ha/scripts/check-role.sh
=== PostgreSQL役割確認 ===
ホスト名: tx100s3-01
コンテナ名: postgres-tx100s3-01
コンテナ状態: 稼働中
役割: 📭 スタンバイ
primary_connection | replication_lag
--------------------+-----------------
(0 rows)
=== VIP状態 ===
VIP: 📭 別ホストにあります
期待される役割: スタンバイ
[172.31.0.2][root@tx100s3-01 21:46:08 /opt/postgres-ha]# [172.31.0.2][root@tx100s3-01 21:46:42 /opt/postgres-ha]#
[172.31.0.2][root@tx100s3-01 21:46:42 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.2 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:46:44.718094+09 | 172.31.0.2 | t
(1 行)
[172.31.0.2][root@tx100s3-01 21:46:44 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.3 -p 5432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
-------------------------------+------------------+-------------------
2026-01-25 21:46:48.869184+09 | 172.31.0.3 | f
(1 行)
[172.31.0.2][root@tx100s3-01 21:46:48 /opt/postgres-ha]# PGPASSWORD=$PGPASSWD psql -h 172.31.0.200 -p 6432 -U k3s -d k3s -c 'SELECT current_timestamp, inet_server_addr(), pg_is_in_recovery();'
current_timestamp | inet_server_addr | pg_is_in_recovery
------------------------------+------------------+-------------------
2026-01-25 21:46:55.04023+09 | 127.0.0.1 | f
(1 行)
05 k3sクラスターの構築
つづく
ocarina
絵が描けるボタン恐怖症インフラエンジニアです。
