PostgreSQLのストリーミングレプリケーションを構築する手順｜pg_basebackupからスタンバイ昇格まで

この記事のポイント

・pg_basebackup -R でstandby.signalとprimary_conninfoが自動生成される
・プライマリ側はwal_level・max_wal_senders・listen_addresses・pg_hba.confの4か所を設定する
・pg_stat_replicationのstateが「streaming」になれば同期成立の合図
・sent_lsn・flush_lsn・replay_lsnの3列が一致していれば遅延なし、write_lag/replay_lagで秒単位確認も可能
・スタンバイ昇格はpg_ctl promoteまたはSELECT pg_promote()で実行できる

# 両サーバーで実行して同じメジャーバージョンであることを確認する
psql --version
psql (PostgreSQL) 16.4

# プライマリ側で実行
ss -tlnp | grep 5432
LISTEN 0      128          0.0.0.0:5432       0.0.0.0:*    users:(("postgres",pid=2341,fd=5))
LISTEN 0      128             [::]:5432          [::]:*    users:(("postgres",pid=2341,fd=5))

# プライマリ側でfirewalldを使っている場合
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.10.102/32" port protocol="tcp" port="5432" accept'
firewall-cmd --reload

# 設定確認
firewall-cmd --list-all | grep rich

# /var/lib/pgsql/16/data/postgresql.conf

# 外部からの接続を受け付ける（デフォルトはlocalhostのみ・スタンバイから接続するために必要）
listen_addresses = '*'

# WALの記録レベルをreplicaに上げる（デフォルトはreplica、念のため明示する）
wal_level = replica

# WAL送信プロセスの最大数（スタンバイ台数+余裕を持たせる）
max_wal_senders = 5

# スタンバイが遅延した際に溜め置くWALの最大サイズ（PostgreSQL 13以降・MB単位）
wal_keep_size = 256

# /var/lib/pgsql/16/data/pg_hba.conf の末尾に追加

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.10.102/32       scram-sha-256

# プライマリ側でpsqlに接続して実行する
sudo -u postgres psql

-- REPLICATION属性を持つロールを作成する
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password_here';

-- 作成確認
\du replicator
                             List of roles
 Role name  |                         Attributes
------------+-------------------------------------------------------------
 replicator | Replication

postgres=# \q

# pg_hba.confはreloadで反映できるが、postgresql.confはrestartが必要なパラメータもある
# wal_level・max_wal_senders・listen_addresses の変更はrestartが必須
sudo systemctl restart postgresql-16

# 起動確認
sudo systemctl status postgresql-16
* postgresql-16.service - PostgreSQL 16 Database Server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled)
     Active: active (running) since Tue 2026-08-26 09:30:12 JST; 3s ago

# スタンバイ側で実行する

# PostgreSQLが動いていれば先に停止する
sudo systemctl stop postgresql-16

