Buena Vista de la Tecnología: PostgreSQL

ラベル PostgreSQL の投稿を表示しています。すべての投稿を表示

2015/12/23

PostgreSQLのネットワークアドレス型と外部モジュールip4r

この記事は PostgreSQL Advent Calendar 2015 の22日目の記事です(代打)。
最近、Amazon RDS for PostgreSQLで、ip4rというEXTENSIONが使えるようになったみたいですが、ip4rって初めて聞いたので簡単に調べてみました。

ip4rとは

ipという名前から連想されるように、ipアドレスやipアドレスの範囲を格納するデータ型です。
このモジュールを入れることで、下記の5つのデータ型を利用できるようになります。

ip4 ：IPv4のアドレス格納用のデータ型
ip4r：IPv4のアドレスレンジ格納用のデータ型
ip6：IPv6のアドレス格納用のデータ型
ip6r：IPv6のアドレスレンジ格納用のデータ型
ipaddress ：IPv4 or IPv6のアドレス格納用のデータ型
iprange ：IPv4 or IPv6のアドレスレンジ格納用のデータ型

ネットワークアドレス型との比較

PostgreSQLには、ネットワークアドレス型がサポートされており、inet型、cidr型というものを使うことで、IPv4, IPv6にIPアドレス、アドレスレンジを格納できます。
「ip4rって何のためにできたの？」と気になった方も多いかと思いますが、ip4rのページに下記のように書いてありました。

While PostgreSQL already has builtin types 'inet' and 'cidr', the authors of this module found that they had a number of requirements that were not addressed by the builtin type. Firstly and most importantly, the builtin types have no support for index lookups of the form (column >>= parameter), i.e. where you have a table of IP address ranges and wish to find which ones include a given IP address. This requires an rtree or gist index to do efficiently, and also requires a way to represent IP address ranges that do not fall precisely on CIDR boundaries. Secondly, the builtin inet/cidr are somewhat overloaded with semantics, with inet combining two distinct concepts (a netblock, and a specific IP within that netblock). Furthermore, they are variable length types (to support ipv6) with non-trivial overheads, and the authors (whose applications mainly deal in large volumes of single IPv4 addresses) wanted a more lightweight representation.

つまり、乱暴に言うと下記の通りでしょうか。

ビルトインのネットワークアドレス型では、(column >>= parameter)という演算子での検索にインデックスが利用できない(これが重要らしい)
ひとつのデータ型に意味を込め過ぎている(inetには、アドレスレンジとアドレスレンジ内の単一のIPアドレスを格納可能で、IPv4,IPv6ともにサポートされている)

確かに、IPv4のアドレスだけ管理したいような時は、inet型やcidr型だと、CHECK制約とかで回避しないといけないので、無駄なコストになりそうですね。

注意しなければいけない点としては、ip4rで利用できる関数とビルトインのネットワークアドレス型で利用できる関数の差でしょうか。
ビルトインのネットワーク型で利用できるすべての関数と同様のものが、ip4rにあるわけではなさそうです。

ちなみに最初に書いた通り、ip4rはAmazon RDS for PostgreSQLでもサポートされていまして、RDSでは

CREATE EXTENSION ip4r;

とやるだけで利用できるので、試すのも楽ちんでした。
IPアドレスの管理をしたい方は、ぜひ一度ネットワークアドレス型なりip4rを試してみてください。簡単ではありますが、こちらからは以上です。

2014/06/13

RDS(PostgreSQL)でJSTを使うたった１つの冴えたやり方

@j3tm0t0 さんのRDS(MySQL)でJSTを使うたった１つの冴えたやり方という記事があったので、「PostgreSQLも！」ということで、RDS for PostgreSQLでJSTを使う方法をブログにまとめようと思います。
このように書くと、RDS使うから特別なことが必要のように見えますが、普段PostgreSQLを使っている場合と同じ設定でOKです。
ただし、RDSを使用する場合、直接postgresql.conf(パラメータ設定ファイル)を編集できないので、DB Parameter Groupsを使用します。

