はじめに

このページでは、BSDライセンスにより配布されているフリーのデータベース管理システム「PostgreSQL」のバージョン8.xをFreeBSD上で構築、利用する方法について説明します。

「postgresql8」のインストール

postgresql8はデフォルトでシステムにインストールされていませんので、別途システムにインストールする必要があります。

インストールはportsを利用して行います。
portsの取得および展開方法については、「最新のportsを取得して展開する」を参照してください。

portsの展開が完了した後、下記手順でpostgresql8をmakeし、インストールします。

「postgresql8」のインストール
$ cd /usr/ports/databases/postgresql84-server
$ make config-recursive
$ make install clean

postgresql7以前では、ビルド時にマルチバイト系のコンパイルオプションを付与する必要がありましたが、postgresql8では、DBクラスタの初期化時にエンコードとロケールを指定するように変更されています。
このため、特にコンパイルオプション等を指定する必要はありません。

上記の例では、「PostgreSQL 8.4.x」を構築しますが、FreeBSDのportsには、古いバージョンのpostgresql8も含まれていますので、古いバージョンのpostgresql8を導入したい場合には、「/usr/ports/database」下の他の「postgresql8?-server」を利用してください。

PostgreSQLの初期化

DBクラスタの初期化を行います。
postgresql8をFreeBSDのportsからインストールすると、PostgreSQL用のアカウント「pgsql」があらかじめ用意されています。

rootから以下のように入力してください。

PostgreSQL 8.xの初期化
$ su pgsql

$ cd /usr/local/pgsql/
$ mkdir data
$ initdb -D ./data --encoding=utf-8 --lc-collate=C

「/usr/local/pgsql」下にDBクラスタ用フォルダが作成されます。（以降、DBが追加されるたびに、このフォルダ以下にデータが格納されていきます。）

jail等で１つのシステム上に複数のpostgresql8を動作しているような場合、上記の手順において「initdb could not create semaphores」と表示され、DBクラスタ用フォルダが作成されない場合があります。
これは、システムで用意しているセマフォの上限値より多いセマフォが要求されたために発生します。

このエラーが発生した場合には、「/boot/loader.conf」に以下の記述を追加し、セマフォの上限値を増やしてください。

セマフォ系パラメータのチューニング
kern.ipc.semmni=50
kern.ipc.semmns=300
kern.ipc.semmnu=150

上記の例では、FreeBSD 6.3の規定値の５倍の容量を確保しています。
これらの値の設定値は「sysctl -a | grep kern.ipc」で確認できます。

「rc.conf」の設定

最後に、システムの起動時にpostgresql8が自動的に起動するよう、「/etc/rc.conf」に下記の記述を追記します。

「/etc/rc.conf」の設定
postgresql_enable="YES"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
postgresql_class="default"

この設定により、システム再起動時にpostgresql8が自動的に起動するようになります。

PostgreSQLをjailゲスト環境（=prisoner）で利用する場合

ここまでは、postgresql8を通常の環境で利用する場合の設定方法について説明してきましたが、FreeBSDのjail環境でpostgresql8を構築するには、これに加えて若干の設定を追加で行う必要があります。

詳しくは「FreeBSDサーバ上に仮想サーバを構築する（jail）:: prisonerでPostgreSQLを動作させる際の注意点」をご確認ください。

DBに対する各種操作

以下にpostgresql8でのDBに対する基本的な操作を説明します。

ROLEの新規作成

ROLEは、postgresql8の各種操作を行うための権限のようなもので、ユーザごとに設定されます。
ユーザ「dbuser」にROLEを付与するには、「createuser」を利用します。

ROLEの新規作成
$ createuser dbuser

いくつかの設問が表示されますので、適宜回答してください。

既存ROLEの削除

既存のROLEを削除するには、「dropuser」を利用します。

既存ROLEの削除
$ dropuser dbuser

DBの新規作成

postgresql8でDBを新規に作成するには、「createdb」を利用します。
「createdb」を実行するには、ユーザがpgsqlか、または予めROLEが登録されたユーザである必要があります。

ROLEの新規作成
$ createdb test_db

既存DBの削除

postgresql8で既存のDBを削除するには、「dropdb」を利用します。
「dropdb」を実行するには、ユーザがpgsqlか、または予めROLEが登録されたユーザである必要があります。

既存DBの削除
$ dropdb test_db

既存DBの一覧表示

