PostgreSQLのデータベースクラスタのサイズを汎用ファイルアクセス関数で算出する

昨晩、U_ikki氏のPostgreSQLのデータベースクラスタのサイズをSQLで確認する という記事を見て、俺なら、データベースクラスタサイズ全体を算出するSQL関数を作るぜ、と思い作ってみた。

今回はplpgsql関数で作る

もちろん、C言語関数を使えば、こんなのはどうにでもなるのだが、それでは面白くないので、あえてplpgsql関数で作ってみる。

汎用ファイルアクセス関数

PostgreSQLにはディレクトリ内のファイルリストを取得したり、そのファイルのstat(サイズやアクセス日時、ディレクトリかどうかのフラグ)を取得できる汎用ファイルアクセス関数というSQL関数がある。

この関数のうち、pg_ls_dir(), pg_stat_file() という関数を使う。
pg_ls_dir()でファイルリストを取得。個々のファイルについて、ディレクトリがどうか判別し、ディレクトリならそのディレクトリ全体のサイズを取得する関数を再帰的に呼び出し、そうでなければpg_stat_file()でサイズを取得し、加算していくという単純な考え方で作ってみる。

やってみた

データベースクラスタ全体のサイズを求める関数名は get_database_cluster_size() という、何のひねりもない名前にした。

[nuko@localhost test]$ vi get_size.sql 
[nuko@localhost test]$ psql postgres -U postgres -f get_size.sql 
CREATE FUNCTION
CREATE FUNCTION
[nuko@localhost test]$ psql postgres -U postgres -c "SELECT get_database_cluster_size()"
 get_database_cluster_size 
---------------------------
                 294310856
(1 row)

はい。なんか数値が出ましたね。
で、そのデータベースクラスタのサイズを du -s -b で算出する。

[nuko@localhost 9.4]$ du -s -b /home/nuko/pgdata/9.4
294310856	/home/nuko/pgdata/9.4

一致しましたね。やったね♪

注意点

汎用ファイルアクセスはPostgreSQLスーパーユーザ権限でないと動作しないので、get_database_cluster_size()の実行にもスーパーユーザ権限が必要です。

SQL関数のコード

そんなに難しいコードではないけど、一応plpgsqlのコードを載せておきます。

CREATE OR REPLACE FUNCTION get_dir_size(path text)
 RETURNS integer
 LANGUAGE plpgsql

AS $function$
DECLARE
  files RECORD;
  stat RECORD;
  size INTEGER;
  dir_size INTEGER;
  target_size INTEGER;
BEGIN
  size = 0;
  FOR files IN SELECT pg_ls_dir(path) AS name LOOP

    -- RAISE NOTICE 'file name = %', path || '/' || files.name;

    SELECT * INTO stat FROM pg_stat_file( path || '/' || files.name );
    IF NOT FOUND THEN
      RAISE EXCEPTION 'file not found, %', files.name;
    END IF;

    target_size = stat.size;
    IF stat.isdir = 'f' THEN
      size := size + target_size;
    ELSE
      dir_size = get_dir_size(path || '/' || files.name) + target_size;
      size := size + dir_size;
    END IF;
  END LOOP;
  RETURN size;
END;
$function$;

CREATE OR REPLACE FUNCTION get_database_cluster_size()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
  size INTEGER;
  stat RECORD;
BEGIN
  SELECT * INTO stat FROM pg_stat_file('.');
  size = stat.size;
  size := size + get_dir_size('.');
  RETURN size;
END;
$function$;