するめごはん日記: 6月 2015

2015年6月9日火曜日

CentOS7のpostgresユーザのホームディレクトリ（PostgreSQL9.4.1）

2015年6月9日　その2

ごきげんよう。

先ほどの記事で
Postgres Toolkitについて記載しましたが
いちいちパスを通すのでめんどかったので
永続化することにしました。

というわけで
postgresユーザのホームディレクトリに行こうとしたら
/home/postgres
なんて存在しなかった。

で、最初にログインしたディレクトリが
ホームディレクトリなんじゃない？
と思ったらその通りでした。

CentOS7　PsotgreSQL9.4.1の場合、
ホームディレクトリは
/var/lib/pgsql/配下になる模様。

なので、ここにいる
.bash_profile君に

# postgres-tooklit
export PATH=$PATH:/opt/uptime/postgres-toolkit-0.2.1/bin

と末尾に追記してあげて
source .bash_profile
とコマンドを打ってあげましたら
無事にPATHに追加されました。

PostgreSQL（Postgres Toolkit）　pg_hba.confファイルの設定　ユーザ”postgres”のIdent認証に失敗しました

2015年6月9日

ごきげんよう。
タイトルが長い・・

今回はPostgreSQLで
ユーザ”postgres”のIdent認証に失敗しました
なんてエラーがでちゃいましたので
それについて記載します。

結論から記載すると
pg_hba.confファイル
でひっかかりました。
#　暫定対処なのでそこはご注意ください

さて突然ですが

Postgres Toolkit

をご存知でしょうか。

先日（2015/05/30）のPostgreSQLアンカンファレンスで

永安さんが発表なさっていたツールです。

スライドはこちら

スライドによると以下のように記載されています。

Postgres Toolkitとは

PostgreSQLのサーバを運用・管理するためのスクリプト・ツールのコレクション
複数のSQLやコマンドを組み合わせて実施する作業を、ひとつのスクリプトで実行できるようにしたもの
PostgreSQL DBAの業務の品質向上や負荷低減を目的として、頻繁に実施する作業にフォーカスして機能提供
オープンソースライセンスで提供（GPL v2）

私の解釈では

PostgreSQLで業務を行うとき、

頻繁に使うSQLやコマンドをツール化して

メンテナンス性を向上した素敵ツール

なのです。

で、この子で遊んでみた際に私が勝手にひっかかったので

備忘録を書いておきます。

【仕様環境】

OS：CentOS7

DB：PostgreSQL9.4.1

ひとまずチューニングなし

【でてきたエラー】

ユーザ”postgres”のIdent認証に失敗しました

【経緯】

Postgres Toolkitの

pt-index-usageコマンド

をpostgresユーザで実行した際に、上記エラーが発生

まず、CentOS7で

postgresユーザになり、

Postgres ToolkitのPATHを通してあげます。

[root@localhost ~]# su - postgres

最終ログイン: 2015/06/08 (月) 16:44:50 JST日時 pts/1

-bash-4.2$

-bash-4.2$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin

-bash-4.2$

-bash-4.2$ export PATH=$PATH:/opt/uptime/postgres-toolkit-0.2.1/bin

-bash-4.2$

-bash-4.2$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/uptime/postgres-toolkit-0.2.1/bin

で、

pt-index-usageを

マニュアル通り実行してあげます。

-bash-4.2$ pt-index-usage -n public -d postgres

[2015-06-09 11:03:01] ERROR: Failed to execute psql. (psql -A -h localhost -p 5432 -U postgres -d postgres)

[2015-06-09 11:03:01] ERROR: psql: FATAL: ユーザ"postgres"のIdent認証に失敗しました

-bash-4.2$

どういうことだオイ！

postgresユーザがはじかれたぞ！！

#　私のせいなんですけど

で、この場合の対処法です。

PostgreSQLはpg_hba.confというファイルで認証を管理してます。

pg_hba.confファイルのマニュアル

なので、この子を編集してあげます。

-bash-4.2$ vi /var/lib/pgsql/9.4/data/pg_hba.conf

ここの

# IPv6 local connections:

host all all ::1/128 ident

これはIPｖ6のループバックアドレスですが ident になってます。

