Zenn
🙆

PostgreSQLスキーマ単位ロジカルレプリケーションメモ

2022/11/08に公開
DB
環境構築
メモ
tech

PostgreSQLスキーマ単位ロジカルレプリケーションメモ

PostgreSQL15でスキーマ単位のロジカルレプリケーション環境が構築できたので備忘しておきます。

各レプリケーションの特徴

  • ストリーミングレプリケーション
    • データベースクラスタ単位で複製
    • 同メジャーバージョン,同OSアーキテクチャ環境が前提
    • 複製先は通常参照のみ可能
    • WAL生成のタイミングでレプリケーションされる
    • DDLも含め全てレプリケーションされる (WALにDDLも含まれる)
    • スタンバイ側をマスターに昇格可能
  • ロジカルレプリケーション
    • データベース単位,スキーマ単位,テーブル単位を選択可能
    • バージョン違い,別OSアーキテクチャ環境でも構築可能
    • 複製先は書込と参照が可能
    • COMMIT完了のタイミングでレプリケーションされる
    • INSERT,UPDATE,DELETE,TRUNCATE(,MERGE)のみレプリケーションされる
    • テーブル,パーティションテーブルのみレプリケーション可能
    • ラージオブジェクトはレプリケーション不可能

構築時の作業

  • 複製元データベース
    1. postgresql.confの以下パラメータ有効化
      • wal_level = logical
      • max_wal_senders = [num]
    2. 権限のあるユーザでpublicationを作成
      • create publication pub1 for tables in schema schm1;
  • 複製先データベース
    1. 複製元と同等のテーブルを作成
      • pg_dump -h [ip] -p [port] --username=[user] --schema-only --table [table] [db] で定義取得可能
    2. 権限のあるユーザでsubscriptionを作成
      • create subscription sub1 connection 'host=[ip] port=[port] dbname=[db] password=[pass]' publication pub1;

運用中の作業

  • 複製元データベースのスキーマにテーブルを追加した時の作業
    • 複製先データベース
      1. 複製元と同等のテーブルを作成
      2. 権限のあるユーザでsubscriptionを更新
        • alter subscription sub1 refresh publication;
  • 複製状況を確認する作業
    • 複製先データベース
      • select * from pg_subscription_rel;
      • 複製されない原因が不明な時はログを確認すると大抵原因がわかる
        • /var/log/postgresql/postgresql-15-main.log

参考文献

Discussion