DB/PostgreSQL の履歴ソース(No.2)

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• 履歴 を表示
• DB/PostgreSQL へ行く。
  • 1 (2009-10-15 (木) 15:24:20)
  • 2 (2011-05-26 (木) 00:13:33)
  • 3 (2011-06-06 (月) 12:05:49)
  • 4 (2011-06-11 (土) 12:30:39)
  • 5 (2012-04-04 (水) 23:53:09)
  • 6 (2012-04-05 (木) 04:40:20)
  • 7 (2014-01-09 (木) 00:56:36)
  • 8 (2014-01-10 (金) 07:45:37)
  • 9 (2014-03-25 (火) 02:39:00)
  • 10 (2014-04-21 (月) 12:29:24)
  • 11 (2015-04-08 (水) 00:34:13)
  • 12 (2015-04-13 (月) 13:55:26)
  • 13 (2016-05-20 (金) 06:56:44)
  • 14 (2016-05-21 (土) 10:54:18)
  • 15 (2019-08-12 (月) 15:33:57)

-日本ではいちばん有名なオープンソースDBMSのPostgreSQL。検索スピード重視のMySQLに比べると若干高機能

#contents

*インストール [#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

*設定ファイル [#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
 
*オペレーション [#c35e5e04]

**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|

*バックアップ&リカバリー [#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名 > バックアップファイル名

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

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

*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;

*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というコマンドがあるらしい

#counter