OSS Fan ~OSSでLinuxサーバ構築~

作成日: 2016/12/15
更新日: 2017/02/20

OSSでLinuxサーバ構築

PostgreSQL 9.6.1でユーザ、データベース、スキーマ、オブジェクトを作成

トップページOSSでLinuxサーバ構築 > PostgreSQL 9.6.1でユーザ、データベース、スキーマ、オブジェクトを作成
このエントリーをはてなブックマークに追加

概要

 CentOS 7.2にPostgreSQL 9.6.1をインストールしたサーバで、 データベースを作成し、テーブルやシーケンスなどのオブジェクトを作成します。 Linux上のコマンド操作はrootユーザではなく、アプリケーション開発用のユーザ・グループを 新規に作成して利用します。

構成

サーバ構成

OSバージョン

CentOS 7.2.1511 x86_64

ソフトウェア・パッケージ一覧

  • postgresql96-9.6.1-1PGDG.rhel7.x86_64.rpm
  • postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64.rpm
  • postgresql96-server-9.6.1-1PGDG.rhel7.x86_64.rpm

サーバ構築

設定

(1) Linuxグループ作成

 アプリケーション開発者がLinux上で使用するユーザが所属するグループ pggroup を作成します。 GIDは 1001 とします。

# groupadd -g 1001 pggroup
# tail -n 1 /etc/group
pggroup:x:1001: ←作成したグループ

(2) Linuxユーザ作成

 アプリケーション開発者がLinux上で使用するユーザ pguser01 を作成します。 GIDは 1001 とし、プライマリグループに pggroup を指定します。

# useradd -u 1001 -g 1001 pguser01
# tail -n 1 /etc/passwd
pguser01:x:1001:1001::/home/pguser01:/bin/bash ←作成したユーザ

(3) PostgreSQLユーザ(ロール)作成

 アプリケーション開発者がPostgreSQL上で使用するユーザ pguser01 を作成します。 パスワードは Pass-123 とします。 スーパーユーザ権限、ロール・DB等の作成権限を持たない、一般ユーザとして作成します。 ユーザの作成はPostgreSQLの管理ユーザである postgres ユーザで実行します。

# su - postgres
最終ログイン: 2017/02/19 (日) 21:47:56 JST日時 pts/1
-bash-4.2$ psql
psql (9.6.1)
"help" でヘルプを表示します.

postgres=# \du
                                               ロール一覧
 ロール名 |                                       属性                                       | メンバー
----------+----------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, Bypass RLS | {}

postgres=# create role pguser01 with login password 'Pass-123';
CREATE ROLE
postgres=# \du
                                               ロール一覧
 ロール名 |                                       属性                                       | メンバー
----------+----------------------------------------------------------------------------------+----------
 pguser01 |                                                                                  | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, Bypass RLS | {}

postgres=#

(4) データベース作成

 アプリケーション用のデータベースを作成します。 今回は住所録をイメージして addressbook という名のデータベースとします。 データベースの所有者は、先ほど作成した pguser01 とします。

postgres=# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権
-----------+----------+------------------+----------+-------------------+-----------------------
 postgres  | postgres | UTF8             | C        | C                 |
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
(3 行)

postgres=# create database addressbook owner pguser01;
CREATE DATABASE
postgres=# \l
                                         データベース一覧
    名前     |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権
-------------+----------+------------------+----------+-------------------+-----------------------
 addressbook | pguser01 | UTF8             | C        | C                 | ←作成したデータベース
 postgres    | postgres | UTF8             | C        | C                 |
 template0   | postgres | UTF8             | C        | C                 | =c/postgres          +
             |          |                  |          |                   | postgres=CTc/postgres
 template1   | postgres | UTF8             | C        | C                 | =c/postgres          +
             |          |                  |          |                   | postgres=CTc/postgres
(4 行)

postgres=#

(5) スキーマ作成

 作成した addressbook データベースに接続し、スキーマを作成します。 PostgreSQLではユーザ(ロール)とスキーマを個別に作成できますが、 Oracleなど他のDBMSではユーザ=スキーマで固定されていたりします。 今回は開発の互換性も加味して addressbook データベースの所有ユーザである pguser01 と 同じ名前のスキーマ pguser01 を作成します。 また、デフォルトで作成される public スキーマは不要なので削除します。

