2020/9 PostgreSQLのクラウド&オンプレミスレプリケーション

2020年9月2日に公開大阪

こんにちは、大阪の渡邉です。業務でaws rds(aurora含む)とオンプレミスのpostgresとのレプリケーションを扱ったのでまとめたいと思います。 PostgreSQLのクラウド→オンプレミスレプリケーション

レプリケーションとは

レプリケーションとは、コンピュータやソフトウェアが持っているデータの複製（レプリカ）を別のコンピュータ上に作成し、ネットワークを介してリアルタイムに更新し常にデータを同期することを言います。

論理レプリケーションってどうゆうやつ？

PostgreSQL10以降で使用できるレプリケーション機能です。同期元のデータの論理的な変更情報を、同期先のサーバーに送信してデータを複製します。
同期先でもレコードの追加、更新が可能なため、レプリケーション時に競合が起こる可能性があります。
同期元のデータベースをパブリッシャー、同期先のデータベースをサブスクライバーと呼びます。

ユースケースとしては、以下のようなものがありました。

２つの異なるDBから解析の為に１つのDBにテーブルを集める。
データベースから一部のテーブルのみを選択して複製する。

できること、できないこと

まず、物理レプリケーション(ストリーミングレプリケーション)についてAWSさんに問い合わせたところ、

Aurora PosrgreSQL、RDS for PostgreSQL共にレプリケーションの手段として物理レプリケーションはご利用いただけません。

とのことでした。(2020/07時点)

なので、本記事ではテーブル単位の論理レプリケーション(ロジカルレプリケーション)を主に扱います。

実際にやってみた

さっそくサーバを購入して、、、とはやってられません。
今回はAWS EC2をオンプレに見立てて実施します。

ネットワーク構築

以下のようなネットワークを構築していきます。

同一VPC内に、RDS用のサブネット２つ、EC2用のサブネットを一つ作成する。
EC2のセキュリティグループにRDSのセキュリティグループの通信を許可するように設定する。
RDSも同様にEC2のセキュリティグループと、EC2インスタンスのプライベートIPアドレスの通信を許可するように設定する。

RDS for postgres(or aurora)を立ち上げる

RDS postgresかaurora postgresのインスタンスを作成します。先ほど作成した、RDS用のサブネットとセキュリティグループを適用してください。

パラメータグループの作成と適用

RDSのマネジメントコンソ―ルからパラメータグループ、パラメータグループの作成を選択し、以下の通り選択する。

RDS for posrgresの場合
パラメータグループファミリー：postgres11
タイプ：パラメータグループ

aurora posrgresの場合
パラメータグループファミリー：aurora-postgresql11
タイプ：DB クラスターのパラメータグループ

作成したパラメータグループの以下の値を編集する。

rds.logical_replication:0->1

RDS for postgres(or aurora)に作成したパラメータグループを設定し再起動する。

EC2にpostgresを立ち上げる

インスト―ルと起動

$ sudo rpm -ivh --nodeps https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo sed -i "s/\$releasever/7/g" "/etc/yum.repos.d/pgdg-redhat-all.repo"
$ yum -y install postgresql11-server postgresql11-contrib
$ systemctl enable postgresql-11.service ・・・自動起動の設定
$ PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-11/bin/postgresql-11-setup initdb
$ su - postgres ・・・なければつくる
$ pg_ctl start ・・・PostgreSQL

外部からの接続を許可する

$ vi /var/lib/pgsql/11/data/postgresql.conf

#listen_addresses = 'localhost'
変更↓
listen_addresses = '*'

$ vi /var/lib/pgsql/11/data/pg_hba.conf

"local" is for Unix domain socket connections only
IPv4 local connections:
host    all    all    0.0.0.0/0    md5  この行を追加

$ pg_ctl restart

ローカルからは以下のコマンドでアクセスする。
$ psql [DB名] postgres

パブリッシャーデータベースを設定

パブリケーションを設定します。
RDS,オンプレどちらかで以下のコマンドを実行してください。

CREATE TABLE LogicalReplicationTest (a int PRIMARY KEY);
INSERT INTO LogicalReplicationTest VALUES (generate_series(1,10));
CREATE PUBLICATION testpub FOR TABLE LogicalReplicationTest;

サブスクライバーデータベースを設定

サブスクリプションを設定します。
パブリケーション設定をしていない方のDBで以下のコマンドを実行してください。

