PostgreSQL

Fedoraでは、以下のようにパッケージをインストールする。(Fedora 10 にて確認)

# yum install postgresql postgresql-server pgadmin3

サービスを開始しようとすると、「”service postgresql initdb”を使用して、最初にクラスタを初期化してください。」と言われる。

# service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [失敗]

指示にしたがってクラスタを初期化する。

# service postgresql initdb
データベースを初期化中:                                    [  OK  ]
# service postgresql start
postgresql サービスを開始中:                               [  OK  ]

/var/lib/pgsql/data/pg_hba.confを修正する。(取りあえず使えれば良い人向け)

# "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

サービスを再起動する。

# service postgresql restart
postgresql サービスを停止中:                               [  OK  ]
postgresql サービスを開始中:                               [  OK  ]

データベース操作時に postgres ユーザを利用するので、スーパーユーザにてパスワードを変更しておく。

# passwd postgres
ユーザー postgres のパスワードを変更。
新しいUNIX パスワード:
新しいUNIX パスワードを再入力してください:
passwd: 全ての認証トークンが正しく更新できました。

postgresql は postgres ユーザ権限で実行されている。

$ ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  5227  0.0  0.2 176268  4996 ?        S    08:25   0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres  5229  0.0  0.0 137904   920 ?        Ss   08:25   0:00 postgres: logger process
postgres  5231  0.0  0.0 176268  1372 ?        Ss   08:25   0:00 postgres: writer process
postgres  5232  0.0  0.0 176268  1124 ?        Ss   08:25   0:00 postgres: wal writer process
postgres  5233  0.0  0.0 176404  1332 ?        Ss   08:25   0:00 postgres: autovacuum launcher process
postgres  5234  0.0  0.0 138036  1112 ?        Ss   08:25   0:00 postgres: stats collector process
～省略～

データベース操作も postgres ユーザ権限で行う。

$ su - postgres
パスワード:

データベース作成

$ createdb -E EUC_JP [-U username] databasename   # EUC_JP encoding
$ createdb -E UNICODE [-U username] databasename  # UTF-8 encoding

データベース削除

$ dropdb [-U username] databasename

ユーザー作成

$ createuser -a -d -U postgres -P username
Enter password for new role: ********
Enter it again: ********
CREATE ROLE

ユーザー削除

$ dropuser -U postgres username

SQL実行

$ psql -d database_name -U postgres
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

database_name=# select * from pg_tables;
...
database_name=#\q

$ pg_dump database options > dumpfile.sql

options
-a データのみをダンプ
-b ラージオブジェクトもダンプする。（注: デフォルトではダンプされません）
-d INSERTコマンドにてダンプ（通常はCOPYコマンド)
-D カラム名を含めたINSERTコマンドにてダンプ
-s テーブルのみダンプ
-t 特定のテーブルのみダンプ
-F アウトプットファイルの形式を指定（cがカスタム、tがtar、pがテキスト)

バックアップ(プレーンテキストの場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v > dumpfile.sql

バックアップ(カスタムアーカイブ形式の場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp

リストア(プレーンテキストの場合)

$ psql -d databasename -U username -f dumpfile.sql

リストア(カスタムアーカイブ形式の場合)

$ pg_restore -d databasename -U username dumpfile.dmp

# pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp  <- まずはフルバックアップ
# dropdb -U username databasename                        <- DBをきれいに消す
# createdb -E EUC_JP -U username databasename            <- DBを新規作成
# pg_restore -d databasename -U username dumpfile.dmp    <- DBを復元する

# su postgres
# createlang plpgsql databasename
# exit

VACUUMによって破損したデータベースを修復できる。

VACUUM FULL FREEZE VERBOSE ANALYZE

# /etc/rc.d/init.d/postgresql stop    <- サービス停止
# su postgres                         <- postgresユーザに移行
$ postgres -O -P -D /var/lib/pgsql/data databasename <- PostgreSQLをスタンドアロンで起動
 LOG:  database system was shut down at 2008-11-30 01:03:16 JST
 LOG:  checkpoint record is at 0/529CDFD8
 LOG:  redo record is at 0/529CDFD8; undo record is at 0/0; shutdown TRUE
 LOG:  next transaction id: 2879007; next oid: 4195414
 LOG:  database system is ready

 POSTGRES backend interactive interface
 $Revision: 1.307.2.2 $ $Date: 2005/06/02 21:04:30 $

backend> REINDEX DATABASE databasename FORCE; <- データベースのインデックス再構築
Ctrl+D                                        <- backend終了
# /etc/rc.d/init.d/postgresql start    <- サービス開始

/var/lib/pgsql/data/postgresql.conf

postgresql.confの内容を変更してサービス再起動。

データベースサーバで使用される共有メモリバッファのページ数を設定する。

shared_buffers = 3072

セッションがソート処理およびハッシュテーブルに使用するメモリの最大量をKByte単位で設定する。

sort_mem = 4096

データベースの不要領域の回収。データベースの解析オプションを持つ。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

FULL
　領域の回収だけでなく、より時間のかかる、テーブルの排他的ロックを行なう、 “完全な”vacuum を選択します。

FREEZE
　積極的にタプルの”凍結”を選択します。

VERBOSE
　各テーブルについてvacuumの詳細な活動報告を出力します。

ANALYZE
　オプティマイザが使用する統計情報を更新し、問い合わせを実行する最も効率的な方法を決定できるようにします。

table
　掃除の対象とするテーブルの名前です。デフォルトは全テーブルです。

column
　解析の対象とする列名です。デフォルトは全列です。

破損したインデックスの再構築。

REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

TABLE
　指定したテーブルの全インデックスを再作成します。

DATABASE
　指定したデータベースの全てのシステムインデックスを再作成します。(ユーザテーブルのインデックスは含まれません。)

INDEX
　指定したインデックスを再作成します。

name
　インデックスの再作成を行なうテーブル/データベース/インデックスの名前を指定します。

FORCE
　強制的にシステムインデックスの再構築を行ないます。このキーワードがなければ、REINDEX は無効とされていないシステムインデックスを処理しません。FORCE を REINDEX INDEX や ユーザインデックスに対する再作成に付与することは意味がありません。

実行例

VACUUM FULL
>クエリーは、147104 ミリ秒で結果なしでうまく帰りました。
REINDEX TABLE tablename
>クエリーは、22064 ミリ秒で結果なしでうまく帰りました。

PostgreSQL リファレンス
PostgreSQL 7.2.3 リファレンスマニュアル
PostgreSQLの手軽なSQLチューニング PostgreSQL9.1　インスコできない　やっぱお前か　編 - 日常茶飯事