2013-03-21 13:10 by 仁伯爵

前回、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である。

別にスーパーユーザーじゃなくても打てるコマンドばっかり打ってしまったが、なんとなくわかってきた。なんだか行けそうな気がする。あと為すべきことは、一般接続用のロールを作って、データベースを作って、スキーマを作って、テーブルを作って、テストデータを入れればよろしかろう。なんだか長くなってしまったので、そのあたりは次回、改めて述べたいと思う。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です


*


*

投げ銭はこちらへ
bitcoin : 1AFU37YroGt8ohmFz8nG1N2ockL56Z4hfQ

ADA Coin: DdzFFzCqrhskq33AqGL8XkJZ3bb1hpxJYTd2UrJFKVpXphWG8d1RuhQrKymmKU1zzjvGi7oU69PaJ7nXECRG4Kpvg27Pghf3hpRNhRMy
2024年11月
 123
45678910
11121314151617
18192021222324
252627282930  
カテゴリー