Help us understand the problem. What is going on with this article?

PostgreSQLインデックスの作成日時と更新日時の取得について

はじめに

以前、「【PostgreSQL】テーブルのインデックスが壊れた話」の記事を書きました、そして数日前に1ヶ月振りにインデックスが壊れました。今回で7度目になります。

再度発生した時にはバックアップ後にREINDEXコマンドを仕掛けるように検討しようと思います。

対応策として、1日1回実行しているバックアップの際にインデックスの再構築をすることにしました。これを仕掛けて数ヶ月発生しないか様子を見ることにします。

インデックスの再構築

インデックスの再構築するには、REINDEXコマンドがあります。

psql -U test -c "reindex INDEX pk_replicate_que;"

インデックスの更新確認

インデックスの再構築の確認ってPostgreSQLのログには何も残らないので、どうすればいいんだろうと考えた時に以前書いた記事「PostgreSQLテーブルの作成日時と更新日時の取得について」を思い出しました。

この時はテーブルに限定していましたが、インデックスでも同様に出来ると考えました。

※schemaname = 'public' は任意のスキーマに変更してください。
※全てのインデックス結果が出力されるので、対象のインデックスのみにしたい場合は抽出条件を追加してください。

SELECT
   indexname
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION')) AS creation
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode)) AS modification
FROM (
    SELECT
        indexname
       ,(SELECT MAX(pg_ls_dir::int)::text
         FROM pg_ls_dir('./base')
         WHERE pg_ls_dir <> 'pgsql_tmp'
           AND pg_ls_dir::int  <= 
        (SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = schemaname AND relname ILIKE indexname)) AS folder 
       ,(SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = schemaname AND relname ILIKE indexname) AS filenode
    FROM pg_indexes
    WHERE schemaname = 'public'
) CTE

結果

indexname creation modification
pk_replicate_que 2019-08-08 10:26:14+09 2020-04-28 09:31:47+09

※ファイル作成時刻(creation)は変更される可能性が最も低いPG_VERSIONの最終更新時刻です。
※更新日時(modification)は、元のテーブルに登録、更新、削除があった場合にも更新されます。

テーブルの更新が無い時に、REINDEXコマンドを実行して更新日時が更新されていれば実行されたんだなと考えます。
注意として、オートバキュームでも更新日時が変更されてしまいますので直後に確認しないと意味がないです。

最後に

今回、1日1回実行しているバックアップの際にインデックスの再構築を仕掛けました。これでインデックスが壊れる現象が発生しなくなることを祈るのみです。

yaju
静岡県島田市在住のシニアSEがコンピューター、機械学習、Unity、数学について考える
http://yaju3d.hatenablog.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした