PostgreSQL/DB操作

PostgreSQL +

ユーザ作成 +

ユーザ作成 +

• PostgreSQLのユーザ作成には[createuser]コマンドを利用します。 ユーザ作成時は下記を用途に合わせて設定します。
  • 新しいロールをスーパーユーザーとするか
  • 新しいロールに対してデータベース作成を許可するか
  • 新しいロールに対してロール作成を許可するか

• 一般ユーザ(hoge)の作成

  $ createuser hoge
  Shall the new role be a superuser? (y/n) n
  Shall the new role be allowed to create databases? (y/n) y
  Shall the new role be allowed to create more new roles? (y/n) y
  CREATE ROLE

• WEB(+PHP)サーバ接続用のユーザを作成 このユーザはアプリ（PHP,JAVA)からデータベースへ接続する際に利用されます。

  $ createuser apache
  Shall the new role be a superuser? (y/n) n
  Shall the new role be allowed to create databases? (y/n) n
  Shall the new role be allowed to create more new roles? (y/n) n
  CREATE ROLE

データベースの確認 +

• 「psql」コマンドでデータベースの一覧を確認します。尚、この操作はpostgresユーザで実施します。

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

データベースの作成 +

• データベース「testdb」を作成します。

  $ createdb testdb
  $ psql -l
          List of databases
     Name    |  Owner   | Encoding
  -----------+----------+----------
   postgres  | postgres | UTF8
   template0 | postgres | UTF8
   template1 | postgres | UTF8
   testdb    | postgres | UTF8
  (4 rows)

• testdbへ接続します。

  $ psql testdb
  Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
  
  Type:  \copyright for distribution terms
         \h for help with SQL commands
         \? for help with psql commands
         \g or terminate with semicolon to execute query
         \q to quit

• テーブルを作成します。

  $ CREATE TABLE t_test(
     id serial primary key,
     name text,
     time timestamp default now()
    );

• テーブルの一覧を表示

  testdb=# \d
                  List of relations
   Schema |       Name        |   Type   |  Owner
  --------+-------------------+----------+----------
   public | t_test            | table    | postgres

テーブルのアクセス権を設定 +

• apacheユーザに「t_test」へのアクセス権を付与します。

  testdb=# GRANT ALL ON t_test TO apache;

• アクセス権の確認

  testdb=# \z
                             Access privileges for database "testdb"
   Schema |       Name        |   Type   |                  Access privileges
  --------+-------------------+----------+-----------------------------------------------------
   public | t_test            | table    | {postgres=arwdRxt/postgres,apache=arwdRxt/postgres}

各種コマンド +

テーブル操作 +

• テーブル一覧

  \d

• テーブル作成

  CREATE TABLE

• テーブル変更

  ALTER TABLE
  

• テーブル削除

  DROP TABLE

アクセス権 +

• アクセス権一覧

  \z

• アクセス権変更

  GRANT ALL ON テーブル名 TO apache;

データ操作 +

• データ検索

  SELECT

• データ登録

  INSERT INTO

• データ変更

  UPDATE

• データ削除

  DELETE FROM

トランザクション関連 +

• トランザクション開始

  BEGIN

• トランザクションのコミット

  COMMIT

• トランザクションのアボート

  ROLLBACK

その他 +

• SQLのチューニング 問い合わせについて、実行計画をコスト表示します。SQL文をチューニングする際に利用します。

  EXPLAIN