Home > PostgreSQL Archive

PostgreSQL Archive

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

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

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

１．データベースのディレクトリ名を調べる

    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//
が該当ディレクトリとなる。

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

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

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

Comments: 0
TrackBack: 0

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

Posted by: Nakunaru
2011-06-16 Thu 23:04:10
PostgreSQL

PostgreSQLでは、各DBのディレクトリは$PGDATAの配下に作成されます。
（デフォルトではインストールdirのdata配下）

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

・見つけた判別方法１
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で探すことができる。

・判別方法その２
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でした。
．

Comments: 0
TrackBack: 0

Index of all entries

Home > PostgreSQL Archive

カレンダー

Seach

参考になった本など

« 2023 03 »
S	M	T	W	T	F	S
-	-	-	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	-

タグクラウド

Categories

Monthｌy

Recent Entries

Recent Comments

Recent Trackbacks

Appendix

Nakunaru

Author:Nakunaru

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

Return to page top

PostgreSQL Archive

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

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

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

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

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