PostgreSQL環境を構築する（PostgreSQL 8.3.x）

はじめに

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

「postgresql8」のインストール

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

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

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

$ cd /usr/ports/databases/postgresql83-server
$ make config-recursive
$ make install clean

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

PostgreSQLの初期化

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

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

$ su pgsql

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

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

jail等で１つのシステム上に複数のPostgreSQLを動作しているような場合、上記の手順において「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」に下記の記述を追記します。

postgresql_enable="YES"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
postgresql_class="default"

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

DBに対する各種操作

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

ROLEの新規作成

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

$ createuser dbuser

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

既存ROLEの削除

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

$ dropuser dbuser

DBの新規作成

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

$ createdb test_db

既存DBの削除

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

$ dropdb test_db

既存DBの一覧表示

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

$ psql -l

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

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

$ psql -f test.sql test_db

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

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

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

$ psql test_db

DBのダンプ（一括バックアップ）

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

$ pg_dump db_name -b -o -Fc -f target.dump

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

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

$ pg_restore -d db_name -c target.dump

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

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

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

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

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未満のインストール直後には、標準出力に下記のようなメッセージが表示されることがあります。

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」内に以下の通り設定してください。

stats_start_collector = on
stats_row_level = on
autovacuum = on

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

stats_start_collector = off
stats_row_level = off
autovacuum = off