- 追加された行はこの色です。
- 削除された行はこの色です。
-かつて日本ではいちばん有名なオープンソースDBMSのPostgreSQL。2000年代後半からMySQLに押されてしまった感がある。検索スピード重視のMySQLに比べると若干高機能
#contents
*特徴 [#l92c7c2f]
-MySQLより高度な機能があるとのうわさ
-追記型データベースのため、大量データは苦手とのうわさ
-マルチプロセスのため、大量接続は苦手とのうわさ
*設定ファイル [#of6c6bd1]
デフォルトではローカルホストからのUNIXソケット接続以外は許可していないので、TCP/IP接続によるリモート接続を許したければ設定を変更する必要がある。postgresql.confはリスンポートの指定などPostgreSQL全般に対する設定で、pg_hba.confではデータベース毎にアクセス制限設定する。設定の反映にはPostgresの再起動が必要。
**7 [#i4cb4fbe]
-postgresql.conf
tcpip_socket=true
-pg_hba.confは8と共通で相違点はなし
**8 [#qd3b0cb0]
-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の詳細解説 [#i410965e]
まずlocalとhostがあるが、localはUNIXソケット接続で、要はサーバの動いている同じマシンからpsqlすること。hostはアクセス元IPで設定することを示す。デフォルトではローカルホスト以外の接続は不可能なので、postgresql.confのlisten_addressの変更が必要。trustはパスワードなしで許可する。identはUNIXユーザと同じとみなす。
*オペレーション [#c35e5e04]
**作成済みのユーザにスーパーユーザ権限付与 [#ub14121e]
ALTER ROLE ユーザ名 WITH SUPERUSER;
**テーブルに対する権限付与(対象DBにログインする) [#idb8156d]
grant select,update,insert,delete on テーブル名 to ユーザー名;
**psql [#yc8b3e49]
-sqlplusのpostgres版。tab補完がきくのがうれしい!
psql DB名
psql DB名 -f ファイル名
**rootのパスワード変更 [#m145b44e]
psql template1 -U postgres
ALTER USER postgres WITH PASSWORD '設定するパスワード';
**その他 [#qa8fdb66]
|テーブル一覧|select * from pg_tables;|
|テーブル定義|\d テーブル名|
|トランザクション開始|BEGIN;|
|トランザクション終了|commit or rollback|
**SQL TIPS [#kcfc56d8]
|曜日取得(日曜0-土曜日6)|select date_part('dow', userdate) from xxx|
|土日フラグがついていないレコード|select * from pos_record where holiday_cd = '0' and date_part('dow', userdate) in (0,6)|
|付与|update pos_record set holiday_cd = '1' where holiday_cd = '0' and date_part('dow', userdate) in (0,6) order by userdate|
*バックアップ&リカバリー [#i6692f59]
**テーブル単位import/export [#l94baed9]
-CSV形式にする場合はdelimiter ','を末尾につけること!
|エクスポート|copy tf_pos to '/var/tmp/tf_pos.dat';|
|インポート|copy tf_pos from '/var/tmp/tf_pos.dat';|
**DB全体バックアップ&リストア [#z4a1a983]
-バックアップ
pg_dump DB名 > バックアップファイル名
-リストア(インポート)前にあらかじめ空DBを作成しておく!
createdb -E UNICODE DB名
psql -e DB名 < バックアップファイル名
8.4からinitdbと異なる値でcreatedbするときは個別にオプション指定しないとエラーとなる。
createdb -E EUC_JP MYDB --template=template0
**バッチ処理でパスワード入力を省略する [#n802a571]
-.pgpassファイルをホームディレクトリに用意する。
#for backup
localhost:5432:DB_NAME:postgres:PASSWORD
-.pgpassはchmod 600にしておき他人に読めないようにしておく。
*Tips [#p5f9e791]
**シーケンスのあるテーブルにCOPYでインサートする方法 [#uc347176]
手順としては一度にはできない。一度シーケンス抜きのテーブルを作成しておき普通にコピーする。その後シーケンスを利用したinsert into文を流す。
insert into skiarea select nextval('skiarea_id_seq'),pref_cd,name from ski_temp
**同時接続チェック [#d24eb6ee]
ps ax | grep post | grep -v grep | wc -l
**シーケンス型(シリアル型) [#j6e489b9]
-テーブル作成時
create table hoge(
id serial
);
シーケンスはテーブル作成時に自動で作られる。テーブル削除時には関連するシーケンスを手動でdrop sequenceしないと消えないので注意。insert into の時にシリアルの列をはずした場合に勝手に挿入される。
-insert時
insert into hoge (nextval('シーケンス名'))
**現在の時刻 [#q3d8ee9b]
|Postgres|now()|
|Oracle|sysdate|
**psqlのヒストリー [#l7134a68]
.psql_historyがホームディレクトリに作成されます。
*Tool [#ya5c28a9]
-[[PGADMINIII:http://www.pgadmin.org/]]
WindowsのGUIツール
-[[phpPgAdmin:http://phppgadmin.sourceforge.net/]]
Web上から管理できるツール。初期設定ではpostgresなどの特権ユーザーはログインできない設定になっている。conf/config.ini.phpの以下の行を修正
$conf['extra_login_security'] = false;
「結果のページ分割処理を行う」をチェックして、select文以外を実行するとエラーになるので注意
*Windowsバージョン [#k192cdf4]
-8.0よりネイティブで対応
**インストール [#h0039eec]
-NTFS領域にインストールする事!さもないと警告が出る上、あとで手動作業がある。
公式サイトよりZIPファイル取得して、解凍。二つのmsiファイルがあるが、-intがついていないほうを実行する。
-Service configuration
サービスとして実行する場合のWindowsアカウントに関する設定。
-Initialise database cluster
どうせテストでしか動かさないのでAdressesのチェックを入れてどこからでも接続可能にしておくべし
-環境変数の追加
INSTALL_DIR/binを追加するべし
**コマンド集 [#i8527e71]
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"
*バックアップシェル [#eecfa813]
**テーブル単位でバックアップ [#g9e3b77b]
-単に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
**テーブル単位バックアップシェルを利用して複数テーブル対応版 [#f32dd25b]
#!/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単位でバックアップ [#ye9dbeca]
# PG backup
BACKUP_BASE=/var/samba/public/backup
su - postgres -c "pg_dump データベース名 > ${BACKUP_BASE}/dbout.dat"
稼働中DBの復元にはpg_restoreというコマンドがあるらしい
*インストール [#kfe89942]
RPMやportsから入れる場合は起動ファイルなどが用意されているので楽。最新版を使うならソースコンパイル。下記はソースコンパイルについて7.4.6と8.0のもの手順は一緒
-[[参考URL:http://www.y-kit.jp/saba/xp/postgreinst.htm]]
-ユーザーの作成とディレクトリの作成
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
#counter