1. RDS for PostgreSQLのデフォルト設定

Amazon RDS for PostgreSQLのシステム標準設定タイムゾーンはUTCです。
デフォルト設定のままPostgreSQLを立ち上げると下記のように表示されます。

$ date
2014年 6月13日 金曜日 13時32分42秒 JST
$ psql -h postgres.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -U postgres -c "SELECT CURRENT_TIMESTAMP"
Password for user postgres: 
              now              
-------------------------------
 2014-06-13 04:32:50.531748+00
(1 row)

2. JSTを使うには

2-1. DB Parameter Groupsによる設定

RDSでは、DB Parameter Groupsを使用することで、パラメータの設定が可能です。

PostgreSQL用のDB Parameter Groupsを作成し、設定項目中のTimezoneを「UTC」から、「Asia/Tokyo」と変更することで、JSTを使用できます。

2-2. RDS起動時にDB Parameter Groupsを割当

DB Parameter Groupsを作成したら、あとは起動時にそれを選ぶだけです。
(Rebootが必要ですが、起動中のRDSでもDB Parameter Groupsの変更が可能です。)

この設定を行ったRDSで試すと、下記のようにJSTで現在時刻が取得できますよー。

$ date
2014年 6月13日 金曜日 13時55分19秒 JST
$ psql -h postgres.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -U postgres -c "SELECT CURRENT_TIMESTAMP"
Password for user postgres: 
              now              
-------------------------------
 2014-06-13 13:55:25.306856+09
(1 row)

以上！

2014/02/23

Zabbix2.2のインストール with PostgreSQL

お久しぶりです。私は元気です。
タイトルの通り、バックエンドのDBをPostgreSQLにして、Zabbix2.2をインストールしてみました。

Zabbixのバックエンドには色んなDBが使えるはずなのに、MySQLの情報ばかりです。
挙げ句の果てに、Zabbix本家のマニュアルにもMySQLを使った場合しか書いていません。
https://www.zabbix.com/documentation/2.2/manual/installation/install_from_packages
というわけで、PostgreSQLを使った場合のインストール方法をまとめてみようかと(zabbixサーバのみ)。

0. 準備

今回はRHEL6.4、PostgreSQL9.3.3を使います。
OSの準備、設定は省略して、PostgreSQLのインストールと設定から始めます。
まず、PostgreSQLのソースコードをダウンロードし、インストールを行います。

$ wget http://ftp.postgresql.org/pub/source/v9.3.3/postgresql-9.3.3.tar.gz
$ sudo yum groupinstall 'Development tools'
$ sudo yum install -y readline-devel zlib-devel bison flex
$ tar xvfz postgresql-9.3.3.tar.gz
$ cd postgresql-9.3.3
$  ./configure --prefix=/usr/local/pgsql-933
$ make
$ sudo make install

yumでインストールしているのはコンパイルに必要なパッケージです。

1. リポジトリ設定パッケージのインストール

Zabbix用のリポジトリ設定パッケージをインストールします。

$ sudo rpm -ivh http://repo.zabbix.com/zabbix/2.2/rhel/6/x86_64/zabbix-release-2.2-1.el6.noarch.rpm

2. Zabbixパッケージのインストール

yumで一発です。

$ sudo yum install zabbix-server-pgsql zabbix-web-pgsql

2. Zabbixデータベースの作成

データベースクラスタを作成し、zabbix管理用のユーザ、データベースを作成します。

$ export PATH=/usr/local/pgsql-933/bin:$PATH:$HOME/bin # PATHの設定
$ mkdir -p /pgdata/data
$ initdb -D /pgdata/data/ -E utf8 --no-locale
$ pg_ctl -D /pgdata/data/ start
$ createuser -U postgres zabbix
$ createdb -U postgres -O zabbix zabbix

続いて、スキーマとデータをインポートします。

$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.2.2/create/schema.sql
$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.2.2/create/images.sql 
$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.2.2/create/data.sql

3. Zabbixサーバプロセスの起動

