DB/PostgreSQL

• かつて日本ではいちばん有名なオープンソースDBMSのPostgreSQL。2000年代後半からMySQLに押されてしまった感がある。検索スピード重視のMySQLに比べると若干高機能

特徴 †

• MySQLより高度な機能があるとのうわさ
• 追記型データベースのため、大量データは苦手とのうわさ
• マルチプロセスのため、大量接続は苦手とのうわさ

VACUUM †

• 追記型のため空き領域の定期的な回収が必要となる
• AUTO VACUUMが有効になっていて、昔ほど気にしなくて良いのでは？

• 以下のクエリで確認可能。n_dead_tupが増えている場合はVACUUM検討

SELECT
 relname,
 n_live_tup,
 n_dead_tup,
 CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100/(n_live_tup+n_dead_tup) ,2) END AS ratio
FROM
 pg_stat_user_tables ;

設定ファイル †

デフォルトではローカルホストからのUNIXソケット接続以外は許可していないので、TCP/IP接続によるリモート接続を許したければ設定を変更する必要がある。postgresql.confはリスンポートの指定などPostgreSQL全般に対する設定で、pg_hba.confではデータベース毎にアクセス制限設定する。設定の反映にはPostgresの再起動が必要。

7 †

• postgresql.conf

tcpip_socket=true

• pg_hba.confは8と共通で相違点はなし

8 †

• postgresql.conf

listen_addresses = '*'
port = 5432

tcpip_socketパラメータがなくなった！初期設定のままだとローカルホスト以外から接続できない設定になっているので上記のように変更する。

• pg_hba.conf

上から設定を見ていくので注意。アクセス制限はゆるい→厳しい順番に書くこと
#172.16.0.0/16のローカルアドレスからはパスワード認証なし、ありにするならtrust→md5に！(fe_sendauth: authentication type 5 not supportedのエラーが出る時はしょうがないのでtrust)
host    all         all         172.16.0.0/16         trust

• phpPgAdminはlocalでの設定が必要。初期設定のままだとidentでApache実行ユーザで認証しようとするので設定はしないこと。

• conf/config.inc.php

  $conf['servers'][0]['host'] = 'localhost';

# for phpPgAdmin(ないと認証失敗する)
local   all         all                               password
# allow from myhome
host    all         all         192.168.11.0/24       password

• v6の設定が必須の模様 http://cockatiel-cage.hateblo.jp/entry/20120412/1334215081

pg_hda.confの詳細解説 †

まずlocalとhostがあるが、localはUNIXソケット接続で、要はサーバの動いている同じマシンからpsqlすること。hostはアクセス元IPで設定することを示す。デフォルトではローカルホスト以外の接続は不可能なので、postgresql.confのlisten_addressの変更が必要。trustはパスワードなしで許可する。identはUNIXユーザと同じとみなす。

オペレーション †

作成済みのユーザにスーパーユーザ権限付与 †

ALTER ROLE ユーザ名 WITH SUPERUSER;

