PostgreSQLアーキテクチャ入門（自習用教材）のご紹介

以前、オンラインで販売していたもののPaypalとのインテグレーションがうまく動かなくなりそのままになっていた「PostgreSQLアーキテクチャ入門（自習用教材）」の販売をGumroadで再開しました。

• PostgreSQLアーキテクチャ入門（自習用教材） http://www.uptime.jp/ja/products-services/pgsql-training/dg01/

1時間弱の同名のセミナーの内容（スライド動画と音声のMP4）とPDFファイルです。

特に、

• 普段、地理的制約や時間的制約でなかなかセミナーに参加できない
• セミナー後に配布された資料を見ただけではよく理解できない
• 後輩や部下の教育などのために簡単な教材を探している

という方にお勧めです。

何年か前のマテリアルですが、今でも十分通用する内容だと思いますので、PostgreSQLに興味のある方は冬休みの軽い勉強用にぜひどうぞ。

では。

JSONで1:Nのデータ構造をカジュアルに生成してみる

先日、第6回 PostgreSQLアンカンファレンスを開催したのですが、その際、Kuwata氏の以下のプレゼンを聴く機会がありました。

• Nippondanji氏に怒られても仕方ない、配列型とJSON型の使い方 (in PostgreSQL)
  http://kwatch.houkagoteatime.net/blog/2015/12/10/pg-array-and-json-types/

一言で言うと、PostgreSQLのJSONを使って



というようなデータ構造を生成したいのだがPostgreSQLでは実現が難しい、という内容でした。

その時は、「ふむふむ」と聞いていたのですが、最近、JSONの機能に興味を持っていたこともあり、「どうにか望むデータ構造をどうにか実現できないだろうか？」ということが気になっていました。その後、いくつか調査してみた結果、わりと簡単に実現できることが分かりましたので、本エントリではその方法を紹介します。

実現したいことは、

• 複数のテーブルをJOINして1:Nの構造を生成すること
• Nの部分にはJSONを使うこと
• JSONのキー名を任意に設定できること

です。

PostgreSQL 9.6のパラレルシーケンシャルスキャンを検証する

今年もこの季節になりました。PostgreSQL Advent Calendar 2015のDay1の記事です。

今回は、現在開発中のPostgreSQL 9.6に実装されたパラレルシーケンシャルスキャンについて、動作確認とフラッシュストレージでの簡単な性能検証をしてみようと思います。

なお、現在開発中の機能ですので、正式リリースされる時には仕様や動作などが変わっている可能性があることをご了承ください。

■「パラレルシーケンシャルスキャン」とは

「パラレルシーケンシャルスキャン」は、その名の通り、シーケンシャルスキャンを並列処理する機能です。

今まで、PostgreSQLでは1つのCPUを使ってシングルスレッド（というかプロセス）でしか処理をすることができませんでした。

しかし、直近のPostgreSQLのメジャーバージョンの拡張を見ていた方はご存じの通り、PostgreSQLではパラレル処理に向けての基本的な機能（開発者たちは infrastructure と呼んでいますが）が少しずつ実装されてきました。

そして、先月、PostgreSQLの待望のパラレル処理の一つ目の機能として、パラレルシーケンシャルスキャンがGitの最新版のコードにコミットされました。

• Robert Haas: Parallel Sequential Scan is Committed!
  http://rhaas.blogspot.jp/2015/11/parallel-sequential-scan-is-committed.html

このコードツリーは「9.6devel」と呼ばれており、約1年後にリリースされる予定のメジャーバージョン9.6から利用できるようになる予定です。

「PostgreSQLセキュリティ総復習」のスライドを公開しました

昨日開催されたPostgreSQLカンファレンス2015でのセッション「PostgreSQLセキュリティ総復習」のスライドを公開しました。

PostgreSQLセキュリティ総復習 from Uptime Technologies LLC (JP)

目次は以下のような感じです。

自動要約API「summpy」を使ってPostgreSQLに文章の要約機能を追加する

昨日、PostgreSQL勉強会で「PostgreSQLハッキング 最初の一歩」と題して、PostgreSQLの拡張開発の初歩についていくつかお話させていただきました。

• 第33回 PostgreSQL 勉強会(2015年11月14日)
  http://www.postgresql.jp/wg/shikumi/pgstudy_33/view

本エントリでは、勉強会でPL/Pythonの例としてご紹介した「自動要約APIをPostgreSQLに組み込む」について、もう詳しく紹介させていただこうと思います。

（右のサムネイルの意味は本エントリの最後に分かります）

■「自動要約API」とは？

自動要約APIは、10月末にリクルートテクノロジーズさんがリリースされた自然言語処理のライブラリで、Pythonで書かれたものです。

• 自動要約APIを作ったので公開します | RECRUIT TECHNOLOGIES Member's blog
  http://blog.recruit-tech.co.jp/2015/10/30/summpy-released/
• recruit-tech/summpy
  https://github.com/recruit-tech/summpy

アルゴリズムについてはあまり詳しくは理解していないのですが、LexRankと呼ばれるアルゴリズムとMCP（Maximum Coverage Problem）と呼ばれるアルゴリズムが実装されており、形態素解析で品詞を分解したものにこれらのアルゴリズムを適用することで、「文章の中で重要度の高いセンテンスを選択する」ということを実現するようです。

Postgres Advanced Security Packテクノロジープレビュー版をリリースしました

本日、「Postgres Advanced Security Pack」というパッケージのプレビュー版をリリースしましたので、ご紹介させていただきます。

■「Postgres Advanced Security Pack」とは何か？

「Posetgres Advanced Security Pack」は、オープンソースで公開されている PostgreSQL のセキュリティ関連の拡張を複数まとめてビルド、RPMパッケージにしたものです。

PostgreSQLの拡張モジュールは、別々の開発者によって個別に開発・配布されているため、自分自身の環境で導入しようとすると手間がかかったり、あるいは単に知られていなかったりしますが、それらの問題を解決して、より簡単に導入できるようにすることを目的としてパッケージ化したものです。

このパッケージを使うことによって、PostgreSQLデータベースにセキュリティ関連の拡張を簡単に追加することができます。

現在、本パッケージには以下の3つのEXTENSION（拡張）が含まれています。

Cognos InsightからPostgreSQLのデータを利用する

最近、デスクトップBIツールである「Cognos Insight」を入手して使い始めたのですが、Cognos InsightのStandard EditionではデータソースにODBC接続を利用することができます。

• IBM - Cognos Insight
  http://www-03.ibm.com/software/products/ja/cogninsi

自分の備忘も兼ねて、Cognos InsightでPostgreSQLにODBC接続してデータをインポートする方法を紹介しておきたいと思います。

なお、今回利用しているソフトウェアバージョンは、PostgreSQL 9.4とCognos Insight 10.2.2です。

■ODBCデータソースの準備

PostgreSQLのODBCドライバおよびデータソースの設定については、以前の記事を参照してください。

• ExcelからPostgreSQLにODBC接続してピボットテーブルを作成する
  http://pgsqldeepdive.blogspot.jp/2013/04/excelpostgresqlodbc.html

一通り確認しましたが、特に大きく手順が変わっているということはありませんでした。なお、本エントリ執筆時点でのPostgreSQLの64ビット版のODBCドライバは「psqlodbc_09_03_0300-x64-1.zip」です。

今回は、DWH系ベンチマークツールであるDBT-3のデータベースを使って、以下のデータソースをユーザDSNとして作成しています。

sql_firewall 0.8.1をリリースしました

先月リリースした sql_firewall の修正版としてバージョン0.8.1をリリースしました。

sql_firewallの詳細については、前回の記事を参考にしてください。

• PostgreSQL用SQLファイアーウォール「sql_firewall」リリース

0.8.1における修正点は以下の通りです。