# データディレクトリを空にする（既存データが消えるため慎重に）
sudo rm -rf /var/lib/pgsql/16/data/*

# ディレクトリが空になったことを確認
sudo ls -la /var/lib/pgsql/16/data/
total 0

# スタンバイ側で postgres ユーザーとして実行する
sudo -u postgres pg_basebackup \
  -h 192.168.10.101 \
  -U replicator \
  -D /var/lib/pgsql/16/data \
  -R \
  -Xs \
  -P \
  --checkpoint=fast

Password: （replicatorのパスワードを入力）
32456/32456 kB (100%), 1/1 tablespace

# standby.signal が作成されているか確認する
ls -la /var/lib/pgsql/16/data/standby.signal
-rw------- 1 postgres postgres 0 Aug 26 09:42 /var/lib/pgsql/16/data/standby.signal

# primary_conninfo が postgresql.auto.conf に書き込まれているか確認する
cat /var/lib/pgsql/16/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator host=192.168.10.101 port=5432 sslmode=prefer sslcompression=0'

# application_name を設定する場合（スタンバイ側で実行・ALTER SYSTEM で追記）
sudo -u postgres psql -c "ALTER SYSTEM SET primary_conninfo = 'user=replicator host=192.168.10.101 port=5432 application_name=standby1';"

# スタンバイの postgres ユーザーのホームディレクトリに .pgpass を作成する
echo "192.168.10.101:5432:replication:replicator:strong_password_here" \
  | sudo -u postgres tee /var/lib/pgsql/.pgpass

# パーミッションを600にする（600以外だと無視される）
sudo chmod 600 /var/lib/pgsql/.pgpass
sudo chown postgres:postgres /var/lib/pgsql/.pgpass

# スタンバイ側で実行する
sudo systemctl start postgresql-16
sudo systemctl status postgresql-16
* postgresql-16.service - PostgreSQL 16 Database Server
     Active: active (running) since Tue 2026-08-26 09:45:03 JST; 5s ago

LOG:  entering standby mode
LOG:  redo starts at 0/3000028
LOG:  consistent recovery state reached at 0/3000100
LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

# スタンバイ自身がリカバリ状態にあるか確認する
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

# プライマリ側で実行する（基本確認）
sudo -u postgres psql -c "SELECT pid, usename, application_name, client_addr, state, sync_state FROM pg_stat_replication;"
  pid  | usename    | application_name | client_addr    | state     | sync_state
-------+------------+------------------+----------------+-----------+------------
 14237 | replicator | walreceiver      | 192.168.10.102 | streaming | async
(1 row)

# プライマリ側でレプリケーション遅延をLSNで確認する
sudo -u postgres psql -c "
SELECT pid, usename, application_name, client_addr,
       state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;"
  pid  | usename    | application_name | client_addr    | state     | sync_state | sent_lsn  | write_lsn | flush_lsn | replay_lsn | replay_lag_bytes
-------+------------+------------------+----------------+-----------+------------+-----------+-----------+-----------+------------+------------------
 14237 | replicator | walreceiver      | 192.168.10.102 | streaming | async      | 0/5002B80 | 0/5002B80 | 0/5002B80 | 0/5002B80  |                0
(1 row)

# 秒単位の遅延を確認する（PostgreSQL 10以降）
sudo -u postgres psql -c "SELECT application_name, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 application_name | write_lag | flush_lag | replay_lag
------------------+-----------+-----------+------------
 walreceiver      | 00:00:00  | 00:00:00  | 00:00:00
(1 row)

# スタンバイ側でWAL受信プロセスの状態を確認する
sudo -u postgres psql -c "SELECT status, receive_start_lsn, received_lsn, last_msg_send_time, last_msg_receipt_time FROM pg_stat_wal_receiver;"
   status    | receive_start_lsn | received_lsn | last_msg_send_time             | last_msg_receipt_time
-------------+-------------------+--------------+--------------------------------+-------------------------------
 streaming   | 0/5000000         | 0/5002B80    | 2026-08-26 09:45:10.123456+09  | 2026-08-26 09:45:10.124789+09
(1 row)

# プライマリ側でテーブルを作成してデータを挿入する
sudo -u postgres psql -c "CREATE TABLE repl_test (id serial, val text);"
sudo -u postgres psql -c "INSERT INTO repl_test(val) VALUES ('hello from primary');"

# スタンバイ側で確認する（192.168.10.102 で実行）
sudo -u postgres psql -c "SELECT * FROM repl_test;"
 id |        val
----+--------------------
  1 | hello from primary
(1 row)

# テストテーブルを削除する（プライマリ側で実行）
sudo -u postgres psql -c "DROP TABLE repl_test;"

# スタンバイサーバー上で実行する
sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data
waiting for server to promote.... done
server promoted

# スタンバイに接続してSQL実行でも昇格できる
sudo -u postgres psql -c "SELECT pg_promote();"
 pg_promote
------------
 t
(1 row)

LOG:  received promote request
LOG:  redo in progress
LOG:  selected new timeline ID: 2
LOG:  database system is ready to accept connections

# 昇格後のスタンバイ（新プライマリ）で実行する
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

# 実際に書き込みができるか確認する
sudo -u postgres psql -c "CREATE TABLE failover_test (id int);"
CREATE TABLE

sudo -u postgres psql -c "DROP TABLE failover_test;"
DROP TABLE

# スタンバイ側でエラーを確認する
sudo journalctl -u postgresql-16 --no-pager | tail -20

FATAL:  could not connect to the primary server: connection to server at "192.168.10.101", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.10.102", user "replicator", SSL off

# スタンバイ側から replicator ロールでプライマリへの接続テストを行う
sudo -u postgres psql "host=192.168.10.101 port=5432 user=replicator dbname=replication"
Password for user replicator:
psql (16.4)
Type "help" for help.

replication=>

# スタンバイのログに以下が出る場合はWALセグメントの欠落
FATAL:  requested WAL segment 000000010000000000000005 has already been removed

# レプリケーションスロットの状態を確認する（プライマリ側）
sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;"

＜PR＞手元に置いて学びを深める1冊

PostgreSQL実践入門──アーキテクチャ、運用監視、性能改善

まず無料メルマガで学ぶのが第一歩、加えてストリーミングレプリケーションの仕組みから運用監視・性能改善まで体系的に深めたい方は手元に置きたい一冊。