このページには PostgreSQL に関する個人的なメモを載せています．
Access Count :
( since Aug 1, 2000 )
内容に関しては一切責任を負いかねますので御了承下さい．
<--メモ のページへ戻る
（例） データベース bookmark を，データベースユーザ dbuser を所有者として作成
$ createdb -U 所有者名 データベース名
（例） データベース bookmark を削除
$ dropdb データベース名
（例）表の名前を Name_list とし，int4 型(32bit 整数型) の属性 id と text 型(可変長文字型)の属性 name を持たせる
create table 表の名前 (
属性名1 型名1 ,
属性名2 型名2 ,
.........................
属性名n 型名n
);
create table Name_list (
id int4 ,
name text
);
上記のSQLコードを name_list.sql
という名前で保存しておく．
対話的に SQL を発行するために psql コマンドを用いる．
すると，「データベース名=>」というプロンプトが表示され，入力待ち状態になる．
$ psql -U dbuser bookmark Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: bookmark bookmark=> \i name_list.sql ← バックスラッシュ+i に続いてファイル名を指定することで， そのファイルに書かれている SQL コードが評価される． table Name_list ( id int4, name text ); CREATE EOF bookmark=> select * from name_list;← 表からデータを選択するための命令 select id|name ← まだ何もデータを入れてないので，空表が表示される --+---- (0 rows) bookmark=> \q
いったん上記のように表を作成すると，その情報は永続的に保持される．
試しに再度 psql コマンドを実行して，今度は
\i name_list.sql を与えずにいきなり
select * from name_list;
を行なっても同じ結果を得る．
データベースから表を削除するには drop 文を用いる．
データベース上の表へデータを挿入するには insert 文を用いる．
insert into 表名 values(値1, 値2, ... );※ 値1, 値2, ... はそれぞれ 列1, 列2, ... に対応する．
（例）データベース bookmark 上の表 name_list に以下の内容を作成する．
psql で insert 文を用い，これらのデータを挿入する：
id name 96342601 Hirohisa_Aman 95102021 Shuichi_Ueda
$ psql -U dbuser bookmark Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: bookmark bookmark=> select * from name_list; id|name --+---- (0 rows) bookmark=> insert into name_list values(96342601, 'Hirohisa_Aman'); INSERT 25536 1 bookmark=> insert into name_list values(95102021, 'Shuichi_Ueda'); INSERT 25537 1 bookmark=> select * from name_list; id|name --------+------------- 96342601|Hirohisa_Aman 95102021|Shuichi_Ueda (2 rows) bookmark=> \q
データベース上の表の内容を表示するには select 文を用いる．
1 番目のタイプでは表中の列名を列挙することで， 指定された列のデータが順に表示される．
（例）データベース bookmark 上の表 name_list に次のようなデータが格納されているとする．
psql で select 文を用い，これらのデータを表示させる：
id name 96342601 Hirohisa_Aman 95102021 Shuichi_Ueda
$ psql -U dbuser bookmark Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: bookmark bookmark=> select * from name_list; id|name --------+------------- 96342601|Hirohisa_Aman 95102021|Shuichi_Ueda (2 rows) bookmark=> select id from name_list; id -------- 96342601 95102021 (2 rows) bookmark=> select name from name_list; name ------------- Hirohisa_Aman Shuichi_Ueda (2 rows) bookmark=> select name, id from name_list; name | id -------------+-------- Hirohisa_Aman|96342601 Shuichi_Ueda |95102021 (2 rows) bookmark=> \q
表に格納されているデータを全て削除する．
ただし，表を空にするだけで，表の存在を消し去る（こちらの方は
drop 文）ものではない．
（例）delete 文を使って表を空にする：前後の select 文の実行結果に注目．
bookmark=> select * from name_list; id|name --------+------------- 96342601|Hirohisa_Aman 95102021|Shuichi_Ueda (2 rows) bookmark=> delete from name_list; DELETE 2 bookmark=> select * from name_list; id|name --+---- (0 rows)
ここで「where 行選択条件」を省略すると，すべての行が更新対象となる．
（例）名前と番号の変更
ここではデータの指定方法に注意が必要である． SQL では文字と文字列に区別はなく---文字は長さ1の文字列と見なされる---， どちらも シングルクォート( ' )で囲む． なお数値の場合はシングルクォートで囲む必要はない．
bookmark=> select * from name_list; id|name --------+------------- 96342601|Hirohisa_Aman 95102021|Shuichi_Ueda (2 rows) bookmark=> update name_list set name='Hirohisa_Aman_XXX' where id=96342601; UPDATE 1 bookmark=> select * from name_list; id|name --------+----------------- 95102021|Shuichi_Ueda 96342601|Hirohisa_Aman_XXX (2 rows) bookmark=> update name_list set id=95100000 where name='Shuichi_Ueda'; UPDATE 1 bookmark=> select * from name_list; id|name --------+----------------- 96342601|Hirohisa_Aman_XXX 95100000|Shuichi_Ueda (2 rows)
特定のデータを指定するには命令文に where 句を付ける． すでに 「表中のデータを更新する」では where 句を用いた． その際，
として「name」の列で値が「'Shuichi_Ueda'」である行のみを更新の対象とした．
where 句に書ける条件としては = だけでなく <, <=, >, >=, !=, and, or, not 等が利用できる．
（例）name_list 中で id が 96000000 〜 96999999 である人の名前を出力する：
既存の表に列を追加するには alter 文を用いる．
（例）表 name_list に， 列 age （整数型）を追加する．
データベースのバックアップを作成するには，
データが管理されているディレクトリ
（/var/lib/pgsql/data 等）
をまるごとコピー（バックアップ）する方法と
PostgreSQL 独自のバックアップツールを用いる方法とがある．
計算機アーキテクチャの違いや PostgreSQL
のバージョンの違いにも対応できるよう，
ここでは後者の方法について述べる．
なお，以下に示す方法はラージオブジェクトもバックアップ対象になっている．
いま n 個のデータベースがあるとする． これらのデータベースをすべてバックアップするには以下の操作を行う．
# su - postgres $ pg_dumpall -g > db.out $ pg_dump -b -F c データベース名1 > データベース名1.dump $ pg_dump -b -F c データベース名2 > データベース名2.dump ・・・・・・・ $ pg_dump -b -F c データベース名n > データベース名n.dump
（
※ちなみに，データベース名の一覧は次のコマンドを実行すると表示される．
ただし，この場合，
実行したユーザがデータベースユーザとして登録されていなければならない．
登録されていない場合は，
psql -l
とすればよい．
）
psql -U データベースユーザ名 -l
【使用例】 データベース Library と javaml をバックアップ
バックアップファイルは /tmp 以下に置く．
ここでは 上述の方法 で作成したバックアップファイルを使ってデータベースを復元する．
まず，pg_dumpall でバックアップしていたデータ（グループとユーザ情報） を復元する． ※ここでは /tmp/db.out とする．
次に，データベースごとに復元する． ※ここでは /tmp/Library.dump と /tmp/javaml.dump を対象とする．
（
※ただし，HBA のデフォルト設定では，
「ユーザ名」＝「データベースユーザ名」
でなければデータベース作成は許されない．
その場合は，HBA 設定ファイル
（/var/lib/pgsql/data/pg_hba.conf など）
で
のように設定しておくとよい．）
# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT
local all trust
#local all ident sameuser
データベースへのアクセス制限に関する設定は
/var/lib/pgsql/data/pg_hba.conf
に記述する．
※マシンによっては設置ディレクトリが異なるかもしれない
この設定ファイルでは，# から行末までがコメント文として解釈される．
ここでは簡単な設定についてのみ説明する．
詳細は文献 [3] 等を参照されたい．
例として
という設定について考える．
local all trust host all 127.0.0.1 255.255.255.255 trust host foo xxx.yyy.zzz.0 255.255.255.0 md5
上の設定の概要は次の通りである：
【1 行目】 local データベースへ UNIX ドメインソケットでアクセス
（実際には，データベースサーバにログインし， psql コマンドでアクセス）
する場合について設定するall 設定対象のデータベースは「すべて」 trust 認証は不要
【2 行目】 host データベースサーバへ TCP/IP でアクセスする場合について設定する all 設定対象のデータベースは「すべて」 127.0.0.1 及び
255.255.255.255接続元 IP は 127.0.0.1 trust 認証は不要
【3 行目】 host データベースサーバへ TCP/IP でアクセスする場合について設定する foo 設定対象のデータベースは「foo」のみ 10.0.0.0 及び
255.255.255.0接続元 IP は xxx.yyy.zzz.1 〜 xxx.yyy.zzz.254 md5 暗号化されたパスワードによる認証が必要
次に，認証に用いるためのパスワードの設定について説明する．
まず，データベースサーバにてスーパーユーザ（おそらくは postgres）に su する．
そして，データベースユーザのパスワードを設定する．
例えば，アクセスが許可されているリモートホスト ( xxx.yyy.zzz.aaa ) から，データベース foo へアクセスする場合， 次のようにパスワードが必要となる （ただし，データベースユーザを「dbuser」，データベースサーバを 「xxx.yyy.zzz.aaa」とした場合）：
【参考文献/サイト】
[1] 笠原 規男, "Linux データベース・レシピ," LinuxJapan 1999 8 月号 〜 12 月号.
[2] 日本 PostgreSQL ユーザ会
[3] 石井達夫，"PostgreSQL 完全攻略ガイド，" 技術評論社，2001.