• Fix sql_firewall.c to suppress `unused-const-variable' warning on OS X.
• Fix sql_firewall_import_rule() to check file status before importing a rule file.
• Fix JumbleRangeTable() to jumble query with relation name instead of oid.
• Fix JumbleExpr() to use function name on query jumbling instead of the oid.
• Fix README to add the Compatibility section.
• Add COPYRIGHT and ChangeLog.

sql_firewallは、クエリの種類を「クエリID」と呼ばれるハッシュ値を使って識別しています。

以前は、クエリIDの生成がテーブルや関数の OID に依存していたので、テーブルや関数の DROP & CREATE をすると、例えそれらの名前が変わらなくてもクエリIDが変わる、という現象が発生していました。（これは sql_firewall の元となった pg_stat_statements でも同様です）

バージョン0.8.1では、この点を修正し、テーブルや関数を作り直してもクエリIDが同一に保たれるようになりました。

PostgreSQL用SQLファイアーウォール「sql_firewall」リリース

先日、sql_firewallというPostgreSQLの拡張モジュール（EXTENSION）をリリースしました。

• https://news.ycombinator.com/item?id=10109566
• https://github.com/uptimejp/sql_firewall

このモジュールは、PostgreSQL上で実行可能なSQLを制限することで、SQLインジェクションを防ぐことを目的としたものです。

今回はこの sql_firewall について、その仕組みと使い方を簡単にご紹介します。

■sql_fiewallの仕組み

sql_firewallには、以下の3つの動作モードがあります。

• 学習モード（learning）
• 警告モード（permissive）
• 防御モード（enforcing）

【9.5新機能チェック】BRINインデックス, Part 1: BRINインデックスとは何か、その仕組みを探る

既に試してみている方もおられるかと思いますが、7月2日にPostgreSQL 9.5 alpha1 がリリースされました。

• PostgreSQL: PostgreSQL 9.5 Alpha 1 Released
• PostgreSQL 9.5 Alpha 1登場 | マイナビニュース

PostgreSQL 9.5にはいろいろと新しい機能が追加されていますが、その中に「BRINインデックス」という機能があります。

最近、人と話すと「BRINってどうなのよ？」と話題になることが増えており、また直近では情報系システムのプロジェクトに参加することが多く、個人的にいろいろと期待している機能の一つだったりします。

というわけで、今回から3回連続で、この「BRINインデックス」について、その仕組みと使いどころを探ってみたいと思います。

全3回は、それぞれ以下の内容になる予定です。

• BRINインデックス, Part 1「BRINインデックスとは何か、その仕組みを探る」
• BRINインデックス, Part 2「BRINインデックスの性能を探る」
• BRINインデックス, Part 3「BRINインデックスの使いどころを探る」

では、さっそく、BRINインデックスを見ていきましょう。

PostgreSQLの透過的暗号化（TDE）モジュールを使ってみる

先日、NECさんからPostgreSQLの暗号化モジュール「Transparent Data Encryption for PostgreSQL Free Edition」がGPLv3ライセンスのOSSとしてリリースされました。

• NEC、国内初データベース暗号化ソフトウェアを無償公開
  ～ 自己復旧や暗号鍵管理の機能を搭載した製品版も発売 ～

多くの方がご存じの通り、データベースのセキュリティは、近年非常に重要なトピックの一つになっています。

PostgreSQLには、以前からpgcryptoと呼ばれるモジュールが提供されていましたが、これはあくまでも暗号化を実現するSQL関数を提供するレベルで、実際に使おうとするとアプリケーションから明示的に呼び出す必要があるなど、使いこなすには煩雑なモジュールでした。（私も昔のプロジェクトで使っていたことがあります）

• pgcrypto

今回NECさんからリリースされた暗号化モジュールは、この（主にアプリケーションから見た）煩雑さを解消する「透過的暗号化（TDE）」と呼ばれるテクノロジー領域です。

私自身も以前から非常に興味を持っていた領域ですので、公開されたOSS版のコードを試しに使ってみました。

■「Transparent Data Encryption for PostgreSQL Free Edition」とは何か？

「今回リリースされたモジュールはつまり何なのか？」という質問に対してダイレクトかつシンプルに答えると、「暗号化をサポートしたPostgreSQLのユーザ定義のデータ型、ユーザ定義関数、およびサポートスクリプト」となるかと思います。

PostgreSQLでは、ユーザ定義のデータ型を作成することができますが、アプリケーションからそのデータ型に対して入出力する処理も自由に実装することができます。今回のモジュールは、その入出力の処理に暗号化機能を追加したものだと考えればよいでしょう。そのため、暗号化に対応したデータ型を使っている特定のカラムに対して、透過的に暗号化処理を行うことができます。

オリジナルのソースコードは以下から参照することができます。

PostgreSQLでFizzBuzzを書く

先日、久しぶりにネットでFizzBuzzネタを目にしたので、PostgreSQLでFizzBuzzする方法をご紹介しようと思います。

今回は小ネタエントリです。

■FizzBuzzとは

詳細はWikipediaを参照してください。

• http://ja.wikipedia.org/wiki/Fizz_Buzz

要は、1から100まで表示して、3で割り切れる時にFizz、5で割り切れる時にBuzz、3でも5でも割り切れる時にFizzBuzzと表示する、というプログラムを書くことです。

■1から100まで数字を表示する

まず、PostgreSQLで1から100までの数字を表示してみます。

PostgreSQLで連番を表示するには、generate_series()関数を使います。

snaga=> select * from generate_series(1,100);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
(snip)
              95
              96
              97
              98
              99
             100
(100 rows)

snaga=>

Postgres Toolkit 0.2.1をリリースしました

Postgres Toolkit 0.2.1をリリースしました。

このリリースは0.2のマイナーバージョンアップ版です。

0.2.1での変更点は、

• Python 2.7がデフォルトのRed Hat Enterprise Linux 7、CentOS 7およびUbuntu 14.04 LTSをサポート対象として追加。
• ヘルプメッセージの修正。 (pt-index-usage, pt-set-tablespace, pt-table-usage)

になります。

詳細は以下のドキュメントとソースコードを参照してください。

• Postgres Toolkit — Postgres Toolkit 0.2.1 ドキュメント
• uptimejp/postgres-toolkit (Github)

マニュアルに記載されているネット経由のインストール方法でインストールすると、0.2.1がインストールされます。

[Postgres Toolkit] pt-table-usage/pt-index-usageコマンドの使い方

今回は、前回のエントリで紹介した Postgres Toolkit に含まれるコマンドのうち、pt-table-usageコマンドとpt-index-usageコマンドを紹介します。

「Postgres Toolkitって何？」という方は前回のエントリからどうぞ。

• PostgreSQL Deep Dive: Postgres Toolkit 0.2をリリースしました

pt-table-usageとpt-index-usageコマンドは、それぞれテーブルとインデックスの利用状況を確認するためのコマンドです。

PostgreSQLの統計情報についてご存じの方はお分かりの通り、通常、PostgreSQLでこれらの情報を取得するには、複数のシステムビューやシステムテーブルを組み合わせなければなりません。

そのため、DBAにとって、もっとも頻繁に行う作業のひとつであるにも関わらず、かなり面倒な部類に入る作業です。

pt-table-usageとpt-index-usageは、これらの作業をコマンドひとつで実行できるようにしたものです。

Postgres Toolkit 0.2をリリースしました

本日、Postgres ToolkitというPostgreSQLのDBA向けツールキットをリリースしました。

• http://www.github.com/uptimejp/postgres-toolkit/
• https://postgres-toolkit-ja.readthedocs.org/

■「Postgres Toolkit」とは何か

Postgres Toolkitは、PostgreSQLの運用管理を楽にするためのスクリプトやツールのコレクションで、DBA業務の品質や生産性を高めることを目的としたツールキットです。

Postgres Toolkitを使うことで、複雑なSQLを実行したり、自前のスクリプトをメンテする必要性が減少します。コンセプトとしては、「PostgreSQL DBA向けのVictorinox（スイスアーミーナイフ）」のようなイメージです。

もともとは、私がトラブルシューティングなどに呼ばれた時などに、現場で使えるツールがあまりに少なく、なかなか生産性が向上しないと感じていたことが発想の原点であり、書き捨てのスクリプトを毎回作るのではなく、いつでも誰でも使えるような便利なツール類が必要なのではないか、と感じていました。位置付けとしてはMySQLのPercona Toolkitと似ています。

Postgres Toolkit 0.2は、PostgreSQLのバージョン9.0、9.1、9.2、9.3、9.4をサポートしており、OSはRed Hat Enterprise Linux 6/CentOS 6で動作確認をしています。Python 2.6がインストールされている必要があります（Python 2.6はRHEL6/CentOS6のデフォルトです）。

HAProxyでPostgreSQLを負荷分散する（デモ動画あり）

PostgreSQLにレプリケーション機能が標準機能として入ってしばらく経ちました。

PostgreSQLでもレプリケーション機能がいろいろなところで使われるようになってきた最近ですが、以前から気になっていたこととして、「L4のロードバランサでPostgreSQLの負荷分散ができないのだろうか？」という素朴な疑問がありました。

ずっと試してみたいと思っていたのですが、ようやく今回 HAProxy を用いることで動作させることができましたので、その方法を簡単にご紹介します。

■「L4のロードバランサ」とは

「L4（Layer4）ロードバランス」とはTCP層におけるロードバランスのことです。L4のロードバランサは、アプリケーションごとのセッションの内容には関係なく、TCP層の情報だけを使ってロードバランスを行います。

ネットワークのロードバランスには、L4以外にもL7のロードロードバランサもあり、L7のロードバランサの場合には、例えばHTTP/HTTPSのロードバランサであれば、HTTPのセッションの内容まで見て、Cookieを挿入したり参照したりして、ロードバランスの挙動を制御したりします。

L4とL7のロードバランサ違いについては、以下のページの「Layer-4 と Layer-7 のロードバランサの違い」の項目を参照してください。

【寄稿】PostgreSQL 9.4特徴紹介とpgCon China参加レポート

SIOSさんのオープンソースブログに「PostgreSQL 9.4特徴紹介とpgCon China参加レポート」と題して、PostgreSQL 9.4と12月に参加したpgCon Chinaについて寄稿させていただきました。

• PostgreSQL 9.4特徴紹介とpgCon China参加レポートhttp://oss.sios.com/guest-blog/gst-blg-20150210

日本では珍しい中国でのPostgreSQLコミュニティの様子よろしければご覧ください。

では、また。