この ident を例えば trust にすれば無条件で認証してくれます。

md5 にすれば二重MD5ハッシュ化パスワードで認証できます。

#　他にも、アドレスの欄をlocalhostにすると幸せになれるかもしれません。

ちなみに、私の場合IPｖ4はtrustにしてました。

なので・・気づくのが遅かった・・・

# IPv4 local connections:

host all all 127.0.0.1/32 trust

で、

# IPv6 local connections:

host all all ::1/128 md5

に試しに変更してあげました。

pg_hba.confファイルは変更したらPostgreSQLを再起動してあげます。

[root@localhost ~]# service postgresql-9.4 restart

Redirecting to /bin/systemctl restart postgresql-9.4.service

さて、

これでもう一度

Postgres Toolkitの

pt-index-usageコマンドを試してあげます。

# コマンドのオプションを変更してtestdbデータベースを確認するようにしてます

-bash-4.2$ pt-index-usage -n public -d testdb

+-------+----------+--------+------------+-----------------+------+------+--------+--------+--------+-------+--------+------------+

+-------+----------+--------+------------+-----------------+------+------+--------+--------+--------+-------+--------+------------+

| 16391 | postgres | public | index_test | index_test_pkey | 1 | 0 | 0 | 0 | 1 | 0 | | pg_default |

| 16393 | postgres | public | index_test | index_num | 1 | 0 | 0 | 0 | 0 | 0 | | pg_default |

| 16394 | postgres | public | index_test | index_moji | 1 | 0 | 0 | 0 | 0 | 0 | | pg_default |

+-------+----------+--------+------------+-----------------+------+------+--------+--------+--------+-------+--------+------------+

ひとまず無事に動作したようです。

今回の場合、暫定対処となるので、そこはご注意ください。

各自のセキュリティポリシーで対応してください。

通常postgresユーザは自動的に作られるので

identでハマることはないと思うのですが・・・なんでなんだぜ？？

2015年6月6日土曜日

PostgreSQL ロケールとLIKE述語そしてpgAdminでの注意

2015年6月6日

ごきげんよう。

前回の記事
PostgreSQL B-treeインデックスの動作検証

記事中に、LIKE述語の前方一致でインデックスが動かないという現象がありましたが、
ありがたいことに記事にコメントをいただきました。

ごきげんよう。LIKEの前方一致ですが、
CREATE INDEX index_moji ON index_test(moji text_pattern_ops);
と、列名の後に text_pattern_ops を付けてインデックスを作り直してみて下さい。
こちらでやったら、ロケールが「C」のDBなら初めからIndex Scanで、
ロケールがJapanese_Japan.932のDBでは記事と同様で、…ops付けたらインデックスが使われました。

kenpg / koda さんありがとうございます！

というわけで

データベースのロケールを見てみました。

・・・・・

ご指摘の通り

Japanese_Japan.932

になってました・・・・

で、提示していただいたインデックスを作り直して再度検証してみました。

CREATE INDEX index_moji ON index_test(moji text_pattern_ops);

これで前方一致のクエリを再投入してみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE 'hoge12345%';

"Index Scan using index_moji on index_test (cost=0.42..8.45 rows=100 width=18) (actual time=0.292..0.440 rows=11 loops=1)"

" Index Cond: ((moji ~>=~ 'hoge12345'::text) AND (moji ~<~ 'hoge12346'::text))"

" Filter: (moji ~~ 'hoge12345%'::text)"

"Planning time: 4.006 ms"

"Execution time: 0.484 ms"

おおおお！

無事にIndex Scanが使われました！

さて、ここで疑問。

ロケールに

Japanese_Japan.932

なんて指定した覚えがないぞ？

まさか・・・デフォルトがこうなってる？

そこで、新しくデータベース（new_db）を

pgAdminのデフォルトのまま、ほいほい作ってみます。

データベースを作る際に「定義」のタブをみてみると

コーレーションと文字型が空白ですね。

ここでOKボタンを押下して、データベースを作ると・・・

なんと！

コーレーションと文字型がJapanese_Japan.932になってます！

ぐぬぬぬ・・・って感じです。

なので、新しくデータベースをロケールCで作ってみます。

