電脳羊(Android Dream)

アクセスカウンタ

zoom RSS UbuntuにPostgreSQLを本格的に使ってみる

<<   作成日時 : 2016/04/13 22:25   >>

ブログ気持玉 0 / トラックバック 0 / コメント 0

ざっくりのインストールと設定は終わったので、少し本格的に使ってみます。

1、ユーザ(ロール)の追加
MySQLではユーザはロールと呼びます。

pgaAdmin IIIで下の方を見ると、「グループロール」「ログインロール」というのが見えます
画像


psqlで確認してみます。
$ psql testdb
psql (9.5.2)
"help" でヘルプを表示します.

testdb=# select rolname from pg_roles;
rolname
----------
oono
postgres
(2 行)

各種テーブルなどはいずれかのロールの持ち物になります。
開発用のロールを作成してその下に必要なものを追加していきます。

$ createuser -U postgres -P test_user
Uオプションはユーザ(ロール)名を指定します。この場合は管理用のpostgresです。
Pオプションはユーザ作成時にプロンプトを表示してパスワードを入力します。
新しいロールのためのパスワード:
もう一度入力してください:
新しいロールのパスワードを2度入力します。

新しいロールを確認します。
testdb=# select rolname from pg_roles;
rolname
-----------
oono
postgres
test_user
(3 行)

新しいロールでDBにログインします。
Uオプションでロール名を指定します
dオプションでDB名を指定します
postgres@ubuntu:~$ psql -U test_user -d postgres
psql: FATAL: ユーザ "test_user" で対向(peer)認証に失敗しました
ん?対向(peer)認証ってなんだ

http://techracho.bpsinc.jp/kazumasa-ogawa/2013_04_25/8244
によるとPeer認証になってるとUNIXのログイン名とMySQLのログインロールが同じじゃないとダメだそうです。
UNIXユーザでログインできたら認証できたと判断するんでしょうね。
確かにUNIXのpostgresユーザで、MySQLのtest_userログインロールにログインしてますね。
/etc/postgresql/9.5/main/pg_hba.conf
を編集します。
local all postgres peer
local all test_user md5

追記してDBを再起動します
$ sudo /etc/init.d/postgresql restart
[sudo] password for oono:
* Restarting PostgreSQL 9.5 database server [ OK ]

今度はログインできました
$ psql -U test_user -d postgres
ユーザ test_user のパスワード:
psql (9.5.2)
"help" でヘルプを表示します.

postgres=>

2、テーブルスペースの作成
デフォルトではpg_defaultが使われます。

専用にテーブルスペースを作ります。
以下のようなスクリプトを作成します。Create_tablespace.sql
CREATE TABLESPACE test_tablespace
OWNER test_user
LOCATION '/var/lib/postgresql/9.5/test_tablespace';

ターミナルから実行します。
$ su postgres
$ psql -f ./Create_tablespace.sql -U postgres -d postgres
CREATE TABLESPACE

3、データベースの作成
専用のデータベースを作成します。
以下のようなスクリプトを作成します。Create_database.sql

CREATE DATABASE test_db
WITH OWNER = test_user
ENCODING = 'UTF8'
TABLESPACE = test_tablespace;

ターミナルから実行します
$ psql -f ./Create_database.sql -U postgres -d postgres
CREATE DATABASE

4、テーブルの作成
せっかくなのでSQL文をバッチで複数実行する方法を調べます。
OracleやMySQLにはSQLを実行するときにパスワードを指定できるのですが、PostgreSQLにはその機能がないようです。
回避するには、Peer認証にしておくのがひとつですね。UNIXユーザでログインできてればパスワード聞かれない
今回は敢えてPeer認証を使わずにバッチのときにパスワードを渡す方法を探してみます。
http://kaede.jp/2015/10/27002723.html
によると
(1).pgpassに入れる方法

(2)環境変数(PGPASSWORD)に入れる方法

があるようです。環境変数に入れるのはセキュリティ上非推奨とありますが、unsetで消しとけば問題ないのでは?

環境変数に入れることにします。

テーブルを2つ作成します。
Create_users.sql
create table USERS (
USERID integer not null
, STATUS character varying(10) not null
, CONTENTS_ID integer default 0 not null
, MKDATETIME timestamp not null
, primary key (USERID)
) ;

Create_shop.sql
create table SHOP (
SHOPID integer not null
, NAME character varying(40) unique
, MKDATETIME timestamp not null
, primary key (SHOPID)
) ;

こいつを実行するスクリプト(create_table.sh)を書いてと
-------
#!/bin/sh
export PGPASSWORD=xxxxx

psql -f ./Create_users.sql -U test_user -d test_db
psql -f ./Create_shop.sql -U test_user -d test_db

unset PGPASSWORD
--------
実行権付けて
$ chmod a+x create_table.sh

実行
$ ./create_table.sh
CREATE TABLE
CREATE TABLE
上手いったみたいです。パスワードも聞かれませんでした。

pgAdmin IIIで確認すると想定した通り、test_db上にテーブル作成されましたね。
画像

オーナーはtest_user
テーブル空間はtest_tablespace
になっています。

とりあえずスクリプトにしておけば同じ環境を何度も作れますし、マスターデータなどもスクリプト叩くだけでいけます。

テーマ

関連テーマ 一覧


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ

トラックバック(0件)

タイトル (本文) ブログ名/日時

トラックバック用URL help


自分のブログにトラックバック記事作成(会員用) help

タイトル
本 文

コメント(0件)

内 容 ニックネーム/日時

コメントする help

ニックネーム
本 文
UbuntuにPostgreSQLを本格的に使ってみる 電脳羊(Android Dream)/BIGLOBEウェブリブログ
文字サイズ:       閉じる