PostgreSQLのデータファイル名を確認する方法

gistfile1.txt

PostgreSQLでテーブルのデータファイル名を確認する方法。

PostgreSQLでは、1テーブル/1インデックス毎に1ファイルが割り当てられる。
ディスク容量にあまり余裕がない場合などに、大きなファイルを持つテーブルを、
別ディスクに移動させたければ、
    ALTER TABLE 表名 SET TABLESPACE テーブルスペース名;
などとして別領域へ移動させたい場合がある。

しかし、ファイル名には一意な数値が使用されているため、どのファイルがどのテーブルの
ものなのかパッと見では判別できない。

以前、テーブルのファイル名を確認する方法を調査した時には以下のような方法を見つけた。
    http://hitai.blog72.fc2.com/blog-entry-86.html

内容は以下のような感じ。

    １．データベースのディレクトリ名を調べる
    SELECT datid, datname FROM pg_stat_database;

    datid | datname
    -------+------------
    10793 | postgres
    16406 | testdb2
    16542 | testdb
    1     | template1
    10792 | template0

    例えばtestdb2の場合、$PG_DATA/base/16406/ がファイルの格納ディレクトリということになる。

    ２．テーブルのファイル名を調べる
    SELECT relid, relname FROM pg_stat_all_tables;
    
    relid | relname
    -------+-------------------------
    16492 | customer3
    2601  | pg_am
    10308 | pg_toast_2619
    10750 | pg_toast_10748
    2610  | pg_index
    16410 | emp
    2612  | pg_language
    16436 | orditems
    16427 | ord
    2620  | pg_trigger
    1214  | pg_shdepend
    2608  | pg_depend
    16456 | customer
    ・・・

    例えばemp表の場合、$PG_DATA/base/16406/16410 というファイルだということになる。

    ・・・ということだったのだけど、これはDBのデフォルトテーブルスペースに格納されたテーブルの場合。
    テーブルや索引毎に個別にテーブルスペースが指定されている場合には、当然そのテーブルスペースの配下に格納される。
    また、テーブルスペースを移動させた場合などに、ファイル名が変わってしまうことがあるが、ファイル名とrelidは、
    連動していないため、この方法ではファイル名が追えなくなってしまった。

    ということで、手順を再構築してみた。


    ケース１：テーブル名から、ファイル名を確認したい場合

    １．そのテーブルのテーブルスペースを確認
    select schemaname, tablename, tablespace from pg_tables where tablename='テーブル名';

         schemaname     |        tablename        | tablespace
    --------------------+-------------------------+------------
     u01                | emp                     |

    tablespace列が空欄の場合はDBのデフォルトディレクトリ。
    tablespace列にテーブルスペース名があれば、該当テーブルスペースに格納されている。
    デフォルトディレクトリの場合はステップ２へ。
    特定テーブルスペースの場合はステップ３へ。

    ２．DBのデフォルトディレクトリを確認
    SELECT datid, datname FROM pg_stat_database;

    datid | datname
    -------+------------
    10793 | postgres
    16406 | testdb2
    16542 | testdb
    1     | template1
    10792 | template0

    datid列がディレクトリ名を表すので、
    $PG_DATA/base/<datid列>/
    が該当ディレクトリとなる。

    テーブルのファイル名を確認するためにはステップ４へ。


    ３．テーブルスペースのディレクトリを確認する
    SELECT * FROM pg_tablespace;

      spcname   | spcowner | spclocation | spcacl | spcoptions
    ------------+----------+-------------+--------+------------
     pg_default |       10 |             |        |
     pg_global  |       10 |             |        |
     test       |       10 | /data/test  |        |

    テーブルのファイル名を確認するためにはステップ４へ


    ４．テーブル/インデックスのファイル名を確認する
    select relname, relfilenode from pg_class where relname='オブジェクト名';

     relname | relfilenode
    ---------+-------------
     t1      |       16943

    relfilenode列がファイル名を表す。
    ということで、DBのデフォルトディレクトリ、またはテーブルスペースのディレクトリの配下の
    relfilenodeを探せばよい、ということになる。


    ケース２：ファイル名からテーブルを特定する

    １．ファイル名の確認
    select * from pg_class where relfilenode='nnnnn';
    ※nnnnnにファイル名を指定

    relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode |
    ---------+--------------+---------+-----------+----------+-------+-------------+---------------+-
     t1      |        16414 |   16572 |         0 |    16412 |     0 |       16943 |         16567 |


    ２．ネームスペースの確認
    必要であればネームスペースの確認（同じ名前のオブジェクトが複数あり得る場合）
    select * from pg_namespace;

          nspname       | nspowner |               nspacl
    --------------------+----------+-------------------------------------
     pg_toast           |       10 |
     pg_temp_1          |       10 |
     pg_toast_temp_1    |       10 |
     pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
     information_schema |       10 | {postgres=UC/postgres,=U/postgres}
     u01                |    16412 |

    ステップ１のrelnamespaceと同じnspname列の行が、該当ネームスペース。


    という感じ。


    で、これがめんどくさい場合は、上のリンク先でもつかった oid2name ユーティリティがある。
    こっちはpsql上ではなく、OSコマンドとして使う。

    > oid2name --help
        oid2name helps examining the file structure used by PostgreSQL.
        
        Usage:
          oid2name [OPTIONS]...
        
        Options:
          -d DBNAME    database to connect to
          -f FILENODE  show info for table with given file node
          -H HOSTNAME  database server host or socket directory
          -i           show indexes and sequences too
          -o OID       show info for table with given OID
          -p PORT      database server port number
          -q           quiet (don't show headers)
          -s           show all tablespaces
          -S           show system objects too
          -t TABLE     show info for named table
          -U NAME      connect as specified database user
          -x           extended (show additional columns)
          --help       show this help, then exit
          --version    output version information, then exit
        
        The default action is to show all database OIDs.
        
        Report bugs to <[email protected]>.

    ということで、例えばこんな感じ。
    oid2name -d testdb -H localhost -p 5432 -U postgres -x -i

    From database "introsql01":
      Filenode    Table Name    Oid  Schema  Tablespace
    ---------------------------------------------------
         16943            t1  16570     u01        test
         16946        t1_idx  16946     u01        test
         16903           emp  16438     u02  pg_default
         ・・・

    これをみると、t1はテーブル作成直後は16570というoidと同じファイル名だったのが
    今は16943というファイル名に変わっていることがわかるし、
    どのテーブルスペースなのかも確認できるので、あとは
    select * from pg_tablespace;
    または
    select * from pg_stat_database;
    でディレクトリも確認できる。

    テーブル名から追いたい場合は -t テーブル名 オプションで絞込みが可能
        oid2name -d testdb -H localhost -p 5432 -U postgres -t テーブル名
    でいいし、ファイル名から追いたい場合も
        oid2name -d testdb -H localhost -p 5432 -U postgres -x | grep ファイル名
    でよい。

    ただしSJIS以外のマルチバイド文字を使ったテーブル名があり、Windows環境の場合は文字化けが発生するので
    テキストファイルにリダイレクトして、エディタで見るのがよいかも。
        oid2name -d testdb -H localhost -p 5432 -U postgres -x > ファイル名

    多分普通にインストールしたPostgreSQLならoid2nameユーティリティがいるはずだけど、
    もしいないようであれば、http://hitai.blog72.fc2.com/blog-entry-86.html のように
    適当なソースからビルドしたバイナリが使えるはず。