2021.06.21

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

文:

各マネージドkubernetesサービスの監視について調査・比較を行い、RKSのユニークな点に迫りたいと思います。

カテゴリ

CONTENTS

この記事をシェア:

Facebook Twitter LINE

はじめに

説明

まず通常の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とのレプリケーションです。


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

今解決したいDXの
お困りごとなんですか?

DXの知識、導入事例を知りたい DXの知識、導入事例を知りたい セミナーに参加する

今取り組むべき事業のDX推進や、本当に活用できるコンテナ製品の導入プランをご紹介しています。お気軽に無料ウェビナーにご参加ください。

何から手をつけていいか分からない 何から手をつけていいか分からない サービスソリューションをみる

DX推進に取り組みたいが詳しい人材がいない。
まずどこから始めればいいのか、どういう方法があるのかイメージが掴め切れていない。

自社の具体的な導入を検討している 自社の具体的な導入を検討している 各種お問い合わせへ

コンテナ製品のご提案や人材育成支援も行います。事業のDX推進でお困りの方は、こちらからお問い合わせください。