背景とアーキテクチャ
半年前、当社の3.8 TBにおよぶPostgreSQLプライマリデータベースは、重いレポーティングクエリの負荷に苦しめられるようになりました。BIチームが必要としていたのは、orders、order_items、payments、inventoryという中核となる4つのテーブルからの継続的かつ低レイテンシな更新データだけでした。その一方で、スキーマ内の残る170テーブルでは、セッションストアや一時的な監査ログによる激しい書き込みアクティビティが発生していました。
当時、私たちは標準的な物理ストリーミングレプリケーションに依存していました。物理レプリケーションは、ストレージブロックレベルでクラスタをバイト単位で完全にミラーリングします。毎秒12,000件の書き込みというピーク負荷のもとで、プライマリは毎日400 GBものWrite-Ahead Log（WAL）を生成し続けていました。分析用レプリカは、分析クエリの実行ではなく、WALブロックのリプレイにNVMe I/Oのほぼすべてを浪費していました。わずか65 GBほどのビジネスデータをクエリするためだけに、4 TBもの高価なディスクを無駄に消費していたのです。
ロジカルレプリケーションは、データの移動を基盤となるディスクブロックから切り離すことでこの問題を解決します。pgoutputと呼ばれるエンジンコンポーネントが、WALエントリを行レベルの個別DMLタプル（INSERT、UPDATE、DELETE）へとデコードします。これにより、特定のテーブルのみをパブリッシュしたり、異なるPostgreSQLメジャーバージョン間で変更をストリーミングしたり、プライマリデータベースに影響を与えることなくレプリカ側で用途に応じた専用インデックスを構築したりすることが可能になります。
分析ノードをロジカルレプリケーションへ移行したことで、レプリカのディスク書き込みスループットは420 MB/sから25 MB/s未満へと大幅に削減されました。また、レプリカのストレージ容量も92%削減できました。しかし、ロジカルレプリケーションには特有の運用上の課題も存在します。監視されていないレプリカがオフラインになると、そのレプリケーションスロットがパブリッシャー側にWALファイルを溜め込み続け、最終的にディスク容量を100%枯渇させてしまうのです。
前提条件と環境構築
ロジカルレプリケーションは、バージョン10以降で利用可能なPostgreSQLのネイティブ機能を使用するため、外部デーモンやサードパーティ製拡張機能は一切不要です。この手順では、両ノードともにUbuntu 22.04 LTS上でPostgreSQL 16を実行します。パブリッシャーは本番データベースを192.168.1.10でホストし、サブスクライバーは192.168.1.20で稼働しています。
まず、両方のホストでPostgreSQLが起動していることを確認します。
# パブリッシャー（192.168.1.10）とサブスクライバー（192.168.1.20）の両方で実行
sudo apt update && sudo apt install -y postgresql-16 postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresqlパブリッシャー側で、最小限の権限を持つ専用ユーザーを作成します。このアカウントにはREPLICATION属性と、パブリッシュ対象スキーマへの読み取りアクセス権が必要です。
-- パブリッシャー（app_prodデータベース）で実行
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'SecretClusterPass2026!';
GRANT CONNECT ON DATABASE app_prod TO replicator;
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replicator;次に、パブリッシャーの/etc/postgresql/16/main/pg_hba.confでサブスクライバーのIPアドレスをホワイトリストに登録します。
# サブスクライバーホストからのロジカルレプリケーション接続を許可
host app_prod replicator 192.168.1.20/32 scram-sha-256ステップ別設定手順
レプリケーションを稼働させるには、パブリッシャーのWALデコーダーの設定、パブリケーションの宣言、そしてサブスクリプションワーカーの初期化が必要です。
1. パブリッシャーエンジンの設定
パブリッシャーの/etc/postgresql/16/main/postgresql.confを開きます。WALレベルがロジカルデコーディングをサポートしていることを確認し、十分なレプリケーションワーカー数を設定します。
# パブリッシャーの /etc/postgresql/16/main/postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
listen_addresses = '*'wal_levelの変更にはサーバーの完全な再起動が必要となるため、以下を実行します。
sudo systemctl restart postgresql2. パブリケーションの作成
ロジカルレプリケーションはデータの変更をストリーミングしますが、データ定義言語（DDL）は転送されません。レプリケーションを開始する前に、双方でテーブル構造を手動で定義しておく必要があります。この例では、ordersとorder_itemsのみをレプリケーション対象とします。
-- パブリッシャー側: サンプルの業務スキーマを作成
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_status VARCHAR(32) NOT NULL,
total_amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE order_items (
item_id BIGSERIAL PRIMARY KEY,
order_id BIGINT REFERENCES orders(order_id),
sku VARCHAR(64) NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10,2) NOT NULL
);
-- 選択したテーブルのみをパブリッシュ
CREATE PUBLICATION analytics_pub FOR TABLE orders, order_items;3. サブスクライバーの設定と初期同期
サブスクライバーノード側で、postgresql.confにバックグラウンドワーカーのキャパシティを割り当てます。
# サブスクライバーの /etc/postgresql/16/main/postgresql.conf
max_logical_replication_workers = 8
max_sync_workers_per_subscription = 4sudo systemctl reload postgresqlを実行して設定変更を再読み込みします。不要な外部キーを省略したり、分析専用のインデックスを追加したりするなど、要件に合わせてサブスクライバー側に対応するテーブルを作成します。その後、サブスクリプションを開始します。
-- サブスクライバー側: 対応するスキーマを定義
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_status VARCHAR(32) NOT NULL,
total_amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE order_items (
item_id BIGSERIAL PRIMARY KEY,
order_id BIGINT REFERENCES orders(order_id),
sku VARCHAR(64) NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10,2) NOT NULL
);
-- サブスクリプションを作成し、スナップショット同期を開始
CREATE SUBSCRIPTION analytics_sub
CONNECTION 'host=192.168.1.10 port=5432 dbname=app_prod user=replicator password=SecretClusterPass2026!'
PUBLICATION analytics_pub
WITH (copy_data = true, create_slot = true);copy_data = trueを設定すると、PostgreSQLはリアルタイムのWAL変更を適用する前に、テーブルの初期スナップショットをコピーします。当社の4,500万行のordersテーブルでは、この初期転送に専用の10 Gbpsネットワークリンク経由で14分を要しました。
4. レプリケーションコンフリクトの解決
物理レプリカとは異なり、サブスクライバー側のデータベースは書き込みが可能です。しかし、この柔軟性はリスクをもたらします。ローカルスクリプトがプライマリキーの重複する行を挿入し、パブリッシャーから送られてくる書き込みと競合した場合、レプリケーションは即座に停止します。
この事象が発生すると、applyワーカーは5秒ごとにリトライを繰り返します。このリトライループはPostgreSQLのシステムログを埋め尽くし、レプリケーション遅延（ラグ）を急激に増大させます。サブスクライバーの/var/log/postgresql/postgresql-16-main.logを確認し、失敗しているコミットLSN（例: 0/3142A90）を特定してください。
-- 選択肢A（PostgreSQL 15以降）: 競合しているトランザクションをクリーンにスキップ
ALTER SUBSCRIPTION analytics_sub DISABLE;
ALTER SUBSCRIPTION analytics_sub SKIP (lsn = '0/3142A90');
ALTER SUBSCRIPTION analytics_sub ENABLE;
-- 選択肢B: サブスクライバー側で対象レコードを手動解決
DELETE FROM orders WHERE order_id = 9042;
ALTER SUBSCRIPTION analytics_sub ENABLE;コンフリクトは発生する前に未然に防ぎましょう。レポート参照ユーザーにはSELECT権限のみを付与し、書き込み権限はレプリケーションワーカーのロールのみに限定することで、サブスクライバー側のユーザー権限を厳格に制限してください。
監視とスロット管理
放置されたレプリケーションスロットは、重大な障害を引き起こす原因になります。パブリッシャーはスロットのrestart_lsn以降のすべてのWALファイルを保持することでデータ配信を保証します。そのため、サブスクライバーがクラッシュしてオフラインのまま放置されると、ディスク容量が完全に枯渇するまでWALファイルが無制限に蓄積され続けます。
1. レプリケーション遅延とスロットサイズの追跡
パブリッシャー側で以下の診断クエリを実行し、スロットのアクティビティと保持されているディスク容量を確認します。
-- スロットの状態と保持されているWAL容量を確認
SELECT
slot_name,
plugin,
active,
wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
-- リアルタイムのリプレイ遅延を監視
SELECT
client_addr,
application_name,
state,
sync_state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag
FROM pg_stat_replication;2. パブリッシャー側ディスクの保護
障害の発生したサブスクライバーがプライマリクラスタを道連れに停止させないようにするため、パブリッシャーのpostgresql.confでWAL保持量の上限を設定します。
# スロットごとのWAL保持量に64 GBのハードリミットを適用
max_slot_wal_keep_size = 65536MBレプリケーション遅延が64 GBを超えると、PostgreSQLはそのスロットを無効化し、wal_statusをlostに設定します。これにより、パブリッシャーは古いWALファイルを再利用（リサイクル）してプライマリのディスク容量を保護します。サブスクライバー側はゼロから再同期が必要になりますが、本番データベースの稼働を継続できます。
3. 不要になったスロットの削除
サブスクリプションを削除する際は、常にサブスクライバー側からDROP SUBSCRIPTION analytics_sub;を実行してください。もしクリーンアップを行わずにサブスクライバーサーバーが破棄または廃止された場合は、パブリッシャー側で残存しているスロットを直接削除します。
-- 放置されたスロットを削除してWAL保持領域を即座に解放
SELECT pg_drop_replication_slot('analytics_sub');ロジカルレプリケーションを利用することで、クラスタ全体のストリーミングに伴う重いインフラ負荷を負うことなく、きめ細やかなデータ分離が実現できます。レプリケーションスロットの監視を怠らず、WAL保持サイズに上限を設けておくことで、下流の分析パイプラインを長年にわたり安定して稼働させることができるでしょう。
