postgresqlスキーマ
postgresqlにはスキーマと呼ばれるものがあります。(オラクルDBにもあるようです。)
[DB] ― [スキーマ] ― [テーブル] ― [カラム] ― [レコード] (※すべて「1対多」の関係)
スキーマはDBとテーブルのあいだに存在する形になります。
「hoge_db」データベースに「public」スキーマと「hoge」スキーマが存在する場合の構成例
hoge_db(DB)
・public(スキーマ) ※1
・user_table(テーブル) ※2
・info_table(テーブル) ※2
・hoge(スキーマ) ※1
・user_table(テーブル) ※3
・attention_table(テーブル)
(※1)1つのDBに対して複数のスキーマを持たせる事ができます。
(※2)1つのスキーマに対して複数のテーブルを持たせる事ができます。
(※3)スキーマ内に作成するテーブルは、別のスキーマ内にあるテーブルと同じ名前であっても問題ありません。同じDB内で複数のテーブル環境を持つことができるため、テスト接続用のために新しいDBを作る必要が無いなど、
使い方によっては作業効率が上がる可能性があると思います。
■スキーマの参照方法
スキーマへの接続はロール名によって振り分けられます。
hoge_db【postgres】
・public【postgres】
・user_table
・info_table
・hoge【hoge】
・user_table
・attention_table
※カッコ【】内はオーナーのロール名です。上記の場合、hogeユーザで「hoge_db」へ接続してきた場合、接続先は「hoge」スキーマとなります。
そのため、参照できるテーブルは
[user_table(hogeスキーマ内)] [attention_table]
の二つ。
同様にpostgresユーザで「hoge_db」へ接続してきた場合は「public」スキーマへの接続となり、
[user_table(publicスキーマ内)] [info_table]
の二つのテーブルが参照できます。
ここで気になるのが、DBへの接続権限があって、ロール名と同名のスキーマが存在しないロールで接続した場合。
postgresにログインして下記コマンドを実行すると接続方法が確認できます。
postgres=# SHOW search_path; search_path ---------------- "$user",public
postgresqlでは、接続したDB内にロール名と同名のスキーマが存在しない場合、publicスキーマに接続される仕組みになっているようです。
詳しくはこちらのサイト->http://www.postgresql.jp/document/pg833doc/html/ddl-schemas.html
DBを作成した時点で自動的にpublicスキーマは作成されますので、スキーマを気にしていない場合publicを使用している事が多いと思います。
以前から使用していたmysqlにはスキーマは無かったため、今回の件でDBの概念が少々変りました。
mysqlでの「DB」は、postgresqlで言うところの「DB」と「スキーマ」を内包するイメージ。
select文の結果をカラムと値で対に表示させる
select文の結果をカラムと値を対にして表示する方法。
■mysql
select文の文末に「\G」をつける。
例)
mysql>select * from users \G;
mysql>select * from user limit 2 \G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: hogehoge
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*************************** 2. row ***************************
Host: localhost.localdomain
User: root
Password: hogehoge
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)■postgresql
「\x」コマンドを入力する。
例)
\x select * from user; ※結果表示はmysqlと同じです。
\xコマンドは一度入力すれば、ログアウトするまで設定が反映されます。
カラム数が多いテーブルやレコード数の多い結果を返すselect文を実行するときに効果的。
ディレクトリ構成をツリー表示する[tree]
Linux系のOSを使用しているとディレクトリ構成が見えづらく、ファイル検索をする機会が多いです。
そんなときに便利なのが「tree」コマンド。
※コマンドとしての歴史はまだ浅いようで、標準インストールされていないOSも多々あるようです。
treeコマンドはカレントディレクトリ配下のディレクトリ構成をツリ-表示してくれるコマンド。
「-f」オプションをつけると絶対パス表示になります。
例)
$tree -f . |-- ./OPQ | `-- ./OPQ/dddd |-- ./aaa.log |-- ./bbb.log `-- ./sss -> OPQ/ 2 directories, 3 files
コマンド結果をテキストに出力すると便利です。
#tree -f > tree.txt
postgresqlに関して
postgresqlに関して触れたのでメモ
DB操作
■DB作成 ※postgresユーザーで実行
$createdb -U ユーザー名 DB名
■DB削除 ※postgresユーザーで実行
$dropdb DB名
■ログイン
$psql ユーザー名 DB名
バックアップ/リストア
■バックアップ取得
$pg_dump -U ユーザー名 DB名 > 出力ファイル名
ファイル出力時にパイプの使用が可能
$pg_dump -U ユーザー名 DB名 | gzip > 出力ファイル名
■リストア
$psql -U ユーザー名 DB名 < ダンプファイル名
teble操作
■table作成
=>create table table名
■table削除
=>drop table table名
■カラム追加
=>alter table table名 add カラム名 型 オプション
postgresqlではカラムの削除や型の変更はできない模様。
変更したい場合はテーブルを作り直す必要あり。
自動でバックアップを取得する
user=hogehoge
自動実行は以前の記事から->「liunx環境における定期処理の実行(crontab)」
pg_dumpコマンドでバックアップを取得する時に、実行ユーザーのパスワード入力が求められる。
そのため自動で実行する場合はパスワード情報をファイルに書き出しておき、それを読み込ませる方法で対応。
.pgpassファイルを作成
「crontab」で自動バックアップ処理を記述したユーザーのホームディレクトリ内に、「.pgpass」ファイルを作成
$vim /home/hogehoge/.pgpass
.pgpassファイルの書式
ホスト:ポート番号:データベース名:ユーザー名:パスワード hostname:port:database:username:password 各項目は「:」で区切る
例
localhost:*:hogehogeDB:hogehoge:hogehogePW
複数のDBをバックアップする場合は、必要な分のパスワード情報を.pgpassに記入しておく。
例
localhost:*:hogehogeDB:hogehoge:hogehogePW localhost:*:hogehogeDB2:hogehoge:hogehogePW
作成後、.pgpassのアクセス権を「600」に設定
$chmod 600 /home/hogehoge/.pgpass
以上で作成終了。
続いて動作確認。
記入してあるDBへログイン。
$psql -U hogehoge hogehogeDB
パスワード入力が自動で行われ、DBにログインできれば確認終了。
linux環境におけるのログファイルの保管設定(logrotate)
アプリで出力されるログやDBのバックアップを一定期間(1ヵ月分など)保管して、それ以上は自動的に削除させたい。
その保存期間の設定と管理をするのに使用するのが「logrotate」。
これは、/var/log ディレクトリ以下にあるapacheやsendmailなどのログファイルの管理にも使用されています。
$ll /var/log/maillog* -rw------- 1 root root 8343 3月 31 04:02 /var/log/maillog <-現時点でのログファイル -rw------- 1 root root 6330 3月 27 04:02 /var/log/maillog.1 <-ファイル名.数字 のファイルが logrotateでローテーションされたファイル -rw------- 1 root root 13737 3月 20 04:02 /var/log/maillog.2 -rw------- 1 root root 8998 3月 13 04:02 /var/log/maillog.3 -rw------- 1 root root 6334 3月 6 04:02 /var/log/maillog.4
詳しくはこちら->wikipedia:サーバログ->ログローテーション
OS=CentOS Ver5
user=hogeuser
ファイル作成
実行ファイルの配置場所は下記ディレクトリ
/etc/logrotate.d
その中にファイルを作成。
#vi/vim ファイル名
viコマンドで任意のファイル名を指定して作成。
実行ファイルの書式は
設定先ファイル/ディレクトリ {
期日
rotate ローテンション数
}この他にも、圧縮や保存容量の指定などさまざまな項目があります。
各項目の指定内容は下記。
期日:daily(日単位)
:weekly(週単位)
:monthly(月単位)
ローテーション数:1〜
今回は
/home/hogeuser/work/backup/database/ ディレクトリ内の
db1_backup.sql
db2_backup.sql
db3_backup.sql
上記3ファイルを毎日ローテーションして、3日分残す指定。
logrotateファイル例)
/home/hogeuser/work/backup/database/*.sql {
daily
rotate 3
missingok
}/home/hogeuser/work/backup/database/*.sql {
/home/hogeuser/work/backup/database/ 以下に配置されている.sqlファイルに対するローテート宣言。daily 1日を指定。
rotate 3 ローテート数を指定。
missingok 指定されたファイルが存在しない場合、エラーを出力せず次のファイル処理へ遷移する指定。
上記で、1日分(daily)を3ローテート(rotate 3)するので、3日分の.sqlファイルが常に保管される設定となる。
設定を反映
#/usr/sbin/logrotate /etc/logrotate.conf
上記コマンドにより実行ファイルが反映される。
以上でlogrotateの設定が終了、次は動作を確認。
logrotateの動作を確認
/var/lib/logrotate.status
上記ファイルを参照することで、現在ローテートが指定されているファイルの一覧が確認できる。
指定したファイルがあることを確認。
"/home/hogeuser/work/backup/database/db1_backup.sql" 2011-3-27 "/home/hogeuser/work/backup/database/db2_backup.sql" 2011-3-27 "/home/hogeuser/work/backup/database/db3_backup.sql" 2011-3-27
/home/hogeuser/work/backup/database/ 内のファイルが2011-3-27にローテーションを開始。
後日に/home/hogeuser/work/backup/database/を確認
$ ll /home/hogeuser/work/backup/database/ -rw-r--r-- 1 root root 46124111 3月 31 09:30 db1_backup.sql -rw-r--r-- 1 root root 46124111 3月 30 09:30 db1_backup.sql.1 -rw-r--r-- 1 root root 46124111 3月 29 09:30 db1_backup.sql.2 -rw-r--r-- 1 root root 46122932 3月 28 09:30 db1_backup.sql.3 -rw-r--r-- 1 root root 11442512 3月 31 09:30 db2_backup.sql -rw-r--r-- 1 root root 11441540 3月 30 09:30 db2_backup.sql.1 -rw-r--r-- 1 root root 11441540 3月 29 09:30 db2_backup.sql.2 -rw-r--r-- 1 root root 11441540 3月 28 09:30 db2_backup.sql.3 -rw-r--r-- 1 root root 8226985 3月 31 09:30 db3_backup.sql -rw-r--r-- 1 root root 8226985 3月 30 09:30 db3_backup.sql.1 -rw-r--r-- 1 root root 8226985 3月 29 09:30 db3_backup.sql.2 -rw-r--r-- 1 root root 8226985 3月 28 09:30 db3_backup.sql.3
無事ローテーションされている。
- Memo
- logrotateは、前回の記事「liunx環境における定期処理の実行(crontab)」と組み合わせることで威力を発揮。
上記の例では、毎日09:30時に各DBのバックアップをcrontabで自動的に取得するように設定して、logrotateによってそのバックアップを3日分ローテーションしている。
ローテーション数もディスク容量などと折り合いが付くようにすれば簡単にふやせるので簡単。
liunx環境における定期処理の実行(crontab)
毎日02:00時にDBのバックアップを取得することや、1時間毎にメモリ使用量をログに書き出すなど定期的に処理を実行させたいことが多々あります。
それを実現するために使用するのが「crontab」。詳しくはこちら->wikipedia:crontab
設定する機会があったためメモ。
OS=CentOS Ver5
user=hogeuser
ファイル作成
実行ファイルの配置場所は下記ディレクトリ。
/var/spool/cron
その中にcronを実行するユーザー名でファイルを作成。
- 操作中のユーザーで作成する場合のコマンド
#crontab -e
編集する場合も同様。
- 別ユーザーのファイルを作成する場合。
#crontab -u ユーザー名 -e
-u オプションで指定したユーザーの設定ファイルを作成。
編集する場合も同様。
上記コマンドを実行するとテキストエディター(vi)が起動。
実行ファイルの書式は
分 時 日 月 曜日 コマンド
各項目の指定内容は下記。
分:0〜59
時:0〜23
日:1〜31
月:1〜12 もしくは jan〜dec
曜日:0〜7 [0と7は日曜日] もしくは sun〜sat
例)cronファイル
MAILTO="" 30 9,23 * * * /home/hogeuser/logs/sar.sh 0 0-23 * * * /home/hogeuser/logs/free.sh 30 10 * * * echo `free` >> /home/hogeuser/logs/free/free_log
MAILTO="" #cronを設定したユーザーに対して実行のたびにメールが送信されるので、それを行わないようにするための設定。
#分 時 日 月 曜日 コマンド 30 9,23 * * * /home/hogeuser/logs/sar.sh #毎日、9:30と23:30に/home/hogeuser/logs/sar.shを実行する設定。 #「30」で分を指定、「9,23」で時間を指定。日、月、曜日は未指定なので毎日実行される。
0 0-23 * * * /home/hogeuser/logs/free.sh #「0-23」で0時〜23時までの各時間を指定。 #毎時/hoge/hogeuser/logs/free.shを実行する設定。上記同様、毎日実行される。
30 10 * * * echo `free` >> /home/hogeuser/logs/free/free_log #ファイル内にコマンドを書くこともできる。
以上でcronの設定が終了、次はcronの動作を確認。
cronのログファイル
/var/log/cron
このファイルを参照することで作成したファイルが読み込まれているか、コマンドが実行されているか確認できる。
例）ファイルの保存時間が｢10:22」実行時間が「10:30」の場合
Mar 21 10:22:22 localhost crontab[5050]: (hogeuser) END EDIT (hogeuser)(*1) Mar 21 10:23:01 localhost crond[5255]: (hogeuser) RELOAD (cron/hogeuser)(*2) Mar 21 10:30:01 localhost crond[5264]: (hogeuser) CMD (free)(*3)
(*1)cronファイルの編集が終了。
(*2)編集されたcronファイルの読み込み。
ファイルが読み込まれるのは編集終了時間の次分の頭。
ここでは10:22にファイルが保存されたので、次の10:23の頭でファイルが読み込まれている。
(*3)指定した時間にコマンドが実行。
- Memo
- 動作確認をする場合、設定ファイルを保存した時間から実行までの時間を3分程度見積もる必要がある。
でないと、ファイル読み込みと処理実行との折り合いが付かず正しく実行されない場合がある。
vimコマンドでファイル操作中、インデント入力を簡単にする
vimコマンドでファイル操作時におけるtabキーの挙動を指定。
下記文言をファイル内にコメントとして記入しておくだけでOK。
「vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=0:」
Tabキー押下でスペース4つがファイルに入力されるようになる。
phpファイルの場合
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=0: */
Memo
- tabstop:ファイル内にあるタブ文字の表示幅。
- shiftwidth:自動インデントに使われる空白の数。
- softtabstop:タブキー押下時に挿入される文字幅を指定。(0の場合「tabstop」で指定されている数値が使用される)
スペース2つ分で表示や入力をする場合は
- tabstop=2
- shiftwidth=2
- softtabstop=0
を指定。
