isucon10の予選に参加しました。

いい感じにスピードアップコンテスト、略してisuconの第十回目の予選に参加してました。 実は初参加でした。メンバーは私と、上司の@kazuhisa1976の2名です。

isucon.net

もう全然惜しくもないし、点数も800点くらいだったので全然ダメでした。あぁ～、技術力のないエンジニアであることが露呈したんじゃ～😭

とりあえずやっていたことと、反省点を上げていこうと思います。（敢えて、まだ他のブログとか見てません！自分が思いつく反省点を上げるため）

やったこと

いきなり修正するのはまず止めよう、問題点を一旦洗い出そうと話していたので、気になる点をtrelloに書いていきました。

1. まずはRubyの実装に変更（デフォルトがgolangのため）
2. サーバの構成の調査（cat /proc/cpuinfoと/proc/meminfo）。全部1コア2GB。
3. インデックスの状態を調査(primary keyのみindexあり)
4. コードを読んでインデックスが貼れそうなところに目星を付ける
5. nginxにリバースプロキシを仕込んでラウンドロビンにする
6. 3台目のDBに接続させるようにして、1,2のDBを落とす
7. 3台目のDBのスキーマファイルを修正して検索条件に使われるものにインデックスを貼るよう設定
8. csvのインサートが1件ずつなのをバルクインサートにする
9. MySQLのバージョンが5.7だったので、8.0にする。その際に、2台目で作業。
10. MySQL8.0のサーバに接続するように書き換え。
11. mysqld.cnfを雑に書き換えたら、めちゃくちゃスコアが下がったのでデフォルトに戻す。
12. なぞっての処理でN+1が発生しているので、位置に入っているか直接見られるようにするためにestateテーブルにgeometry型のカラムを追加
13. カラムを追加したせいでレスポンスが変わってしまい、select *できなくなり（大失態）クエリのselectを書き換えていく
14. 2台目をDB専用にして、mysqld.cnfを修正して調整…。
15. unicornのワーカー数を調整

これくらいだろうか…。600点台からなかなか抜け出せなくて、途中で700点台でまた止まって、ぎりぎり800点台に到達して終わった感じだったけれど、本戦参加者は2500とかいってたから全然あかんかったなぁと思う。

反省点

1. botを弾いてなかった

終わった後で気づいた。Bot弾いてない。Discordの会話を見る限りでは、nginxで弾けそうだった。

2. 直接サーバで作業しすぎた

各サーバのコードの同期をとるのが大変で、毎回そこで時間食ってたと思う。同期をとる仕組みを先に作るべきだった。

3. MySQLに詳しくなさ過ぎた

普段PostgreSQLを使っているので全然わからんかった…。めっちゃググった。

4. MySQL8.0にしたけれどよかったのか？

過去のやつでミドルウェアのバージョンアップで性能が向上するという話を見かけていたのでやったのだが、効果が今一つわからなかった（ベンチマークが不安定過ぎて5.7の状態で取れなかったため）。ベンチマークが取れるようになるまで待つという選択肢もあったが、時間がもったいないからエイヤでやってしまった。8.0からカラムにgeometry型が使えるとあったのでやったのだが、カラムを追加する必要はなかったかも…。

5. 位置情報は関数インデックスでよかったのではないか？

後で思ったこと。カラムを追加して位置情報で検索させることでN+1はなくなったのだが、カラムを追加したせいでレスポンスの形が変わってしまい、エラーになりつづけて結構時間をロスした。追加じゃなくて関数インデックスで位置情報を保持しておけば、5.7のままでもよかったかもしれないし、カラムを追加していないのでjsonのレスポンスの形も変わらず、エラーでハマることはなかっただろう…（多分）

6. viに慣れてなさすぎ

vi滅多に使わないので修正に時間がかかった。まぁ2で言ってることと被るけど。

7. New Relic使えるの知らなかった

isucon.net

