Postgres : funini.com

PostgreSQL

基本的な使い方

初めの操作はpostmasterユーザやpostgresユーザで行う。とりあえず必要な設定はこのくらい。(tcshの場合)
```
setenv  POSTGRES_HOME   /var/lib/postgresql
setenv  PGLIB   $POSTGRES_HOME/lib
setenv  PGDATA  $POSTGRES_HOME/data
```
設定が済んだら起動しましょう。パッケージで入れたときは軌道スクリプトが出来てるので、
```
$ sudo /etc/init.d/postgresql start (開始)
$ sudo /etc/init.d/postgresql stop  (終了)
```
とか。 tarから入れたときは
```
$ pg_ctl start
postmaster successfully started
```
これで色々な操作ができるようになる。
起動できてるか確認するには
```
$  pg_ctl status
pg_ctl: postmaster is running (pid: 1907)
```
とする。終了するには
```
$ pg_ctl stop
postmaster successfully shut down
```
まあ実際WEBサーバとかで使う場合は、立ち上げっぱなしにしないと使いものにならないから、 tarから入れた場合でも/etc/init.d/に自動起動の設定をしましょう。
データベース作成/削除
psql経由ではなくて、普通のコマンドラインで行う
```
$ createdb hoge-db
CREATE DATABASE

$ dropdb hoge-db
DROP DATABASE
```
ユーザー作成
普段からデータベースにpostmasterでアクセスするのはこわいので、別のユーザーを作ります。
```
$ createuser lucy
CREATE USER
```
削除は
```
$ dropuser lucy
DROP USER
```
とする。これらはpsql中でcreate user lucyみたいにしてもいい。
ユーザーを作っただけでは何もできないので、grantで権限を委譲します。
```
$ psql hoge-db
hoge-db-# grant select on test_table to lucy;
```
ユーザーにパスワードを作成するには
```
alter user lucy with password 'hogehoge';
```
とする。
ユーザーの一覧を表示するには、psql中で
```
# select * from pg_user;
```
とする。
postgresは標準ではローカルのマシンからしかアクセスできない。パスワードを用いて、外部からのアクセスを許可するには、以下の設定ファイルを編集。
(…なんでこんな分かりにくい場所にあるんだろ。/etc/postgres.confとかだったらいいのに)
```
[/var/lib/pgsql/data/pg_hba.conf]
#local all postgres ident sameuser
local all all md5

[/var/lib/pgsql/data/postgresql.conf]
tcpip_socket = on
```
とか。
PostgreSQLではデータベース自体に関する問い合わせはSQL文を使わないようです。 (MySQLのようにshow tablesみたいなコマンドはない)
その代わり、psql -[オプション]や、 psqlの対話式のプロンプトで\で初まるコマンドを用いて表示します。例えば、データベース一覧の表示は下のようにします。
```
$ psql -l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+----------
 hoge-db   | postgres | SQL_ASCII
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(4 rows)
```
これは、psqlの対話式のプロンプトで\lと打ってもよいです。
```
$ psql hoge-db
hoge-db=# \l
```
他に使えるコマンドとしては、\dt [テーブル名](テーブルの構成)など。
テーブル一覧やテーブルの情報は、独自のテーブルで管理されているらしいです。とりあえずpg_tables、pg_classあたりが怪しいです。
対話式での操作方法
```
$ psql hoge-db
hoge-db=#
```
この画面でcreate tableやinsert,deleteを行う。 sql文は最後にセミコロン(;)が必要な点に注意。;がないと、文の途中と扱われる。例えば、
```
hoge-db=# create table members (
hoge-db(# id integer,
hoge-db(# name varchar(20)
hoge-db(# );
CREATE TABLE
hoge-db=#
```
って感じ。終了するときは
```
postgres=# \q
$
```
と入力する。
対話式での操作方法
短いsql文なら、普通のシェルで
```
$ psql hoge-db -c "insert into members values(1, 'kate')"
```
のようにしてもよい。こっちの方がシェルに作業ログが残っていいかも。

バッチ処理
逆に長いsql文は予めファイルに書いておいて、まとめて処理する(バッチ処理)
下のようなhoge-db.sqlを書いてみた。

create table members(
id integer,
name varchar(20),
age integer
);

insert into members values(0, 'kay', 20);
insert into members values(1, 'ray', 15);
insert into members values(2, 'may', 10);
select * from members;

