6 はこちら
・postmaster の起動と停止 $ su # su - postgres $ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start …… 起動 $ /usr/local/pgsql/bin/pg_ctl stop …… 通常停止 $ /usr/local/pgsql/bin/pg_ctl -m f stop …… 強制停止 (ユーザのトランザクションが強制終了される) ・システムＤＢ (postgres) の作成 ユーザの追加／削除はシステムＤＢとは関係ないが、 各ユーザのパスワードファイルはシステムＤＢのテーブルに格納される。 ※認証を crypt にしないなら作成の必要はありません # su - postgres postgres$ createdb postgres ・ユーザの追加と削除 (ユーザ:postgres で行う) DB をアクセスするユーザは UNIX のユーザとは無関係です。 唯一、'postgres' というユーザだけが関係します。 # su - postgres ユーザの追加 (31バイトまで,[A-Za-z0-9_][A-Za-z0-9_\-]*) postgres$ createuser UserName Shall the new user be allowed to create databases? (y/n) y ……… DB作成権を与える Shall the new user be allowed to create more new users? (y/n) n ……… 特権は与えない CREATE USER ユーザの削除 postgres$ dropuser UserName DROP USER postgres$ exit # exit $
・data/pg_hba.conf の編集 #local all trust #host all 127.0.0.1 255.255.255.255 trust local postgres trust ……… システムDB local template1 trust ……… 〃 local DBName crypt ……… ユーザDB ・書式 host DBName IPAddress NetMask AuthType AuthType ident ident による認証 trust 認証なし reject 接続拒否 password file pg_passwd data/file による認証 crypt テーブル pg_shadow による認証 kbr4 KerberosV4 による認証 kbr5 KerberosV5 による認証
・crypt パスワードの設定 上記 crypt の設定行で参照されるユーザのパスワードを設定する。 $ su # su - postgres postgres$ psql postgres …… DB 名を省略すると postgres が使用される postgres=# create user UserName with password 'Password'; …… ユーザを登録 postgres=# alter user UserName with password 'Password'; …… パスワードを変更 postgres=# \q …… psql を終了 postgres$
・password パスワードの設定 上記 password の設定行で参照されるユーザのパスワードを設定する。 $ su # su - postgres postgres$ pg_passwd data/file File "data/file" does not exist. Create? (y/n): y Username: UserName New password: ******** Re-enter new password: ******** postgres$ /etc/passwd に似たような構造なので、ユーザを削除するときはその行を削除する。 ※パスワードは crypt で格納されている。 ※ pg_passwd はユーザ postgres だけが実行可能なので、 他のユーザが自分でパスワードを変更することは出来ない。
・DB ホストとしての稼動 通常、PostgreSQL はローカルからの接続 (UNIX ドメインソケット) しか許可しない。 DB ホストとして他ホストからの要求に応えるためには TCP/IP を指定して postmaster を起動する。 - data/postgresql.conf から指定する場合は tcpip_socket = true とする。 - pg_ctl から指定する場合は pg_ctl -o '-i' とする。 pg_hba.conf でホストのアドレスを制限するのは云うまでも無い。 このときの動作は、なぜか (TCP) コネクトしてから拒否する。 コネクト出来ないんだから、ソケットレベルで切断すれば良いと思うが…。 クライアント側の接続方法 psql -p Port -h DBHostName -U User
・DBの作成と削除 DB の作成 (1) $ su # su - postgres postgres$ createdb DBName DB の作成 (2) $ su # su - UserName UserName$ createdb DBName 省略時は UserName がDB名になる DB の作成 (3) $ createdb -U UserName DBName ユーザを指定して DB を作成 DB の削除 $ su # su - postgres postgres$ dropdb DBName
・psql の操作 $ psql -U UserName DBName … UserName としてログインし DBName を操作する場合 (1) DBName=> $ su - UserName UserName$ psql DBName … UserName としてログインし DBName を操作する場合 (2) DBName=> $ su - UserName UserName$ psql … 操作する DB 名が UserName と同一の場合はこちらでも可 DBName=# ※プロンプトに注目 $ psql -l … DBName 一覧の表示 テーブルの作成 $ psql DBName DBName=> create table TableName (TermName text, TermValue int) ; DBName=> create table TableName (idx int primary key, TermValue int) ; テーブルの確認 DBName=> \d Tablename Attribute | Type | Modifier -----------+------+---------- TermName | text | TermValue | int | データの挿入 DBName=> insert into TableName (Term1,Term2) values (100,150) ; あるいは DBName=> insert into TableName values ('Term1', 100) ; DBName=> insert into TableName values ('Term2', 150) ; DBName=> select * from TableName; TermName|TermValue --------+--------- Term1 | 100 Term2 | 150 (2 rows) 特定のデータに 10 を加算 (対象は TermValue) DBName=> update TableName set TermValue = TermValue +10 where TermName = 'Term1'; DBName=> select * from TableName; TermName|TermValue --------+--------- Term1 | 110 Term2 | 150 (2 rows) 全てのデータに 10 を加算 (対象は TermValue) DBName=> update TableName set TermValue = TermValue +10; DBName=> select * from TableName; TermName|TermValue --------+--------- Term1 | 120 Term2 | 160 (2 rows) 特定のデータを削除 DBName=> delete from TableName where TermName = 'Term1'; DBName=> select * from TableName; TermName|TermValue --------+--------- Term2 | 160 (1 rows) テーブルの削除 DBName=> drop table TableName; テーブルの内容をファイルに出力 (セパレータ=TAB(0x09), 改行=LF(0x0A)) DBName=> \copy TableName to CSV-PATH テーブルの内容をファイルに出力 (セパレータ=コンマ(,), 改行=LF(0x0A)) DBName=> \copy TableName to CSV-PATH using delimiters ',' テーブルの内容をファイルから追加 (セパレータ=TAB(0x09), 改行=LF(0x0A)) DBName=> \copy TableName from CSV-PATH ヘルプの表示 (コマンド) DBName=> \? DBName は何でも良い ヘルプの表示 (SQL 他) DBName=> \h DBName は何でも良い システムテーブルの表示 DBName=> \dS DBName は何でも良い ユーザの確認 DBName=> select * from pg_user ; DBName は何でも良い (pg_user は上記 \dS で表示されるもの) グループの確認 DBName=> select * from pg_group ; DBName は何でも良い (pg_group は上記 \dS で表示されるもの)
select を CSV で出力 (バッチモード) $ psql -F ',' -A -t -c 'select * from table'
バックアップ／リストア オンラインバックアップ (DB 単位) postgres$ pg_dump DBName > DBName.bup オンラインリストア (DB 単位) postgres$ createdb DBName postgres$ psql -e DBName < DBName.bup オンラインバックアップ (全 DB) postgres$ pg_dumpall > DBName.bup オンラインリストア (全 DB) postgres$ psql -e template1 < DBName.bup ※ pg_dumpall は、実際には pg_dump を呼び出すスクリプト ※ pg_dumpall はラージオブジェクトをバックアップが出来ない
アクセス権設定 DBName=> grant PermitList on TableName to User ; DBName=> revoke PermitList on TableName from User ; PermitList: select,insert,update,delete,rule,all User はユーザか public ※ public は一般ユーザの意味 アクセス権の確認 DBName=> \z Access permissions for database "DBName" Relation | Access permissions -----------+-------------------- TableName | {"=","foo=Permit"} (1 row) Permit: a insert r select w update/delete R rule ※ "=" は一般ユーザの意味 (右辺には何の権限もない)
| PostgreSQL | SQL92 | 意味 |
|---|---|---|
| char | character または char | 1バイト文字。マルチバイト文字は格納できない。1バイトを占有 |
| char(n) | character(n) または char(n) | 固定長文字列 4+n バイトを占有。 入力文字が指定桁に満たなければ空白文字で詰められる。 n は 4096 以下。 オーバー分は捨てられる。 |
| varchar(n) | character varying(n) または char varying(n) または varchar(n) | 可変長文字列 4+n バイトを占有 n は 4096 以下 オーバー分は捨てられる。 |
| float4/8 | float(p) | 精度pの浮動小数点 |
| float8 | double precision | 倍精度浮動小数点 |
| float8 | real | 単精度浮動小数点 |
| int4 | integer または int | 符号付き整数 |
| int2 | smallint | 小桁符号付き整数 |
| int4 | numeric(p,s) | 任意精度の 10 進数数値 |
| int4 | decimal(p,s) | 任意精度の 10 進数数値 |
| date | date | 年月日 '2001-01-01' |
| time | time (with timezone) | 時分秒 '20:30:10.555' (24 時間制) |
| timsetamp | timestamp (with timezone) | 年月日時分秒 '1901-12-14 00:00:00' - '2038-01-19 23:59:59' SQL92 とは実装が違う |
| timespan | interval | 時間間隔 |
| PostgreSQL | SQL3 | 意味 |
| bool | boolean | true/falseを示す |
| PostgreSQL | 意味 | 使い方 |
| box | 矩形 | 左下と右上の点を指定 |
| circle | 円 | (x,y),<r> または x,y,r として指定 |
| lseg | 直線 | [(x1,y1),(x2,y2)] (x1,y1),(x2,y2) x1,y1,x2,y2 (x1,y1,x2,y2) のいずれか |
| path | 経路 | [(x1,y1),(x2,y2),..] (x1,y1),(x2,y2),.. x1,y1,x2,y2,.. (x1,y1,x2,y2,..) のいずれか |
| point | 点 | (x,y) x,y のいずれか |
| polygon | 多角形 | (x1,y1),(x2,y2),.. x1,y1,x2,y2,.. (x1,y1,x2,y2,..) のいずれか |
| text | 可変長テキスト | PostgreSQL では varchar では無く text を推奨 4+x バイトを占有 |
| int8 | 8バイト整数 | contrib に添付。非サポートのプラットホームもある |
| datetime | 8バイト浮動小数点 | SQL92 の timestamp 型よりも高機能 timestamp型よりも広い範囲で精度が高い |
※psql ではDB名を指定しない場合、ユーザ名と同じDB名が使用されるが、 perl(Pg.pm) では DBName を明示しなくてはいけない。 ※Queryはセミコロンを付けない。(psql の SQL コマンドとは違う) ※NはQueryの項目の番号。 例： "select a,b from tableZ" の場合、 $rslt->getvalue($i,0) --- a を取得 $rslt->getvalue($i,1) --- b を取得 になる。
#!/usr/local/bin/perl use Pg ; $conn = Pg::connectdb("dbname=DBName") ; $rslt = $conn->exec("Query") ; if ($rslt->resultStatus ne PGRES_TUPLES_OK) { print "execute fail: $conn->errorMessage\n" ; exit ; } $n = $rslt->ntuples ; for ( $i = 0 ; $i < $n ; $i ++ ) { print $rslt->getvalue($i,N) ; print "\n" ; } print "--- $n selected ---\n" ;
※SQLは通常のSQL。最後の ';' は不要。(ここでは SELECT を想定しています) 項目の取出しに fetchrow_array() を使用しているので、SELECT 句の列指定順に気をつけること ※connect のフォーマットは $dbh = DBI->connect("dbi:Pg:dbname=DBName;host=HostName;port=Port;options=Options;tty=tty", 'UserName', 'Password');
#!/usr/local/bin/perl use strict ; # あったほうが良い use DBI ; my $dbh = DBI->connect("dbi:Pg:dbname=DBName", 'UserName', 'Password', {RaiseError=>1, PrintError=>1}) ; my $sth ; if (!($sth = $dbh->prepare('SQL'))) { die $dbh->errstr ; } else { if (!($sth->execute())) { die $sth->errstr ; } else { while (my($t1,$t2) = $sth->fetchrow_array()) { print "$t1,$t2\n" ; } if ($sth->err) { die $sth->errstr ; } } $sth->finish ; } $dbh->disconnect ;
#!/usr/local/bin/perl use strict ; … あったほうが良い use Tie::DBI () ; … Tie::DBI モジュールがインストールされていること tie my %DB, 'Tie::DBI', { 'db' => 'Pg:dbname=DBName', 'table' => 'TableName', 'key' => 'ColmnX', 'user' => 'UserName', … オプション 'password' => 'Password', … オプション } or die "couldn't open database" ; foreach my $key (sort keys %DB) { print "$key : " ; print $DB{$key}->{'Colmn1'} . " " ; print $DB{$key}->{'Colmn2'} . " " ; … print $DB{$key}->{'ColmnN'} . " " ; print "\n" ; } untie %DB ;
|