使えたんかい！？😖事前情報のチェックをしてなさ過ぎた。始まる前から終わっていた感ある…。 パフォーマンスチューニングなのに運営のベンチマークの値に頼り過ぎていた。何をやればいいかが手探りすぎてダメだった。New Relic見れていたらだいぶ違っただろうなぁ…。

8. アプリをブラウザで動かしてなさ過ぎた

もっと自分の手でブラウザから動かして、実際に遅そうなところを見ればよかった。ブラウザで動かさずにコードのほうばかりに注目しすぎた。chromeのdevtoolを見ても遅いところはわかるし、ちゃんとアプリに向き合えてなかったと思う。

これから

もうほんまにダメダメだったので、他の人のブログを読みながら試していけそうなところとかを勉強させてもらおうと思います。あといつもPaaSのHerokuを使っているからミドルウェアの設定に弱すぎて個人的にダメだった。やはり何か自分でウェブアプリを運用してみるしかない。最近やってないからな…。Conohaでなんか作るかなぁ～。

スクラムで開発しようとしている

弊社の他のプロジェクトでスクラムを導入しているので、今期から、うちのプロジェクトでもスクラムやってみるか～と思って勉強中です。勉強中というかチケットの管理方法とかを本を読みながら模索してルールを作ったりしました。

元々ある課題

結論からいうと、開発チームがなかった。正確には、1人だからチームじゃなかった。

私が担当しているプロジェクトは、開発チームではなく、長いことほぼ1人で担当していたため、そもそもスクラムをやるというモチベーションがありませんでした。途中で開発に参加してくれる人もいたのですが、元々違うプロジェクトを任されていて兼任になるため、私的にもなんか気を遣ってしまっていました。兼務なので、そちらが忙しくなるとフェードアウトしていってそのまま…という感じ。で、ようやく昨年新卒の後輩さんが私の下についたので、徐々に教えている状態。まだ本格的にプロダクト開発には入ってないので、これから。

まぁ他にも色々と課題はあるのですが、それは一旦置いといて（書けない😛）、とりあえず2人体制になれたので、メイン開発を後輩さん、スクラムマスター兼開発サポート・裏方を私がやろうかなというところです。これがいい体制なのかどうかといえばよくはないのだが人数が足りないんだ！（スクラムの開発チームは3～9人とか…）

やったこと

本を読んだ

とりあえず、スクラムに関する知識が齧ってる程度だったので、指針になるものを仕入れようと思って本を読みました。参考にした本は、SCRUM BOOT CAMP THE BOOKです。

SCRUM BOOT CAMP THE BOOK【増補改訂版】 スクラムチームではじめるアジャイル開発

非常に面白くて読みやすいし、こういう時どうするの？っていうのが載っててよかったです。これを読んで、うちのプロダクトは月末リリースなので、スプリント期間を3週間、リリーススプリントを1週間にしようという考えになれました。また何度も読み返すことになりそう。

チケットを整理した

元々、顧客からの要望やアンケートの結果、運用の課題などについてはRedMineのチケットにして管理していたのですが、開発上の課題はそこには書かず、GitHubのissueで管理していました。まぁそれは当初は、開発上の課題は見せなくてもええやろという感じだったのですが、それだと要望と開発の課題のどちらが重要かという判断が難しいし（両方見えているのは私だけになるので私の判断になる）、全然RedMineのチケットの消化が進んでいないときは、「開発（というか私）は一体何やってるの？🤔」っていうのが関係者に見えないのが辛いなと思ってました。いや、前期はRails6にアップグレードしたり、webpackerに移行したり、やってるんやで…😭

スクラムでスプリントを回そうと思ったら、ベロシティの計測しなければ開発の予測が立てられませんし、開発の課題も関係者に認識してもらうためにRedMineのチケットとして登録しました。GitHubからはissueからRedMineのチケットにリンクを貼るようにしました。その際に、「RedMineのチケットへのリンクが面倒やなぁ～」と悩んでいたら、同僚がGitHubのAutolink referenceの記事を書いてたのでそれを参考にさせてもらいました！🙏

ryosms.livedoor.blog

