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

/
が該当ディレクトリとなる。

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


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

    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 .

ということで、例えばこんな感じ。

    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 のように適当なソースからビルドしたバイナリが使えるはず。