前回、PHPからの接続の不安要素であったpdo_sqlite、pgsqlとPosgtgreSQLの間のバージョンの違いを是正してみたわけだが、これまでwordpressで作るアンテナサイトシリーズではWordPressを使っていたのでMySQLを使用しており、業務ではもっぱらDB2を使用していたため、実のところPostgreSQLを扱うのは初めてなのである。なのでPostgreSQLのお作法がとんと知れぬ。MySQLにはスキーマが無かったりするが、なんとPostgreSQLにはちゃんとスキーマがあったりするのだ!すごい!なのでまずはそこからPostgreSQLの公式ドキュメントを紐解きながら学んでいき、差し当たってはtestdbを作成しPHPから接続できるところまでを目標と定めて作業を行っていく。以下はその行った作業の備忘録である。
1.とりあえずPostgreSQLのスーパーユーザーでログインしてみる
PostgreSQLは言うまでもなくデータベースサーバーである。Webサーバーであるところのnginxと同様に、外部からの接続を前提としたソフトウェアだ。なので、起動するユーザーはrootユーザでは在り得ない。なぜなら、rootで起動しちゃったら外部から接続してきたユーザが悪意を持っていたらどんな攻撃でも自由自在にできちゃって困っちゃうからだ。nginxはデフォルトではnginxというユーザーで起動する仕組みになっている(コンフファイルの設定により変更可能)。PostgreSQLではpostgresというユーザーで起動されている。
# ps -ef | grep nginx
nginx 1065 1064 0 12:08 ? 00:00:00 php-fpm: pool www
nginx 1066 1064 0 12:08 ? 00:00:00 php-fpm: pool www
nginx 1067 1064 0 12:08 ? 00:00:00 php-fpm: pool www
nginx 1068 1064 0 12:08 ? 00:00:00 php-fpm: pool www
nginx 1069 1064 0 12:08 ? 00:00:00 php-fpm: pool www
root 1081 1 0 12:08 ? 00:00:00 nginx: master process /usr/sbin/nginx -c /etc/nginx/nginx.conf
nginx 1083 1081 0 12:08 ? 00:00:00 nginx: worker process
nginx 1084 1081 0 12:08 ? 00:00:01 nginx: cache manager process
root 4090 4073 0 23:18 pts/1 00:00:00 grep nginx
#
#
# ps -ef | grep postgre
postgres 967 1 0 12:08 ? 00:00:11 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres 969 967 0 12:08 ? 00:00:00 postgres: logger process
postgres 971 967 0 12:08 ? 00:00:00 postgres: checkpointer process
postgres 972 967 0 12:08 ? 00:00:02 postgres: writer process
postgres 973 967 0 12:08 ? 00:00:01 postgres: wal writer process
postgres 974 967 0 12:08 ? 00:00:10 postgres: autovacuum launcher process
postgres 975 967 0 12:08 ? 00:00:07 postgres: stats collector process
postgres 1469 1468 0 13:24 pts/0 00:00:00 -bash
postgres 1494 1469 0 13:25 pts/0 00:00:00 psql hagakuredb -U hagakure -W
postgres 1495 967 0 13:25 ? 00:00:00 postgres: hagakure hagakuredb [local] idle
root 4094 4073 0 23:19 pts/1 00:00:00 grep postgre
#
PostgreSQLのスーパーユーザーであるところのpostgresにスイッチするとpsqlコマンドが使えるので、そこからロールを作ったり、データベースを作ったりすることになる。
postgresユーザーにパスワードを設定
# passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: 全ての認証トークンが正しく更新できました。
#
#
postgresユーザーにスイッチ
# su - postgres
-bash-4.1$
-bash-4.1$
-bash-4.1$
満を持してpsqlコマンド!
-bash-4.1$ psql
psql (9.2.3)
"help" でヘルプを表示します.
postgres=#
postgres=#
postgres=#
psqlコマンドを実行した後、プロンプトが「ユーザー名=#」となっているのはスーパーユーザーのプロンプトである。これでとりあえず、スーパーユーザーでデータベースを作ったり、ロールを作ったり出来るようになった。
postgresユーザーにパスワードを設定したのは、デフォルトではpsqlコマンドでDB(ドラゴンボールではない)に接続する認証をデフォルトのPeer認証からパスワード認証に変更することをたくらんでいるからだ。PostgreSQLのロールと、OSのユーザーは必ずしも一致しなくてもいいんだぞ!というのを試すためである。パスワードを設定しないままpeer認証からパスワード認証に変更するとpsqlコマンドがらろグイン出来なくなってしまう。この辺りはロールの作成の所で詳しく述べたいと思う。
2.スーパーユーザーでいろいろ確認してみる
psqlの世界に入ったら、\(バックスラッシュ)から始まるPostgreSQLのコマンドと、;(セミコロン)でしめるSQLコマンドが打てる。それぞれヘルプコマンドがわかれていて、PostgreSQLのコマンドは\?で、SQLコマンドは\hでヘルプが表示される。
postgres=#
postgres=# help;
PostgreSQL へのコマンドライン・インターフェース、psql へようこそ。
\copyright とタイプすると、配布条件を表示します。
\h とタイプすると、SQL コマンドのヘルプを表示します。
\? とタイプすると、psql コマンドのヘルプを表示します。
\g と打つかセミコロンで閉じると、問い合わせを実行します。
\q で終了します。
postgres=#
postgres=#
postgres=# \?
一般
\copyright PostgreSQL の使い方と配布条件を表示
\g [ファイル] または ';' 問い合わせを実行(し、結果をファイルまたは |パイプ へ書き出す)
\h [名前] SQL コマンドの文法ヘルプ、* で全コマンド
\q psql を終了する
問い合わせバッファ
\e [ファイル] [行番号] 現在の問い合わせバッファ(やファイル)を外部エディタで編集する
\e [関数名 [行番号]] 関数定義を外部エディタで編集する
\p 問い合わせバッファの内容を表示する
\r 問い合わせバッファをリセット(クリア)する
\s [ファイル] ヒストリを表示またはファイルに保存する
\w ファイル 問い合わせバッファの内容をファイルに書き出す
入出力
\copy ... クライアントホストに対し、データストリームを使ってSQLコピーを行う
\echo [文字列] 文字列を標準出力に書き出す
\i ファイル ファイルからコマンドを読み込んで実行する
\ir ファイル \iと同じ。ただし現在のスクリプトの場所からの相対パス
\o [ファイル] すべての問い合わせの結果をファイルまたは |パイプ へ送る
\qecho [文字列] 文字列を問い合わせ出力ストリームに出力(\o を参照)
情報
(修飾子: S = システムオブジェクトを表示 + = 付加情報)
\d[S+] テーブル、ビュー、シーケンスの一覧を表示する
\d[S+] 名前 テーブル、ビュー、シーケンス、インデックスの説明を表示する
\da[S] [パターン] 集約関数の一覧を表示する
\db[+] [パターン] テーブルスペースの一覧を表示する
\dc[S+] [パターン] 変換ルールの一覧を表示する
\dC[+] [パターン] キャストの一覧を表示する
\dd[S] [パターン] 他では表示されないオブジェクトの説明を表示する
\ddp [パターン] デフォルト権限の一覧を表示する
\dD[S+] [パターン] ドメインの一覧を表示する
\det[+] [パターン] 外部テーブルの一覧を表示する
\des[+] [パターン] 外部サーバーの一覧を表示する
\deu[+] [パターン] ユーザマッピングの一覧を表示する
\dew[+] [パターン] 外部データラッパーの一覧を表示する
\df[antw][S+] [パターン] 関数(集約/通常/トリガー/ウィンドウのみ)の一覧を表示する
\dF[+] [パターン] テキスト検索設定の一覧を表示する
\dFd[+] [パターン] テキスト検索用辞書の一覧を表示する
\dFp[+] [パターン] テキスト検索用パーサーの一覧を表示する
\dFt[+] [パターン] テキスト検索用テンプレートの一覧を表示する
\dg[+] [パターン] ロールの一覧を表示する
\di[S+] [パターン] インデックスの一覧を表示する
\dl ラージオブジェクトの一覧を表示する。\lo_list と同じ。
\dL[S+] [パターン] 手続き言語の一覧を表示する
\dn[S+] [パターン] スキーマの一覧を表示する
\do[S] [名前] 演算子の一覧を表示する
\dD[S+] [パターン] 照合順序の一覧を表示する
\dp [パターン] テーブル、ビュー、シーケンスのアクセス権一覧を表示する
\drds [パターン1 [パターン2]] データベース毎のロール(ユーザー)設定の一覧を表示する
\ds[S+] [パターン] シーケンスの一覧を表示する
\dt[S+] [パターン] テーブルの一覧を表示する
\dT[S+] [パターン] データ型の一覧を表示する
\du[+] [パターン] ロールの一覧を表示する
\dv[S+] [パターン] ビューの一覧を表示する
\dE[S+] [パターン] 外部テーブルの一覧を表示する
\dx[+] [パターン] 拡張の一覧を表示する
\l[+] データベースの一覧を表示する
\sf[+] 関数名 関数定義を表示する
\z [パターン] \dp と同じ
書式設定
\a 出力モードの 'unaligned' / 'aligned' を切り替える
\C タイトル テーブルのタイトルを設定する。指定がなければ解除
\f [文字列] 桁揃えを行わない(unaligned)問い合わせ出力におけるフィールド区切り文字を表示または設定
\H HTML の出力モードを切り替える(現在: オフ)
\pset 名前 [値] テーブル出力のオプションを設定する
(名前 := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
\t [on|off] 行のみを表示するか? (現在: オフ)
\T [文字列] HTML の <table> タグの属性をセット。引数がなければ解除
\x [on|off|auto] 拡張出力の切り替え(現在: オフ)
接続
\c[onnect] [DB名|- ユーザ名|- ホスト名|- ポート番号|-]
新しいデータベースに接続する (現在: "postgres")
\encoding [エンコーディング]
クライアントのエンコーディングを表示またはセット
\password [ユーザ名] ユーザのパスワードを安全に変更する
\conninfo 現在の接続に関する情報を表示する
オペレーティングシステム
\cd [DIR] カレントディレクトリを変更
\setenv NAME [VALUE] 環境変数の設定、設定解除を行う
\timing [on|off] コマンドのタイミングを切り替える(現在: オフ)
\! [コマンド] シェルでコマンドを実行、もしくは会話型シェルを起動
変数
\prompt [テキスト] 変数名 ユーザに内部変数をセットするよう促す
\set [変数名 [値]]
内部変数の値をセット。引数がない場合は一覧表示。
\unset 変数名 内部変数を削除する
ラージオブジェクト
\lo_export LOBOID ファイル名
\lo_import ファイル名 [コメント]
\lo_list
\lo_unlink LOBOID ラージオブジェクトの操作
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# \h
利用可能なヘルプ:
ABORT COMMENT DECLARE EXECUTE
ALTER AGGREGATE COMMIT DELETE EXPLAIN
ALTER COLLATION COMMIT PREPARED DISCARD FETCH
ALTER CONVERSION COPY DO GRANT
ALTER DATABASE CREATE AGGREGATE DROP AGGREGATE INSERT
ALTER DEFAULT PRIVILEGES CREATE CAST DROP CAST LISTEN
ALTER DOMAIN CREATE COLLATION DROP COLLATION LOAD
ALTER EXTENSION CREATE CONVERSION DROP CONVERSION LOCK
ALTER FOREIGN DATA WRAPPER CREATE DATABASE DROP DATABASE MOVE
ALTER FOREIGN TABLE CREATE DOMAIN DROP DOMAIN NOTIFY
ALTER FUNCTION CREATE EXTENSION DROP EXTENSION PREPARE
ALTER GROUP CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER PREPARE TRANSACTION
ALTER INDEX CREATE FOREIGN TABLE DROP FOREIGN TABLE REASSIGN OWNED
ALTER LANGUAGE CREATE FUNCTION DROP FUNCTION REINDEX
ALTER LARGE OBJECT CREATE GROUP DROP GROUP RELEASE SAVEPOINT
ALTER OPERATOR CREATE INDEX DROP INDEX RESET
ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE
ALTER OPERATOR FAMILY CREATE OPERATOR DROP OPERATOR ROLLBACK
ALTER ROLE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK PREPARED
ALTER SCHEMA CREATE OPERATOR FAMILY DROP OPERATOR FAMILY ROLLBACK TO SAVEPOINT
ALTER SEQUENCE CREATE ROLE DROP OWNED SAVEPOINT
ALTER SERVER CREATE RULE DROP ROLE SECURITY LABEL
ALTER TABLE CREATE SCHEMA DROP RULE SELECT
ALTER TABLESPACE CREATE SEQUENCE DROP SCHEMA SELECT INTO
ALTER TEXT SEARCH CONFIGURATION CREATE SERVER DROP SEQUENCE SET
ALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP SERVER SET CONSTRAINTS
ALTER TEXT SEARCH PARSER CREATE TABLE AS DROP TABLE SET ROLE
ALTER TEXT SEARCH TEMPLATE CREATE TABLESPACE DROP TABLESPACE SET SESSION AUTHORIZATION
ALTER TRIGGER CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH CONFIGURATION SET TRANSACTION
ALTER TYPE CREATE TEXT SEARCH DICTIONARY DROP TEXT SEARCH DICTIONARY SHOW
ALTER USER CREATE TEXT SEARCH PARSER DROP TEXT SEARCH PARSER START TRANSACTION
ALTER USER MAPPING CREATE TEXT SEARCH TEMPLATE DROP TEXT SEARCH TEMPLATE TABLE
ALTER VIEW CREATE TRIGGER DROP TRIGGER TRUNCATE
ANALYZE CREATE TYPE DROP TYPE UNLISTEN
BEGIN CREATE USER DROP USER UPDATE
CHECKPOINT CREATE USER MAPPING DROP USER MAPPING VACUUM
CLOSE CREATE VIEW DROP VIEW VALUES
CLUSTER DEALLOCATE END WITH
postgres=#
これでだいたい使い方は分かった!色々表示させてみればなんとなくPostgreSQLの世界が見えてくるはずだ。早速、情報を表示させる系のコマンドを打ちまくってみよう。
PostgreSQLのバージョンを表示
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
(1 行)
postgres=#
postgres=#
うむ。9.2.3だ!コンパイルしたgccのバージョンまで分かってしまう。なるほどぉ。次行ってみよう!
ロール一覧を表示
postgres=# \du
ロール一覧
ロール名 | 属性 | メンバー
----------+----------------------------------------------------------------------+----------
postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}
postgres=#
postgres=#
\duでロールを表示させると、postgresというスーパーユーザーのロールが一つあるだけである。PostgresSQLではロールをユーザーとして使用する事が一般的であるようだ。
データベース一覧を表示
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権
------------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行)
postgres=#
postgres=#
postgres=#
デフォルトの状態でデータベースが3つある。postgresはPostgreSQLのコマンドから参照される。template0とtemplate1はデータベースを作成する際の雛形として使用される。template0は接続はできないし、しちゃいけない。template1は、編集してデーターベースの作成時に指定すると、雛形として便利に使える。
スキーマ一覧を表示
postgres=# \dn
スキーマ一覧
名前 | 所有者
--------+----------
public | postgres
(1 行)
postgres=#
postgres=#
デフォルトではpublicというスキーマ一つがある。何も指定しなければこのスキーマが暗黙的に使用される。
現在使用しているDBを表示
postgres=# select current_database();
current_database
------------------
postgres
(1 行)
postgres=#
postgres=#
postgresロールでは、データベース名postgresに接続されている。
現在使用してるスキーマを表示
postgres=# select current_schema();
current_schema
----------------
public
(1 行)
postgres=#
postgres=#
現在使用しているスキーマは予想通りpublicである。
別にスーパーユーザーじゃなくても打てるコマンドばっかり打ってしまったが、なんとなくわかってきた。なんだか行けそうな気がする。あと為すべきことは、一般接続用のロールを作って、データベースを作って、スキーマを作って、テーブルを作って、テストデータを入れればよろしかろう。なんだか長くなってしまったので、そのあたりは次回、改めて述べたいと思う。