これを設定すると、例えばGitHubのissueでTICKET-12345と書くと、自動的にRedMineのチケット番号12345にリンクが貼られるようにできます。圧倒的に楽！見た目も綺麗！👍

チケットのルールを定義した

スクラムだと、課題はプロダクトバックログと呼び、プロダクトバックログに対するタスクはスプリントバックログと呼びます。

最初はRedMineのチケットの呼び方を「チケット」と変えずに、関係者には軟着陸しようかと考えていましたが、多分ごちゃごちゃしてくるなと思ったので、ルールをRedMineのWikiに書きました。その時に、チケットはプロダクトバックログとスプリントバックログの2種類になりますが、皆さんは今までのチケットの運用と同様にプロダクトバックログのみを意識してもらえばいいです、開発者が何しているかはスプリントバックログを見ればわかります、と言えるようにしました。

要は、「言葉の定義や呼び方は変わるけれど、皆さんのチケット運用方法はほぼ変わりませんよ～😀」ということで、大きな変化ではないと思ってもらえるようにしました。まぁ受け手がどう感じたかはわかりませんが…。

あとは、開発側はTiDD（チケット駆動開発）にしたいと思っていたので、スプリントバックログには必ず親チケットとしてプロダクトバックログのIDを付けるように！というルールにしました。これで、どのプロダクトバックログを実現するために何をやっているのかが明白になるはず。また、スプリントバックログに関しては、ステータスを簡潔にするために、

1. 新規
2. 対応中
3. 終了

の3つだけにしました。

チケットのテンプレートを作った

今まではチケットのフォーマットが特に決まっていなかったので（こう書いてほしいという指針はあった）、書く人によっては情報が足りなかったり、以前に却下された要望が何度もゾンビのように復活してきたりということがありました。そこで、テンプレートを作りました。

テンプレートでは、ユーザーストーリー形式で書いてほしいことを伝え、フォーマットとサンプルを付けているのと、もう対応しないことになっている件をリスト表示しておきました。

後輩とプランニングポーカーした

した、と過去形にしていますが、正確には、している最中です。基本的に作業をしてもらうのは後輩さんだけれど、まだ実際に開発していないので、プロダクトバックログの内容を私が製品の画面のここの修正のこと、と説明して、それからプランニングポーカーでお互いに投票して、数値が違えば意見を聞いたりして、基本的には後輩さんの主張したストーリーポイントを設定していく、という感じ。

今は在宅勤務することが多いので、プランニングポーカーもオンラインでやっています。これもまた同僚が教えてくれたやつですが、hatjitsuというプランニングポーカーをするためのサービスがあって便利😁

hatjitsu.toolforge.org

これからどうするか？

現時点でも、後輩さんにメインをやってもらうと決めたので、開発の課題なのに後回しになっていたところ（ミドルウェアのアップデートとか、リファクタリングとか）に切り込んでいきやすくなったので、現時点でもちょっと効果は実感できてます。 とはいえ、色々あってまだ準備段階。まだちゃんとしたスプリントには入れてません…。とりあえずスプリントが開始できたら、関係者からフィードバックをもらって、修正していきたいと考えてます。

aws-sdk-s3を使いつつ、timecopで時間をずらす

ストレージを伴うテストのためにminioを使うようにしようとしたのですが、設定をしただけではうまく動きませんでした。

timecopを使っているところで、aws-sdkがエラーを起こしました。timecopは時間を固定したり過去・未来に移動したりするライブラリです。

https://github.com/travisjeffery/timecop

エラーメッセージは以下のようなものでした。

Aws::S3::Errors::RequestTimeTooSkewed: The difference between the request time and the server's time is too large.

このメッセージでググるとわかるのが、アプリケーションサーバとS3のサーバ（minio）の間で時刻が離れすぎているから起きる例外でした。timecopで3年前とかに移動しているため、当然起きました…😩

解決方法

解決方法は、aws-sdk-s3から時刻の確認をするときだけ、Time.nowが現在の時刻を返せばいいというアプローチでした。

参考にしたのは以下のページです。

qiita.com

