PostgreSQLのロジカルレプリケーション

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください

【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

はじめに

説明

まず通常のPostgreSQLロジカルレプリケーション構築をおさらいしましょう。
今回はCentOS8でrootはログイン不可にしている環境を想定しております。
また、PostgreSQLは10と13で行います。

30分程度を想定しております。

PostgreSQLのインストール

FWなどの確認

最初にまず対抗とのPostgreSQLの疎通障害になりそうなものを確認しましょう。
特にiptablesとfirewalldは有効の場合PostgreSQLの疎通に問題ないか確認してください。

sudo getenforce
sudo iptables -L
sudo iptables -t nat -L
sudo systemctl status firewalld.service

レポジトリのインストール

公式のPostgreSQLインストールは下記の手順を参考に両サーバにインストールします

https://yum.postgresql.org/

$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo dnf -qy module disable postgresql

パブリケーションのPostgreSQLインストール

マスターサーバに相当するのはパブリケーションと呼ばれてます。PostgreSQL10にします。

サーバの識別上pub$と致します。

pub$ sudo dnf install -y postgresql10-server
pub$ export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale"
pub$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
pub$ sudo systemctl enable postgresql-10
pub$ sudo systemctl start postgresql-10

サブスクリプションのPostgreSQLインストール

スレーブサーバに相当するのはサブスクリプションと呼ばれてます。PostgreSQL13にします。

サーバの識別上sub$と致します。

sub$ dnf install -y postgresql13-server
sub$ export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale"
sub$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sub$ sudo systemctl enable postgresql-13
sub$ sudo systemctl start postgresql-13

PostgreSQL設定

パラメータ設定

パブリケーションのレプリケーション設定をします。
設定が必要な項目は2項目になります。
今回はPostgreSQL10以上ですのでパスワードもmd5からsha256に変更しております。

14からデフォルトがsha256になるようですね。

pub$ sudo vi /var/lib/pgsql/data/postgresql.conf
---
wal_level = logical
listen_addresses = '*'
password_encryption = scram-sha-256  #レプリケーションには本来不要
--

クライアント認証設定

パブリケーションのクライアント認証を設定します。必要に応じて変更してください。
設定は上から評価されますので既存設定のどこにマージするか注意下さい。

pub$ sudo vi /var/lib/pgsql/data/pg_hba.conf
---
local   all             replica                                 scram-sha-256
host    all             replica         192.168.0.0/16          scram-sha-256
host    all             replica         172.16.0.0/12           scram-sha-256
host    all             replica         10.0.0.0/8              scram-sha-256
---

レプリケーションの設定

設定の反映とスーパーユーザログイン

両サーバにて設定反映とpostgresユーザのログイン設定をし、スーパーユーザでログインします

pub,sub$ sudo systemctl restart postgresql
pub,sub$ sudo passwd postgres
pub,sub$ su - postgres
pub,sub$ psql

パブリケーションのパラメータ確認

パブリケーションのパラメータ確認を行います。

設定変更した箇所以外はデフォルトであれば動作します。

パブリケーションのPostgreSQLログイン時は識別上postgres#と致します。

postgres# show wal_level;
postgres# show max_wal_senders;
postgres# show max_replication_slots;
postgres# show synchronous_commit;
postgres# show password_encryption;

パブリケーションの設定

パブリケーションユーザの作成と設定を行います。
今回は本来不要のLOGINとSUPERUSERも付与してますが
SUPERUSERは動作確認後に削除すべきです。

select * from pg_publicationよりも\dRpの方が日本語はわかりやすいです。

postgres# CREATE ROLE replica WITH ENCRYPTED PASSWORD 'password' LOGIN REPLICATION SUPERUSER;
postgres# create table japan ( id serial primary key, pref text, city text );
postgres# CREATE PUBLICATION "logical_pub" FOR TABLE japan;
postgres# \dRp
                         パブリケーション一覧
    名前     |  所有者  | 全テーブル | Insert文 | Update文 | Delete文
-------------+----------+------------+----------+----------+----------
 logical_pub | postgres | f          | t        | t        | t
postgres# select * from pg_publication_tables;
   pubname   | schemaname | tablename
-------------+------------+-----------
 logical_pub | public     | japan

第一弾のデータを流し込みます。

postgres# INSERT INTO japan
  ( pref , city )
VALUES
  ( '東京都', '八王子市' ),
  ( '東京都', '立川市' ),
  ( '東京都', '武蔵野市' ),
  ( '東京都', '三鷹市' ),
  ( '東京都', '青梅市' ),
  ( '東京都', '府中市' ),
  ( '東京都', '昭島市' ),
  ( '東京都', '調布市' ),
  ( '東京都', '小金井市' ),
  ( '東京都', '小平市' ),
  ( '東京都', '日野市' ),
  ( '東京都', '東村山市' ),
  ( '東京都', '国分寺市' ),
  ( '東京都', '国立市' ),
  ( '東京都', '福生市' ),
  ( '東京都', '狛江市' ),
  ( '東京都', '東大和市' ),
  ( '東京都', '清瀬市' ),
  ( '東京都', '東久留米市' ),
  ( '東京都', '武蔵村山市' ),
  ( '東京都', '多摩市' ),
  ( '東京都', '稲城市' ),
  ( '東京都', '羽村市' ),
  ( '東京都', 'あきる野市' ),
  ( '東京都', '西東京都市' );
postgres# select * from japan;
\q
pub$ exit

ログインの確認

まず、ローカルからのログインを確認します

pub$ psql -U replica postgres
\q

