2014年5月28日水曜日

PostgreSQLの他データベースにアクセスする仕組み(FDW(Foreign Data Wrapper)編)

こんにちは、稲垣です。

前回は PostgreSQLには他のデータベースにアクセスする仕組みとして dblink をご案内しました。
今回は FDW(Foreign Data Wrapper) についてご案内します。

前回の dblink の記事はこちらからご確認ください。

■ FDWとは?

FDWはPostgreSQL 9.1から導入され、9.2からpostgres_fdwが利用できるようになりました。現在も様々な拡張が行われています。
FDWはより汎用的な外部データベースへのアクセス手段を提供します。PostgreSQL Wikiによると以下のデータラッパーが利用できます。

SQL Databases Wrappers
postgres_fdw
oracle_fdw
mysql_fdw
tds_fdw(not working)
odbc_fdw
jdbc_fdw
informix_fdw (WIP)
NoSQL Databases Wrappers
couchdb_fdw
MonetDB FDW
mongo_fdw
redis_fdw
Neo4j fdw
Tycoon FDW
File Wrappers
file_fdw
file_text_array_fdw
file_fixed_length_record_fdw
json_fdw
Others
twitter_fdw
ldap_fdw
PGStrom
s3_fdw
www_fdw

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

PostgreSQLのデータラッパーはもちろん、MySQL、Oracle、TDS(MS SQL Server)、ODBC、JDBC、Informixなども用意されています。NoSQL系のデータベース、ファイル、Webサービスのデータラッパーまであります。この他にPythonでFDWを記述できるMulticorn FDWもあります。

新しいPostgreSQLではdblinkよりpostgres_fdwを利用する方が色々頭を悩ませる事が少なくなります。

■ PostgreSQL FDW

PostgreSQL FDWはdblinkと似たような機能を提供します。しかし、トランザクション管理とリモートクエリ最適化の面でdblinkより優れています。

F.31.3. トランザクション管理

外部サーバ上のリモートテーブルを参照する際に、まだトランザクションが開始されていなければpostgres_fdwはリモートサーバ上でトランザクションを開始します。 ローカルのトランザクションがコミット、あるいはアボートした時、リモートのトランザクションも同様にコミット、あるいはアボートします。 セーブポイントも同様に管理され、リモート側に関連付けられたセーブポイントが作成されます。

F.31.4. リモート問合せの最適化

外部サーバからのデータ転送量を削減するため、postgres_fdwはリモート問合せを最適化しようと試みます。 これは問い合わせのWHERE句をリモートサーバに送出する事、およびクエリで必要とされていないカラムを取得しない事により行われます。 問い合わせの誤作動のリスクを下げるため、ビルトインのデータ型、演算子、関数だけを用いたものでない限り、リモートサーバにWHERE句は送出されません。また、WHERE句で使われる演算子と関数はIMMUTABLEでなければなりません。

http://www.postgresql.jp/document/9.3/html/postgres-fdw.html

■ PostgreSQL FDWの利用

PostgreSQL FDWはcontribディレクトリにあります。PostgreSQL 9.3から機能が拡張されています。今回は9.3を利用します。

■ インストール

PostgreSQL 9.3のソースディレクトリから以下のコマンドを実行し、インストールします。

$ make -C contrib && sudo make -C contrib install

次にPostgreSQL 9.3を起動し、データベースにpostgresql_fdw拡張を登録します。psqlのコマンドラインからCREATE EXTENSIONコマンドを実行します。

localhost ~=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
時間: 299.364 ms

これで、アクセス中のデータベースにpostgres_fdwが登録されました。psqlのコマンドラインからは以下のように確認できます。

localhost ~=# \dx
インストール済みの拡張の一覧
名前     | バージョン |  スキーマ  |                             説明
--------------+------------+------------+--------------------------------------------------------------
dblink       | 1.1        | public     | connect to other PostgreSQL databases from within a database
plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(3 行)

これでpostgres_fdwを利用する準備が整いました。

■ リモートデータベースの準備

テスト用にリモートデータベースを準備するにはpgbenchが便利です。テスト用にremotedbを作成し、pgbenchでデータデータを作成します。別のPostgreSQLサーバーに準備しても構いませんが、今回はテストなのでローカルのPostgreSQLに準備します。

remoteデータベースの作成

$ createdb -U postgres -h 127.0.0.1 -p 5432 -E UTF-8 remote

データベースの初期化

$ pgbench -U postgres -h 127.0.0.1 -p 5432 -i -s 1000 remote

