ふと、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みたいなツールが動いているんでしょうね(想像))
ここも特に迷わずポチポチして作成が完了しました。
[テーブルマッピング]というセクションで同期するテーブル、しないテーブルを制御できるのも分かりやすくて良いです。
最後にソースおよびターゲットそれぞれに テストの実行
ができるので、成功を確認して作成完了です。
※実はここで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同期開始
コンソールから開始を実行します。
再開
という方法もある様ですが、今回は初回のため 開始
です。
ステータスが完了になれば成功です。
同期先の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
主キー無しテーブルを試した結論
結論としては主キーの無いテーブルの場合以下の挙動になりました。サービスとしては使えませんが、動きとして知っておくとトラブル対応が捗るかもですね。(主キー無しテーブルを同期して変な動きをしてしまう未来が見える)
念のため主キーがあるテーブル確認
ここまでくると不安なのでサクッと主キーありテーブルの挙動を確認しました、もちろん正しく伝搬したので何の問題も無しでした!
送信元で以下を実施。
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
と指定できるので問題なさそうかな。。。