証明書の流れ
2018年9月15日土曜日
■ストリーミングレプリケーション(ホットスタンバイ)設定メモ
簡単にまとめておくと
【プライマリサーバでの設定】
・postgresql.confのmax_wal_sendersに十分大きな値を設定
・postgresql.confのwal_levelをhot_standbyに設定
・postgresql.confのarchive_modeをonに設定
・postgresql.confのarchive_commandでWALのアーカイブ先を設定
・pg_hba.confにreplication指定
【スタンバイサーバでの設定】
・recovery.confのstandby_modeをonに設定
・recovery.confのprimary_connninfoをプライマリへの接続情報に設定
・recovery.confのrestore_commandでアーカイブWALの取得元を設定
こんなかんじかな。
【プライマリサーバでの設定】
・postgresql.confのmax_wal_sendersに十分大きな値を設定
・postgresql.confのwal_levelをhot_standbyに設定
・postgresql.confのarchive_modeをonに設定
・postgresql.confのarchive_commandでWALのアーカイブ先を設定
・pg_hba.confにreplication指定
【スタンバイサーバでの設定】
・recovery.confのstandby_modeをonに設定
・recovery.confのprimary_connninfoをプライマリへの接続情報に設定
・recovery.confのrestore_commandでアーカイブWALの取得元を設定
こんなかんじかな。
2018年3月30日金曜日
■pgbenchの使い方をメモっとく
インストールパッケージ：
postgresql92-9.2.1-1PGDG.rhel5.i386.rpm
postgresql92-libs-9.2.1-1PGDG.rhel5.i386.rpm
postgresql92-server-9.2.1-1PGDG.rhel5.i386.rpm
必要パッケージ：postgresql92-contrib-9.2.1-1PGDG.rhel5.i386.rpm
uuid-1.5.1-3.el5.i386.rpm（上記の依存パッケージ）
①必要パッケージ2つをインストール
# rpm -ivh postgresql92-contrib-9.2.1-1PGDG.rhel5.i386.rpm
# rpm -ivh uuid-1.5.1-3.el5.i386.rpm
②ユーザ、データベース作成
$ createuser -d -r -l -P testuser
d…新しいユーザに対してデータベースの作成を許可
r…新しいユーザに対して新しいロールの作成を許可
P…createuserは新しいユーザのパスワードのプロンプトを表示
$ Enter password for new role:xxxxxxx
$ createdb -O testuser testdb2
OはOWNER
③ベンチマーク用のテーブルを作成（初期化）
★測定するときは都度、初期化をする
$ pgbench -U testuser -i testdb2
= $ pgbench -U testuser -i -s 1 testdb2
※スケールファクタ1
⇒以下のようなテーブルが追加される
public | pgbench_accounts | table | testuser
public | pgbench_branches | table | testuser
public | pgbench_history | table | testuser
public | pgbench_tellers | table | testuser
[初期状態]
testdb2=> select count(*) from pgbench_history;
count
-------
0
(1 row)
testdb2=> select count(*) from pgbench_accounts;
count
--------
100000
(1 row)
testdb2=> select count(*) from pgbench_branches;
count
-------
1
(1 row)
testdb2=> select count(*) from pgbench_tellers;
count
-------
10
(1 row)
testdb2=> select * from pgbench_branches;
bid | bbalance | filler
-----+----------+--------
1 | 0 |
(1 row)
testdb2=> select * from pgbench_tellers;
tid | bid | tbalance | filler
-----+-----+----------+--------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
(10 rows)
testdb2=> select * from pgbench_accounts;
aid | bid | abalance | filler
--------+-----+----------+------------------------------------------------------
--------------------------------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
④ベンチマーク実行
$ ./pgbench -c 10 -t 1000 testdb2
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 280.879365 (including connections establishing)
tps = 281.992691 (excluding connections establishing)
⇒-c 10は同時に10人のユーザが利用（接続）できることを想定
⇒-t 1000は各接続あたり、1000回のトランザクションを実行
[実行後]
testdb2=> select count(*) from pgbench_history;
count
-------
10000
(1 row)
testdb2=> select count(*) from pgbench_accounts;
count
--------
100000
(1 row)
testdb2=> select count(*) from pgbench_branches;
count
-------
1
(1 row)
testdb2=> select count(*) from pgbench_tellers;
count
-------
10
(1 row)
testdb2=> select * from pgbench_branches;
bid | bbalance | filler
-----+----------+--------
1 | 219104 |
(1 row)
testdb2=> select * from pgbench_tellers;
tid | bid | tbalance | filler
-----+-----+----------+--------
5 | 1 | 74665 |
3 | 1 | 76425 |
8 | 1 | -64329 |
6 | 1 | 43609 |
2 | 1 | 122252 |
7 | 1 | 19944 |
9 | 1 | 99234 |
10 | 1 | -60867 |
4 | 1 | -90336 |
1 | 1 | -1493 |
(10 rows)
testdb2=> select * from pgbench_accounts;
aid | bid | abalance | filler
--------+-----+----------+------------------------------------------------------
--------------------------------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
…
125 | 1 | -2035 |
177 | 1 | -2745 |
165 | 1 | -2258 |
148 | 1 | 2020 |
179 | 1 | -1359 |
136 | 1 | 1237 |
146 | 1 | 4247 |
180 | 1 | 711 |
⑤出力内容
scaling factor: 1とは
⇒10万件のデータを利用してベンチマーク実行
query mode: simple
⇒psqlによる単純な問い合わせを実施
number of clients: 10
⇒同時接続ユーザ10
number of transactions per client: 1000
⇒１クライアントあたり1000回トランザクションを実施
※-Tを利用すると指定した秒数の間、トランザクションを実施
number of transactions actually processed: 10000/10000
⇒正常に実行されたトランザクションの割合。
10*1000=10000回正常に実行できてるということ。
tps = 280.879365 (including connections establishing)
tps = 281.992691 (excluding connections establishing)
⇒1秒間に実行できたトランザクションの数を表示。
数値が大きいほど性能がよいということ。
前者はDBに接続する時間を含んでいる。
⑥通常のpg_benchの流れ
1.pgbench_accountsを1件更新
2.pgbench_accountsから1件検索
3.pgbench_tellersを1件更新
4.pgbench_branchesを1件更新
5.pgbench_historyに1件行を追加
⇒3,4を省略するには-Nをつける。
検索処理のみ測定するには-Sをつける。
デフォルトのトランザクションスクリプトは、1トランザクションで以下の7コマンドを発行します。
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
-Nを指定した場合、第4コマンドと第5コマンドはトランザクションに含まれません。
-Sを指定した場合、SELECTのみが発行されます。
:deltaは1から1000までの値を取る乱数，:aid は 1から100000までの値を取る乱数
:tid は 1から10の間の値をとる乱数
:bid は 1 から[スケリングファクター]の間の値を取る乱数
⑦その他
任意のSQLをトランザクションとして実行できる独自スクリプト機能
⇒以下のようなSQL文を用意
BEGIN;
SELECT 文;
SELECT 文;
END;
上記をtestuser.pgbenchのような名前のファイルで保存して実行。
$ /usr/pgsql-9.2/bin/pgbench testdb2 -f testuser.pgbench
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 175.152821 (including connections establishing)
tps = 282.613611 (excluding connections establishing)
以上
postgresql92-9.2.1-1PGDG.rhel5.i386.rpm
postgresql92-libs-9.2.1-1PGDG.rhel5.i386.rpm
postgresql92-server-9.2.1-1PGDG.rhel5.i386.rpm
必要パッケージ：postgresql92-contrib-9.2.1-1PGDG.rhel5.i386.rpm
uuid-1.5.1-3.el5.i386.rpm（上記の依存パッケージ）
①必要パッケージ2つをインストール
# rpm -ivh postgresql92-contrib-9.2.1-1PGDG.rhel5.i386.rpm
# rpm -ivh uuid-1.5.1-3.el5.i386.rpm
②ユーザ、データベース作成
$ createuser -d -r -l -P testuser
d…新しいユーザに対してデータベースの作成を許可
r…新しいユーザに対して新しいロールの作成を許可
P…createuserは新しいユーザのパスワードのプロンプトを表示
$ Enter password for new role:xxxxxxx
$ createdb -O testuser testdb2
OはOWNER
③ベンチマーク用のテーブルを作成（初期化）
★測定するときは都度、初期化をする
$ pgbench -U testuser -i testdb2
= $ pgbench -U testuser -i -s 1 testdb2
※スケールファクタ1
⇒以下のようなテーブルが追加される
public | pgbench_accounts | table | testuser
public | pgbench_branches | table | testuser
public | pgbench_history | table | testuser
public | pgbench_tellers | table | testuser
[初期状態]
testdb2=> select count(*) from pgbench_history;
count
-------
0
(1 row)
testdb2=> select count(*) from pgbench_accounts;
count
--------
100000
(1 row)
testdb2=> select count(*) from pgbench_branches;
count
-------
1
(1 row)
testdb2=> select count(*) from pgbench_tellers;
count
-------
10
(1 row)
testdb2=> select * from pgbench_branches;
bid | bbalance | filler
-----+----------+--------
1 | 0 |
(1 row)
testdb2=> select * from pgbench_tellers;
tid | bid | tbalance | filler
-----+-----+----------+--------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
(10 rows)
testdb2=> select * from pgbench_accounts;
aid | bid | abalance | filler
--------+-----+----------+------------------------------------------------------
--------------------------------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
④ベンチマーク実行
$ ./pgbench -c 10 -t 1000 testdb2
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 280.879365 (including connections establishing)
tps = 281.992691 (excluding connections establishing)
⇒-c 10は同時に10人のユーザが利用（接続）できることを想定
⇒-t 1000は各接続あたり、1000回のトランザクションを実行
[実行後]
testdb2=> select count(*) from pgbench_history;
count
-------
10000
(1 row)
testdb2=> select count(*) from pgbench_accounts;
count
--------
100000
(1 row)
testdb2=> select count(*) from pgbench_branches;
count
-------
1
(1 row)
testdb2=> select count(*) from pgbench_tellers;
count
-------
10
(1 row)
testdb2=> select * from pgbench_branches;
bid | bbalance | filler
-----+----------+--------
1 | 219104 |
(1 row)
testdb2=> select * from pgbench_tellers;
tid | bid | tbalance | filler
-----+-----+----------+--------
5 | 1 | 74665 |
3 | 1 | 76425 |
8 | 1 | -64329 |
6 | 1 | 43609 |
2 | 1 | 122252 |
7 | 1 | 19944 |
9 | 1 | 99234 |
10 | 1 | -60867 |
4 | 1 | -90336 |
1 | 1 | -1493 |
(10 rows)
testdb2=> select * from pgbench_accounts;
aid | bid | abalance | filler
--------+-----+----------+------------------------------------------------------
--------------------------------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
…
125 | 1 | -2035 |
177 | 1 | -2745 |
165 | 1 | -2258 |
148 | 1 | 2020 |
179 | 1 | -1359 |
136 | 1 | 1237 |
146 | 1 | 4247 |
180 | 1 | 711 |
⑤出力内容
scaling factor: 1とは
⇒10万件のデータを利用してベンチマーク実行
query mode: simple
⇒psqlによる単純な問い合わせを実施
number of clients: 10
⇒同時接続ユーザ10
number of transactions per client: 1000
⇒１クライアントあたり1000回トランザクションを実施
※-Tを利用すると指定した秒数の間、トランザクションを実施
number of transactions actually processed: 10000/10000
⇒正常に実行されたトランザクションの割合。
10*1000=10000回正常に実行できてるということ。
tps = 280.879365 (including connections establishing)
tps = 281.992691 (excluding connections establishing)
⇒1秒間に実行できたトランザクションの数を表示。
数値が大きいほど性能がよいということ。
前者はDBに接続する時間を含んでいる。
⑥通常のpg_benchの流れ
1.pgbench_accountsを1件更新
2.pgbench_accountsから1件検索
3.pgbench_tellersを1件更新
4.pgbench_branchesを1件更新
5.pgbench_historyに1件行を追加
⇒3,4を省略するには-Nをつける。
検索処理のみ測定するには-Sをつける。
デフォルトのトランザクションスクリプトは、1トランザクションで以下の7コマンドを発行します。
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
-Nを指定した場合、第4コマンドと第5コマンドはトランザクションに含まれません。
-Sを指定した場合、SELECTのみが発行されます。
:deltaは1から1000までの値を取る乱数，:aid は 1から100000までの値を取る乱数
:tid は 1から10の間の値をとる乱数
:bid は 1 から[スケリングファクター]の間の値を取る乱数
⑦その他
任意のSQLをトランザクションとして実行できる独自スクリプト機能
⇒以下のようなSQL文を用意
BEGIN;
SELECT 文;
SELECT 文;
END;
上記をtestuser.pgbenchのような名前のファイルで保存して実行。
$ /usr/pgsql-9.2/bin/pgbench testdb2 -f testuser.pgbench
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 175.152821 (including connections establishing)
tps = 282.613611 (excluding connections establishing)
以上
2018年1月27日土曜日
■スキーマについて学習してみた その２
再度、ちょっとした練習
①Ownerがtestuserでtestdb2を作成
$ createuser -d -r -l -P testuser
$ createdb -O testuser testdb2
②DBに接続
$pslq -U testuser -d testdb
testdb2=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
dvdrental | postgres | 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
testdb | postgres | UTF8 | C | C |
testdb2 | testuser | UTF8 | C | C |
③スキーマを作成
testdb2=> create schema testuser;
④テーブルを作成
testdb2=> create schema testuser;
CREATE SCHEMA
testdb2=> create table TripType(
testdb2(> TripTypeID INTEGER NOT NULL,
testdb2(> Name VARCHAR(15),
testdb2(> Description VARCHAR(50),
testdb2(> LastUpdated TIMESTAMP
testdb2(> );
CREATE TABLE
testdb2=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
----------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | testuser | 13 MB |
public | pgbench_branches | table | testuser | 64 kB |
public | pgbench_history | table | testuser | 1152 kB |
public | pgbench_tellers | table | testuser | 72 kB |
★testuser | triptype | table | testuser | 0 bytes |
→スキーマがtestuserでtriptypeテーブルが作成された
⑤publicスキーマのテーブルを作成
testdb2=> create table public.TripType(
testdb2(> TripTypeID INTEGER NOT NULL,
testdb2(> Name VARCHAR(15),
testdb2(> Description VARCHAR(50),
testdb2(> LastUpdated TIMESTAMP
testdb2(> );
CREATE TABLE
testdb2=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
----------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | testuser | 13 MB |
public | pgbench_branches | table | testuser | 64 kB |
public | pgbench_history | table | testuser | 1152 kB |
public | pgbench_tellers | table | testuser | 72 kB |
testuser | triptype | table | testuser | 0 bytes |
(5 rows)
→publicスキーマのtriptypeテーブルは表示されない
⑥データの挿入
testdb2=> insert into TripType values( 1, 'TRNG', 'Training', NULL);
INSERT 0 1
testdb2=> select count(*) from triptype;
count
-------
1
(1 row)
testdb2=> select count(*) from public.triptype;
count
-------
0
→publicのtriptypeテーブルにはデータは格納されず。
⑦サーチパスの確認
testdb2=> show search_path;
search_path
-----------------
"$user", public
→自分のユーザに紐づいたスキーマを1番最初にリストするから。
⑧テーブル情報の確認
estdb2=> select * from pg_tables where tableowner='testuser';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules
| hastriggers | rowsecurity
------------+------------------+------------+------------+------------+---------
-+-------------+-------------
public | pgbench_tellers | testuser | | t | f
| f | f
public | pgbench_branches | testuser | | t | f
| f | f
public | pgbench_accounts | testuser | | t | f
| f | f
public | pgbench_history | testuser | | f | f
| f | f
testuser | triptype | testuser | | f | f
| f | f
public | triptype | testuser | | f | f
| f | f
(6 rows)
うーん、ややこしいね。
①Ownerがtestuserでtestdb2を作成
$ createuser -d -r -l -P testuser
$ createdb -O testuser testdb2
②DBに接続
$pslq -U testuser -d testdb
testdb2=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
dvdrental | postgres | 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
testdb | postgres | UTF8 | C | C |
testdb2 | testuser | UTF8 | C | C |
③スキーマを作成
testdb2=> create schema testuser;
④テーブルを作成
testdb2=> create schema testuser;
CREATE SCHEMA
testdb2=> create table TripType(
testdb2(> TripTypeID INTEGER NOT NULL,
testdb2(> Name VARCHAR(15),
testdb2(> Description VARCHAR(50),
testdb2(> LastUpdated TIMESTAMP
testdb2(> );
CREATE TABLE
testdb2=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
----------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | testuser | 13 MB |
public | pgbench_branches | table | testuser | 64 kB |
public | pgbench_history | table | testuser | 1152 kB |
public | pgbench_tellers | table | testuser | 72 kB |
★testuser | triptype | table | testuser | 0 bytes |
→スキーマがtestuserでtriptypeテーブルが作成された
⑤publicスキーマのテーブルを作成
testdb2=> create table public.TripType(
testdb2(> TripTypeID INTEGER NOT NULL,
testdb2(> Name VARCHAR(15),
testdb2(> Description VARCHAR(50),
testdb2(> LastUpdated TIMESTAMP
testdb2(> );
CREATE TABLE
testdb2=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
----------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | testuser | 13 MB |
public | pgbench_branches | table | testuser | 64 kB |
public | pgbench_history | table | testuser | 1152 kB |
public | pgbench_tellers | table | testuser | 72 kB |
testuser | triptype | table | testuser | 0 bytes |
(5 rows)
→publicスキーマのtriptypeテーブルは表示されない
⑥データの挿入
testdb2=> insert into TripType values( 1, 'TRNG', 'Training', NULL);
INSERT 0 1
testdb2=> select count(*) from triptype;
count
-------
1
(1 row)
testdb2=> select count(*) from public.triptype;
count
-------
0
→publicのtriptypeテーブルにはデータは格納されず。
⑦サーチパスの確認
testdb2=> show search_path;
search_path
-----------------
"$user", public
→自分のユーザに紐づいたスキーマを1番最初にリストするから。
⑧テーブル情報の確認
estdb2=> select * from pg_tables where tableowner='testuser';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules
| hastriggers | rowsecurity
------------+------------------+------------+------------+------------+---------
-+-------------+-------------
public | pgbench_tellers | testuser | | t | f
| f | f
public | pgbench_branches | testuser | | t | f
| f | f
public | pgbench_accounts | testuser | | t | f
| f | f
public | pgbench_history | testuser | | f | f
| f | f
testuser | triptype | testuser | | f | f
| f | f
public | triptype | testuser | | f | f
| f | f
(6 rows)
うーん、ややこしいね。
■スキーマについて学習してみた
postgreSQLのスキーマって名前空間とか言ってるけど、よく分らん。
調べてみると”A”というユーザがいて、そのAが所有するスキーマを複数指定でき、そのスキーマに各々のテーブルを格納できる的な感じかな。
1．testuserの作成
template1=# create user testuser password 'testuser';
２．testuserでtestdbの作成
-bash-4.1$ psql -U testuser -d template1
template1=> create database testdb;
ERROR: データベースを作成する権限がありません
→exitして、postgresユーザでtemplate1データべースに接続し、
以下を実行。
template1=# alter role testuser createdb;
ALTER ROLE
template1=# \q
-bash-4.1$ psql -U testuser -d template1
template1=> create database testdb;
CREATE DATABASE
→データベース作成できた。
３．一覧表示
-bash-4.1$ psql -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
testdb | testuser | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
→testdbのオーナーはtestuser
４．testuserでtestdbに接続し、テーブルを作成
→作成するテーブルは下記
psql -U tesuser -d testdb
create table TripType (
TripTypeID INTEGER NOT NULL,
Name VARCHAR(15),
Description VARCHAR(50),
LastUpdated TIMESTAMP
);
５．作成したテーブルの確認
testdb=> \dt+
リレーションの一覧
スキーマ | 名前 | 型 | 所有者 | サイズ | 説明
----------+----------+----------+----------+---------+------
public | triptype | テーブル | testuser | 0 bytes |
(1 行)
６．triptypeテーブルにデータの追加
→インサートするレコードは下記
insert into TripType values( 1, 'TRNG', 'Training', NULL);
７．結果確認
testdb=> \dt+
リレーションの一覧
スキーマ | 名前 | 型 | 所有者 | サイズ | 説明
----------+----------+----------+----------+------------+------
public | triptype | テーブル | testuser | 8192 bytes |
(1 行)
→上記のように、スキーマはpublicである。
８．testuserスキーマの作成
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d template1
→postgresユーザでtemplate1に接続
template1=#
template1=# create schema testuser;
CREATE SCHEMA
→testuserスキーマができた
９．テーブル名にスキーマを追加して、テーブル作成
-bash-4.1$ psql -d testdb;
→postgresユーザでtestdbに接続
→testdb内にTripTypeテーブルを作成
create table testuser.TripType (
TripTypeID INTEGER NOT NULL,
Name VARCHAR(15),
Description VARCHAR(50),
LastUpdated TIMESTAMP
);
ERROR: スキーマ"testuser"は存在しません
→何？testuserスキーマ作ったじゃん。
つまりtemplate1のDBにtestuserスキーマを作ってしまったのでは？
testdb=# create schema testuser;
CREATE SCHEMA
→もしや、DB単位でスキーマを用意する必要があるのでは。
→今度はうまくいった。
10．testdbにtestuserスキーマのテーブルを作成
testdb=# create table testuser.TripType (
testdb(# TripTypeID INTEGER NOT NULL,
testdb(# Name VARCHAR(15),
testdb(# Description VARCHAR(50),
testdb(# LastUpdated TIMESTAMP
testdb(# );
CREATE TABLE
testdb=# select * from triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
(1 行)
testdb=# select * from testuser.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
(0 行)
testdb=> \dt triptype
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
public | triptype | テーブル | testuser
(1 行)
→publicスキーマのtriptypeテーブル
testdb=> \dt testuser.triptype
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
(1 行)
→testuserスキーマでテーブルできているじゃん。
testuserスキーマはpostgresユーザで作成しているので、所有者はpostgresとなっている
11．tesuserスキーマのtriptypeテーブルにレコードを追加
psql -U testuser -d testdb
insert into tesuser.TripType values( 2, 'SALES', 'Sales', NULL);
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
→testuserではtestdbに接続できてもtestuser.Triptypeテーブルにレコードの追加はできないようだ。
ERROR: スキーマ testuser への権限がありません
行 1: insert into testuser.TripType values( 2, 'SALES', 'Sales', N...
→testuserでは、スキーマへのアクセス権限ないって。
→マニュアルには以下のように記載。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。
アクセスするためには、そのスキーマの所有者からスキーマのUSAGE権限を付与してもらう必要があります。
→つまり、postgresユーザでスキーマを作成したからpostgresユーザでtestuserにUSAGE権限を付与する必要あり？
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d testdb;
testdb=# grant usage on schema testuser to testuser;
→postgresユーザでtestuserに対し、testuserスキーマへのusage権限付与
→テーブル作成しようとしたが、まだ権限がないってよ。
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR: リレーション triptype への権限がありません
testdb=# grant all on schema testuser to testuser;
GRANT
→わからんので、全権限付与。
→これでどうだ。
testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR: リレーション triptype への権限がありません
→まだだめだよ。
→testuserスキーマの所有者はpostgresだね。
testdb=# \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
----------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
testuser | postgres | postgres=UC/postgres+|
| | testuser=UC/postgres |
testdb=# insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
INSERT 0 1
→postgresユーザで接続したtestdbでは、testuser.TripTypeにインサートできるわけね。
testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;
→抜けて、testuserでtestdbに接続
-bash-4.1$ psql -U testuser -d testdb;
testdb=> \dt;
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
→testuserスキーマが表示。publicじゃないのかよ。
testdb=> select * from triptype;
ERROR: リレーション triptype への権限がありません
→えっ。
testdb=> select * from public.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
→publicスキーマは表示されるね。
testdb=# grant select on testuser.triptype to testuser;
GRANT
→上記のように、postgresユーザでtestuser.triptypeへの参照権限を与えたら
見えるようになった。
※少し前でgrant all ,usage権限を付与したが、
grant all on testuser.triptype…
とか
grant usage on testuser.triptype…
とかする必要があるのでは。あるいは、
grant usage on schema testuser to testuser;
↑これ、そもそも間違えてて
grant usage on testuser to testuser;
↑こうじゃん。！！
testdb=> \dt;
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
(1 行)
testdb=> select * from triptype;
triptypeid | name | description | lastupdated
------------+-------+-------------+-------------
2 | SALES | Sales |
(1 行)
testdb=> select * from public.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
(1 行)
→testuserユーザと同じ名前のtestuserスキーマができているので、
最初の参照は$userということからpublicのテーブルではなく、testuserスキーマの
テーブルから検索しているようだ。
おしまい。
調べてみると”A”というユーザがいて、そのAが所有するスキーマを複数指定でき、そのスキーマに各々のテーブルを格納できる的な感じかな。
1．testuserの作成
template1=# create user testuser password 'testuser';
２．testuserでtestdbの作成
-bash-4.1$ psql -U testuser -d template1
template1=> create database testdb;
ERROR: データベースを作成する権限がありません
→exitして、postgresユーザでtemplate1データべースに接続し、
以下を実行。
template1=# alter role testuser createdb;
ALTER ROLE
template1=# \q
-bash-4.1$ psql -U testuser -d template1
template1=> create database testdb;
CREATE DATABASE
→データベース作成できた。
３．一覧表示
-bash-4.1$ psql -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
testdb | testuser | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
→testdbのオーナーはtestuser
４．testuserでtestdbに接続し、テーブルを作成
→作成するテーブルは下記
psql -U tesuser -d testdb
create table TripType (
TripTypeID INTEGER NOT NULL,
Name VARCHAR(15),
Description VARCHAR(50),
LastUpdated TIMESTAMP
);
５．作成したテーブルの確認
testdb=> \dt+
リレーションの一覧
スキーマ | 名前 | 型 | 所有者 | サイズ | 説明
----------+----------+----------+----------+---------+------
public | triptype | テーブル | testuser | 0 bytes |
(1 行)
６．triptypeテーブルにデータの追加
→インサートするレコードは下記
insert into TripType values( 1, 'TRNG', 'Training', NULL);
７．結果確認
testdb=> \dt+
リレーションの一覧
スキーマ | 名前 | 型 | 所有者 | サイズ | 説明
----------+----------+----------+----------+------------+------
public | triptype | テーブル | testuser | 8192 bytes |
(1 行)
→上記のように、スキーマはpublicである。
８．testuserスキーマの作成
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d template1
→postgresユーザでtemplate1に接続
template1=#
template1=# create schema testuser;
CREATE SCHEMA
→testuserスキーマができた
９．テーブル名にスキーマを追加して、テーブル作成
-bash-4.1$ psql -d testdb;
→postgresユーザでtestdbに接続
→testdb内にTripTypeテーブルを作成
create table testuser.TripType (
TripTypeID INTEGER NOT NULL,
Name VARCHAR(15),
Description VARCHAR(50),
LastUpdated TIMESTAMP
);
ERROR: スキーマ"testuser"は存在しません
→何？testuserスキーマ作ったじゃん。
つまりtemplate1のDBにtestuserスキーマを作ってしまったのでは？
testdb=# create schema testuser;
CREATE SCHEMA
→もしや、DB単位でスキーマを用意する必要があるのでは。
→今度はうまくいった。
10．testdbにtestuserスキーマのテーブルを作成
testdb=# create table testuser.TripType (
testdb(# TripTypeID INTEGER NOT NULL,
testdb(# Name VARCHAR(15),
testdb(# Description VARCHAR(50),
testdb(# LastUpdated TIMESTAMP
testdb(# );
CREATE TABLE
testdb=# select * from triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
(1 行)
testdb=# select * from testuser.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
(0 行)
testdb=> \dt triptype
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
public | triptype | テーブル | testuser
(1 行)
→publicスキーマのtriptypeテーブル
testdb=> \dt testuser.triptype
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
(1 行)
→testuserスキーマでテーブルできているじゃん。
testuserスキーマはpostgresユーザで作成しているので、所有者はpostgresとなっている
11．tesuserスキーマのtriptypeテーブルにレコードを追加
psql -U testuser -d testdb
insert into tesuser.TripType values( 2, 'SALES', 'Sales', NULL);
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
→testuserではtestdbに接続できてもtestuser.Triptypeテーブルにレコードの追加はできないようだ。
ERROR: スキーマ testuser への権限がありません
行 1: insert into testuser.TripType values( 2, 'SALES', 'Sales', N...
→testuserでは、スキーマへのアクセス権限ないって。
→マニュアルには以下のように記載。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。
アクセスするためには、そのスキーマの所有者からスキーマのUSAGE権限を付与してもらう必要があります。
→つまり、postgresユーザでスキーマを作成したからpostgresユーザでtestuserにUSAGE権限を付与する必要あり？
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d testdb;
testdb=# grant usage on schema testuser to testuser;
→postgresユーザでtestuserに対し、testuserスキーマへのusage権限付与
→テーブル作成しようとしたが、まだ権限がないってよ。
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR: リレーション triptype への権限がありません
testdb=# grant all on schema testuser to testuser;
GRANT
→わからんので、全権限付与。
→これでどうだ。
testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR: リレーション triptype への権限がありません
→まだだめだよ。
→testuserスキーマの所有者はpostgresだね。
testdb=# \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
----------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
testuser | postgres | postgres=UC/postgres+|
| | testuser=UC/postgres |
testdb=# insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
INSERT 0 1
→postgresユーザで接続したtestdbでは、testuser.TripTypeにインサートできるわけね。
testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;
→抜けて、testuserでtestdbに接続
-bash-4.1$ psql -U testuser -d testdb;
testdb=> \dt;
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
→testuserスキーマが表示。publicじゃないのかよ。
testdb=> select * from triptype;
ERROR: リレーション triptype への権限がありません
→えっ。
testdb=> select * from public.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
→publicスキーマは表示されるね。
testdb=# grant select on testuser.triptype to testuser;
GRANT
→上記のように、postgresユーザでtestuser.triptypeへの参照権限を与えたら
見えるようになった。
※少し前でgrant all ,usage権限を付与したが、
grant all on testuser.triptype…
とか
grant usage on testuser.triptype…
とかする必要があるのでは。あるいは、
grant usage on schema testuser to testuser;
↑これ、そもそも間違えてて
grant usage on testuser to testuser;
↑こうじゃん。！！
testdb=> \dt;
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+----------+----------+----------
testuser | triptype | テーブル | postgres
(1 行)
testdb=> select * from triptype;
triptypeid | name | description | lastupdated
------------+-------+-------------+-------------
2 | SALES | Sales |
(1 行)
testdb=> select * from public.triptype;
triptypeid | name | description | lastupdated
------------+------+-------------+-------------
1 | TRNG | Training |
(1 行)
→testuserユーザと同じ名前のtestuserスキーマができているので、
最初の参照は$userということからpublicのテーブルではなく、testuserスキーマの
テーブルから検索しているようだ。
おしまい。
2017年5月4日木曜日
■排他ロックの特徴を簡単にまとめてみた
排他ロック(Access Exclusive Lock)は 業務処理を停めてしまうので要注意
主なものを下記に記載する。
①VACUUM FULL/CLUSTER
テーブルの物理圧縮/物理再編成
物理サイズの圧縮をしたい時によく使う
テーブルに排他ロックを取る処理
②REINDEX
インデックスの再作成
断片化したインデックスのリフレッシュに使う
インデックスに排他ロックを取る処理
システムカタログにロックを取るため、プランナー処理でロック待ちになる
他にもいろいろあるけど、そのうち加筆します。
主なものを下記に記載する。
①VACUUM FULL/CLUSTER
テーブルの物理圧縮/物理再編成
物理サイズの圧縮をしたい時によく使う
テーブルに排他ロックを取る処理
②REINDEX
インデックスの再作成
断片化したインデックスのリフレッシュに使う
インデックスに排他ロックを取る処理
システムカタログにロックを取るため、プランナー処理でロック待ちになる
他にもいろいろあるけど、そのうち加筆します。
■VACUUMの特徴についてまとめてみた
[VACUUMの動き]
最初に実行されるVACUUMはテーブルのフルスキャンを行いVisibility Mapを作成する。
2回目以降のVACUUMは部分スキャンを行い、Visibility Mapを最新化する。
Visibility Mapは更新処理(UPDATE/DELETE)で更新される。
以降は、Visibility Mapを見て必要なところだけVACUUMを行い、回収領域はFreeSpaceMapに登録する。
[実行結果]
テーブルやインデックスデータのread/write負荷が多少なりともかかる。
Visibility Mapの最新化に伴うIndex-Only-Scan※の性能が向上する。
※Index-Only-ScanはVisibility Mapのビットが立って入れば、テーブルは見ない。
→Select文の検索項目がインデックスのキーのみの場合、テーブルデータへのアクセスを省略してくれる。Index-Only-ScanはVer9.2からサポート。
ページ内のデータ変更が実施されるため、ガベージ回収に伴いWALが出力される。
こんな感じです。
最初に実行されるVACUUMはテーブルのフルスキャンを行いVisibility Mapを作成する。
2回目以降のVACUUMは部分スキャンを行い、Visibility Mapを最新化する。
Visibility Mapは更新処理(UPDATE/DELETE)で更新される。
以降は、Visibility Mapを見て必要なところだけVACUUMを行い、回収領域はFreeSpaceMapに登録する。
[実行結果]
テーブルやインデックスデータのread/write負荷が多少なりともかかる。
Visibility Mapの最新化に伴うIndex-Only-Scan※の性能が向上する。
※Index-Only-ScanはVisibility Mapのビットが立って入れば、テーブルは見ない。
→Select文の検索項目がインデックスのキーのみの場合、テーブルデータへのアクセスを省略してくれる。Index-Only-ScanはVer9.2からサポート。
ページ内のデータ変更が実施されるため、ガベージ回収に伴いWALが出力される。
こんな感じです。
登録:
投稿 (Atom)
-
インストールパッケージ： postgresql92-9.2.1-1PGDG.rhel5.i386.rpm postgresql92-libs-9.2.1-1PGDG.rhel5.i386.rpm postgresql92-server-9.2.1-1PGDG.rhel5.i...
-
vacuum fullを実行したことがほとんどないので、動きを確認 マニュアルとか見ると下記のような特徴があるらしい。 ・不要領域を回収 ・実行中は読み書き不可 ・中間ファイルを作成し、新たなタプルID（TID）を作成する 上記の上２つを確認してみる ■vac... -
pg_xlogdumpはバイナリ形式のファイルであるWALファイルをテキスト形式で可視化するツールであり、contribモジュールの追加で利用できる。とのこと。 １．contribモジュールのインストール確認 $ rpm -aq | grep postgres ...