定義タブで

コーレーションと文字型にCを選択します。

ちなみに

ここでOKを押してもエラーがでます。

これはTemplateにtemplate0を選んであげれば動いてくれます。

#　Templateについては

#　ひとまずデータベース作成の際の雛形みたいなものだと認識してください。

#　詳細は各自で検索をお願いします。

で、Templateにtemplate0を選んであげてOKボタンを押下してあげると

コーレーションと文字型がCの新規データベース（testdb_c）が出来上がります。

ここで前回記事と同じように

テーブルとインデックスを作ってあげます。

COPY文で再びデータをindex_testテーブルに入れてあげて

前方一致のLIKE述語のクエリを実行してみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE 'hoge12345%';

"Index Scan using index_moji on index_test (cost=0.42..8.45 rows=100 width=18) (actual time=0.074..0.195 rows=11 loops=1)"

" Index Cond: ((moji >= 'hoge12345'::text) AND (moji < 'hoge12346'::text))"

" Filter: (moji ~~ 'hoge12345%'::text)"

"Planning time: 15.047 ms"

"Execution time: 0.225 ms"

おおおお！！

無事にIndex Scanが選ばれました。

というわけで

PostgreSQL9.4.2をpgAdminⅢ（1.20.0）で使用する際に

ロケールも気を付けないといけません。

# 設定や初回起動等によるのでしょうが

ちなみに、今回ご指摘をいただいた

ロケールとtext_pattern_opsについては

Let's Postgresで記事になってました。

ご興味がある方はご参考までに。

Let's Postgres 国際化と地域化

では、今回はこれにて失礼します。

何か補足や「何言ってんだこいつ」ってのがありましたら

コメント欄にご記入をお願いいたします。

2015年6月5日金曜日

PostgreSQL B-treeインデックスの動作検証

2015年6月5日　その2

ごきげんよう。

今回はPostgreSQLのB-Treeインデックスについて検証してみました。

データベースについて勉強した方は
以下の本を読んだことがあるかもしれません。

達人に学ぶDB設計徹底指南書　初心者で終わりたくないあなたへ
著者：ミック
2012年初版発行

この本によると
以下の場合はインデックスが利用できないと記載されています。

インデックス列に演算を行っている

ORを用いている

後方一致、または中間一致のLIKE述語を用いている

#　他にもあるんですが、とりあえずこの3つをピックアップしました

LIKE述語の件は知ってたんですが、
演算とORについて知らなかったので合わせて検証してみました。

【環境】
OS：windows8.1
DB：PostgreSQL9.4.2
PostgreSQLのチューニング一切なし（デフォルトのまま）

【事前準備】

テーブル環境

CREATE TABLE index_test
(
id integer PRIMARY KEY,
num integer NOT NULL,
moji text NOT NULL
)

CREATE INDEX index_num ON index_test(num);

CREATE INDEX index_moji ON index_test(moji);

データは前回記事
Javaで重複なしのランダムな値をファイル出力する
http://show-surumegohan.blogspot.jp/2015/06/java.html
で作成した
100万件のCSVファイルです。

中身は以下のような感じです。

1,742983,hoge742983
2,903173,hoge903173
3,738660,hoge738660
：
：
999998,761436,hoge761436
999999,927983,hoge927983
1000000,128247,hoge128247

【検証1：インデックス列に演算を行っている】

num列の値を演算して検索してみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE num * 2 > 8000000;

"Seq Scan on index_test (cost=0.00..21370.00 rows=333333 width=18) (actual time=110.917..110.917 rows=0 loops=1)"
" Filter: ((num * 2) > 8000000)"
" Rows Removed by Filter: 1000000"
"Planning time: 0.081 ms"
"Execution time: 110.938 ms"

Seq Scan（シーケンススキャン）が走ってしまっています。

#　Seq Scanはテーブル内のデータをすべて参照する

で、この場合の対応策としては
WHERE句はあくまでnum列をそのまま呼び出して
同値の式変形をします。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE num > 8000000 / 2;

"Index Scan using index_num on index_test (cost=0.42..8.44 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)"
" Index Cond: (num > 4000000)"
"Planning time: 0.120 ms"
"Execution time: 0.019 ms"

