![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2021-11-10T00:07:49+00:00","default:wikiadmin","wikiadmin") #author("2021-11-10T00:09:20+00:00","default:wikiadmin","wikiadmin") -かつて日本ではいちばん有名なオープンソースDBMSのPostgreSQL。2000年代後半からMySQLに押されてしまった感がある。検索スピード重視のMySQLに比べると若干高機能 #contents *特徴 [#l92c7c2f] -MySQLより高度な機能があるとのうわさ -追記型データベースのため、大量データは苦手とのうわさ -マルチプロセスのため、大量接続は苦手とのうわさ *VACUUM [#m4c4093b] -追記型のため空き領域の定期的な回収が必要となる -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 ; *設定ファイル [#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; **テーブルに対する権限付与(対象ＤＢにログインする） [#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名 > バックアップファイル名 -リストア（インポート）前にあらかじめ空ＤＢを作成しておく！ 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 *MySQLへ行こう [#m9019005] **pg_dump [#gc53d605] -tでテーブル指定 pg_dump --data-only --no-owner --no-privileges --disable-dollar-quoting DB_NAME -t TABLE_NAME > /var/tmp/table.tsv **MySQL [#t194f02e] mysqlimport test -L -u root -p pos_records.tsv #counter