設定ファイルにデータベースの情報を入力します。

$ sudo vi /etc/zabbix/zabbix_server.conf
    :
    :
DBHost=localhost
DBName=zabbix
DBUser=zabbix
DBPassword=
    :
    :

サーバプロセスを起動します。

$ sudo service zabbix-server start

4. Zabbix WebインターフェースのPHP設定

ZabbixはWebインタフェースを使用するみたいです。
Apacheの設定ファイルが、/etc/httpd/conf.d/zabbix.confにインストールされます。
ここでは、以下のように設定しました。
最低限、timezoneは設定する必要があります。

$ sudo vi /etc/httpd/conf.d/zabbix.conf
    :
    :
    php_value max_execution_time 600
    php_value memory_limit 128M
    php_value post_max_size 32M
    php_value upload_max_filesize 16M
    php_value max_input_time 600
    php_value date.timezone Asia/Tokyo
    :
    :

設定した後は、httpdを再起動する必要があります。

$ sudo service httpd restart

5. WebアクセスのためのOS設定

HTTP通信ができるように設定を行っていきます。
まず、石川さんに謝ります。

$ sudo setenforce 0
$ sudo reboot

続いて、ファイアウォールの設定を行います。
ポート80での通信を許可します。

$ sudo /sbin/iptables -I INPUT -p tcp --dport 80 -j ACCEPT

6. Zabbix Webインターフェースからの設定

http://[zabbixサーバのホスト名 or IPアドレス]/zabbix へブラウザでアクセスします。
下記のような画面が見えれば成功です。
「NEXT」をクリックします。

続いて下記の画面にて、全ての項目が[OK]であることを確認して「NEXT」をクリックします。

続いて、DB設定を行います。下記のように設定を行い、「Test Connection」をクリックします。
接続に成功したら、「NEXT」をクリックします。

Zabbixサーバの設定を行います。Zabbix サーバとフロントエンドが同じところで動いているので、このまま「NEXT」をクリックします。

確認画面で問題なければ、「NEXT」をクリックします。

無事インストールが終わると下記のように表示されます。

そのままZabbix 管理画面へのログインします。

下記のような画面が見えて、Zabbixサーバーの起動が「Yes」とか「はい」となっていればよいみたいです。
(言語設定はプロファイルで変更できます。)

ひとまず今日はここまで。
では。

参考資料

下記、参考にさせていただきました。ありがとうございます。
○本家マニュアル
https://www.zabbix.com/documentation/2.2/manual/installation/install_from_packages
○第 1 回 Zabbix を動かしてみよう - SRS OSS
http://www.sraoss.co.jp/technology/zabbix/introduction/01-firststep.php#3

2013/12/16

SQLの単体テストとpgtap

本記事は2013年のPostgreSQL Advent Calendarの 12/16の記事です。

近頃、SQLの単体テストをやらないといけないなーということが、仕事であったので、本日は、単体テストを行う上での考え方と一つのやり方の例を紹介したいと思います。

●最初に明確にすること

当たり前の話になりますが、単体テストに限らず、試験を行うときには、「何を確認するか」、「どの程度試験を実施するか」を明確にする必要があります。
重要なことは、その明確にしたことを「テストの受入者」と合意することです。

この話をしだすと、このブログだけでは語りきれなくなるので、これ以上は割愛します。

●試験を行う上で、ツールを使うことの是非

試験を行う上で、「ツールを使って試験をするべきだ」、「いや、使う必要はない」というようなやりとりを耳にすることがよくあります。
このときによくないのは、「なんとなくツールを使うとよさそうだから」、「今までのやり方で慣れているから」、「ツール使うと学習コストが、かかりそうだから」などと、あまり先のことを考えずに、適当にやり方を決めてしまうことです。

ツールを使う、使わないはあくまでも手段であり、目的は別にあります。
ツールのメリット、デメリットを考えた上で、決めるべきでしょう。

私が考えるツールを使用するメリット、デメリットは以下の通りです。