無事にindex_numインデックスが使用されました。
演算が必要な場合は列を呼び出すときはそのままの状態で呼び出してあげましょう。

【検証2：ORを用いている】

本によると

ORを用いた場合はインデックスが利用できません。

と明記してあります。

ということでやってみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE num = 12345 OR num = 67890 OR num = 987654;

"Bitmap Heap Scan on index_test (cost=13.30..25.16 rows=3 width=18) (actual time=0.030..0.032 rows=3 loops=1)"
" Recheck Cond: ((num = 12345) OR (num = 67890) OR (num = 987654))"
" Heap Blocks: exact=3"
" -> BitmapOr (cost=13.30..13.30 rows=3 width=0) (actual time=0.024..0.024 rows=0 loops=1)"
" -> Bitmap Index Scan on index_num (cost=0.00..4.43 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
" Index Cond: (num = 12345)"
" -> Bitmap Index Scan on index_num (cost=0.00..4.43 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)"
" Index Cond: (num = 67890)"
" -> Bitmap Index Scan on index_num (cost=0.00..4.43 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)"
" Index Cond: (num = 987654)"
"Planning time: 0.105 ms"
"Execution time: 0.064 ms"

PostgreSQL9.4.2だと、
Bitmap Heap Scan
が動きました。

で、OR句はBitmapOr が走ってるようで
さらにその中で
Bitmap Index Scan
がORの回数だけ走っているようです。
つまり
ORでもインデックスは作用する
ようです。
#　本が発行された2012年の段階だとダメだった？

ちなみにミックさんは
こういうときはINにすればよいと記載しています。

なのでやってみました。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE num IN (12345,67890,987654);

"Index Scan using index_num on index_test (cost=0.43..25.33 rows=3 width=18) (actual time=0.008..0.018 rows=3 loops=1)"
" Index Cond: (num = ANY ('{12345,67890,987654}'::integer[]))"
"Planning time: 0.173 ms"
"Execution time: 0.052 ms"

ORに比べてINだとシンプルに
Index Scan
が走ってますね。
中をよく見てみると
Index Cond: (num = ANY ('{12345,67890,987654}'::integer[]))
となっていてANYでまとめられてるようです。

ただ、今回のケースだと実行速度はそんなに変わらないみたいです。
もっと複雑な条件が重なるとINの方がはやいんでしょうかね？

【検証3：後方一致、または中間一致のLIKE述語を用いている】

これは私も知ってたんですが
やってみたら意外な結果がでました。

まずは後方一致から。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE '%ge12345';

"Seq Scan on index_test (cost=0.00..18870.00 rows=100 width=18) (actual time=7.713..135.400 rows=1 loops=1)"
" Filter: (moji ~~ '%ge12345'::text)"
" Rows Removed by Filter: 999999"
"Planning time: 0.079 ms"
"Execution time: 135.419 ms"

Seq Scanが選ばれてしまっています。
インデックスが作用してませんね。

次に中間一致をやってみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE '%ge12345%';

"Seq Scan on index_test (cost=0.00..18870.00 rows=100 width=18) (actual time=1.423..134.443 rows=11 loops=1)"
" Filter: (moji ~~ '%ge12345%'::text)"
" Rows Removed by Filter: 999989"
"Planning time: 0.101 ms"
"Execution time: 134.470 ms"

この場合もSeq Scanが選ばれてしまっています。

さて、本にはこんなことが書いてあります。
LIKE述語を使うときは前方一致検索の場合のみ索引が使用されます。
私もその知識だったのですがひとまずやってみました。

前方一致をやってみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE 'hoge12345%';

"Seq Scan on index_test (cost=0.00..18870.00 rows=100 width=18) (actual time=1.395..119.152 rows=11 loops=1)"
" Filter: (moji ~~ 'hoge12345%'::text)"
" Rows Removed by Filter: 999989"
"Planning time: 0.104 ms"
"Execution time: 119.177 ms"

あれ・・・
前方一致のクエリを投げたつもりなのですが
結果はSeq Scanが走っています。
なんでなんだぜ？？

前方一致で似たようなクエリを何パターンかやってみたんですが
Seq Scanが走りました。