このページの、例外に備えよう、という見出しのところに書いてあります。

まんまでは動かない

しかし、記事自体が3年前のものなためか、aws-sdkのバージョンが変わり、まんまでは動かなくなっていました。

# aws-sdk-s3では動かないので注意
class Time
  class << self
    def now_wrap
      if (caller || []).first.match('aws-sdk')
        now_without_mock_time # return real time
      else
        mock_time || now_without_mock_time # return mock time or real time
      end
    end
    alias_method :now, :now_wrap
  end
end

修正

置き換えなければならない箇所の呼び出し元のパス情報が、aws-sdkではなく、aws-sigを含むものに変わっていたので、修正しました。

class Time
  class << self
    def now_wrap
      # p caller.first if caller.first.match('aws') # awsという文字列でなんとなくあたりを付けた
      # すると、aws-sigというものを発見
      if (caller || []).first.match('aws-sig') # aws-sdkをaws-sigに変更
        now_without_mock_time # return real time
      else
        mock_time || now_without_mock_time # return mock time or real time
      end
    end
    alias_method :now, :now_wrap
  end
end

修正後、テストを実行したところ、timecopを使っている箇所でもテストが成功しました👍

他に試したこと

最初は、エラーメッセージから、アプリケーションサーバとS3サーバの時刻の比較をしないで済むオプションがあるのではないか？と考え、S3クライアントにそれっぽいものを設定してみたのですが、結局ダメでした。

s3_client = Aws::S3::Client.new(
    access_key_id: Rails.application.credentials.aws_access_key_id,
    secret_access_key: Rails.application.credentials.aws_secret_access_key,
    region: Rails.application.credentials.aws_region,
    correct_clock_skew: false, # default true
    retry_mode: 'standard', # default 'legacy'
)

UNIXという考え方を読んだ

タイトルと本の見た目から、なんとなく難しそうだからと読んでなかったんじゃないかと思うんだけれど、お薦めの本として紹介されていたので読んでみた。感想としては、もっと若い時に読んでおくべき本だったなぁ～と思った。私、もうオッサンなので…。いや、オッサンだと学びが少ないとかではなくて、こういう考え方を若いうちから取り入れておけば、作ってきたアプリケーションやライブラリにもこの考え方を適用することができただろうから、柔軟なものを作ることができただろうになぁ…というやつです。

そして、この考え方を知っていれば、なぜこのライブラリはこんな作りになっているのか？とか、どうしてシェルだとパイプを使ってゴニョゴニョせにゃならんのか？という理由もわかる。わかると「便利だな」と思うんだけれど、わからないと「なんでこんなに組み合わせなければならないんだ！？」と思ってしまって苦手意識がついてしまうんじゃなかろうか？まぁ実際に苦手意識はかなりある。

効率よりも移植性を優先する

最も読んでよかったなと思ったところは「効率よりも移植性を優先する」というところだった。綺麗なコードや効率のよい処理のことはよく意識するけれど、移植性についてはあまり意識していなかったかもしれない。この章は示唆に富んでいて、耳が痛い。「プログラムを速くすることに時間をかけない」とか、「最も効率のよい方法は、ほとんどの場合移植性に欠ける」とか。

「来年になればもっと速いハードウェアが出てくるから、そこでもそのプログラムが実行できれば勝手に速くなる（意訳）」と書かれていた。本が書かれた当時はムーアの法則がめちゃくちゃ効いてた頃だから、それは納得なのだが、とはいえ現代においてもそれは言えるのかなと思う。クラウドにおいても、移植性が高ければ、スペックの高いインスタンスを立てればいいわけだ（いわゆる金の弾丸で解決するやつ）。

すべてのプログラムをフィルタにする

この章も、なるほどなと思った。もっと標準入出力(stdin, stdout)を使って、処理の結果をどうするかはユーザーに委ねたほうがいいなと思った。そうすれば、ユーザーの判断で様々な処理を組み合わせることができる。結果をファイルに出したいのであれば、 > hoge.txt とすればいいわけだし。

