ちょっと需要があるもののMySQLの記事しか見つからなかったのでテストしてみた備忘録です。
・準備する
・インスタンスをつくる
インスタンス ID komitestdb
「postgres」ユーザーのパスワード av******
リージョン asia-northeast1 ※変更できない
ゾーン asia-northeast1-b ※変更できる
データベースのバージョン
PostgreSQL 9.6
コア数 １ ※s-in前や負荷テストの際にスペックは決めて上げたほうがよさそう
メモリ 3.75GB ※同上
ネットワークスループット250/2,000
ストレージの種類 HDD ※テスト用のためで本番機ならSSD推奨
ストレージ容量 10GB
ストレージの自動増量を有効化 チェック入れる
バックアップを自動化する 2-6時
可用性 高可用性（リージョン）
ネットワークの承認 なにもしない ※プロキシ使うため
データベース フラグの追加 何もしない
メンテナンスの時間枠 土曜日 2-3時
ラベル追加
role:db
environment:test
インスタンスのリソースごとの同接制限などがある模様
https://cloud.google.com/sql/docs/quotas
・postgresqlのクライアントを入れる
sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
sudo yum install --enablerepo=pgdg96 --disablerepo=base,updates postgresql96 postgresql96-libs
インストール:
postgresql96.x86_64 0:9.6.8-1PGDG.rhel7 postgresql96-libs.x86_64 0:9.6.8-1PGDG.rhel7
・cloudproxyいれる
sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
sudo chmod +x cloud_sql_proxy
sudo cp ./cloud_sql_proxy /usr/bin/
・systemd化しておく
sudo mkdir /etc/systemd/system/cloud_sql_proxy\@.service.d
sudo vi /etc/systemd/system/cloud_sql_proxy\@.service.d/limits.conf
[Service]
LimitNOFILE=65535
sudo vi /etc/systemd/system/cloud_sql_proxy\@.service
------------
[Unit]
Description = CloudSQL Proxy %i
After = network.target
[Service]
EnvironmentFile = /etc/sysconfig/cloud_sql_proxy/%i.conf
ExecStart = /usr/bin/cloud_sql_proxy -instances=${PROJECT_ID}:${REGION}:${INSTANCE_NAME}=tcp:${PORT}
ExecStop = /bin/kill ${MAINPID}
ExecReload = /bin/kill -HUP ${MAINPID}
Restart = always
Type = simple
[Install]
WantedBy = multi-user.target
------------
%iはdbのinstance_nameであり、起動時に@のうしろに指定する。
そうすると別の名前指定で起動するようにconf用意して別のSQLインスタンスにつなぐ用のproxyのプロセスも起動、ができるものと思われる(instancesにカンマ区切りでインスタンス毎に指定できる模様）
https://www.freedesktop.org/software/systemd/man/systemd.unit.html
http://enakai00.hatenablog.com/entry/20130917/1379374797
sudo mkdir /etc/sysconfig/cloud_sql_proxy
sudo vi /etc/sysconfig/cloud_sql_proxy/komitestdb.conf
------------
PROJECT_ID="komitest-xxxxxx"
REGION="asia-northeast1"
INSTANCE_NAME="komitestdb"
PORT="5432"
------------
sudo systemctl daemon-reload
・起動して自動起動を設定しておく
$ sudo systemctl start cloud_sql_proxy@komitestdb.service
$ sudo systemctl status cloud_sql_proxy@komitestdb.service
$ sudo systemctl is-enabled cloud_sql_proxy@komitestdb.service
$ sudo systemctl enable cloud_sql_proxy@komitestdb.service
$ sudo systemctl is-enabled cloud_sql_proxy@komitestdb.service
is-enabled,enableに失敗する場合はunitファイルtypoしていてbadとか返ってくるが
typoなおしてdaemon-reloadすると失敗しなくなるはず。
・API有効化
接続テストの前にURLにアクセスしてAPIを有効化する
https://console.developers.google.com/apis/library/sqladmin.googleapis.com?authuser=2&project=komitest-xxxxxx
・接続テストとタイムゾーン変更
$ psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"
psql (9.6.10, サーバー 9.6.6)
"help" でヘルプを表示します.
postgres=> help
PostgreSQL へのコマンドライン・インターフェース、psql へようこそ。
\copyright とタイプすると、配布条件を表示します。
\h とタイプすると、SQL コマンドのヘルプを表示します。
\? とタイプすると、psql コマンドのヘルプを表示します。
\g と打つかセミコロンで閉じると、問い合わせを実行します。
\q で終了します。
postgres=> \h
postgres=> \l
postgres=> \du
postgres=> ALTER DATABASE postgres SET timezone TO 'Asia/Tokyo';
postgres=> \q
$ psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"
postgres=> select now();
postgres=> \q
インスタンスに対する更新内容は全部テンプレートにしておく
テンプレ差し替え
スナップショットからインスタンス作成後停止してディスクイメージ作成しテンプレート作成しマネージグループで差し替える
そのあとに以下のようにインスタンス再作成(既存はそのまま）
gcloud compute --project "komitest-xxxxxx" instance-groups managed recreate-instances "instance-group-1" --zone "asia-northeast1-b" --instances "instance-group-1-wfd7"
・フェイルオーバーのテストをする
・コンソール画面からフェイルオーバ
以下の参考リンク先のテストはMySQLでリードレプリカを使っているものであるが
今回接続のエンドポイントは1つでよいらしいので普通に復旧速度などを計測する
https://qiita.com/fumihiko-hidaka/items/c291e2f08846b01cc3de
GUIコンソール右上のフェイルオーバ実行リンクを押すとフェイルオーバできる。
mysqladmin pingみたいなのがないので適当なスクリプト作ってループで実行さす
$ vi ~/.pgpass
127.0.0.1:5432:postgres:postgres:******
vi pgping.sh
---
#!/bin/bash
for i in `seq 1 5`; do
# echo "Wait for 5 seconds..."
sleep 3
psql postgresql://127.0.0.1:5432/postgres?sslmode=disable -U postgres -c 'select now();' -t 2>&1 > /dev/null
if [[ $? -eq 0 ]]; then
echo "$(date) Connection established."
exit 0
fi
done
echo "$(date) Failed to connect to database."
exit 1
---
$ while : ; do ./pgping.sh ; done
2018年 8月 10日 金曜日 17:01:15 JST Connection established.
2018年 8月 10日 金曜日 17:01:18 JST Connection established.
2018年 8月 10日 金曜日 17:01:21 JST Connection established.
2018年 8月 10日 金曜日 17:01:24 JST Connection established.
2018年 8月 10日 金曜日 17:01:27 JST Connection established.
psql: サーバとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバが異常終了
したことを意味しています。
～略～
2018年 8月 10日 金曜日 17:01:42 JST Failed to connect to database.
psql: サーバとの接続が想定外にクローズされました
psql: サーバとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバが異常終了
したことを意味しています。
2018年 8月 10日 金曜日 17:02:27 JST Failed to connect to database.
2018年 8月 10日 金曜日 17:02:31 JST Connection established.
2018年 8月 10日 金曜日 17:02:34 JST Connection established.
コンソールの右上のフェイルオーバ・フェイルバック押すリンクはデータ更新するとバックアップ取るまで押せなくなる現象を確認したが、実は次に実施したgcloudコマンドはデータ更新しながらでもフェイルオーバ・フェイルバックができていた。
・テストデータ投入しながらgcloudコマンドでフェイルオーバ
空のデータベースだとちょっとテストの甲斐がないのでなんかのcsvを入れてみる
https://qiita.com/cobot00/items/8d59e0734314a88d74c7
http://kawa.at.webry.info/200610/article_8.html
https://dev.classmethod.jp/etc/postgresql-create-sample-database/
https://whitemitt.com/2012/04/14-113046.htm
上のリンク先を参考に直積サンプルデータとか郵便番号やレンタルdvdのcsvなどをつっこんでみてなんとかpostgresのDBが33MBになったので
そのデータベースを100回くらいコピーして10GBのうち3GBくらいにしてみる
・各DBスキーマの容量確認
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
datname | pg_size_pretty
---------------+----------------
cloudsqladmin | 7319 kB
template0 | 7145 kB
postgres | 12 MB
template1 | 7145 kB
(4 行)
・スキーマをスクリプトでコピーしまくる
$ cat hoge-create.sh
#!/bin/bash
for i in `seq 1 100`; do
psql postgresql://127.0.0.1:5432/postgres?sslmode=disable -U postgres -c "CREATE DATABASE hoge${i} TEMPLATE postgres;"
done
$ ./hoge-create.sh
$ psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"
postgres=> SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
datname | pg_size_pretty
---------------+----------------
cloudsqladmin | 7319 kB
hoge20 | 33 MB
template0 | 7145 kB
postgres | 33 MB
template1 | 7145 kB
zip | 7255 kB
pgis | 7255 kB
dvdrental | 15 MB
hoge1 | 33 MB
hoge2 | 33 MB
hoge3 | 33 MB
hoge4 | 33 MB
hoge5 | 33 MB
hoge6 | 33 MB
～略～
hoge98 | 33 MB
hoge99 | 33 MB
hoge100 | 33 MB
(107 行)
・直積インサートで5万件ほどいれる
postgres=> INSERT INTO item(account_id, master_item_id)
SELECT
a.id,
i.master_item_id
FROM
item AS i,
account AS a
WHERE
i.account_id = 1
AND
a.id BETWEEN 2 AND 10000;
postgres=> select count(1) from item;
・gcloudコマンドでの更新ありフェイルオーバを試す
ちょっとマニュアルみてみる
cloudshell:~ (komitest-xxxxxx)$ gcloud beta sql instances --help
SYNOPSIS
gcloud beta sql instances COMMAND [GCLOUD_WIDE_FLAG ...]
DESCRIPTION
(BETA) Provide commands for managing Cloud SQL instances including
creating, configuring, restarting, and deleting instances.
GCLOUD WIDE FLAGS
These flags are available to all commands: --account, --configuration,
--flatten, --format, --help, --log-http, --project, --quiet, --trace-token,
--user-output-enabled, --verbosity. Run $ gcloud help for details.
～略～
gcloud beta sql instances failover --help
NAME
gcloud beta sql instances failover - causes a high-availability Cloud SQL
instance to failover
SYNOPSIS
gcloud beta sql instances failover INSTANCE [--async]
[GCLOUD_WIDE_FLAG ...]
DESCRIPTION
(BETA) Causes a high-availability Cloud SQL instance to failover.
POSITIONAL ARGUMENTS
INSTANCE
Cloud SQL instance ID.
FLAGS
--async
Do not wait for the operation to complete.
GCLOUD WIDE FLAGS
These flags are available to all commands: --account, --configuration,
--flatten, --format, --help, --log-http, --project, --quiet, --trace-token,
--user-output-enabled, --verbosity. Run $ gcloud help for details.
NOTES
This command is currently in BETA and may change without notice. These
variants are also available:
$ gcloud sql instances failover
$ gcloud alpha sql instances failover
5万行追加した直後に再度フェイルオーバコマンドを実施
$ gcloud sql instances failover komitestdb
Failover will be initiated. Existing connections to the master
instance will break and no new connection can be established during
the failover.
Do you want to continue (Y/n)? Y
Failing over Cloud SQL instance...done.
postgres=> INSERT INTO item(account_id, master_item_id)
SELECT
a.id,
i.master_item_id
FROM
item AS i,
account AS a
WHERE
i.account_id = 1
AND
a.id BETWEEN 2 AND 10000;
INSERT 0 49995
postgres=> select count(1) from item;
count
--------
249980
(1 行)
$ while : ; do ./pgping.sh ; done
2018年 8月 10日 金曜日 20:35:26 JST Connection established.
2018年 8月 10日 金曜日 20:35:29 JST Connection established.
2018年 8月 10日 金曜日 20:35:32 JST Connection established.
psql: サーバとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバが異常終了
したことを意味しています。
psql: サーバとの接続が想定外にクローズされました
～略～
2018年 8月 10日 金曜日 20:36:32 JST Failed to connect to database.
2018年 8月 10日 金曜日 20:36:36 JST Connection established.
$ psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"
psql (9.6.10, サーバー 9.6.6)
"help" でヘルプを表示します.
postgres=> select count(1) from item;
count
--------
249980
(1 行)
つまりコンソール上でフェイルオーバボタンが押せなくなるのは気にせずとも
フェイルオーバコマンドは実施できて直前に更新があってもフェイルオーバ後に反映されており、
同じコマンドでフェイルオーバフェイルバックが可能で強制オプションはないがエラーはでない
じゃー次はスキーマいっぱい追加しながらフェイルオーバしようかなとやってみたところ、
38こスキーマ追加したところでフェイルオーバコマンドが効いて残りはエラーが返り、
接続再開されたあとにスキーマの件数確認したところ効いたとこまで反映されていた。
gcloud sql instances failover komitestdb --quiet
Y押すのは --quietつけると回避できた。
postgres=> SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
datname | pg_size_pretty
---------------+----------------
cloudsqladmin | 7319 kB
hoge20 | 33 MB
template0 | 7145 kB
postgres | 49 MB
template1 | 7145 kB
zip | 7145 kB
pgis | 7145 kB
dvdrental | 15 MB
hoge1 | 33 MB
hoge2 | 33 MB
～略～
hoge96 | 33 MB
hoge97 | 33 MB
hoge98 | 33 MB
hoge99 | 33 MB
hoge100 | 33 MB
hoge101 | 49 MB
～略～
hoge137 | 49 MB
hoge138 | 49 MB
hoge139 | 49 MB
(146 行)
なんか共有ディスクっぽい動作だと思ったりしたけどリージョンまたぎだしよくわからないです。フェイルオーバーもフェイルバックも全部1分程で接続できるようになってました。すごい。（同接制限がPostgresqlのが低めですが）
単純な更新しながらは切り替わりできたとこまで反映されている（のでたぶん大丈夫）。
あとマニュアルみたところ、フェイルオーバの条件は以下の2通りになってたので監視してフェイルオーバ実行さす仕組みの実装はたぶん不要かも。
（60秒応答しない以外の条件を必要とするかどうか次第ですかね）
https://cloud.google.com/sql/docs/postgres/high-availability?hl=ja&_ga=2.157907708.-779814870.1500864087
フェイルオーバーがトリガーされるとき
次のいずれかのシナリオが発生すると、フェイルオーバーがトリガーされます。
・リージョン インスタンスが配置されているゾーンが停止した。
・リージョン インスタンスが約 60 秒間応答しない。
インスタンスは通常の動作状態（停止していないかメンテナンス中）でなければなりません。
手動でフェイルオーバーを開始することもできます。詳しくは、フェイルオーバーを初期化するをご覧ください。
あとPostgresqlのフェイルオーバレプリカにはアクセスするエンドポイントが表示されてなくてスレーブとして参照に使うということはMySQLだとできそうな検証記事を見かけたけどPostgresqlはそういうのはできないようだった。
・あとしまつ
・高可用性の設定をシングルにする
これをしないで停止すると別のリージョンでよみがえります。
・データベース停止
思い残しがなければ削除でも。