これを処理する例を下にあげる。

$ psql < hoge-db.sql 
CREATE TABLE
INSERT 17017 1
INSERT 17018 1
INSERT 17019 1
 id | name | age 
----+------+-----
  0 | kay  |  20
  1 | ray  |  15
  2 | may  |  10
(3 rows)

あるいは、-fオプションを使ってもいいです

$ psql -f hoge-db.sql

これでも全く同じ結果が得られます。

postgresは標準では一回一回の操作に対しTransactionが完結しているが、データが巨大な時や、複数の操作をまとめて行いたい時は明示的にTransactionを指定できる。
```
BEGIN;
insert into hoge VALUES('hoge!', 'hoge!');
...
END;
```
あるいは、全く新しいDBにデータを突っこむ場合は
```
COPY sec_name FROM stdin USING DELIMITERS ',';\n
1, 'hoge'
2, 'fuga'
\.
```
みたいなファイルをstdinから流すと楽。
構築したデータベースをSQLファイルに戻す(バックアップ)
```
$ pg_dump hoge-db > hoge-bak.sql
```
これはデータのバックアップや、同じデータベースを別の場所に構築する際に用いられる。復元するときは、予め空のデータベース(この場合はhoge2-db)を用意した上で
```
$ psql -e hoge2-db < hoge-bak.sql
```
とする。
cronを使って毎日バックアップ。backup.shをしたのように書く。
```
#!/bin/sh

pg_dump hoge-db > /somewhere/`date +"db_%Y_%m%d".sql`
```
として、
```
$ crontab -e
59 11 1 * *     /some/place/backup.sh
```
とすると、一ヶ月に一回(1日の11:59)にdb_2007_0801.sqlみたいなファイルにdbがバックアップされる。
テーブル名変更。
```
alter table hoge rename to super_hoge;
```
テーブルにカラム(行)を追加。soft_genreという行をsoftwareテーブルに追加してみた。
```
ALTER TABLE software ADD COLUMN soft_genre text;
```
ここで、増やしたカラムにはnullが入っているので注意。空文字列('')とnullは違う。増やした場合は、nullが入る。
```
update software set soft_genre = '' where soft_genre is null
```

サーバー乗り換え

データベースはバージョンによって中のデータの保存形式は違うかもしれないけど、見かけ上のデータはSQLの文法に従っているから、簡単に引っ越しができるはず。「方言」のおかげでなかなかそうもいかないけど。
以下はpostgres同士の乗り換えの話。基本は、

$ pgdump hoge-db > hoge-dump.sql

とすると、hoge-dump.sqlにデータベースを作ったときに発行したコマンドが並ぶから、これを引っ越し先で

$ psql  hoge-db < hoge-dump.sql

とすれば完了…なはず。だけどなかなかうまくいかない。
とりあえずデータが移ればいいなら、テーブル一つずつcopyするのが良さげ。

$ psql hoge-db
hoge-db=# \copy test-table to "test-table.sql"

とやって、移行先のDBでtest-tableを作った上で、

$ psql hoge-db
hoge-db=# \copy test-table from "test-table.sql"

とすると、テーブルの中身を移行できる。

インデックス使ってよ！

postgresですごく簡単なテーブルを作って、クエリをするんだけど、インデックスを使ってくれない。

> create table members(id int2 primary key, name char(16));
NOTICE: ... create implicit index "members_pkey" for table "members"

> insert into members values(0, 'kei');
> insert into members values(1, 'rei');
...(大量にinsert)
> select name from members where id = 1;
...(遅い)

idに対してインデックスがあるはずなのに、なんかすごく遅い。ecplainしてみると

> explain select name from members where id = 1;
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on members  (cost=0.00..22.50 rows=1 width=52)
   Filter: (id = 1)
(2 rows)

シーケンシャルスキャンになってる。
vacuum analyze (select方法のキャッシュを削除) しても同じ。
でも、1を''で囲むとなんか速くなった。explainしてみると

> explain select name from members where id = '1';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Scan using members_pkey on members  (cost=0.00..4.82 rows=1 width=52)
   Index Cond: (id = 1::smallint)
(2 rows)

ちゃんとIndex Scanしてる。
どうやら、int2やint8に対する比較は、値の方に''を付けないと、インデックスが用いられない模様。キャストの関係なんだろうけど、変な仕様…
☆この件に関しては、井谷さんにお世話になりました。