□ 単体テストツールを使うメリット

○再テストの容易さ
- テストプログラムを再実行することで、繰り返しテストを行うことができる

○テストが楽に
- 試験結果を自動的に判定するため、試験結果を確認する際に誤りが発生しにくい
- 証跡取得の漏れを防ぐことができる

○汎用性
- 一度やり方を確立すれば、他のプロジェクトにも展開可能

□ 単体テストツールを使うデメリット

○工数が大きくなる可能性
- 大量のテストプログラムを書くことになり、試験準備に大きい工数がかかる場合がある
- テストプログラム自体が正しいか、確認が必要で、長大なプログラムの場合、さらなる工数が発生する可能性がある

私が単体テストを担当するプロジェクトでは、定期的にアプリケーションのメンテナンスが入ったり、仕様の変更が入ったりする可能性がありました。
そのため、繰り返しテストを行う可能性があり、ツールを使って、試験を自動化したいなーと考えました。

※ ツールを使うと速く試験を行うことができるという声を耳にすることがよくあります。個人的な意見ですが、ツールを使うと早くなるというのはケースバイケースだと思います。特に、繰り返しテストを実施せず、一回のみの時間で比較した場合はツールの方が早くならないケースも多いと思います。早くなる、ならないは、どの程度のテストプログラムをどのように書くのかに依る所が大きいのではないかと思います（当然、速く正確に書くことを意識するのは重要です）。

●ツール選択

SQLの単体テストを行うにあたっては、大きく以下の２つの種類のツールに分かれるのではないかと思います。

SQLのみを確認するツール：pgtapなど
アプリケーションからSQLを実行した結果を確認する：xUnit(jUnit, dbUnitなど)

ここで重要になるのは、「何を確認するか」です。
JavaからSQLが実行されるアプリケーションで、そのJavaで実行される処理結果を確認したいのに、1のツールを使用してもテストの意味は半減してしまうでしょう。

私のケースでは、アプリケーションとSQLで責任分解点を設けていたため、1のツールを使用することにしました。

●pgtapとは

pgtapでは、SQLの確認だけではなく、スキーマ、プロシージャのテストも行うことができます。pgtapについては、昨年のPostgreSQL Advent Calendar 2012（全部俺）で、永安さんが解説しています。基本的なことはこちらで書かれているので、ここでは永安さんのブログで解説されていない注意点などを意識して、記述したいと思います。

□ 対応バージョンなどについて

私の環境では、pgtap最新バージョンの0.93を使用しています。
2013年12月16日現在、PostgreSQL9.2系までは対応していますが、9.3系には未対応のようです。

□ 使用するための準備

pgtapをインストールするには、依存パッケージとして、TAP::Parser::SourceHandler::pgTAPのインストールが必要です。

# tar xvfz TAP-Parser-SourceHandler-pgTAP-3.29.tar.gz
# cd TAP-Parser-SourceHandler-pgTAP-3.29
# perl Build.PL
# ./Build
# ./Build install

こちらのインストールが完了してから、pgtapをインストールします。

# unzip pgtap-0.93.0.zip
# cd pgtap-0.93.0
# make USE_PGXS=1
# make USE_PGXS=1 install

そして、対象となるデータベースにログインし、pgtapの拡張登録をします。

$ psql -d [データベース名] -c "CREATE EXTENSION pgtap"

□ 使用方法

ここでは、SQL結果を確認するための方法について記述します。
pgtapで確認するのは、「実行したSQLの結果が、どの"型"でどういう"値"を返すか」です。これは、後述の注意点でも書きますが、少しやっかいな面があります。

以下、私の使っているサンプルDBを使ってpgtapを試します。
# 今回使用したDBの情報、テーブルはこちらからダウンロードできます。

pgtapで試験を開始するには、トランザクションを開始しする必要があります。

hr=# BEGIN;
BEGIN

続いて、plan関数を使用して、実施するテスト数を決めます。

hr=# SELECT plan(1);
 plan
------
 1..1
(1 行)