postgres=# \c addressbook ←addressbook データベースに接続
データベース "addressbook" にユーザ"postgres"として接続しました。
addressbook=# \dn ←スキーマ一覧を表示するメタコマンド
   スキーマ一覧
  名前  |  所有者
--------+----------
 public | postgres ←デフォルトのスキーマだが、不要なので後で削除する
(1 行)

addressbook=# create schema pguser01 authorization pguser01; ←pguser01 スキーマを作成
CREATE SCHEMA
addressbook=# \dn
    スキーマ一覧
   名前   |  所有者
----------+----------
 pguser01 | pguser01 ←pguser01 スキーマが作成された
 public   | postgres
(2 行)

addressbook=# drop schema public; ←public スキーマを削除
DROP SCHEMA
addressbook=# \dn
    スキーマ一覧
   名前   |  所有者
----------+----------
 pguser01 | pguser01 ←public スキーマが削除されて pguser01 スキーマだけになった
(1 行)

addressbook=# \q
-bash-4.2$

(6) テーブル・シーケンス作成用DDL作成

 テーブル作成用のDDLとシーケンス作成用のDDLを作成します。 viエディタでファイルを新規に作成します。

-bash-4.2$ cd /data/sql/
-bash-4.2$ vi createtable.ddl
-bash-4.2$ cat createtable.ddl
create table mst_user (
  id integer primary key,
  name varchar(20),
  address varchar(40),
  phone varchar(13),
  create_date timestamp,
  delete_date timestamp,
  last_update timestamp
);

create table mst_group (
  id integer primary key,
  name varchar(20),
  create_date timestamp,
  delete_date timestamp,
  last_update timestamp
);
-bash-4.2$ vi createsequence.ddl
-bash-4.2$ cat createsequence.ddl
create sequence seq_userid increment by 1 minvalue 1;
create sequence seq_groupid increment by 1 minvalue 1;

(7) テーブル作成用DDL実行

 viエディタで作成した、テーブル作成用DDLを実行します。 データベース addressbook の所有者である pguser01 でデータベースに接続します。 先ほど作成した pguser01 スキーマにオブジェクト(テーブル)を作成するには、 create table文で スキーマ名.テーブル名 の形式で指定します。 ただし、優先スキーマとして、接続ユーザ名と同名のスキーマがデフォルトで設定されていますので、 今回はスキーマ名を明記しなくても、pguser01 スキーマにテーブルが作成されます。

-bash-4.2$ psql -U pguser01 addressbook
ユーザ pguser01 のパスワード: ←pguser01のパスワードを入力
psql (9.6.1)
"help" でヘルプを表示します.

addressbook=> show search_path;
   search_path
-----------------
 "$user", public ←ユーザ名と同名のスキーマ(pguser01スキーマ)が優先的に利用される設定になっている
(1 行)

addressbook=> \dt ←テーブル一覧を表示するメタコマンド
リレーションがありません。
addressbook=> \i /data/sql/createtable.ddl ←テーブル作成用DDLを実行
CREATE TABLE
CREATE TABLE
addressbook=> \dt
             リレーションの一覧
 スキーマ |   名前    |    型    |  所有者
----------+-----------+----------+----------
 pguser01 | mst_group | テーブル | pguser01 ←テーブルが作成された
 pguser01 | mst_user  | テーブル | pguser01 ←テーブルが作成された
(2 行)

addressbook=> \d mst_user ←作成したテーブルの定義を表示
             テーブル "pguser01.mst_user"
     列      |             型              |  修飾語
-------------+-----------------------------+----------
 id          | integer                     | not null
 name        | character varying(20)       |
 address     | character varying(40)       |
 phone       | character varying(13)       |
 create_date | timestamp without time zone |
 delete_date | timestamp without time zone |
 last_update | timestamp without time zone |
インデックス:
    "mst_user_pkey" PRIMARY KEY, btree (id)