処理の成果物はなんとなくファイルにしたほうがいいよなぁ～と思っていた節がある。でもまぁそれをどうするかはユーザに委ねるべき。

pecoを使って処理をしたいときがあって、そのために標準出力に出す、ということはしていた。

フィルタにするためには、標準出力に出すことと、標準入力から受け取れるようにすること。こういうのが理解できると、gulpとかでstreamを使って処理を順々にしていくのはこの考え方なんやなってのがわかる。webpackのプラグインも同じ。だから、処理の流れの途中にやりたいことを差し込める。

他の学び

梃子の効果なども面白かった。他の考え方（移植性を重視とか）の部分と切り離せない部分はあるが、Cで書くよりもシェルスクリプトにすれば、大概の環境で動くし速度も速い。汎用的なものにしておけば、どこでも動くから再利用性が高まる。

90%の解を目指す、とか、部分の総和は全体よりも大きい、のところもウーンと唸りながら読んだ。疎結合のプログラムの組み合わせでアプリケーションを作るのと、単体のプログラムでアプリケーションを組んだ時の話では、マイクロサービスとモノリシックなサービスを連想した。マイクロサービスはUNIXの考え方に基づいたものなのだなと思った（マイクロサービスアーキテクチャに対する知識はほぼないけれど…）。

疎結合にしておけば、より効率のよいプログラム実装が組めれば、そこだけを差し替えることが可能になる。不要になれば外すこともできる。しかしでかいプログラムになっていると、外せない…。今も絶賛それで苦しんでいるところがある…。

感想

感想、この記事の最初に書いちゃってるんだけれど、この考え方を最初から知っていれば、なんでUNIX・Linuxの環境はこういう書き方なんだろうか？とか、シェルわけわからん～みたいな苦手意識は薄まるのではないか？と思う。そして、UNIX的考え方を持っていれば、ライブラリを作るときにも汎用的に作ることができそう。この考え方はコンピュータで仕事をする上でベースとなっているし、普段利用しているものはこういう思想で作られているのだなとわかっておいたほうが絶対にいい。早い段階で知っておくべきもんだなと思うので、もっと布教していきたい。

UNIXという考え方: その設計思想と哲学

OSS-DB Gold Ver.2.0不合格体験記

OSS-DB Gold Ver.2.0を受けてきたのですが、落ちました😭完全に準備不足でしたが、まぁまぁ問題集が解けるようになってきていたので、運が良ければ合格するんじゃないかな？と思っていたのですが、蓋を開けてみると56点という超微妙なレベルで（ちなみに合格ラインは70点）、あと5問は正解しないと合格しませんでした…。

なんで落ちたのかとか、そのあたりを考察しておいて、これから受験する人へのヒントや再受験するであろう自分自身の今後の試験勉強にも活かそうと思います。

OSS-DB Goldについて

OSS-DB Goldは、PostgreSQLの運用・設計・パフォーマンスチューニング・障害対応などに対して深い知識があるかどうかを問われる試験です。普通の開発者であれば、OSS-DB Silverを取得していれば十分かと思います。GoldはDBA(データベース管理者)の領域を問われる試験なので、実際に運用している等の経験がないと難しいです（落ちた私が言うのもなんですが…）

使用教材

主に使ったのは、OSS-DB Gold認定教材と、内部構造本と、Webのサンプル問題でした。あとはVMにインストールしたPostgreSQLで実際に設定したりSQLを実行したりして試したりなど…。

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

内部構造本はめちゃくちゃ勉強になるので4周くらい流し読みはしていました。まぁまぁ分かった気になっていただけに、落ちたのはショック…😥この本は最高なので私が悪いのです。

LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト

