ふと、AWS上のpostgresってどうやってデータ同期するんだろうと疑問に思ったので検証してみたメモです。

オンプレでネイティブなpostgresならデータ同期にはいくつかのパターンがあってざっくり以下だと認識しています。

ver9以上で同期元と同期先が同じバージョンならストリーミングレプリケーション
ver10以上ならロジカルレプリケーション
ver9.4以上なら2ndQuadrantのpglogical extentionでロジカルレプリケーション
バッチ方式で良いならpgdumpとかembulkとか

※他にattunityとかここのツール群とかは良く知らないので割愛

検証結果

結果を先に書くと、 RDS PostgreSQLからRDS Aurora PostgreSQLへDMSによるロジカルレプリケーションが可能 です。

注意事項として、ロジカルレプリケーションの制約である「プライマリキーが存在しないテーブルは対象外」ですので、利用するテーブルには注意しましょう。中身はpglogicalだと思うのでドキュメントを読んでおくことをお勧めします。

必要な設定

設定はシンプルで以下の2点

RDS PostgreSQL用のパラメータグループで rds.logical_replication = 1 を設定
DMSをぽちぽち設定（特殊な設定はなし）

注意点として、rds.logical_replicationを1にした後は必ずインスタンスの再起動が必要です。show rds.logical_replicationで確認すると動的に変更されるのですが、ロジカルレプリケーションで必須のwal_level が再起動をしないと replica から logical に変更されません。パラメータとしても適用はstaticになっています。なお同様の内容をクラスメソッドさんでも発見しました、流石です。

検証開始

どうやるかを調べる

とりあえず最初にあげた方法を順に考えましたが、以下の通りオンプレ脳ではRDS(pg)->Aurora(pg)レプリケーションは達成できそうにありませんでした。

ストリーミングレプリケーション -> 不可。バージョンも違うしサービスも違うしwalファイル操作できない
ロジカルレプリケーション -> 不可。現状でRDS PostgreSQLにしろRDS Aurora PostgreSQLにしろver9.6まで。ver10は今後。
pglogical extentionでロジカルレプリケーション -> 不可。バージョン的には問題ないが、自前でextentionは入れられない（だってマネージドだし...)
バッチ方式 -> 可。できるのはわかっているが、レプリがしたい。。

そこで頭を切り替えます。AWSのデータ同期と言えばDMSでしょうということで、ドキュメントをつらつら読んでいきました。

ソースデータベースにはオンプレミス、または Amazon RDS か Amazon EC2、
ターゲットデータベースには Amazon RDS または Amazon EC2 のいずれかを使用できます。

なるほどRDSはソースにもターゲットにもできる様なので問題なしですね。さらに2016年と古いですがこんな記事も。

Amazon RDS for PostgreSQLがロジカルレプリケーションをサポートしました。

略

AWS Database Migration ServiceはAWSへのデータベースマイグレーションをサポートするサービスです。
ロジカルレプリケーションと一緒に使用する事により...

やったぜ！DMSを使えばRDS PostgreSQLからロジカルレプリケーションでRDS Aurora PostgreSQLへデータ同期できるとのことで、検証を行っていきます。

手を動かす

各サービス起動

はじめに検証環境として以下の3サービスを立ち上げます。

RDS PostgreSQL
RDS Aurora PostgreSQL
EC2

次にRDSのセキュリティグループでEC2のプライベートIPを許可します。port 5432に対してインバウンド172.31.0.0/16 と許可します（ちょっと無駄に広いですけど）。

DBへ接続確認

EC2へログインし、送信元、送信先のDBインスタンスに接続できることを確認します。毎回パスワードを入力するのが面倒なのでpgpassファイルを最初に作成します。

vi ~/.pgpass

morihaya-aurorapg.xxxxxx.us-east-1.rds.amazonaws.com:5432:morihaya:morihaya:password
morihaya.xxxxxxx.us-east-1.rds.amazonaws.com:5432:morihaya:morihaya:password