addressbook=> \d mst_group ←作成したテーブルの定義を表示
            テーブル "pguser01.mst_group"
     列      |             型              |  修飾語
-------------+-----------------------------+----------
 id          | integer                     | not null
 name        | character varying(20)       |
 create_date | timestamp without time zone |
 delete_date | timestamp without time zone |
 last_update | timestamp without time zone |
インデックス:
    "mst_group_pkey" PRIMARY KEY, btree (id)

addressbook=>

(8) シーケンス作成用DDL実行

 続いてシーケンス作成用DDLを実行します。要領はテーブル作成用DDLと同じです。

addressbook=> \ds ←シーケンス一覧を表示するメタコマンド
リレーションがありません。
addressbook=> \i /data/sql/createsequence.ddl ←シーケンス作成用DDLを実行
CREATE SEQUENCE
CREATE SEQUENCE
addressbook=> \ds
               リレーションの一覧
 スキーマ |    名前     |     型     |  所有者
----------+-------------+------------+----------
 pguser01 | seq_groupid | シーケンス | pguser01 ←シーケンスが作成された
 pguser01 | seq_userid  | シーケンス | pguser01 ←シーケンスが作成された
(2 行)

addressbook=> \d seq_userid ←作成したシーケンスの状態を表示
       シーケンス "pguser01.seq_userid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_userid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=> \d seq_groupid ←作成したシーケンスの状態を表示
       シーケンス "pguser01.seq_groupid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_groupid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=>

(9) 作成したテーブルにデータを挿入

 作成した2つのテーブルにSQLを実行してデータを挿入します。

addressbook=> select * from mst_user;
 id | name | address | phone | create_date | delete_date | last_update
----+------+---------+-------+-------------+-------------+-------------
(0 行) ←まだデータが入っていない

addressbook=> insert into mst_user values ←1行で書いても良い
addressbook-> (1, 'Taro', 'Tokyo', '03-1234-5678', current_timestamp, null, current_timestamp);
INSERT 0 1
addressbook=> select * from mst_user;
 id | name | address |    phone     |        create_date         | delete_date |        last_update
----+------+---------+--------------+----------------------------+-------------+----------------------------
  1 | Taro | Tokyo   | 03-1234-5678 | 2016-12-27 00:29:44.239193 |             | 2016-12-27 00:29:44.239193
(1 行) ←データが1行挿入された

addressbook=> select * from mst_group;
 id | name | create_date | delete_date | last_update
----+------+-------------+-------------+-------------
(0 行) ←まだデータが入っていない

addressbook=> insert into mst_group values ←1行で書いても良い
addressbook-> (1, 'student', current_timestamp, null, current_timestamp);
INSERT 0 1
addressbook=> select * from mst_group;
 id |  name   |        create_date         | delete_date |        last_update
----+---------+----------------------------+-------------+----------------------------
  1 | student | 2016-12-27 00:30:33.339362 |             | 2016-12-27 00:30:33.339362
(1 行) ←データが1行挿入された

addressbook=>

(10) 作成したシーケンスの値を取得

 作成した2つのシーケンスの現在値を取得します。

addressbook=> \d seq_userid
       シーケンス "pguser01.seq_userid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_userid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=> select last_value from seq_userid;
 last_value
------------
          1
(1 行)

addressbook=> \d seq_userid
       シーケンス "pguser01.seq_userid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_userid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=> \d seq_groupid
       シーケンス "pguser01.seq_groupid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_groupid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=> select last_value from seq_groupid;
 last_value
------------
          1
(1 行)

addressbook=> \d seq_groupid
       シーケンス "pguser01.seq_groupid"
      列       |   型    |         値
---------------+---------+---------------------
 sequence_name | name    | seq_groupid
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

addressbook=> \q
-bash-4.2$

 以上で、ユーザとデータベースの作成から、データを挿入するまでの一連の操作が確認できました。

プロフィール

らのっち

損害保険会社のIT企画部に勤務するSEです。OSSを勉強中です。

<所属>
日本PostgreSQLユーザ会とくしまOSS普及協議会


第000414号