逆に微妙なのが、OSS-DB Gold認定教材のほうで、これはまぁVer.1.0対応の本なので仕方がないのです…。因みに買ったのはVer.2.0が出る前で、Goldを受けようかなと本腰を入れようとしたらVer.2.0がアナウンスされてしまい（2019年4月頃だったはず）、今更1.0を受けてもあかんな…と思い、受験を断念してました。それでまぁいつか2.0の本が出るだろうと思っていたら、全然出ないのでとりあえず受けてみたという感じです。 この本は受験対策の講座のパワポ資料とその解説っぽい感じで、勉強にはなるのですが、同じことがページの上下で書いてあったりしてやや冗長…。しかし、問題集が付いているので、その点は評価できます！でも問題のバリエーションがやはりVer.1.0の頃のものなので少ないかなと思います。

早くVer.2.0対応版を出してください！😣

oss-db.jp

Webのサンプル問題は2周くらいやって、正答率7割くらいにまではなってたので、正答率7割で解説読んで勉強したから次は8割くらい取れるだろうからイケるに違いないという謎理論で受験に臨みました。

勉強期間

勉強期間は1か月半ですが、家だと全然集中して勉強できなかったりして（子供が寝ない…）、会社で勉強したり、試験前は妻が協力してくれて外出した先で勉強したり等していました。合計時間はわかりません…。

実際に受験してどうだったか

認定教材・内部構造本・Web問題でも見たことのないような問題が結構出てきて、全然わからないものがまぁまぁ出ました。とはいえ、25問くらいは見たことあるようなものばかりだったかと思います。

私が苦手なのが、答えを複数選ぶ問題なのですが、1つはわかるんだけれど、あと1つが2択まで絞り込めるんだけどわからない…というやつ。結局こういうのをいくつも落としてたんだろうなぁ…。

なぜ落ちたか？

正直なところ、問題集に頼り過ぎました(Web問題も含む)。問題集の内容を押さえていれば合格できるんじゃないかなと思い、問題集を中心にして勉強していました。まぁ本格的に勉強する時間があまりとれなかったからという言い訳をしておきます…。

どう勉強すればいいと思ったか？

今思い返すと、試験の出題範囲になっているキーワード類を元にPostgreSQLの公式ドキュメントを熟読するべきだったと思います。

oss-db.jp

全然見たことないキーワードの問題めっちゃ出た…と思っていたのですが、試験概要にはキーワード、ちゃんと書いてあるんですよ。そのあたりを放置して内部構造本でなんとなく全体的に押さえれば、なんとかなるんちゃうかなと高を括っていた面がありました。実際にどんな問題が出たかは書けないのですけど、これから受ける人はこの試験概要に書いてあるキーワードを、一通りちゃんと調べておいたほうがいいです。かなり多いけど！

落ちたけれど勉強してよかったこと

パフォーマンスチューニングや、実行計画の読み解き方等は結構わかるようになったかと思います。まぁ内部構造本を見ながらになるとは思いますけど…。しかし、実行計画の結合の仕方の種類について等はちゃんと勉強することができてよかったなと思います。どの結合が速いか、ソートのためにインデックスを貼るべきか等も、理由がわかるようになりました。期待した結合が選ばれてないようだったらWORKMEM増やしてみては？とか言えるようになりましたし。

再受験するか？

先ほど書いたように、試験範囲に含まれるキーワードを全部調べ直してから、再受験しようと思ってます。ただ、10月にDBスペシャリストを受ける予定なので、ひとまずは頭を切り替えてそれの対策に臨みます。その試験が終わった後にでも、再挑戦しようと思います。まぁ年末くらい？

コンテナのTimezoneは統一しよう

Railsアプリの開発中に、ちょっと物によっては時間がかかる処理があったので、ActiveJobに処理を移行させたのですが、perform_nowとperform_laterでデータを保存した時のcreated_at等が9時間ずれる現象が発生しました。

結論から書くと…

結論は、rails serverしているコンテナのTimezoneは'Asia/Tokyo'になっていたのですが、ActiveJobを処理するresqueを動かしているコンテナのTimezoneが未設定でした。

未設定だと、UTCになるのですが、Railsアプリケーション的には

config.active_record.default_timezone = :local
config.time_zone = 'Tokyo'

