Home > 2012年08月

2012年08月 Archive

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
  • Comments (Close): -
  • TrackBack (Close): -

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

  • Posted by: Nakunaru
  • 2012-08-16 Thu 22:28:51
  • PostgreSQL

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

などとして別領域へ移動させたい場合がある。

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

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



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

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

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

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

2.テーブルのファイル名を調べる

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は、連動していないため、この方法ではファイル名が追えなくなってしまった。

ということで、手順を再確認してみた。



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

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

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

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

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

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

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

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


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

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

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


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

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

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


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

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

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


2.ネームスペースの確認
必要であればネームスペースの確認(同じ名前のオブジェクトが複数あり得る場合)
    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 |

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

Index of all entries

Home > 2012年08月

タグクラウド
Categories
Monthly
Recent Entries
Recent Comments
Recent Trackbacks
Appendix

Nakunaru

    Author:Nakunaru

    データベース(ORACLEとかSQL ServerとかDB2とかMySQLとか)とか技術者教育とかプログラムとか。
    気になる技術を少しずつ勉強していきます。


Return to page top

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。