CREATE TABLE LogicalReplicationTest (a int PRIMARY KEY);
SELECT count(*) FROM LogicalReplicationTest;
CREATE SUBSCRIPTION testsub CONNECTION 
   'host=publisher-cluster-writer-endpoint port=5432 dbname=db-name user=user password=password' 
   PUBLICATION testpub;

レプリケーションを確認

サブスクリプションを設定した時点でのデータの同期(初期同期)が完了した後、パブリッシャーの更新内容を随時同期していくようです。
初期同期が完了するまではパブリッシャーへの更新は適応されません。
初期同期の完了はDBのログファイルに以下のように出力されます。

2021-01-23 07:38:47.229 UTC [13520] LOG:  statement: CREATE SUBSCRIPTION reversesub1 CONNECTION 'host=aurora-cview-osakatech-blog.rds.amazonaws.com port=5432 dbname=name user=postgres password=pass' PUBLICATION testpub;
2021-01-23 07:38:47.278 UTC [13522] LOG:  logical replication apply worker for subscription "testsub" has started
2021-01-23 07:38:47.294 UTC [13530] LOG:  logical replication table synchronization worker for subscription "testsub", table "test_table" has started       
2021-01-23 07:39:48.498 UTC [13530] LOG:  logical replication table synchronization worker for subscription "testsub", table "test_table" has finished

障害発生時の対応

パブリッシャーの障害発生時はサブスクライバーに接続しなおすだけで対応が完了しますがパブリッシャー復旧の際には少し問題があります。

理想的な復旧方法は、パブリッシャーとサブスクライバーの差分を吸収してレプリケーション設定を再度行うというものだったのですが、論理レプリケーションでは実現できませんでした。

パブリッシャーの復旧

私がとった手法は、元テーブルをtruncateするという少し暴力的なものです。
まず、既存のレプリケーション設定を破棄します。

drop SUBSCRIPTION testsub;
drop PUBLICATION testpub;

※このコマンドが失敗した場合、以下のコマンドを実行して、サブスクリプションとレプリケーションスロットの関連付けを解除してからサブスクリプションを削除します。
    ALTER SUBSCRIPTION testsub disable; 
    ALTER SUBSCRIPTION testsub  SET (slot_name = NONE); 
    DROP SUBSCRIPTION testsub ;

パブリッシャーのAテーブルをtruncateし、サブスクライバーのA'テーブルから差分ごと論理レプリケーションで同期させます。(A'→Aへ同期)

また、レプリケーションの向きを元に戻す為にサブスクライバーのA'テーブルをtruncateしてレプリケーションを再度設定します。(A→A'へ同期)

これで初期同期が完了するのを待てば元通りなのですが、少しもやっとします。

まとめ

今回はpostgresの論理レプリケーションについて、構築と障害対応をまとめてみました。
いつもはCRUDするくらいでして、postgres（というかDB）をここまで深く触ったのは初めてだったのでなかなか骨が折れました。

参考URL:
第3回「ロジカルレプリケーション」 | NTTデータ先端技術株式会社
 2017年度WG3活動報告書レプリケーション調査編
 PostgreSQL 11.5文書
 ロジカルレプリケーションのレプリケーション衝突を解決する

2020/9 PostgreSQLのクラウド&オンプレミスレプリケーション

レプリケーションとは

論理レプリケーションってどうゆうやつ？

できること、できないこと

実際にやってみた

ネットワーク構築

RDS for postgres(or aurora)を立ち上げる

パラメータグループの作成と適用

EC2にpostgresを立ち上げる

インスト―ルと起動

外部からの接続を許可する

パブリッシャーデータベースを設定

サブスクライバーデータベースを設定

レプリケーションを確認

障害発生時の対応

パブリッシャーの復旧

まとめ

関連記事

こんな記事も読まれています

2020/9 PostgreSQLのクラウド&オンプレミス レプリケーション

レプリケーションとは

論理レプリケーションってどうゆうやつ？

できること、できないこと

実際にやってみた

ネットワーク構築

RDS for postgres(or aurora)を立ち上げる

パラメータグループの作成と適用

EC2にpostgresを立ち上げる

インスト―ルと起動

外部からの接続を許可する

パブリッシャーデータベースを設定

サブスクライバーデータベースを設定

レプリケーションを確認

障害発生時の対応

パブリッシャーの復旧

まとめ

関連記事

こんな記事も読まれています

2020/9 PostgreSQLのクラウド&オンプレミスレプリケーション