となっていたので、Time.zone.nowなどをActiveJobで行ってもずれていることもなく…。データ保存後に再取得してcreated_atを表示しても正しい値を出しているのに、rails sをしているコンテナ側からアクセスすると時間が9時間前になっているという状況で本当に参りました。

対処

docker-compose.ymlのresqueのところの環境変数TZに値を設定しました。

services:
  # 略
  rails:
    # 略
    environment:
      TZ: "/usr/share/zoneinfo/Asia/Tokyo"
      # 略

  resque:
    # 略
    environment:
      TZ: "/usr/share/zoneinfo/Asia/Tokyo"
      # 略

PITRを試してみた

PITRとは、ポイント・イン・タイム・リカバリーと言って、指定した時間まで遡ってリカバリすることができます。pg_dumpだとそのときのスナップショットになるので、実際に稼働しているサービスが突然落ちた時などに、直前まで復旧することができませんが、PITRならば、直前まで復旧することができます！ただし、設定方法と復旧作業が複雑です。

本を読みながらやってみました。

［改訂新版］内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 Software Design plus

基本方針

試してみたいだけなので、まずはpgbenchで作ったデータベースを対象にやってみることにしました。

1. pg_start_backupを実行
2. rsyncでデータベースクラスタをバックアップする
3. pg_stop_backupを実行する
4. データを沢山登録する
5. 時間を空ける（ここまでリカバリさせるため）
6. 更にデータを登録する
7. dbを停止させる
8. リカバリしてみる

postgresql.confを編集

まず、postgresql.confを編集しました。 archive_modeをonにすることで、WAL(Write Ahead Log)を作成させます。WALは、データ更新系のログで、DBに反映する前に作られるやつです。リカバリ時には、ベースバックアップからバックアップ時点まで戻し、そこからWALを再適用することで復旧させていきます。しかし、WALはある程度までしか保存されず、一定数になると削除されていきます。それを消される前にアーカイブするためにコマンドを指定するのが、archive_commandです。cpコマンドでWALアーカイブを保存します。本来は、データベースクラスタと異なるディスク上に保存しておくべきものですが、試したいだけなので同じディスクに置いてます。

archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archivedir/%f'

設定後、postgresqlを再起動させました。設定ファイルの再読み込みだけでもよかったかも…。

pg_ctl restart

PITRやっていく

まずデータ投入

最初にデータ投入しときましょう。pgbenchでやります。デフォルトの初期化です。pgbench_accountsに10万件登録されます。

pgbench -i benchdb

pg_start_backupを実行

次にバックアップの開始のラベルを作成します。これは結構時間がかかるときもあります。

psql -c "SELECT pg_start_backup(now()::text)"

ベースバックアップを保存する

