PostgreSQLでディスク容量の測定

PostgreSQLってデータディレクトリーの名前が数字になっているのでどのディレクトリーとデータベースが対応しているか直感的にわからないため各データベースが使用しているディスク容量を調べるにはツールが必要になります。

それで、本やネットで調べるとoid2nameを使えばOidとデータベース名の対応を表示してくれるみたい。どうやら数字のディレクトリーはOidのようです。公式ページや本ではREADME.oid2nameを見れと一言だけしか書いてないので使い方はREADME.oid2nameを参照することにしました。

…が、そもそもソースコンパイルではoid2nameが入っていない。しかもoid2name導入についての記述がどこにも見当たらない。一応Makefileはあるので考えられる方法で導入してみることに。

まずoid2nameのソースがおいてある場所は
/usr/local/src/postgresql-8.1.0/contrib/oid2name/
でした。ここに、
README.oid2name
Makefile
oid2name.c
の3つのファイルがあります。

postgres> cd /usr/local/src/postgresql-8.1.0/contrib/oid2name
postgres> /usr/local/bin/make install

ここでmakeが絶対パスで指定していますが、これはGNU makeの方です。本体と同じくoid2nameも通常のmakeでは利用できませんでした。ただ本体の方は通常makeを指定してもさらにGNU makeを探しに行ってくれるので/usr/bin/makeで打ってしまっても大丈夫だったりします。

これでoid2nameが/usr/local/pgsql/bin/oid2nameにインストールされました。
早速使ってみます。

postgres> oid2name -P ********
All databases:
Oid Database Name Tablespace
———————————-
10793 postgres pg_default
10792 template0 pg_default
1 template1 pg_default
16452 test pg_default

Pオプションはパスワードです。ローカルでの認証をtrustにしている場合は不要だと思いますが、それはあまり好ましくないのでパスワード設定しておきましょう。
ただしこのコマンドは対話的にパスワードを入力できず上記のようにコマンドライン上か~/.pgpassに設定する必要があるので困り者です。

さらにテーブルごとに調査したい場合は
postgres> ll -S $PGDATA/base/16452 | head -10
total 3684
-rw——- 1 postgres postgres 352256 May 21 2006 1255
-rw——- 1 postgres postgres 344064 May 21 2006 2691
-rw——- 1 postgres postgres 237568 Jun 11 2006 2608
-rw——- 1 postgres postgres 204800 Jun 11 2006 1249
-rw——- 1 postgres postgres 204800 Jun 11 2006 2658
-rw——- 1 postgres postgres 172032 Jun 11 2006 2674
-rw——- 1 postgres postgres 155648 Jun 11 2006 2673
-rw——- 1 postgres postgres 122880 May 21 2006 2609
-rw——- 1 postgres postgres 114688 May 21 2006 10305

のようにテーブルのoidを確認して次のようにコマンドを打ちます。
postgres> oid2name -d test -f 1255 -f 2691 -f 2608 -f 1249 -f 2658 -f 2674 -f 2673 -f 2609
-f 10305 -P ********
From database “test”:
Filenode Table Name
——————————————-
1249 pg_attribute
2658 pg_attribute_relid_attnam_index
2608 pg_depend
2673 pg_depend_depender_index
2674 pg_depend_reference_index
2609 pg_description
1255 pg_proc
2691 pg_proc_proname_args_nsp_index
10305 pg_toast_2618
実際テーブルレベルになってくるとデータベース側が定義したものが多すぎて単純に振り分けるのが困難ですね。上記はサンプル通りファイルサイズの多い順でやってみましたが、独自で定義したテーブルは出てきていません。

ちょっと脱線しましたが、データベースごとの測定は簡単にできます。先程testデータベースのoidは16452と分かりましたのでduコマンドで測定します。

postgres> du -sh $PGDATA/base/16452
3.6M /usr/local/pgsql/data/base/16452

各データベースということであればdepth 1でもいいと思います。
postgres> du -d 1 -h $PGDATA/base/
3.6M /usr/local/pgsql/data/base/1
3.6M /usr/local/pgsql/data/base/10792
3.6M /usr/local/pgsql/data/base/10793
3.6M /usr/local/pgsql/data/base/16452
14.4M /usr/local/pgsql/data/base/

PostgreSQLの設定

先月のMT4検証で気になっていたのがデフォルトのPostgreSQLはローカルマシンでDBに接続するときは誰でも入れてしまうなーっと。これはpg_hba.confの設定でローカルマシンからの接続は無条件に許可しているからでした。

共用サーバになるといろいろな人が利用するのではこれではマズいと設定の見直し。

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# “local” is for Unix domain socket connections only
local all all password
# IPv4 local connections:
host all all 127.0.0.1/32 password
# IPv6 local connections:
host all all ::1/128 password

localはコマンドラインで通常接続するときに使用しているUNIXドメインソケット、hostがTCP/IPで接続するときの設定です。デフォルトではMETHODがtrustになっていますがこれをpasswordに変えることでDB接続時にPostgreSQL内で設定したユーザパスワードが必要になります。但しパスワード未設定のユーザは接続できなくなるので事前に設定の確認をしてください。(特にpostgresは忘れがち)