PostgreSQLの論理レプリケーション（PUBLICATION・SUBSCRIPTION）をLinuxで設定する手順｜テーブル単位の非同期複製と動作確認

この記事のポイント

・PUBLICATION（送信側）とSUBSCRIPTION（受信側）でテーブル単位に複製を制御できる
・postgresql.confのwal_levelを「logical」に変更しないとPUBLICATIONが作成できない
・pg_stat_replication / pg_stat_subscriptionで同期状態をリアルタイムに確認できる
・バージョン差のある2台でも動作するためメジャーバージョンアップのダウンタイム短縮にも活用できる

# パブリッシャーサーバーで実行
sudo -u postgres psql -c "SHOW wal_level;"

 wal_level
-----------
 replica
(1 row)

# RHEL 9系・Rocky Linux 9の例
sudo vi /var/lib/pgsql/16/data/postgresql.conf

# 変更前（コメントアウトされていることが多い）
#wal_level = replica

# 変更後
wal_level = logical

sudo -u postgres psql -c "SHOW max_wal_senders; SHOW max_replication_slots;"

# RHEL 9系・Rocky Linux 9
sudo systemctl restart postgresql-16

# Ubuntu 24.04の場合
sudo systemctl restart postgresql

sudo -u postgres psql -c "SHOW wal_level;"

 wal_level
-----------
 logical
(1 row)

sudo -u postgres psql

-- レプリケーション用ロールの作成
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'パスワード';

-- 対象データベースへのCONNECT権限を付与
GRANT CONNECT ON DATABASE mydb TO replicator;

-- 対象スキーマ内の全テーブルへSELECT権限を付与
\c mydb
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

sudo vi /var/lib/pgsql/16/data/pg_hba.conf

# レプリケーション接続（WAL送信用）
host    replication     replicator      192.168.1.20/32         scram-sha-256
# 通常DB接続（SUBSCRIPTION作成時のメタデータ取得用）
host    mydb            replicator      192.168.1.20/32         scram-sha-256

sudo -u postgres psql -c "SELECT pg_reload_conf();"

sudo -u postgres psql -d mydb

-- 特定テーブルのみPUBLICATIONに含める場合
CREATE PUBLICATION mypub FOR TABLE orders, products;

-- データベース内の全テーブルを含める場合
-- CREATE PUBLICATION mypub FOR ALL TABLES;

SELECT * FROM pg_publication;

  pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-----------+----------+--------------+-----------+-----------+-----------+-------------
 mypub     |       10 | f            | t         | t         | t         | t
(1 row)

# パブリッシャーサーバーで実行 — スキーマのみエクスポート（データは除外）
pg_dump -U postgres -d mydb --schema-only -t orders -t products > mydb_schema.sql

# サブスクライバーサーバーで実行
scp user@192.168.1.10:/path/to/mydb_schema.sql ./

# データベースの作成（存在しない場合）
sudo -u postgres createdb mydb

# スキーマのインポート
sudo -u postgres psql -d mydb < mydb_schema.sql

sudo -u postgres psql -d mydb

CREATE SUBSCRIPTION mysub
  CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replicator password=パスワード'
  PUBLICATION mypub;

sudo -u postgres psql -d mydb -c "SELECT pid, usename, application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;"

  pid  | usename   | application_name | client_addr  | state     | sent_lsn  | replay_lsn | sync_state
-------+-----------+------------------+--------------+-----------+-----------+------------+-----------
  3421 | replicator| mysub            | 192.168.1.20 | streaming | 0/1A00028 | 0/1A00028  | async
(1 row)

sudo -u postgres psql -d mydb -c "SELECT subid, subname, pid, received_lsn, last_msg_receipt_time, latest_end_lsn FROM pg_stat_subscription;"

 subid | subname | pid  | received_lsn | last_msg_receipt_time         | latest_end_lsn
-------+---------+------+--------------+-------------------------------+---------------
 16390 | mysub   | 5234 | 0/1A00028    | 2026-10-21 14:00:00.123456+09 | 0/1A00028
(1 row)

# パブリッシャーサーバーで実行
sudo -u postgres psql -d mydb -c "INSERT INTO orders (id, item, qty) VALUES (1, 'widget', 10);"

# サブスクライバーサーバーで確認
sudo -u postgres psql -d mydb -c "SELECT * FROM orders;"

 id | item   | qty
----+--------+-----
  1 | widget |  10
(1 row)

# パブリッシャーでPUBLICATIONにテーブルを追加
sudo -u postgres psql -d mydb -c "ALTER PUBLICATION mypub ADD TABLE customers;"

# サブスクライバーでSUBSCRIPTIONを更新
sudo -u postgres psql -d mydb -c "ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;"

# パブリッシャーでPUBLICATIONからテーブルを削除
sudo -u postgres psql -d mydb -c "ALTER PUBLICATION mypub DROP TABLE products;"

# SUBSCRIPTIONを一時停止
sudo -u postgres psql -d mydb -c "ALTER SUBSCRIPTION mysub DISABLE;"

# SUBSCRIPTIONを削除（レプリケーションスロットも自動解放される）
sudo -u postgres psql -d mydb -c "DROP SUBSCRIPTION mysub;"

# PUBLICATIONを削除（パブリッシャー側）
sudo -u postgres psql -d mydb -c "DROP PUBLICATION mypub;"

# パブリッシャーで実行 — スロット名はサブスクリプション名と同一
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('mysub');"

sudo -u postgres psql -c "
SELECT
  application_name,
  (sent_lsn - replay_lsn) AS lag_bytes,
  now() - pg_last_xact_replay_timestamp() AS lag_time
FROM pg_stat_replication;
"