ベースバックアップは、rsyncを使うのが確実だそうです。pg_walディレクトリ以下にWALが作成されるのですが、どうせ後でコピーするので、ベースバックアップからは外します。また、postmaster.pidがあるとpostgresqlが起動しない場合など、トラブルになるかもしれないので、これも除外します。

 rsync -av --delete --exclude=pg_wal/* --exclude=postmaster.pid $PGDATA/* /var/lib/pgsql/backup

pg_stop_backupを実行

ベースバックアップを取り終えたので、pg_stop_backupを実行します。

 psql -c "select pg_stop_backup()"

これでバックアップ体制は出来上がってるので、データを登録していっても大丈夫なはず。

データを登録する

pgbenchで作られたテーブルに、データを登録していきます。大量にデータを登録するときに便利なgenerate_seriesを使います。10万1件目から20万件登録します。

INSERT INTO pgbench_accounts (aid, bid, abalance, filler) SELECT i, 1, 0, '' FROM generate_series(100001, 200000) AS i;

これを行うことで、大量のWALが発生します。収まらなくなったWALはarchive_commandで指定したコマンドの通りにコピーされていってます。

時間を空ける

空白時間を作ります。この時間をが何時なのかを記憶しておきます。

-bash-4.2$ psql -c "select now()"
              now
-------------------------------
 2020-08-16 17:59:10.852806+00
(1 row)

戻す時間の目安を2020-08-16 18:00:00 UTCとすることにしました。2～3分待ちます。

更にデータを登録する

20万1件目から30万件登録します。これで更にWALが作られます。

INSERT INTO pgbench_accounts (aid, bid, abalance, filler) SELECT i, 1, 0, '' FROM generate_series(200001, 300000) AS i;

postgresqlを停止する

killで止めたほうが本当の障害ぽさがあるとは思いますが、普通に止めました。

pg_ctl stop

これでリカバリを実行する条件が整いました。

リカバリする

WALをコピーする

まずは、落ち着いてWALをコピーします。WALアーカイブに移動していないものを取り出します。

cp -r ${PGDATA}/pg_wal /tmp/

元のデータベースクラスタを別名で残す

次に、元のデータベースクラスタを別名で残しておきます。消してしまうと、リカバリが失敗したときに問題の調査ができなくなるので、残しましょう。

mv ${PGDATA} ${PGDATA}.temp

ベースバックアップから復元する

rsyncでベースバックアップをデータベースクラスタを復元します。

rsync -av /var/lib/pgsql/backup/* ${PGDATA}

これでベースバックアップ時点までは戻せますが、まだWALがないので後で登録されたものが存在しない状態になります。

WALを戻す

さきほどtmpにコピーしたWALを戻します。

cp /tmp/pg_wal/* ${PGDATA}/pg_wal/

recovery.confを作成

recovery.confを作っていきます。最小限の設定は、restore_commandでWALアーカイブを取ってくる命令を書いておくことです。

vi ${PGDATA}/recovery.conf

内容は以下の通り。まずは、DBを停止させる直前まで全部戻すほうを確認します。

restore_command = 'cp /var/lib/pgsql/archivedir/%f %p'

PostgreSQLを起動

これで、後はPostgreSQLを起動すると、recovery.confがあるため、リカバリーモードで起動し、WALをWALアーカイブから順々に適用していくようになります。

pg_ctl start

これだと、全てのWALが適用されるため、pgbench_accountsには、30万件のデータが復元されます。確認してみます。

benchdb=# select aid from pgbench_accounts order by aid desc limit 5;
  aid
--------
 300000
 299999
 299998
 299997
 299996
(5 rows)

バッチリです！

指定時刻まで復元する

ところで時間を空けておいたので、今度はそこまでの復元をやってみましょう。postgresqlを停止して、データベースクラスタを削除してまた復元作業をやります。一部はもうやってるので端折ります。

pg_ctl stop
rm -rf /var/lib/pgsql/data # もうdata.tempにバックアップしてあるので先ほど復元したものは消す
rsync -av /var/lib/pgsql/backup/* ${PGDATA} # もう一度ベースバックアップを持ってくる
cp /tmp/pg_wal/* ${PGDATA}/pg_wal/ # もう一度WALを持ってくる
vi ${PGDATA}/recovery.conf

recovery.confの内容は、以下の通り。recovery_target_timeで、適用させたいところまでの時刻を指定します。この時間までであれば、データは30万件ではなく、20万件のはずです。

restore_command = 'cp /var/lib/pgsql/archivedir/%f %p'
recovery_target_time = '2020-08-16 18:00:00 UTC'

PostgreSQLを起動します。

pg_ctl start

では、psqlで確認してみます。

benchdb=# select aid from pgbench_accounts order by aid desc limit 5;
  aid
--------
 200000
 199999
 199998
 199997
 199996
(5 rows)

20万が最後のaidになってました！完璧です！

まとめ

手順が少々複雑なので、とりあえず試しておいたほうがやっぱりいいなと思いました。 本当にPITRをやろうと思ったら、WALのディレクトリや、WALアーカイブのディレクトリは外部ストレージに保存するべきだし、バックアップも多重化する必要があるので、もっと複雑になるかと思います。でも決まった時間に戻すことができるので、オペレーションミスでうっかりデータを削除してしまっても、PITRならば、戻せます。マネージドデータベースを使えない場合はやっておく価値あるでしょう。