特徴 †
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 †
tcpip_socket=true
8 †
listen_addresses = '*' port = 5432 tcpip_socketパラメータがなくなった!初期設定のままだとローカルホスト以外から接続できない設定になっているので上記のように変更する。
上から設定を見ていくので注意。アクセス制限はゆるい→厳しい順番に書くこと #172.16.0.0/16のローカルアドレスからはパスワード認証なし、ありにするならtrust→md5に!(fe_sendauth: authentication type 5 not supportedのエラーが出る時はしょうがないのでtrust) host all all 172.16.0.0/16 trust
# for phpPgAdmin(ないと認証失敗する) local all all password # allow from myhome host all all 192.168.11.0/24 password pg_hda.confの詳細解説 †まずlocalとhostがあるが、localはUNIXソケット接続で、要はサーバの動いている同じマシンからpsqlすること。hostはアクセス元IPで設定することを示す。デフォルトではローカルホスト以外の接続は不可能なので、postgresql.confのlisten_addressの変更が必要。trustはパスワードなしで許可する。identはUNIXユーザと同じとみなす。 オペレーション †作成済みのユーザにスーパーユーザ権限付与 †ALTER ROLE ユーザ名 WITH SUPERUSER; テーブルに対する権限付与(対象DBにログインする) †grant select,update,insert,delete on テーブル名 to ユーザー名; psql †
psql DB名 psql DB名 -f ファイル名 rootのパスワード変更 †psql template1 -U postgres ALTER USER postgres WITH PASSWORD '設定するパスワード'; その他 †
SQL TIPS †
バックアップ&リカバリー †テーブル単位import/export †
DB全体バックアップ&リストア †
pg_dump DB名 > バックアップファイル名
createdb -E UNICODE DB名 psql -e DB名 < バックアップファイル名 8.4からinitdbと異なる値でcreatedbするときは個別にオプション指定しないとエラーとなる。 createdb -E EUC_JP MYDB --template=template0 バッチ処理でパスワード入力を省略する †
#for backup localhost:5432:DB_NAME:postgres:PASSWORD
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 into hoge (nextval('シーケンス名')) 現在の時刻 †
psqlのヒストリー †.psql_historyがホームディレクトリに作成されます。 Tool †WindowsのGUIツール Web上から管理できるツール。初期設定ではpostgresなどの特権ユーザーはログインできない設定になっている。conf/config.ini.phpの以下の行を修正 $conf['extra_login_security'] = false; 「結果のページ分割処理を行う」をチェックして、select文以外を実行するとエラーになるので注意 Windowsバージョン †
インストール †
公式サイトよりZIPファイル取得して、解凍。二つのmsiファイルがあるが、-intがついていないほうを実行する。
コマンド集 †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 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のもの手順は一緒
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
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
Counter: 22340,
today: 3,
yesterday: 4
|