スマホとタブレットでロジカルレプリケーションを試してみた

本記事は「PostgreSQL Advent Calendar 2020」の 15日目です。

##自己紹介
@yamatattsu と言います。oracle_fdw のコミッタや PGConf.Asia の主催者メンバをやっています。PostgreSQLコミュニティ開発にも参画しており、直近だとPGの運用監視に役立つ pg_stat_progress_cluster (PG12) や pg_stat_progress_analyze (PG13) を実装したりと、DBA にとって役立つ機能の開発に取り組んでいます。
国内ではあまり目立った活動をしておりませんが、国外では12/14,16のアドベントカレンダーの著者である @yugo-nさん や @kaigaiさん と共に PGConf.Eu 2018 で講演したりと PostgreSQLの発展に貢献しております。好きなビールはよなよなビールです。

##はじめに
当初は oracle_fdw かプランチューニングツールについて執筆しようと考えていましたが、すみません、テーマを変更しました。
というのは、就寝時に眠れずスマホを用いてサーバに ssh接続しコードを修正しようとした際に Termux というターミナルを見つけたからです (いまさらかもしれませんが・・)。
Termux のリポジトリを見るとPostgreSQLのパッケージも提供されていることがわかり、スマホなど Android端末間でレプリケーションが出来たら面白いかもしれないと考え、当初のテーマを変更しロジレプの構築を試してみることにしました。

##結論を最初に言うと

• スマホとタブレットの間でロジレプできました！

##環境

• Android端末 2個
  • スマホ (例. g08)
  • タブレット (例. Amazon Fire 7 Tablet 第9世代)
• Termux 0.101
• PostgreSQL 13

##構築するロジレプのイメージ

送信側 (テーブル名)	----->	受信 (テーブル名)
pub_a (tbl_a)	----->	sub_test (tbl_a)

メモ

• Publication: 送信側、スマホ上に構築
• Subscription: 受信側、タブレット上に構築

##注意事項

• 本記事はジョークだと思ってください
• スマホやタブレットが不調になっても責任は取れないため、もし試す場合は do it at your own risk でお願いします

##構築手順

###Publication側、Subscription側の両方で実施

####1. Termuxを起動し、とりあえずupdateしてupgradeしておく

$ apt update
$ apt upgrade

####2. 設定ファイルなど編集するためにvimをインストール

$ pkg install vim

####3. sshdを動かすためにopensshをinstall (マニュアル参照[1])

$ apt install openssh

####4. IPアドレスやユーザ名を確認しておく

$ ip addr
$ whoami

####5. passwordを設定する

$ passwd

####6. sshdを起動

$ sshd

---

####sshdが起動したので、ここからはPCからAndroid端末にsshで接続し、ロジレプの設定を進めていくこととします。

• sshの接続には上記4. 5.の情報を利用
• sshで接続するポートは8022 (termuxのデフォルト、マニュアル参照[1])
• 認証方式は本来は公開鍵を使った方がいいかもしれませんが、ここではお試しなので気にしないこととします
• Android端末の節電機能でssh接続が切れたり反応が悪くなるようなので、termuxで Wakelock を取得しておくと良いようです
• 構築手順＆動作確認は[3][4]の資料を参考にさせていただきました (Thanks!)

接続するとこんな画面

Welcome to Termux!

Wiki:            https://wiki.termux.com
Community forum: https://termux.com/community
Gitter chat:     https://gitter.im/termux/termux
IRC channel:     #termux on freenode

Working with packages:

 * Search packages:   pkg search <query>
 * Install a package: pkg install <package>
 * Upgrade packages:  pkg upgrade

Subscribing to additional repositories:

 * Root:     pkg install root-repo
 * Unstable: pkg install unstable-repo
 * X11:      pkg install x11-repo

Report issues at https://termux.com/issues

$

---

###Publication側、Subscription側の両方で実施
####7. PostgreSQLをインストール

$ pkg install postgresql

####8. 環境変数を設定し、ディレクトリを作成

$ export PGHOME=/data/data/com.termux/files/home/PG13
$ export PGDATA=$PGHOME/data
$ export PGDATABASE=postgres
$ mkdir -p $PGHOME
$ mkdir -p $PGDATA

####9. DBを作成し起動

$ initdb --no-locale --encoding=UTF8
$ pg_ctl start

• メモ: postgresユーザを作成しなくてもOKでした

---

###Publication側で実施
####10. ロジレプのための設定変更を行う

$ psql -c "ALTER SYSTEM SET listen_addresses TO '*';"
$ psql -c "ALTER SYSTEM SET wal_level TO 'logical';"
$ psql -c "CREATE ROLE replicator PASSWORD 'secret' LOGIN REPLICATION;"
$ echo "host all replicator SubscriptionのIPアドレス/32 md5" >> $PGDATA/pg_hba.conf
$ pg_ctl restart