これでテスト用のリモートデータベースが準備できました。

■ リモートデータベースの利用

FDWを利用したリモートデータベースには更に、外部サーバー登録、ユーザーマッピング登録、外部テーブル登録のステップが必要です。

リモートサーバーの登録

まずCREATE SERVERを利用して、利用するデータラッパーを指定してリモートサーバーを定義します。

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]

http://www.postgresql.org/docs/9.3/static/sql-createserver.html

実際にローカルホストのremoteデータベースを登録する場合

localhost ~=# CREATE SERVER remote_postgres FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'remote');
CREATE SERVER
時間: 121.581 ms

psqlから"\des”と入力すると作成した外部サーバー名、所有者、外部データラッパーを確認できます。

localhost ~=# \des
外部サーバー一覧
名前 | 所有者 | 外部データラッパー
-----------------+---------+--------------------
remote_postgres | username | postgres_fdw
(1 行)

ユーザーマッピングの登録

マッピング登録にはCREATE USER MAPPINGを利用します。

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[ OPTIONS ( option 'value' [ , ... ] ) ]

http://www.postgresql.org/docs/9.3/static/sql-createusermapping.html

この記事の執筆に利用しているPostgreSQLサーバーはローカルからの接続にtrustモードを利用しているので、パスワード設定などが必要ありません。次のコマンドでマッピングを作成します。

localhost ~=# CREATE USER MAPPING FOR PUBLIC SERVER remote_postgres OPTIONS (password '');
CREATE USER MAPPING
時間: 21.034 ms

外部テーブルの登録

外部テーブルの作成にはCREATE FOREIGN TABLEを利用します。

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
DEFAULT default_expr }
http://www.postgresql.org/docs/9.3/static/sql-createforeigntable.html

pgbenchでテーブルを定義したデータベースを保存する、remote_postgresサーバーへアクセスする外部テーブル定義は以下の様に行います。

  localhost ~=# CREATE FOREIGN TABLE remote_pgbench_accounts(aid int, bid int, ablance int, filler CHAR(84)) SERVER remote_postgres OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
時間: 197.626 ms

これで外部データベースのpgbench_accountsテーブルにアクセスできるようになりました。

■ 外部テーブルへのアクセス

外部テーブルへアクセス方法はローカルのデータベースと変わりません。

localhost ~=# SELECT * FROM remote_pgbench_accounts LIMIT 3; aid | bid | abalance | filler
-----+-----+----------+--------------------------------------------------------------------------------------
1 |   1 |        0 | 
2 |   1 |        0 | 
3 |   1 |        0 | 
(3 行)
時間: 1.686 ms

これだけではローカルのテーブルにアクセスしているのか、リモートのテーブルにアクセスしているのかわかりません。EXPLAINでクエリプランを表示するとリモートテーブルにアクセスしている事が確認できます。

localhost ~=# EXPLAIN SELECT * FROM remote_pgbench_accounts LIMIT 3;
QUERY PLAN
-----------------------------------------------------------------------------------------
  Limit  (cost=100.00..100.14 rows=3 width=100)
  ->  Foreign Scan on remote_pgbench_accounts  (cost=100.00..129.80 rows=660 width=100)
  (2 行)
時間: 373.532 ms

ローカルテーブルにアクセスした場合には見られない"Foreign Scan"が表示され、リモートテーブルにアクセスしていることが確認できます。

■ まとめ

今回はdblinkとpostgres_fdwの利用方法のみを紹介しました。dblinkとpostgres_fdwは似たような機能を提供しますが、postgres_fdwの方がクエリを効率的に実行できます。

2014年5月22日木曜日

PostgreSQLの他データベースにアクセスする仕組み(dblink編)

こんにちは、稲垣です。

PostgreSQLには他のデータベースにアクセスする仕組みが2種類用意されています。
一つはdblinkで他のPostgreSQLのデータベースにアクセスできます。もう一つはFDW(Foreign Data Wrapper)です。FDWはPostgreSQL 9.1から導入され、9.2からpostgres_fdwが利用できるようになりました。現在も様々な拡張が行われています。

今回は2回に分けて、dblink と FDW についてご案内します。

■ dblinkとは?

dblinkは別のPostgreSQLサーバーが管理するデータベースにアクセスする仕組みです。PostgreSQLのCクライアントライブラリであるlibpqを利用して、他のPostgreSQLデータベースにアクセスします。

dblinkは以下の書式を持っています。

dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record

