はじめに
説明
まず通常の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-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でパスワードが表示されますがドキュメントに説明があります。
レプリケーションの確認
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も行って下さい。
ALTER SUBSCRIPTION logical_sub1 REFRESH PUBLICATION;
あとがき
これでレプリケーションが構築できるはずです。最後にスーパーユーザ権限を外します。
postgres# alter role replica NOSUPERUSER;
次回の内容はCNPとのレプリケーションです。
ここまでお読みいただき、ありがとうございました！
