Tihiroの頭を休めるIT教室

少しだけ頭使って後は根性

PostgreSQLのテーブルに対応するデータファイルパスをSELECT文で確認する方法について

記事の概要

PostgreSQL 9.6のお話です。 現在のデータベース内に存在するテーブルのデータファイルがどこに格納されているかを確認する方法を解説します。 残念ながら全てのデータベース内のテーブルに対する方法は見つけきれませんでした。

PostgreSQLでのテーブルのデータファイル

Oracleとは違い、PostgreSQLはテーブルとデータファイルが「基本的に1対1」で存在しています。 ユーザーが作成したテーブルのデータファイルは、デフォルトでbaseディレクトリ内に格納されます。

システム全体で利用するテーブルのデータファイルはglobalフォルダに格納されます。 格納されるファイルの名前はPostgreSQLでの内部的なID(ファイルノード番号)となり、数値の羅列となっています。

ex1) base/1/12345
ex2) global/35265

「基本的に1対1」ということですので、「テーブル(1)対データファイル(複数)」になったりもするのですが、テーブルのデータサイズが上限(1GB)を超えると、対応するデータファイルが複数に分割されます。 その場合、ファイル名に「12345.1」「12345.2」というふうに「ファイル名.n」という形式で連番が設定されます。

テーブルのデータファイルを確認する

SQLで確認する

実際にテーブルとデータファイルの対応を確認する方法についてですがpg_relation_filepathという関数で、引数に指定したテーブル名に対応するデータファイルのパスを取得することができます。

SELECT文にしてみると

SELECT
    tablename
  , pg_relation_filepath(tablename::TEXT)
FROM
  pg_tables
WHERE
  schemaname != 'information_schema'
ORDER BY
  tablename

という感じですね。 ただし、pg_relation_filepathの引数にinformation_schemaに所属するテーブル名(sql_partsなど)を指定した場合、

ERROR: relation "sql_parts" does not exist

SQL state: 42P01

というエラーが発生しますので、WHERE句で除外するようにしています。

information_schemaに所属するテーブルに対してもデータファイルのパスを知りたい場合は

WITH
  pg_current_database AS (
      SELECT
          oid
        , datname
        , dattablespace AS dattablespace_oid
      FROM
        pg_database
      WHERE
        datname = CURRENT_DATABASE()
  )
SELECT
    CASE
      WHEN PG_TABLESPACE_LOCATION(pg_tablespace.oid) = ''
        THEN 'global/' || pg_relation_filenode(pg_class.oid)
      WHEN PG_TABLESPACE_LOCATION(pg_current_database.dattablespace_oid) = ''
        THEN 'base/'|| pg_current_database.oid || '/' || pg_relation_filenode(pg_class.oid)
      ELSE
           PG_TABLESPACE_LOCATION(pg_current_database.dattablespace_oid)
        || '/' || pg_current_database.oid
        || '/' || pg_relation_filenode(pg_class.oid)
    END AS "ディレクトリ"
  , pg_tables.tablename
FROM
  pg_tables
    CROSS JOIN pg_current_database
    INNER JOIN pg_class
            ON pg_tables.tablename = pg_class.relname
    LEFT OUTER JOIN pg_tablespace
                 ON pg_tables.tablespace = pg_tablespace.spcname
ORDER BY
    pg_tables.tablename

というSELECT文で求めることができます。

まとめ

今回の解説は「SELECT文でテーブルに対応するデータファイルパスを確認する」ということでした。

「oid2nameモジュールを使ったデータファイルから対応するテーブルを確認する方法」や「pg_relation_size関数を使ってテーブル容量を調べる方法」などについても、いずれ解説したいと思います。