chmod 0600 ~/.pgpass
cat ~/.pgpass

続いてEC2上でpsqlコマンドを使える状態にします。postgres-serverを入れる必要はないのですが、大して重くないので入れてしまいました。contribはpgbenchで簡単な負荷テストをするためです。

yum install postgresql96-server postgresql96-contrib

ここまできたら接続テストをします。

# RDS(postgres)
psql -U morihaya -h morihaya.xxxx.us-east-1.rds.amazonaws.com

# RDS Aurora(postgres)
psql -U morihaya -h morihaya-aurorapg.xxxx.us-east-1.rds.amazonaws.com

無事つながりました。

DMS設定

続いてDMSを設定していきます。 DMSは レプリケーションインスタンス という専用EC2を起動し、その中で タスク を設定することでデータ同期を行う様です。(多分その専用EC2でattunityみたいなツールが動いているんでしょうね(想像))

ここも特に迷わずポチポチして作成が完了しました。 [テーブルマッピング]というセクションで同期するテーブル、しないテーブルを制御できるのも分かりやすくて良いです。 f:id:morihaya:20180430194747p:plain

最後にソースおよびターゲットそれぞれに テストの実行 ができるので、成功を確認して作成完了です。

※実はここでrds.logical_replication 変更後のRDS側の再起動を行っておらずプチハマりしました。以下のエラーをRDSのログでみて気づけましたが。。

04-30 08:27:37 UTC,9/36,0,ERROR,55000,"logical decoding requires wal_level >= logical",,,,,,"BEGIN;declare ""SQL_CUR0x....

初期データ投入

DMSによるデータ同期を行ってからデータ投入というケースはあまりなさそうなので、とりあえず初期データを送信元のRDS PostgreSQLに入れます。

EC2から以下を実行します。

pgbench -i -h morihaya.xxxx.us-east-1.rds.amazonaws.com -U morihaya

念のためカウントで確認します。

psql -h morihaya.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT count(*) from pgbench_accounts'
 count
--------
 100000
(1 row)

DMS同期開始

コンソールから開始を実行します。 再開 という方法もある様ですが、今回は初回のため 開始 です。

ステータスが完了になれば成功です。 f:id:morihaya:20180430200736p:plain

同期先のauroraにてデータの伝搬を確認します。

psql -h morihaya-aurorapg.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT count(*) from pgbench_accounts'
 count
--------
 100000
(1 row)

無事にレプリケーションが組めました。簡単！

主キー無しテーブルを試してみる

ここでちょっと興味が湧いたので、主キー無しのテーブルがどの様に伝搬されるかを確認します。

なおネイティブなPostgreSQL ver10のロジカルレプリケーションではDDLは伝搬しないのですが、それも含めてテストします。

送信元のRDS PostgreSQLへpsqlで接続、以下を実行します。

psql -U morihaya -h morihaya.xxxx.us-east-1.rds.amazonaws.com

# table作成
create table morihaya ( date date );
insert into morihaya ( date ) values ('2018-04-30' );

# tableへ列追加
alter table morihaya add column time time;
insert into morihaya ( date, time ) values ('2018-04-30' , '18:00:00' );

# データ確認
morihaya=> select * from morihaya;\q
    date    |   time
------------+----------
 2018-04-30 |
 2018-04-30 | 18:00:00
(2 rows)

結果をaurora側でも確認します。

# psql -h morihaya-aurorapg.xxxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT * from morihaya'
    date    |   time
------------+----------
 2018-04-30 |
 2018-04-30 | 18:00:00
(2 rows)

DMSのタスク作成時のフィルタを % と全指定したこともあり、DDL,DML全てが送信先のAuroraに伝搬されていることを確認しました。素晴らしい。

そして肝心のUPDATEを同期元のRDS PostgreSQLで実行します。