テーブルに対する権限付与(対象ＤＢにログインする） †

grant select,update,insert,delete on テーブル名 to ユーザー名;

psql †

• sqlplusのpostgres版。tab補完がきくのがうれしい！

psql DB名
psql DB名 -f ファイル名

rootのパスワード変更 †

psql template1 -U postgres
ALTER USER postgres WITH PASSWORD '設定するパスワード';

その他 †

SQL TIPS †

バックアップ&リカバリー †

テーブル単位import/export †

• CSV形式にする場合はdelimiter ','を末尾につけること！

DB全体バックアップ＆リストア †

• バックアップ

pg_dump DB名 > バックアップファイル名

• リストア（インポート）前にあらかじめ空ＤＢを作成しておく！

createdb -E UNICODE DB名
psql -e DB名 < バックアップファイル名

8.4からinitdbと異なる値でcreatedbするときは個別にオプション指定しないとエラーとなる。

createdb -E EUC_JP MYDB --template=template0

バッチ処理でパスワード入力を省略する †

• .pgpassファイルをホームディレクトリに用意する。

#for backup
localhost:5432:DB_NAME:postgres:PASSWORD

• .pgpassはchmod 600にしておき他人に読めないようにしておく。

Tips †

シーケンスのあるテーブルにCOPYでインサートする方法 †

手順としては一度にはできない。一度シーケンス抜きのテーブルを作成しておき普通にコピーする。その後シーケンスを利用したinsert into文を流す。

insert into skiarea select nextval('skiarea_id_seq'),pref_cd,name from ski_temp

同時接続チェック †

ps ax | grep post | grep -v grep | wc -l

シーケンス型(シリアル型) †

• テーブル作成時

create table hoge(
id serial
);

シーケンスはテーブル作成時に自動で作られる。テーブル削除時には関連するシーケンスを手動でdrop sequenceしないと消えないので注意。insert into の時にシリアルの列をはずした場合に勝手に挿入される。

• insert時

insert into hoge (nextval('シーケンス名'))

現在の時刻 †

psqlのヒストリー †

.psql_historyがホームディレクトリに作成されます。

Tool †

• PGADMINIII

WindowsのGUIツール

• phpPgAdmin

Web上から管理できるツール。初期設定ではpostgresなどの特権ユーザーはログインできない設定になっている。conf/config.ini.phpの以下の行を修正

$conf['extra_login_security'] = false;

「結果のページ分割処理を行う」をチェックして、select文以外を実行するとエラーになるので注意

Windowsバージョン †

• 8.0よりネイティブで対応

インストール †

• NTFS領域にインストールする事！さもないと警告が出る上、あとで手動作業がある。

公式サイトよりZIPファイル取得して、解凍。二つのmsiファイルがあるが、-intがついていないほうを実行する。

• Service configuration

  サービスとして実行する場合のWindowsアカウントに関する設定。

• Initialise database cluster

  どうせテストでしか動かさないのでAdressesのチェックを入れてどこからでも接続可能にしておくべし

• 環境変数の追加

  INSTALL_DIR/binを追加するべし

コマンド集 †

psql -h localhost -U postgres -d template1
createdb -E UNICODE -h localhost -U postgres nbj_win
psql -h localhost -U postgres -d nbj_win < "./nbj_live.dat"

バックアップシェル †

テーブル単位でバックアップ †

• 単にexportするだけだが、以下のようなシェルを作成してみた。引数の順番はDB名、テーブル名、エクスポート先ディレクトリ

# export table
PG_COMMAND="psql ${1} -c \"copy ${2} to '${3}/${2}.dat'\""
su - postgres -c "${PG_COMMAND}"

# sh export_table.sh homedb tf_pos /var/tmp

テーブル単位バックアップシェルを利用して複数テーブル対応版 †

#!/bin/sh
# DEFINE DB_NAME AND EXPORT DIR
DB_NAME=homedb
EXPORT_DIR=/var/tmp
TABLE_LIST="tf_pos
tf_key
pg_table
"
for var in ${TABLE_LIST}
do
  sh export_table.sh ${DB_NAME} ${var} ${EXPORT_DIR}
done
exit;

DB単位でバックアップ †

# PG backup
BACKUP_BASE=/var/samba/public/backup
su - postgres -c "pg_dump データベース名 > ${BACKUP_BASE}/dbout.dat"

稼働中DBの復元にはpg_restoreというコマンドがあるらしい

インストール †

RPMやportsから入れる場合は起動ファイルなどが用意されているので楽。最新版を使うならソースコンパイル。下記はソースコンパイルについて7.4.6と8.0のもの手順は一緒

• 参考URL

• ユーザーの作成とディレクトリの作成

groupadd postgres
mkdir -p /usr/local/pgsql
useradd -g postgres -d /usr/local/pgsql postgres
chown -R postgres:postgres /usr/local/pgsql/

• コンパイル＆インストール

./configure --enable-multibyte=EUC_JP
readlineとzlibがconfigureでエラーになるなら下記オプションで
./configure --enable-multibyte=EUC_JP --without-readline --without-zlib
make check
make install

• postgresユーザーの環境設定

su - postgres
vi .bash_profile
export PATH=$PATH:/usr/local/pgsql/bin
export POSTGRES_HOME=/usr/local/pgsql
export PGLIB=$POSTGRES_HOME/lib
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":"$PGLIB"
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export PGDATA=/usr/local/pgsql/data
source .bash_profile
initdb
pg_ctr start

MySQLへ行こう †

pg_dump †

-tでテーブル指定
pg_dump --data-only --no-owner --no-privileges --disable-dollar-quoting DB_NAME -t TABLE_NAME > /var/tmp/table.tsv

MySQL †

mysqlimport test -L -u root -p pos_records.tsv