DB/PostgreSQL
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
-かつて日本ではいちばん有名なオープンソースDBMSのPostgreS...
#contents
*特徴 [#l92c7c2f]
-MySQLより高度な機能があるとのうわさ
-追記型データベースのため、大量データは苦手とのうわさ
-マルチプロセスのため、大量接続は苦手とのうわさ
*VACUUM [#m4c4093b]
-追記型のため空き領域の定期的な回収が必要となる
-AUTO VACUUMが有効になっていて、昔ほど気にしなくて良いの...
-以下のクエリで確認可能。n_dead_tupが増えている場合はVACU...
SELECT
relname,
n_live_tup,
n_dead_tup,
CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100...
FROM
pg_stat_user_tables ;
*設定ファイル [#of6c6bd1]
デフォルトではローカルホストからのUNIXソケット接続以外は...
**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のローカルアドレスからはパスワード認証なし...
host all all 172.16.0.0/16 tr...
-phpPgAdminはlocalでの設定が必要。初期設定のままだとident...
-conf/config.inc.php
$conf['servers'][0]['host'] = 'localhost';
# for phpPgAdmin(ないと認証失敗する)
local all all pa...
# allow from myhome
host all all 192.168.11.0/24 pa...
-v6の設定が必須の模様
http://cockatiel-cage.hateblo.jp/entry/20120412/1334215081
**pg_hda.confの詳細解説 [#i410965e]
まずlocalとhostがあるが、localはUNIXソケット接続で、要は...
*オペレーション [#c35e5e04]
**作成済みのユーザにスーパーユーザ権限付与 [#ub14121e]
ALTER ROLE ユーザ名 WITH SUPERUSER;
**テーブルに対する権限付与(対象ＤＢにログインする） [#idb...
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...
|土日フラグがついていないレコード|select * from pos_recor...
|付与|update pos_record set holiday_cd = '1' where holida...
*バックアップ&リカバリー [#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でインサートする方法 [#uc...
手順としては一度にはできない。一度シーケンス抜きのテーブ...
insert into skiarea select nextval('skiarea_id_seq'),pre...
**同時接続チェック [#d24eb6ee]
ps ax | grep post | grep -v grep | wc -l
**シーケンス型(シリアル型) [#j6e489b9]
-テーブル作成時
create table hoge(
id serial
);
シーケンスはテーブル作成時に自動で作られる。テーブル削除...
-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['extra_login_security'] = false;
「結果のページ分割処理を行う」をチェックして、select文以...
*Windowsバージョン [#k192cdf4]
-8.0よりネイティブで対応
**インストール [#h0039eec]
-NTFS領域にインストールする事！さもないと警告が出る上、あ...
公式サイトよりZIPファイル取得して、解凍。二つのmsiファイ...
-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.d...
*バックアップシェル [#eecfa813]
**テーブル単位でバックアップ [#g9e3b77b]
-単にexportするだけだが、以下のようなシェルを作成してみた...
# 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単位でバックアップ [#ye9dbeca]
# PG backup
BACKUP_BASE=/var/samba/public/backup
su - postgres -c "pg_dump データベース名 > ${BACKUP_BASE...
稼働中DBの復元にはpg_restoreというコマンドがあるらしい
*インストール [#kfe89942]
RPMやportsから入れる場合は起動ファイルなどが用意されてい...
-[[参考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...
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...
**MySQL [#t194f02e]
mysqlimport test -L -u root -p pos_records.tsv
#counter
終了行:
-かつて日本ではいちばん有名なオープンソースDBMSのPostgreS...
#contents
*特徴 [#l92c7c2f]
-MySQLより高度な機能があるとのうわさ
-追記型データベースのため、大量データは苦手とのうわさ
-マルチプロセスのため、大量接続は苦手とのうわさ
*VACUUM [#m4c4093b]
-追記型のため空き領域の定期的な回収が必要となる
-AUTO VACUUMが有効になっていて、昔ほど気にしなくて良いの...
-以下のクエリで確認可能。n_dead_tupが増えている場合はVACU...
SELECT
relname,
n_live_tup,
n_dead_tup,
CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100...
FROM
pg_stat_user_tables ;
*設定ファイル [#of6c6bd1]
デフォルトではローカルホストからのUNIXソケット接続以外は...
**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のローカルアドレスからはパスワード認証なし...
host all all 172.16.0.0/16 tr...
-phpPgAdminはlocalでの設定が必要。初期設定のままだとident...
-conf/config.inc.php
$conf['servers'][0]['host'] = 'localhost';
# for phpPgAdmin(ないと認証失敗する)
local all all pa...
# allow from myhome
host all all 192.168.11.0/24 pa...
-v6の設定が必須の模様
http://cockatiel-cage.hateblo.jp/entry/20120412/1334215081
**pg_hda.confの詳細解説 [#i410965e]
まずlocalとhostがあるが、localはUNIXソケット接続で、要は...
*オペレーション [#c35e5e04]
**作成済みのユーザにスーパーユーザ権限付与 [#ub14121e]
ALTER ROLE ユーザ名 WITH SUPERUSER;
**テーブルに対する権限付与(対象ＤＢにログインする） [#idb...
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...
|土日フラグがついていないレコード|select * from pos_recor...
|付与|update pos_record set holiday_cd = '1' where holida...
*バックアップ&リカバリー [#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でインサートする方法 [#uc...
手順としては一度にはできない。一度シーケンス抜きのテーブ...
insert into skiarea select nextval('skiarea_id_seq'),pre...
**同時接続チェック [#d24eb6ee]
ps ax | grep post | grep -v grep | wc -l
**シーケンス型(シリアル型) [#j6e489b9]
-テーブル作成時
create table hoge(
id serial
);
シーケンスはテーブル作成時に自動で作られる。テーブル削除...
-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['extra_login_security'] = false;
「結果のページ分割処理を行う」をチェックして、select文以...
*Windowsバージョン [#k192cdf4]
-8.0よりネイティブで対応
**インストール [#h0039eec]
-NTFS領域にインストールする事！さもないと警告が出る上、あ...
公式サイトよりZIPファイル取得して、解凍。二つのmsiファイ...
-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.d...
*バックアップシェル [#eecfa813]
**テーブル単位でバックアップ [#g9e3b77b]
-単にexportするだけだが、以下のようなシェルを作成してみた...
# 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単位でバックアップ [#ye9dbeca]
# PG backup
BACKUP_BASE=/var/samba/public/backup
su - postgres -c "pg_dump データベース名 > ${BACKUP_BASE...
稼働中DBの復元にはpg_restoreというコマンドがあるらしい
*インストール [#kfe89942]
RPMやportsから入れる場合は起動ファイルなどが用意されてい...
-[[参考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...
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...
**MySQL [#t194f02e]
mysqlimport test -L -u root -p pos_records.tsv
#counter
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