• pg_hba.confの記載例: "host all replicator 192.168.11.13/32 md5"

####11. テスト用テーブル作成

$ psql -c "CREATE TABLE tbl_a (c int primary key);"

####12. tbl_aにレコード100件をインサート

$ psql -c "INSERT INTO tbl_a SELECT generate_series(1,100);"

####13. replicatorユーザにtbl_aのSELECT権限付与

$ psql -c "GRANT SELECT ON tbl_a TO replicator;"

####14. Publicationを作成＆確認

$ psql -c "CREATE PUBLICATION pub_a FOR TABLE tbl_a;"
$ psql -c "\dRp"

結果

• pub_a が表示されたので成功です

$ psql -c "\dRp"
                               List of publications
 Name  |  Owner  | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+---------+------------+---------+---------+---------+-----------+----------
 pub_a | u0_a220 | f          | t       | t       | t       | t         | f
(1 row)

---

###Subscription側で実施
####15. テスト用テーブルを作成 (DDLは伝搬されないので)

$ psql -c "CREATE TABLE tbl_a (c int primary key);"

####16. Subscriptionを作成＆確認

$ psql -c "CREATE SUBSCRIPTION sub_test CONNECTION 'Publication側への接続文字列' PUBLICATION pub_a;"
$ psql -c "\dRs"

• 接続文字列の説明はマニュアル参照[4]
• 例. psql -c "CREATE SUBSCRIPTION sub_test CONNECTION 'host=192.168.11.13 dbname=postgres user=replicator password=secret' PUBLICATION pub_a;"

結果

• sub_test が表示されたので成功

$ psql -c "\dRs"
           List of subscriptions
   Name   |  Owner  | Enabled | Publication
----------+---------+---------+-------------
 sub_test | u0_a163 | t       | {pub_a}
(1 row)

####17. レコード100件を受信できたか確認

$ psql -c "SELECT count(*) FROM tbl_a;"

結果

• sub_testの構築後に初期データの同期が行われ、tbl_a に 100件のレコードがコピーされたことを確認できました

$ psql -c "SELECT count(*) FROM tbl_a;"
 count
-------
   100
(1 row)

---

##ここからはテストです。レコードの送受信を確認してみます

###Publication側でINSERTを試す
####18. レコード1件をINSERT

$ psql -c "INSERT INTO tbl_a VALUES(123);"

###Subscription側で確認
####19. レコード1件を受信しているか確認

$ psql -c "SELECT count(*) FROM tbl_a;"
$ psql -c "SELECT max(c) FROM tbl_a;"

結果

• tbl_a に1件追加されレコード数が101件になっている
• 追加されたレコードのカラムの値は 123 であることから無事伝搬されているようです

$ psql -c "SELECT count(*) FROM tbl_a;"
 count
-------
   101
(1 row)

$ psql -c "SELECT max(c) FROM tbl_a;"
 max
-----
 123
(1 row)

というわけで、スマホとタブレット間のロジカルレプリケーションが無事構築出来ました。 :-D
もっと色々と試すべきでしょうが、長くなりましたのでこれぐらいにします。
お疲れさまでした！

##おわりに

• RDBMSは多々ありますが、スマホとタブレット間でロジレプを実現した RDBMS は PostgreSQL が "初" ではないでしょうか :^)
• 高可用性という観点では、マスタのスマホを水没させてもスタンバイのタブレットが生きていればデータを消失しなくてすむような仕組みができそうですね (笑)
• 今回は自宅のローカルネットワーク内で試しましたが、遠隔地に激甚サイトを用意しても面白いかもしれません。頑張ればマルチマスタもいける？！
• 性能の観点では、スマホやタブレットの性能は年々向上しておりますし、ロジレプとは少し離れますが、データを分散させてシャーディングというのも面白いのではないでしょうか
• というわけで、これで 12/15のアドベントカレンダー を終わります。 次に機会がありましたら oracle_fdw や pg_plan_advsr についてコミッタや author という立ち位置で執筆したいと思っています
• 明日は @kaigaiさんです。pg_strom もスマホの GPU で動くようになる日がいつか来るのでしょうか？！？ (そんなことはないですね？)
• それではみなさん良い休日を！ :-D

###参考
[1] https://wiki.termux.com/wiki/Remote_Access
[2] https://qiita.com/sawada_masahiko/items/ff99f34249b171df4093
[3] https://www.slideshare.net/noriyoshishinoda/pgconfasia-2017-logical-replication-internals-japanese
[4] https://www.postgresql.jp/document/12/html/sql-createsubscription.html