はじめに
説明
まず通常の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インストールは下記の手順を参考に両サーバにインストールします
$ 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-13PostgreSQL設定
パラメータ設定
パブリケーションのレプリケーション設定をします。
設定が必要な項目は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とのレプリケーションです。
ここまでお読みいただき、ありがとうございました！
