RHEL8とFlexible Server PostgreSQLでLogical Replicationする

Azure
2021.11.29

自宅のRHEL8上のPostgreSQLから、Azure Database for PostgreSQL Flexible ServerにLogical Replicationを設定してみる。自宅鯖がPublisher、AzureがSubscriberとなる、つまりオンプレからクラウドに向けて同期を取ろうという構成。コネクションとしては、Flexible Serverが自宅鯖の5432/tcpに接続しにくる。

Flexible Server(Subscriber)のデプロイ（Azure Portal）

まずはAzure側。Flexible ServerのPostgreSQLをデプロイする。

[リソースグループ]、[サーバー名]、[管理者アカウント]などを設定。

[ネットワーク]で[ファイアウォール規則]に拙宅のIPアドレスを追加する。あくまで設定用なので、設定が完了したらこの[ファイアウォール規則]は削除する。で、デプロイ。

作成したFlexible Serverに接続し、テーブルを作成しておく。

$ psql "host=riologrep.postgres.database.azure.com port=5432 dbname=postgres user=rifujita password={your_password} sslmode=require"
postgres=> CREATE TABLE basic(id SERIAL, name varchar(40));
CREATE TABLE
postgres=> \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | basic | table | rifujita
(1 row)

自宅鯖の設定

RHEL8でそのままpostgresql-serverをインストールするとPostgreSQL 10がインストールされてしまうので、dnf moduleコマンドでPostgreSQL 11以降に切り替えてインストール。initdbを実行したらpostgresql.confのwal_levelをlogicalにする。

$ diff -up postgresql.conf.orig postgresql.conf
--- postgresql.conf.orig	2021-11-29 21:00:30.986706646 +0900
+++ postgresql.conf	2021-11-29 21:06:18.774634013 +0900
@@ -56,7 +56,7 @@
 
 # - Connection Settings -
 
-#listen_addresses = 'localhost'		# what IP address(es) to listen on;
+listen_addresses = '*'		# what IP address(es) to listen on;
 					# comma-separated list of addresses;
 					# defaults to 'localhost'; use '*' for all
 					# (change requires restart)
@@ -193,7 +193,7 @@ dynamic_shared_memory_type = posix	# the
 
 # - Settings -
 
-#wal_level = replica			# minimal, replica, or logical
+wal_level = logical			# minimal, replica, or logical
 					# (change requires restart)
 #fsync = on				# flush data to disk for crash safety
 					# (turning this off can cause

PostgreSQLを起動。

$ sudo systemctl --now enable postgresql

Flexible Serverが自宅鯖の5432/tcpに接続出来るようにfirewalldを設定する。

$ sudo firewall-cmd --add-port 5432/tcp --permanent

postgresユーザでpsqlで接続し、テスト用のテーブルを作成し、データを適当に入れる。

$ su - postgres
$ psql
postgres=# \du
                                             ロール一覧
 ロール名 |                                   属性                                   | 所属グループ 
----------+--------------------------------------------------------------------------+--------------
 postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}

postgres=# CREATE TABLE basic(id SERIAL, name varchar(40));
CREATE TABLE
postgres=# \dt
            リレーション一覧
 スキーマ | 名前  |  タイプ  |  所有者  
----------+-------+----------+----------
 public   | basic | テーブル | postgres
(1 行)

postgres=# INSERT INTO basic(name) VALUES ('apple');
INSERT 0 1
postgres=# INSERT INTO basic(name) VALUES ('banana');
INSERT 0 1

Publicationを作成する。

postgres=# CREATE PUBLICATION pub FOR TABLE basic;
CREATE PUBLICATION

Subscriptionの作成（失敗する）

Flexible Serverにpsqlで接続して、Subscriptionを作成「しよう」とする。

postgres=# CREATE SUBSCRIPTION sub CONNECTION 'host=rio.st user=postgres dbname=postgres password=password' PUBLICATION pub;
ERROR:  could not connect to the publisher: FATAL:  pg_hba.conf にホスト"20.194.186.216"、ユーザ"postgres", SSL無効用のエントリがありません

pg_hba.confで許可していないので、当然エラーとなる。また、このIPアドレスは作成したFlexible Serverのエンドポイントとは異なるので、Flexible Serverの[サーバー名]を正引きしたIPアドレスでは無い。

pg_hba.confの設定

Subscriptionを作成した際に判明したIPアドレスを自宅鯖のpg_hba.confに追加する。

$ diff -up pg_hba.conf.orig pg_hba.conf
--- pg_hba.conf.orig	2021-11-29 22:38:30.623682568 +0900
+++ pg_hba.conf	2021-11-29 22:38:56.082454590 +0900
@@ -88,6 +88,7 @@
 local   all             all                                     trust
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            trust
+host    all             all             20.194.186.216/32            trust
 # IPv6 local connections:
 host    all             all             ::1/128                 trust
 # Allow replication connections from localhost, by a user with the

PostgreSQLを再起動する。

$ sudo systemctl restart postgresql

Subscriptionの作成

Subscriptionを作成する。今度は成功する。

postgres=> CREATE SUBSCRIPTION sub CONNECTION 'host=rio.st user=postgres dbname=postgres password=password' PUBLICATION pub;
NOTICE:  created replication slot "sub" on publisher
CREATE SUBSCRIPTION

テーブルを表示するとデータが同期していることが分かる。

postgres=> select * from basic;
 id |  name  
----+--------
  1 | apple
  2 | banana
(2 rows)

まとめ

あくまで最低限動作することを確認したのみなので、本番ではVPN Gateway等を利用して通信をセキュアにしつつpg_hba.confに設定するIPアドレスの範囲を限定することや、パスワード認証を有効にするといった対策も必要ではあるし、postgresユーザーではなくレプリケーション用のユーザーを用意する必要もある。けれども、PostgreSQL 11以上であればFlexible ServerとのLogical Replicationは可能なので、オンプレ＋Azureという構成も検討に値する、ということでよろしいかと。