Home > PostgreSQL Archive

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

PostgreSQLのデータファイルの判別方法

  • Posted by: Nakunaru
  • 2011-06-16 Thu 23:04:10
  • PostgreSQL
PostgreSQLでは、各DBのディレクトリは$PGDATAの配下に作成されます。
(デフォルトではインストールdirのdata配下)

この時、各ディレクトリは名前が数値になっているため、どのDBのディレクトリなのか名前だけでは判別できません。
そこでちょっと調べてみました。


・見つけた判別方法1
http://d.hatena.ne.jp/hogem/20090620/1245431458#


SELECT datid, datname FROM pg_stat_database;

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


※datidがDBのディレクトリ名と一致する


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
・・・


※relidが各テーブルのデータファイル名と一致。
※pg_stat_databaseの配下から該当ファイルを探せばよい。
※INDEDXも同様にpg_stat_all_indexesで探すことができる。


・判別方法その2
oid2name ユーティリティを使用する。
http://www.postgresql.jp/document/8.4/html/oid2name.html

DBのディレクトリ判別


oid2name -U psadmin -P psadmin

Oid Database Name Tablespace
----------------------------------
16406 testdb2 pg_default
10793 postgres pg_default
10792 template0 pg_default
1 template1 pg_default
16542 testdb pg_default


※oidがディレクトリ名と一致


oid2name -U psadmin -P psadmin -d testdb2

Filenode Table Name
------------------------
16498 クワオメ・゙・ケ・ソ
16414 クワオメ・゙・ケ・ソ
16456 クワオメ・゙・ケ・ソ
16532 クワオメ・゙・ケ・ソ」イ
16448 クワオメ・゙・ケ・ソ」イ
16490 クワオメ・゙・ケ・ソ」イ
16534 クワオメ・゙・ケ・ソ」ウ
16450 クワオメ・゙・ケ・ソ」ウ
16492 クワオメ・゙・ケ・ソ」ウ


※filenodeがファイル名と一致
※ただし、DBのキャラセットとクライアントのキャラセットが一致してないと化ける

Windows版のPostgreSQLでは、キャラクタセットにSJISが指定できずEUCのため化けるようだ。

ということで、Windowsの場合は、以下の方法でもファイル名の判別ができる。


SELECT relfilenode as "Filenode", relname as "Table Name"
FROM pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d ON d.datname = current_database(),
pg_catalog.pg_tablespace t
WHERE relkind IN ('r', 'i', 'S', 't') AND
t.oid = CASE
WHEN reltablespace <> 0 THEN reltablespace
ELSE dattablespace
END AND
(c.relname ~~ ANY (ARRAY['table_name']))
ORDER BY relname






おまけ。
Win環境ではoid2nameが綺麗に動かなかったので、ubuntu環境で試してみた。
ところが、ubuntuのパッケージマネージャでビルドしたPostgreSQLには、oid2nameが含まれていなかったため、以下の手順でソースパッケージからビルドして確認した。

まずはパッケージの入手

wget ftp://ftp2.jp.postgresql.org/pub/postgresql/source/v9.0.4/postgresql-9.0.4.tar.gz



展開

tar xvf ./postgresql-9.0.4.tar.gz



ビルド

$ ls
postgresql-9.0.4/ postgresql-9.0.4.tar.gz*
cd postgresql-9.0.4/contrib/oid2name
ls
Makefile oid2name.c
../../configure
make


これでoid2nameがビルドされてるのでこれを適当に使えばOKでした。


Index of all entries

Home > PostgreSQL Archive

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

Nakunaru

    Author:Nakunaru

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


Return to page top

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