既存のDBを一覧表示するには、下記のようにします。

既存DBの一覧表示
$ psql -l

ファイル内に記述されたSQL文の一括実行

ファイル内に記述されたSQL文をバッチで実行するには、下記のようにします。

ファイル内に記述されたSQL文の一括実行
$ psql -f test.sql test_db

「test.sql」の部分には、SQL文が書かれたテキストファイル名を、「test_db」の部分には、対象となるDB名を指定してください。

SQLコマンドプロンプト入力

psqlのコマンドプロンプトを利用してSQL文を入力したい場合には、以下のようにします。

SQLコマンドプロンプト入力
$ psql test_db

DBのダンプ（DBごとにバックアップ）

DBに保存されているデータを丸ごとバックアップするには、「pg_dump」を利用します。

「pg_dump」によるDBの一括バックアップ
$ pg_dump db_name -b -o -Fc -f target.dump

DBのダンプ（すべてのDBを一括でバックアップ）

DBごとではなく、すべてのDBを丸ごとバックアップするには、「pg_dumpall」を利用します。

「pg_dumpall」による全DBの一括バックアップ
$ pg_dumpall -b -o -Fc -f target.dump

ダンプしたデータのリストア

「pg_dump」によってバックアップされたデータを復元するには、「pg_restore」を利用します。
「pg_dump」で出力されたダンプデータは、全てのプラットフォームで共通に利用できるため、DBの内容を別の環境に移行する場合にも利用できます。

「pg_restore」によるDBの復元
$ pg_restore -d db_name -c target.dump

データベースのパフォーマンスチューニング

ここでは、データベースのパフォーマンスチューニングに関する設定について、説明します。

シェアドメモリのチューニング

OSのシェアドメモリを増やすことにより、postgresql8のパフォーマンスを向上させることが可能です。
「/etc/sysctl.conf」に以下の記述を追加すると、シェアドメモリを256MBに拡張することが出来ます。

「/etc/sysctl.conf」への追記
kern.ipc.shmall=65536
kern.ipc.shmmax=268435456

FreeBSDでは「kern.ipc.shmall」がページ数（1ページ＝4KB）、「kern.ipc.shmmax」がバイト数になっています。
値を増減させる場合には、「kern.ipc.shmmax」＝「kern.ipc.shmall」×4096となるように設定してください。

autovacuumの設定

PostgreSQL8.1.x未満のインストール直後には、標準出力に下記のようなメッセージが表示されることがあります。

PostgreSQL8.1.x以降のインストール直後のメッセージ
WARNING:  autovacuum not started because of misconfiguration
HINT:  Enable options "stats_start_collector" and "stats_row_level".

これは、PostgreSQL8.1.x以降でサーバに統合されたautovacuum（データベースの利用頻度に応じて自動的にvacuumをかける）機能がONになっているものの、autovacuumの実行に必要な各種統計情報が収集できない設定になっているために表示されるものです。

autovacuumを利用することにより、定期的な保守メンテナンスがなくとも、DBを一定のパフォーマンスのまま維持することができますが、その一方でDBアクセス時に各種統計情報を収集するため、トランザクションあたりの性能が低下します。

autovacuumを利用する場合には、「/usr/local/pgsql/data/postgresql.conf」内に以下の通り設定してください。

autovacuumを利用する場合の「postgresql.conf」の設定
stats_start_collector = on
stats_row_level = on
autovacuum = on

逆に、autovacuumを利用しない場合には、「/usr/local/pgsql/data/postgresql.conf」内に以下の通り設定してください。

autovacuumを利用しない場合の「postgresql.conf」の設定
stats_start_collector = off
stats_row_level = off
autovacuum = off

変更履歴

2010/03/31

・postgresql8をjail上で利用する場合の注意点について、リンクを追加。

2010/03/01

・「pg_dumpall」を使った全DBの一括バックアップの方法を追記。

あなたの探し物は見つかりましたか？
まさにこれだ
参考になった
ちょっと違う
これじゃない

何かメッセージがあればお願いします

このメッセージを非公開にする

ご注意

・頂いたメッセージは管理者のチェックの後、公開されます。
・メッセージの公開を希望されない場合には、「このメッセージを非公開にする」にチェックを入れてください。
・管理者が不適切と判断したメッセージは公開しませんので、予めご了承ください。

まだ評価がありません

表示できるメッセージはありません。