postgreSQL基本操作

データベース作成・削除

まずは、データベースを作成しなければ始まりません。
PostgreSQLでは1つのシステムで複数のデータベースを持つことが出来ます。
また、ここで説明する操作はpostgreSQLのDBスーパーユーザであるpostgresユーザへスイッチして実施する必要があります。

# su - postgres

それでは、postgresユーザへスイッチできたとして進めていきましょう。

(1) データベースの作成
【書式】
createdb データベース名

$ createdb dbtest
CREATE DATABASE

これで、dbtestというデータベースが作成できました。

(2) データベースの確認
作成したデータベースができているか確認してみましょう。

$ psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 dbtest    | postgres | SQL_ASCII    <--作成したDB名が表示されます。
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(3 rows) 

作成したDBが表示されればDB作成は出来ています。また、template0,template1はデフォルトで作成されています。

(3) データベースの削除
【書式】
dropdb データベース名

$ dropdb dbtest
DROP DATABASE

dbtestというデータベースが削除されます。
実際に削除されたか確認してみましょう。

$ psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(2 rows) 

デフォルトの2つのデータベースのみになっています。

データベースへの接続

データベース接続には psql コマンドを使って接続をします。先ほどのDB接続にも psql コマンドがでましたが引数にデータベースを指定することで接続ができます。
【書式】
psql データベース名

$ psql dbtest
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

dbtest=#

これでDBへの接続ができました。
データベース名# が接続したコンソール画面になります。

接続語は、SQL文を受け付けるようになります。psql では、SQL の最後に;(セミコロン)をつける必要があります。ここでは、SQLコマンドについては省略させて頂きます。

SQLコマンドではなくpsqlで利用できるコマンドはよく利用するコマンドは以下のものがあります。

\q	終了
\d	テーブルの表示
\?	\からはじまるpsqlコマンドのHELP表示です。その他のコマンドにつてはHELPを参照してください。

では、いったんデータベースから外にでましょう。

dbtest=#  \q
$

データベースユーザの作成

デフォルトではpostgresユーザのみしかDBへの接続ができません。接続かのうなユーザを作成するには以下のように実行します。
【書式】
createuser ユーザ名

ここで作成するユーザは /etc/password にいるユーザとは別ユーザだという認識をしてください。データベースのユーザは別で管理します。

$ createuser hogehoge
Shall the new user be allowed to create databases? (y/n) y
# ここでの質問はデータベースを作成する事が出来るようにするかという意味です。
# 特に支障がなければy(yes)で問題ないでしょう。

Shall the new user be allowed to create more new users? (y/n) n
# ここでの質問はuser作成が出来るようにするかという意味になります。
# postgres意外にDBのスーパーユーザを作成したいならy(yes)ですが通常はn(no)
# を選択します。

CREATE USER

これで、hogehogeというユーザを作成したことになります。
で、先ほどからpostgresユーザでコマンドを実行していましたが実は、コマンドに -U を付加すればその権限で接続できるんです。省略をするとシステムユーザのユーザ名でデータベースへ接続することになります。どういう事かというと。。。。↓

$ exit   <- rootへ戻ります
$ psql dbtest   <- rootの状態でデータベースへ接続をすると。。。
psql: FATAL:  user "root" does not exist 
 <- というようにデータベースにrootユーザは存在しないと怒られます。(^^;
 そこで -U を利用してpostgresを指定すると
$ psql -U postgres dbtest
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

dbtest=# <- postgresユーザで接続できました。

という具合に -U を省略するとシステムユーザの名前でそのままデータベースへ接続しようとします。-U をつければそのユーザで接続できますのでいちいち、postgreへスイッチをしなくてもよいのです。
createuser -U postgres データベース名 とすることも出来ます。

しかし、現在の状態ではDBへの接続には認証もありませんのでローカルユーザなら誰でも簡単に接続が出来てまいます。これではセキュリティ的にあんまりよろしくないですよね。自分だけ利用するのであれば問題ないですが、友人にデータベースの領域を提供したりしたときには問題が生じます。つづいてパスワード設定方法を説明します。

また、ユーザの削除は
【書式】
dropuser ユーザ名

# dropuser -U postgres hogehoge
DROP USER

早速 -U で postgres を指定してユーザを削除してみました。
createuser,dropuserコマンドはユーザの管理する権限のあるユーザのみ利用が認められています。

接続パスワード設定

ローカルユーザならだれでもDBを利用できてはまずいですよね。
では、早速パスワード設定をしてみましょう。パスワード方式には password crypt md5 があるようですが今回は md5 について説明しています。

# psql -U postgres template1

とりあえず、デフォルトにあるデータベースへ接続をします。
そのあと以下の書式でパスワード設定を行います。

【書式】
alter user ユーザ名 encrypted password 'パスワード';

template1=# ALTER USER postgres encrypted password 'passtest';
ALTER USER

ここではスーパーユーザpostgresのパスワードを passtest にしました。
いったんデータベースからでましょう。

template1=# \q
#

つづいて /usr/local/pgsql/data/pg_hba.conf を編集します。
一番下の定義を trust から md5 へ変更します。

local   all         all                                             md5
host    all         all         127.0.0.1         255.255.255.255   md5

変更が完了したらpostgresを再起動します。
再起動後、データベースへの接続をしてみてください。

# psql -U postgres template1
Password: <-パスワードが問われる
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=#

接続にパスワード認証が必要になりましたね。これで安心です（＾＾；

ついでに、パスワードはmd5されておりLINUXと同じように生パスワードをみることができません。パスワードを忘れてしまっては接続できなくなりますので大切に管理してください。
# 接続できなくなれば /usr/local/pgsql/data/pg_hba.conf を md5 から trust に変更して
# 希望のパスワードに変更すればよいのですが。

また、実際に作成されているユーザをみるにはデータベースへ接続後、以下のコマンドで確認ができます。

template1=# SELECT * FROM pg_shadow;

ずらーっとパスワードのデータベースが参照できると思います。
もちろん、パスワードはmd5で生パスワードは分かりません。
簡単ですが基本操作の説明は終わりです。

marronおすすめの参考書 by Amazon

	PostgreSQL 8 for Windows (Database Professional's Library) Mcgraw-Hill Osborne Media / Richard Blum ￥ 5,707
	Beginning PostgreSQL 8 Hungry Minds Inc,U.S. / Edward Lecky-Thompson, Clive Gardner ￥ 6,453
	Database-SQL-Rdbms Howto: Postgresql Object Relational Database System Iuniverse Inc / Alavoor Vasudevan