PostgreSQLの初歩

前置き

PostgreSQLのインストールや管理、psqlコマンドについて整理します。SQLのことには触れません。普通なら、インストール手順から始めるところですが、ちょっと気分を変えて、頻度の高いユースケースから先に書きます。

本文

psqlコマンド

man pageによればpsqlは、a terminal-based front-end to PostgreSQLです。シェルと言うかインタープリタと言うか、そんな感じのものです。主な使い方は以下の通りです。

 $ psql -l
 $ psql -c "SELECT * FROM mytable1" mydb1 user1
 $ psql -f script.sql mydb1 user1
 $ psql mydb1

上から順に、①DBリスト表示、②SQL文実行、③SQLスクリプト実行、④シェル起動です。mydb1はDB名、user1はDBのユーザ名です。DB名とユーザ名は、Linuxユーザ名と同じなら省略可能です。②や③に関しては、さらに下記のオプションが使えます。

-t
見出しを出さない。
-e
エコーバックする。
-H -T border
HTMLの<table>要素の形式で結果を表示。
-o FILENAME
ファイルへ結果を出力。

シェルを起動すると下記のようなプロンプトが表示されるので、ここにSQL文や、psqlコマンドを打ち込んでいきます。

 mydb1=#

SQL文の場合、最後のセミコロンまで書かないと実行してくれません。psqlコマンドには文末のセミコロンは不要です。psqlコマンドは \ から始まります。主なものを挙げておきます。

\h COMMAND
SQLコマンドのヘルプを表示。
\?
psqlコマンドのヘルプを表示。
\dt
テーブル一覧を表示。
\d TABLENAME
テーブルの構造を表示。
\dp
各テーブルのアクセス権を表示。
\dT
型の一覧を表示。
\du
ユーザ一覧を表示。
\dS
システムテーブルの一覧を表示。
\dn
スキーマ(名前空間)の一覧を表示。
\a
結果出力時の、列の桁揃えをON/OFF(デフォルトはON)。
\x
結果出力時の、列の行展開をON/OFF(デフォルトはOFF)。
\i FILENAME
ファイルの内容を実行。
\s
ヒストリーを表示。
\encoding
エンコーディングを表示。
\encoding ENCODE
エンコーディングを変更。

長いSQL文は複数行に分けてシェルに入力することが可能ですが、入力途中のコマンドはクエリーバッファに格納されており、エディタで編集することも可能です。

\e
エディタを起動。
\p
クエリバッファを表示。
\g
クエリバッファのコマンドを実行。
\r
クエリバッファをリセット。
\w FILENAME
クエリバッファをファイルへ保存。

エディタでコマンドを編集し、保存してエディタを閉じると、あたかも、それを直接シェルに打ち込んだかのように振る舞います。例えばSQLの文末のセミコロンまで書いた場合、(\gしなくても)エディタを閉じるだけでSQL文が実行されます。

ユーザ管理

ユーザを追加するにはcreateuserコマンドを使います。いくつか例を挙げます。

 $ createuser newuser1 -U postgres
 $ createuser -A -R -D newuser2 -U postgres
 $ createuser -a -r -d -P -E -W newuser3 -U postgres

オプションの意味は以下の通りです。

-U
ユーザ追加を実行するユーザ名。ユーザ追加権限を持ったユーザ名を指定すること。ユーザ名がLinuxユーザ名と同じなら省略可能
-a
新ユーザをスーパーユーザーにする。
-A
新ユーザをスーパーユーザーにしない。
-r
新ユーザにユーザ追加権限を与える。
-R
新ユーザにユーザ追加権限を与えない。
-d
新ユーザにDB追加権限を与える。
-D
新ユーザにDB追加権限を与えない。
-P
新ユーザのパスワード入力を促す。
-E
新ユーザのパスワードを暗号化して保存する。
-W
サーバにつなぐためにパスワード入力を強要する。

ユーザを削除するにはdropuserコマンドを使います。

 $ dropuser newuser1 -U postgres

-Uオプションが省略可能な点は、createuserコマンドと同様です。

DB管理

DBの作成と削除には、それぞれ、createdbとdropdbコマンドを使います。

 $ createdb db1
 $ dropdb db1

DBやテーブルをバックアップするには、pg_dumpコマンドを使います。

 $ pg_dump db1 -f db1.dump -U postgres
 $ pg_dump -t table1 db1 -f db1_table1.dump -U postgres

リストアするには、pg_restoreコマンドを使います。また、生成されたバックアップファイルはpsqlのスクリプトファイルになっているので、psqlで読み込んでもリストアできます。

 $ pg_restore -d db1 db1.dump
 $ psql db1 -f db1.dump -U postgres

インストール

インストールするには、もちろんemergeコマンドを使います。成功すると、「emerge --configしろ」みたいなメッセージが出るので、言われた通りにします(いろいろとカスタマイズしたい人でない限り)。

 # emerge postgresql
 ...
 LOG: postinst
 Execute the following command to setup the initial database environment:

 emerge --config =postgresql-8.1.11
 ...
 # emerge --config =postgresql-8.1.11

自動的にLinuxユーザpostgresが登録されるので、パスワードを設定しておきます(DBユーザとしてのpostgresも自動登録されています)。

 # passwd postgres

あとは、いつものやつで。

 # rc-update add postgresql default
 # /etc/init.d/postgresql start

ちなみに、デフォルトでは以下のディレクトリがDB用のストレージとなります。

 /var/lib/postgresql/data
Last modified:2010/05/10 21:36:32
Keyword(s):
References:[サーバ管理・Linux関連]
This page is frozen.