これはなんでなんでしょう？？
誰か教えてください。。。

というわけで
検証の結果、本の内容と若干異なる結果がでちゃいました。
2012年の本なので、2015年現在ではRDBも進化してるということでしょうか？
LIKE述語の前方一致の件、、誰か教えてください・・・

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

追記：
ありがたいことにコメントをいただけました。
次の記事でいただいたコメントをもとに再検証しました。
PostgreSQL ロケールとLIKE述語そしてpgAdminでの注意

Javaで重複なしのランダムな値をファイル出力する

2015年6月5日

ごきげんよう。

PostgreSQLで試験用のサンプルデータが欲しかったので、
Javaでデータ用CSVファイルを出力するコードを書きました。
重複しないランダムの整数値がポイントです。
#　Qiitaにもあげてみました。

欲しいデータの形としては
昇順の数値,重複なしのランダムな数値,重複なしのランダムな数値を含めた文字列
です。

とりあえず 1000000行出力します。

ところで SyntaxHighlighter ってのを使ってソースコードを表示してみました。
Chromeだと表示までに時間がかかるかもしれないです。

/*
 * 2015年6月5日作成
 *
 * 作成者：show
 *
 * 以下のCSVファイルを出力する。
 *
 * 1から1000000の値を順番に出力,
 * 1から1000000の値を重複なしでランダムな値を出力,
 * 1から1000000の値を重複なしでランダムな値と文字列と組み合わせて出力
 *
 */

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Random;

public class RandomValueFileCreate {

    public static void main(String args[]) {

        try {
            //出力先を作成する
            FileWriter fw = new FileWriter("C:\\temp\\sampleData.csv", false);
            PrintWriter pw = new PrintWriter(new BufferedWriter(fw));


            String str = "hoge"; //出力する文字列
            final int n = 1000000; //要素数
            boolean num[] = new boolean[n]; //重複判定用
            Random rand = new Random(); //ランダムな数値


            // すべての重複判定用配列をfalseにしておく
            for(int i=0; i<n; i++){

             num[i] = false;

            }

            //要素数回数をループ
            for(int i=0; i < n ; ){

             int p = rand.nextInt(n);

             if(num[p] == false){ //まだ使ってない値か判定

              pw.println((i+1) + "," + (p+1) + "," + str + "," + (p+1)); //初めて使う値ならファイル出力

              num[p] = true; //使った値はtrueにしておく

              i++; //ループ用の値をインクリメント

             }

            }

            //閉じる
            pw.close();

            //終了メッセージを画面に出力する
            System.out.println("出力が完了しました。");

        } catch (IOException e) {
            //例外時処理
            e.printStackTrace();
        }
    }

}

2015年6月2日火曜日

PostgreSQL　COPY文のちょっとした罠

2015年6月2日

ごきげんよう。

先日、PostgreSQLでCOPY文を使って
テーブルにCSVファイルのデータを投入する際に
ちょっとした罠にハマりましたので備忘録を。

#　既知の方も多いと思いますが。。

【環境】
OS：Windows8.1
PostgreSQL：9.4.2
pgAdmin：1.20.0
テキストエディタ：TeraPad 1.09

こんな普通のテーブルを作ります。

CREATE TABLE copy_test
(
id integer PRIMARY KEY,
num integer NOT NULL,
moji text NOT NULL
)

こんな変哲もないCSVデータを作ります。

で、挿入しようとしてCOPY文を実行したところエラーが・・・

ERROR: 型integerの入力構文が無効です: "1"
CONTEXT: copy_testのCOPY。行番号 1。列 id: "1"
********** エラー **********

ERROR: 型integerの入力構文が無効です: "1"
SQLステート:22P02
コンテキスト:copy_testのCOPY。行番号 1。列 id: "1"

お前は何を言ってるんだ・・（゜ω゜；）
1
が無効ってなんだ・・・

で、いろいろ調べた結果
UTF-8のCSVファイルに　BOM　ってのが含まれてるので
エラーを吐いているとのこと。

#　BOMについては各自検索してあげてください

というわけで
テキストエディタ（今回はTeraPad）でUTF-8NにCSVファイルを変更して上書き保存。