requets_eq関数を使用して、下記のように試験対象のSQL、期待する結果を記述して、想定通りの結果が返ってくるかを確認します。

hr=# SELECT results_eq(
hr(#   'SELECT
hr'#      first_name,
hr'#      last_name
hr'#    FROM
hr'#      employees emp,
hr'#      departments dept
hr'#    WHERE
hr'#      emp.department_id = dept.department_id AND
hr'#      dept.department_id = 60;
hr'#   ',
hr(#     $$
hr$#       VALUES
hr$#         ('Alexander'::varchar, 'Hunold'::varchar),
hr$#         ('Bruce'::varchar, 'Ernst'::varchar),
hr$#         ('David'::varchar, 'Austin'::varchar),
hr$#         ('Valli'::varchar, 'Pataballa'::varchar),
hr$#         ('Diana'::varchar, 'Lorentz'::varchar)
hr$#     $$);
 results_eq
------------
 ok 1
(1 行)

okが返ってくれば、試験成功です。すべてのテストが終わったら、下記のようにトランザクションをロールバックさせて、テストを終了させます。

hr=# ABORT;
ROLLBACK

□pgtapの注意点

○結果確認の厳密性
pgtapの注意点として、「結果確認がものすごく厳密に行われる」ということがあげられます。
pgtapで確認するのは、「実行したSQLの結果が、どの"型"でどういう"値"を返すか」なので、上記で示した通り、SQLで確認する結果は、SQLで確認する項目をキャストしてあげる必要があります。
下記のようにキャストをしないで実行すると、NGとなります。

hr=# SELECT results_eq(
hr(#   'SELECT
hr'#      first_name,
hr'#      last_name
hr'#    FROM
hr'#      employees emp,
hr'#      departments dept
hr'#    WHERE
hr'#      emp.department_id = dept.department_id AND
hr'#      dept.department_id = 60;
hr'#   ',
hr(#     $$
hr$#       VALUES
hr$#         ('Alexander', 'Hunold'),
hr$#         ('Bruce', 'Ernst'),
hr$#         ('David', 'Austin'),
hr$#         ('Valli', 'Pataballa'),
hr$#         ('Diana', 'Lorentz')
hr$#     $$);
                           results_eq                             
-------------------------------------------------------------------
 not ok 1                                                           +
 # Failed test 1                                                  +
 # Number of columns or their types differ between the queries
(1 row)

このことは、pgtapのMLなどでも質問にあげられていたこともあるようで、
"You always have to compare objects of the same types"などと言われています。

ちなみに、文字列型は、TEXT型、整数型はINTEGER型がデフォルトのようで、これらの試験を行う場合は、キャストしなくても大丈夫です。

スキーマを確認する関数でも大文字小文字の一致や、型名の完全一致（VARCHARではNGで、character varying(20)などと記述する必要がある）が厳密に行われるので、結果が正しいはずなのにNGと表示される場合は、そういった部分を疑ってみてください。

○エスケープ処理
とても些細な話ですが、試験対象のSQLのWHERE句で文字列型などの列を条件式で使用する場合、下記のようにエスケープしてあげないとSQLがエラーになります。

hr=# SELECT results_eq('SELECT first_name FROM employees WHERE employee_id=''105''', ARRAY['David'::varchar]);

●テストプログラムをどう書くかについて

最後にテストプログラムを書き方について、一言。
単他テストにツールを用いるデメリットとして、「工数が大きくなる可能性」をあげました。pgtapも大量のプログラムを書くことになります。
それを時間をかけずに行うには、テストプログラムを人力で書くのではなく、別のプログラムやシェルに書かせるという方法があげられます。
テスト対象のSQLや期待される結果セットを、CSVでもたせるようにして、pgtap用のスクリプトに書き換えるのでもいいですし、EXCELでもたせてマクロでプログラムを生成してもいいと思います。なんでもよいので、仕組みをつくることが重要です。
その具体的な方法はまた書けたらと思いますが、本日はこんな所で

登録: 投稿 (Atom)