http://www.postgresql.jp/document/9.3/html/contrib-dblink-function.html

実際に利用する場合、以下の様に利用します。

local=# SELECT * FROM dblink('dbname=remote user=postgres', 'SELECT version()') AS t(v text);

このクエリはdblinkで接続したデータベース名を返します。任意のクエリを実行できますが、record型を返さなければなりません。このため、この例ように、t(v text)と戻り値となるレコードの定義を明示しなければなりません。

何度も dblink を利用する場合、ビューを作成すると便利です。

CREATE VIEW myremote_pg_proc AS
SELECT *
FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text);

ビューを作成すると通常のテーブルのように扱えます。

SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

 

■ dblinkの制限

dblinkはリモートDBのデータをローカルデータベースのテーブルのように利用できるので、他のPostgreSQLサーバーのデータにアクセスしたい場合に便利です。しかし、制限もあります。

クエリの中で利用されると、結果全てをローカルデータベースに転送します。結果のレコード数が少ない場合は問題となりませんが、大きな場合にはクエリ実行効率が非常に悪くなります。

特にdblinkをビューとして作成した場合、クエリはローカルデータベースのテーブルを利用する場合と変わりません。ジョインを行った場合、ジョインに必要なレコードのみが選択されて送信されるのではなく、テーブル全てのデータがローカルデータベースに転送された後、ローカルでジョイン処理が行われます。この動作はほとんどの場合、この好ましくない動作でしょう。

この他にも整合性の問題があります。dblinkはリモートDBのテーブルや行を全くロックしません。この為、クエリの整合性が必要な場合は二層コミットを利用する必要があります。

 

■ dblink_openとdblink_close

クエリ内容を一度に全て送信されるとパフォーマンスに大きく影響します。必要なレコードのみを順次取得するにはカーソルを利用します。dblink_open関数はリモートデータベースでカーソルを開きます。

dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text

dblink_openで開いたカーソルはdblink_closeで閉じます。dblink_openとdblink_closeの間にdblink_execを使用してデータを変更した場合、エラーが発生します。 dblink_closeの前にdblink_disconnectを使用すると、トランザクションがアボートしますので変更が失われます。

dblink についてはここまでとなります。来週は「PostgreSQLの他データベースにアクセスする仕組み(FDW編)」 をご案内します。

2014年5月15日木曜日

PostgreSQL : マテリアライズドビューを利用した設計

マテリアライズドビューを利用した設計

こんにちは、稲垣です。
PostgreSQL9.3からビューを作成した時点からデータが変わらないビューである「マテリアライズドビュー」が追加されました。今回はPostgreSQL9.3の目玉新機能である、更新されないマテリアライズドビューの動作(ディスク使用量)を紹介します。

■ システムの要件

実用的な例として複数のデータソースからバッチジョブでデータを更新するシステムを想定します。

    システムの要件
  • システムは基本的に24稼働を前提とする
  • データが更新されている途中のデータは一般ユーザーが参照できない事
  • データ更新中も特定のユーザーは更新中のデータを参照できる事
  • データが更新された場合の更新時間を最小限にする事
  • データ量が多いのでディスク使用量は最小限にする事

このようなシステム要件でテーブル設計を考えてみます。

2014年5月8日木曜日

2014年6月4日 第5回 OSS運用管理勉強会開催のお知らせ

こんにちは、OSSビジネス推進の村田です。

サイオステクノロジーが法人企業会員として参加しているOSS運用管理勉強会の「第5回 OSS運用管理勉強会」が6月4日(水)に開催決定となりましたのでお知らせします。
一般にはあまり公開されていない運用をノウハウを、会員企業およびユーザー企業で情報共有を行おうという目的で開かれているため、運用に携わる方であれば良い勉強の機会になるのではないかと思います。ぜひ、皆様お気軽にご参加ください。   
  
今回は、以下の2つのプレゼンテーションを予定しています。

一つ目のプレゼンテーションは、複数のZabbixサーバ、およびNagiosサーバからデータを取得し、一括監視を可能にするオープンソースソフトウェア『運用統合ソフトウェアHatohol』をご紹介いただきます。

二つ目のプレゼンテーションは、書籍『Zabbix統合監視徹底活用 ~複雑化・大規模化するインフラの一元管理』を執筆された池田 大輔さんに、効率良い運用環境を実現するための様々なZabbix活用テクニックをご紹介いただきます。

今回も終了後に懇親会を開催します。毎回、懇親会も大変盛り上がりますので、お時間のある方は是非ご参加ください。