> update morihaya set time='11:11:11';

> select * from morihaya;
    date    |   time
------------+----------
 2018-04-30 | 11:11:11
 2018-04-30 | 11:11:11
(2 rows)

同期先をみるとなんてことだ！伝搬されてませんね><

# psql -h morihaya-aurorapg.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT * from morihaya'
    date    |   time
------------+----------
 2018-04-30 |
 2018-04-30 | 18:00:00
(2 rows)

ではDELETEはどうでしょう。送信元で以下実施。

> delete from morihaya WHERE date='2018-04-30';
DELETE 2

> select * from morihaya;
 date | time
------+------
(0 rows)

送信先のauroraで確認すると、「マイガッ！」DELETEもダメですね。

# psql -h morihaya-aurorapg.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT * from morihaya'
    date    |   time
------------+----------
 2018-04-30 |
 2018-04-30 | 18:00:00
(2 rows)

整合性がなくなったのでテーブルを削除します。

> drop table morihaya;
DROP TABLE

送信先でも無事に削除されました。

# psql -h morihaya-aurorapg.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT * from morihaya'
ERROR:  relation "morihaya" does not exist
LINE 1: SELECT * from morihaya

主キー無しテーブルを試した結論

結論としては主キーの無いテーブルの場合以下の挙動になりました。サービスとしては使えませんが、動きとして知っておくとトラブル対応が捗るかもですね。（主キー無しテーブルを同期して変な動きをしてしまう未来が見える）

DML
- INSERT -> 伝搬される
- UPDATE,DELETE -> 伝搬されない
DDL -> 伝搬される

念のため主キーがあるテーブル確認

ここまでくると不安なのでサクッと主キーありテーブルの挙動を確認しました、もちろん正しく伝搬したので何の問題も無しでした！

送信元で以下を実施。

create table morihaya (id SERIAL primary key, date date , time time);

insert into morihaya ( date ) values ('2018-04-30' );
insert into morihaya ( date ) values ('2018-04-30' );
insert into morihaya ( date ) values ('2018-04-30' );

> select * from morihaya;
 id |    date    | time
----+------------+------
  1 | 2018-04-30 |
  2 | 2018-04-30 |
  3 | 2018-04-30 |
(3 rows)

 update morihaya set time='11:11:11';
UPDATE 3

 select * from morihaya;
 id |    date    |   time
----+------------+----------
  1 | 2018-04-30 | 11:11:11
  2 | 2018-04-30 | 11:11:11
  3 | 2018-04-30 | 11:11:11
(3 rows)

送信先でもご覧の通り！

# psql -h morihaya-aurorapg.xxxxx.us-east-1.rds.amazonaws.com -U morihaya -c 'SELECT * from morihaya'
 id |    date    |   time
----+------------+----------
  1 | 2018-04-30 | 11:11:11
  2 | 2018-04-30 | 11:11:11
  3 | 2018-04-30 | 11:11:11
(3 rows)

以上、DMSを使ってRDS PostgreSQLからRDS Aurora PostgreSQLへのデータ同期を試したメモでした。

（余談）RDS Aurora PostgreSQLはDMSの送信元にできるのか疑惑

実はRDS Aurora PostgreSQLのパラメタグループに rds.logical_replication = 1 に該当するパラメタが存在しないんですよね。さらにwal_levelを確認するとreplicaとなっています。これはもしかしてAurora Postgresを送信元にできないのじゃ無いかなって思ったりしましたが、本日は力尽きたので次に試したいと思います。

> show wal_level;
 wal_level
-----------
 replica
(1 row)

さらっとDMSの設定みるとソースに aurora と指定できるので問題なさそうかな。。。 f:id:morihaya:20180430204554p:plain

もりはやメモφ(・ω・ )

ITとか読書感想文とか

RDS PostgreSQLからRDS Aurora PostgreSQLへDMSでデータ同期してみる