Tag Archives: PostgreSQL

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は忘れがち)

PostgreSQLへC言語で接続する

MySQLが一段落したので今度はPostgreSQLへ接続してみます。

ソースファイルはこちら(C言語)
pg20060521-1

PostgreSQLとMySQLでは値の取り出し方法が違うみたいですね。C++でクラス化する際には共通のほ方法で利用できるように考慮したほうが良いですね。(DBI使ったほうが早いとか置いといて)

もうひとつ重大な問題がこのソースにはあります。SELECTで*を指定しておいて取り出し時に何番目というやり方で行っていること。つまりテーブルのフィールドに追加変更があった場合にプログラムが動かなくなってしまう危険があります。単にSELECTで*を使わなければ良いのですがそれでも要素名でアクセスできないのは可読性に影響します。

C++版は以上を考慮に入れてMySQL版と移植性の高いものを作ってみます。

PostgreSQLの基本コマンド

PostgreSQLにログインしたとき良く使うコマンドの一覧
ログインするとき
# su – postgres
# psql -d [DB名] -U [ユーザ名]
※どんなDBが登録されているか不明な場合はtemplate1を指定します。(殆どの場合存在しています)

データベースの一覧を表示する
l

ログインしたデータベース内のテーブルの一覧を表示する
d

テーブルの定義を表示する。
d [テーブル名]

ログアウト
q

PostgreSQLのバックアップとリストア

そういえば、インストールと起動だけでバックアップ関係は記録してなかった。

PostgreSQLの場合はpg_dumpを使ってバックアップを取ります。
その前にpostgresユーザーになりましょう。

# su – postgres

データベースtestをバックアップ

# pg_dump test > test.dmp

ここで指定したtest.dmpはデータベースのバックアップ先ファイル名です。

今度は、バックアップしたデータベースを復元(リストア)します。

確認のため先程のtestを消します。

# dropdb test

そして空で作り直します。

# createdb test

リストア

# psql -e test < test.dmp

注意点として、データベースのエンコード情報を合わせて操作してください。特にcreatedbを行うとき等。

PostgreSQL8.1.0インストール

入手したソースアーカイブのチェック
# md5 postgresql-8.1.0.tar.gz
MD5 (postgresql-8.1.0.tar.gz) = 644b50b88cd2b93e465bb8ff30da09ed

FreeBSDの最小構成ではgmakeが入ってないのでmake時に失敗するためgmakeをインストール。

ftp://ftp.gnu.org/gnu/make/

# tar zxvf make-3.80.tar.gz
# ./configure
# make
# make install

postgresグループとユーザーを作成する。このとき出来るならばgidとuidを70にセットする。
これはアーカイブを解凍したときにchownを省略できるため。(元々postgresのuid/gidは70で定義されているみたい)※意味がわからなければ解凍後chownでpostgresにしてください。

今回はpwコマンドを初めて使ったので(というかリファレンス不足でpwコマンドの詳細が不明)uidとgidは後から編集しました。

# pw groupadd postgres
# pw useradd postgres -m -d /usr/local/pgsql -g postgres -s /bin/tcsh

## -g グループ
## -s シェル
## -d ホーム
## -m ホームが無ければ作成

パスワードを設定
# passwd postgres

uidを編集(vipwを使わなくて vi /etc/passwdでもOKです)
# vipw

gidを編集
# vi /etc/group

uid/gidが変わったのでホームディレクトリの所有者・グループを修正。
# chown -R postgres:postgres /usr/local/pgsql

解凍
# tar xzvf postgresql-8.1.0.tar.gz

postgresユーザーに切り替え( – をつけるとログインするユーザーのプロファイルを使用します)
# su – postgres

コンフィグレーション
% cd /usr/local/src/postgresql-8.1.0
% ./configure –enable-multibyte=EUC_JP –with-perl

–enable-multibyte=EUC_JP 日本語を使用するとき必要。コードセットはEUC
–with-perl Perl用Pgライブラリをコンパイル

コンパイル
% make

インストール
% make install

環境変数の設定(csh,tcshの場合)
% vi ~/.login

下記を追加

————————————————————
# PostgreSQL
setenv PG_HOME “/usr/local/pgsql”
setenv PATH “${PATH}:${PG_HOME}/bin”
setenv PGLIB “${PG_HOME}/lib”
setenv PGDATA “${PG_HOME}/data”

if ($?MANPATH) then
setenv MANPATH “${MANPATH}:${PG_HOME}/man”
else
setenv MANPATH “/usr/man:/usr/local/man:${PG_HOME}/man”
endif

if ($?LD_LIBRARY_PATH) then
setenv LD_LIBRARY_PATH “${LD_LIBRARY_PATH}:${PGLIB}”
else
setenv LD_LIBRARY_PATH “${PGLIB}”
endif
————————————————————

環境変数の変更を適用(csh,tcshの場合)
source ~/.login

データディレクトリの作成
% mkdir /usr/local/pgsql/data

データの初期化
% initdb /usr/local/pgsql/data

テスト
% postmaster -D /usr/local/pgsql/data

問題なければ起動
% postmaster -i -S -D /usr/local/pgsql/data

-i TCP/IP経由で接続する場合に使用。現在多くのサーバーシステムでは必須。
-S サイレントモード(バックグランドで起動)
-D [ディレクトリ] データディレクトリを指定

最小構成以外でLinuxやBSDを入れている人は、rpmやportsのpostgresが混入していないかチェックしてください。既に起動していてポートが使えなかったり、バージョンやディレクトリ構成の差異によって予期せぬ不具合を起こします。(違う場所のinitdbやpostmasterを呼び出したりなど)

自動起動(FreeBSD)
ここからはまたrootに戻って作業

ソースに起動ファイルがあるのでコピーする
# cd /usr/local/src/postgresql-8.1.0/contrib/start-scripts
# cp freebsd /usr/local/etc/rc.d/postgres.sh

オプションやパス等が正しいか確認する。(ここでは-iを追加)
# vi /usr/local/etc/rc.d/postgres.sh

実行権を与える
# chmod 755 /usr/local/etc/rc.d/postgres.sh

これで自動起動の設定は完了です。iオプションを有効にしているので外部からの接続が制限できているかpostgresユーザーになって下記のファイルを確認します。

# su – postgres
% less /data/pg_hba.conf

————————————————————
# TYPE DATABASE USER CIDR-ADDRESS METHOD

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

デフォルトではこのようになっておりlocalhost以外からは接続できないように設定されているようです。これだけでは安心できないので前段にファイヤーウォールを設置した方が良いと思います。