サブスクリプションからパブリケーションにログインできるか確認します

sub$ psql -U replica -h IPアドレス postgres
\q

サブスクリプションのパラメータ確認

サブスクリプションのパラメータ確認を行います。

サブスクリプションのPostgreSQLログイン時は識別上postgres$と致します。

postgres# show max_replication_slots;
postgres# show max_logical_replication_workers;
postgres# show max_worker_processes;

サブスクリプションの設定

サブスクリプションの設定を行います。
まず、DDLは一部しか対応していないのでcreate tableを行います。
サブスクリプション名は-が使えないなどパブリケーション名より条件が厳しいです。

設定時点で同期が始まります。

postgres$ create table japan ( id serial primary key, pref text, city text );
postgres$ CREATE SUBSCRIPTION "logical_sub1" CONNECTION 'hostaddr=パブリケーションIP port=5432 user=replica' publication "logical_pub";
postgres$ SELECT * FROM pg_subscription;                     
  oid  | subdbid |   subname    | subowner | subenabled |                         subconninfo                          | subslotname  | subsynccommit | subpublications
-------+---------+--------------+----------+------------+--------------------------------------------------------------+--------------+---------------+-----------------
 24594 |   13434 | logical_sub1 |       10 | t          | hostaddr=192.168.1.99 port=5432 dbname=postgres user=replica | logical_sub1 | off           | {logical_pub}
postgres$ select * from japan;

pg_subscriptionでパスワードが表示されますがドキュメントに説明があります
https://www.postgresql.jp/document/10/html/catalog-pg-subscription.html

レプリケーションの確認

INSERT,UPDATE,DELETEの確認

パブリケーションでinsert,update,deleteを行います

postgres# INSERT INTO japan
  ( pref , city )
VALUES
  ( '神奈川県', '横浜市' ),
  ( '神奈川県', '川崎市' ),
  ( '神奈川県', '相模原市' ),
  ( '神奈川県', '町田市' ),
  ( '神奈川県', '横須賀市' ),
  ( '神奈川県', '平塚市' ),
  ( '神奈川県', '鎌倉市' ),
  ( '神奈川県', '藤沢市' ),
  ( '神奈川県', '小田原市' ),
  ( '神奈川県', '茅ヶ崎市' ),
  ( '神奈川県', '伊豆市' ),
  ( '神奈川県', '三浦市' ),
  ( '神奈川県', '秦野市' ),
  ( '神奈川県', '厚木市' ),
  ( '神奈川県', '大和市' ),
  ( '神奈川県', '伊勢原市' ),
  ( '神奈川県', '海老名市' ),
  ( '神奈川県', '座間市' ),
  ( '神奈川県', '南足柄市' ),
  ( '神奈川県', '綾瀬市' );
postgres# UPDATE japan SET  pref = '東京都' where city = '町田市';
postgres# DELETE FROM japan WHERE pref = '神奈川県';

サブスクリプションでデータを確認します。

insert,update,deleteの結果、東京26市が残るはずです。

postgres$ select * from japan;

レプリケーションの操作

レプリケーションの開始、停止コマンドは下記になります。
ON/OFFはsubenabledを確認します。
デフォルトでは起動時に前回の設定を引き継ぐようです。

postgres$ ALTER SUBSCRIPTION logical_sub1 ENABLE;
postgres$ SELECT subname,subenabled FROM pg_subscription;
postgres$ ALTER SUBSCRIPTION logical_sub1 DISABLE;
postgres$ SELECT subname,subenabled FROM pg_subscription;

また、必要に応じてREFRESHも行って下さい。

https://www.postgresql.jp/document/10/html/sql-altersubscription.html

ALTER SUBSCRIPTION logical_sub1 REFRESH PUBLICATION;

あとがき

これでレプリケーションが構築できるはずです。

最後にスーパーユーザ権限を外します。

postgres# alter role replica NOSUPERUSER;

次回の内容はCNPとのレプリケーションです。

ここまでお読みいただき、ありがとうございました！

About サイオステクノロジーの中の人です 88 Articles

サイオステクノロジーで働く中の人です。

ご覧いただきありがとうございます！この投稿はお役に立ちましたか？

役に立った役に立たなかった

0人がこの投稿は役に立ったと言っています。

PostgreSQLのロジカルレプリケーション

はじめに

説明

PostgreSQLのインストール

FWなどの確認

レポジトリのインストール

パブリケーションのPostgreSQLインストール

サブスクリプションのPostgreSQLインストール

PostgreSQL設定

パラメータ設定

クライアント認証設定

レプリケーションの設定

設定の反映とスーパーユーザログイン

パブリケーションのパラメータ確認

パブリケーションの設定

ログインの確認

サブスクリプションのパラメータ確認

サブスクリプションの設定

レプリケーションの確認

INSERT,UPDATE,DELETEの確認

レプリケーションの操作

あとがき

Be the first to comment

Leave a Reply コメントをキャンセル

はじめに

説明

PostgreSQLのインストール

FWなどの確認

レポジトリのインストール

パブリケーションのPostgreSQLインストール

サブスクリプションのPostgreSQLインストール

PostgreSQL設定

パラメータ設定

クライアント認証設定

レプリケーションの設定

設定の反映とスーパーユーザログイン

パブリケーションのパラメータ確認

パブリケーションの設定

ログインの確認

サブスクリプションのパラメータ確認

サブスクリプションの設定

レプリケーションの確認

INSERT,UPDATE,DELETEの確認

レプリケーションの操作

あとがき

Related Articles

Be the first to comment

Leave a Reply コメントをキャンセル