PostgreSQL 9.6.1でユーザ、データベース、スキーマ、オブジェクトを作成
カテゴリ：OSSセットアップ | ソフトウェア：PostgreSQL | タグ：
最終更新日：2021/01/01 | 公開日：2016/12/15
目次
概要
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
環境構築
設定
Linuxグループ作成
アプリケーション開発者がLinux上で使用するユーザが所属するグループ pggroup を作成します。 GIDは 1001 とします。
# groupadd -g 1001 pggroup
# tail -n 1 /etc/group
pggroup:x:1001: ←作成したグループ
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 ←作成したユーザ
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=#
データベース作成
アプリケーション用のデータベースを作成します。 今回は住所録をイメージして 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=#
スキーマ作成
作成した 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$
テーブル・シーケンス作成用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;
テーブル作成用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=>
シーケンス作成用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=>
作成したテーブルにデータを挿入
作成した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=>
作成したシーケンスの値を取得
作成した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$
以上で、ユーザとデータベースの作成から、データを挿入するまでの一連の操作が確認できました。