まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:postgresql

このケースというか、この利用パターンでの情報がググっても意外と見つからなかったので、自分でメモを残します。

なお、以下の内容は x86_64 アーキテクチャの docker 環境が導入済みであるという前提で説明します。


【やりたかったこと】
やりたかったことは、
- クラウドなどでデータベース・サーバー(今回は PostgreSQL)を利用して、
- そのデータベース・サーバーを GUI 管理するためのクライアント(今回は pgadmin4)をローカルの docker コンテナとして起動する

というものです。環境自体は docker なしでももちろん可能ですが、自分の開発環境に余計なものを入れたくなかったので、docker コンテナで ON/OFF できる形で用意できれば理想かな、と思い、docker を使って構築しようと考えました。

この場合、前者(PostgreSQL)は既に起動している前提となるので、後者(pgadmin4)をどうやって docker 内に用意すればよいか、が課題となります。

クラウドのデータベース利用時ではそれほど珍しくない利用パターンだと思っているのですが、ググってみると(docker-compose 等を使って)ローカル docker コンテナに PostgreSQL と pgadmin4 の両方を起動して利用するケースでの手順が多く見つかり、pgadmin4 だけを単独でローカル docker に起動して利用する手順が意外と見つかりませんでした。 というわけで、以下は自分で調べた内容の備忘録メモです。


【調べたこと】
pgadmin は dpage/pgadmin4 イメージを使わせていただくことにします。ページ概要だけを見るとコンテナ起動時にどんなパラメータをどのように指定すればよいかわかりにくかったのですが、結論としてはこんな感じでパラメータを指定すればよさそうでした:
パラメータ指定方法パラメータの意味
PGADMIN_DEFAULT_EMAIL 環境変数 pgadmin ログイン時のユーザー名
PGADMIN_DEFAULT_PASSWORD 環境変数 pgadmin ログイン時のパスワード
ポートフォワード docker run 時の -p パラメータ 80 番(http)への内部ポートをどのポート番号からフォーワードするか


【動かしてみる】
ターミナルやコマンドプロンプトを起動し、上述の内容を docker コマンドで指定して dpage/pgadmin イメージをコンテナ化します:
$ docker pull dpage/pgadmin

$ docker run --name pgadmin4 -e "PGADMIN_DEFAULT_EMAIL=dotnsf@xxxx.com" -e "PGADMIN_DEFAULT_PASSWORD=P@ssw0rd" -d -p 8000:80 dpage/pgadmin4

上の例では PGADMIN_DEFAULT_EMAIL に dotnsf@xxxx.com を、PGADMIN_DEFAULT_PASSWORD に P@ssw0rd を指定し、ポートフォワードのポート番号は 8000 番に指定して、pgadmin4 という名前でコンテナを起動しています。必要に応じて上述部分を変更して使ってください。


今回は 8000 番ポートで起動しているので、ウェブブラウザで 8000 番ポートを指定して http://localhost:8000/ にアクセスします:
2021021001


pgadmin(4) が起動していれば上記のような画面になります。ここで docker コンテナ起動時に環境変数で指定したログインユーザー名とパスワードを指定し、必要であれば言語を "Japanese" に指定してログインします:
2021021002


無事に pgAdmin にログインできました。実際にクラウドで起動中のデータベース・サーバーに接続するにはトップページから「新しいサーバを追加」を選択します:
2021021003


起動中の PostgreSQL データベース・サーバーに接続するためのホスト名やポート番号等の情報を入力して保存します:
2021021004


正しい情報が入力できているとデータベース・サーバーに接続でき、統計情報やスキーマ、実際のデータを pgadmin 画面から参照できるようになります:
2021021005


使わない時はコンテナを止めてしまえばポートも開放されるし、コンテナを削除しなければ接続情報なども保存されるので、気軽に ON/OFF できる pgadmin 環境が構築できました。


MySQL 派な自分にとって、初体験中の PostgreSQL の話です。CLI から利用する場合の(MySQL との)コマンドの違いに戸惑いましたが、まあ慣れてしまえばさほどは気になりません。

ただ1つ困ったことがありました。前提として自分はプログラミングで Node.js を使っていて、Node.js から PostgreSQL にアクセスするには node-postgres(pg) というライブラリパッケージを使っています。

今、ある配列(数値または文字列)があったとして、「その配列内のいずれかの値と一致する ID を持つレコードをすべて取り出す」という処理を実行したいとします。PostgreSQL 含めて一般的なリレーショナル・データベースであれば、"in" 句を使って以下のように処理できます:
> select * from mytable where id in ( '000', '001', '002' );

node-postgres を使った場合、この処理は以下のように記述することで同様に実行することができます:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    client.query( { text: "select * from mytable where id in ( '000', '001', '002' )", values: [] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});


困ったことというのは、上述の配列部分を変数にした場合です。例えば以下のようにすると文法エラーにはなりませんが、(期待通りに展開されないのか)該当データが存在していても結果は空でした:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in ( $1 )", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

文法的にはこっちの書き方のほうが正しいかな、と思って以下の SQL に変えてみると、今度は文法エラーになってしまいました:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in $1", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

-> syntax error at or near "$1"

いずれにせよ、配列部分を SQL 内で直接記述して具体的に指定すれば動くのですが、配列を変数化して実行する正しい方法がわかりませんでした。配列をループさせて SQL 文を直に作ればできないこともなさそうですが、そうすると SQL インジェクションにも気を付ける必要がでてくるので、配列変数のままでうまいこと実行する術はないだろうか、、、と悩んでいました。

で、やっとその解決策を見つけることができました。具体的には以下の方法です:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id = any($1::varchar[])", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});


SQL 文中の "varchar" 部分はテーブル定義した際の配列要素の型です。上の例では id は文字列(varchar)だったのでこのように varchar[] となりますが、int 型で定義していた場合は int[] などとなります。

このように記述すると PostgreSQL 側が実行時に any($1::varchar[]) 部を指定された型(varchar)のパラメータに自動変換してくれるらしく、SQL インジェクションの心配もなく実現できるようでした。


(参考)
https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query



IBM Cloud から提供されているマネージドデータベースサービスの1つである PostgreSQL を使う機会がありました(MySQL は過去に使ったことありましたが、PostgreSQL でサービスを作ったのは初めてでした)。PostgreSQL をデータベースとする Node.js のウェブアプリケーションを開発するのが目的でしたが、最初の準備がちと独特だと感じたので次回戸惑わないようにその手順をまとめておきました:
2021020300



【サービス作成までの手順】
まずは IBM Cloud 内のダッシュボードで Database for PostgreSQL という名前のサービスを探して選択します。"PostgreSQL" という名前でいくつかのサービスが見つかりますが、他を選択しないように注意してください(なおこのサービスは有償サービスのみです。無料でのサービスプランは存在していません):
2021020301


"Standard" プランを選択し、必要に応じて容量などのパラメータを調整し、最後に "Create" ボタンでサービスを作成します:
2021020302


サービス作成が完了するとダッシュボードからも参照・選択できるようになります:
2021020303


【サービス作成後の作業、および手順】
Database for PostgreSQL サービスを実際に利用する(テーブルを作ったり、データを読み書きしたりする)ためには、その前にいくつかの準備段階が必要です:
1. 証明書のダウンロード
2. admin ユーザーのパスワード変更
3. 接続情報の確認


まずは証明書をダウンロードします(プログラムコードからデータベースに接続する際に必要です)。作成したサービスインスタンスの画面を開き、画面左の "Overview" メニューを選択します:
2021020304


"Overview" 画面を下にスクロールすると、エンドポイントに関する情報を参照できる画面が現れます。ここの "Quick start" タブ内に TLS 証明書の内容が表示されている箇所があります。その下の "Download Certificate" ボタンをクリックすると、同証明書の内容をテキストファイルでダウンロードできます:
2021020305

(後述の作業のため、ダウンロードしたファイルの名前を cert.crt と変更しておきます)

次に実際にデータベースを読み書きする際のログインユーザー(admin)のパスワードを設定します。画面左の "Setup" メニューを選び、"Change Password" 欄から新しいパスワードを設定できます。ここで設定したパスワードを使って PostgreSQL にログインできるようになります:
2021020306


最後に PostgreSQL へ接続するための接続情報を確認します。"Service credentials" メニューから "New credential" ボタンをクリックして新しい接続情報を作成します:
2021020307


作成した接続情報を展開して、以下3箇所の内容を確認しておきます:
2021020308

接続情報の場所値が意味するもの
connection.postgres.hosts.hostnameホスト名
connection.postgres.hosts.portポート番号
connection.postgres.databaseデータベース名


以上、この3点の作業を行うことで外部プログラムからデータベースに接続するための準備は完了しました。


【外部アプリケーションからデータベースに接続】
以下、Node.js を例として、プログラムから同データベースに接続してクエリーを発行するサンプルを紹介します。上述の準備段階で確認した内容を使って、以下のようなコードを記述して実行します:
var fs = require( 'fs' );
var PG = require( 'pg' );  //. node-postgres https://www.npmjs.com/package/pg

//. PostgreSQL
var pg_hostname = 'hostname';    //. connection.postgres.host.hostname の値
var pg_port = 35432;             //. connection.postgres.host.port の値
var pg_database = 'ibmclouddb';  //. connection.postgres.database の値
var pg_username = 'admin';       //. ユーザー名(固定値)
var pg_password = 'password';    //. 設定したパスワード

var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
var caCert = fs.readFileSync( './cert.crt', 'utf-8' );  //. ダウンロードした証明書ファイル
var pg = new PG.Client({ 
    connectionString: connectionString,
    ssl: { ca: caCert, rejectUnauthorized: true }
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var sql = 'select * from mytable where id = $1';
    var query = { text: sql, values: [ "123" ] };
    client.query( query, function( err, result ){
      if( err ){
        console.log( err );
      }else{
        console.log( result );
      }
    });
  }
});

今回は node-postgres という npm パッケージを使って PostgreSQL にアクセスしています。まず接続情報から取得した値を使って接続文字列を作成します。pg_username(ユーザー名)だけは "admin" で固定になりますが、それ以外の値は上述の作業で接続情報から参照したものや、自分で設定したパスワードを指定します。

次に接続する際に TLS 証明書が必要です。この証明書も上述の作業でダウンロードした TLS 証明書ファイル(cert.crt という名前でダウンロードしたと仮定しています)をファイルパスを指定して読み込み、接続時のパラメータとして含めています。こうすることで node-postgres 経由で証明書を使った接続が可能となります。

接続後は一般的な PostgreSQL 利用と同じですが、接続結果として得られた client オブジェクトを利用して SQL が実行できるようになる、というものです。


↑の「証明書ファイルを指定して接続する」という部分が(手順としては)少し特殊ですが、より安全な接続が実現できるようになるものです。



これまで MariaDB などの MySQL 系のリレーショナルデータベースばかり使っていたのですが、PostgreSQL を使う機会が増えそうなので、改めて勉強することにしました。

というわけで PostgreSQL を(いつもの CentOS に)インストール・・・しようとしたのですが、1つ問題が生じました。CentOS や RedHat の yum の標準リポジトリに PostgreSQL が含まれているので、yum で簡単にインストールできます。ただ標準リポジトリからインストールできるのは PostgreSQL 8.4 です。より新しいバージョン(9.x)をインストールしたい場合の、その手順を紹介します。

CentOS に yum で PostgreSQL 9.x をインストールするには、まず標準のリポジトリで PostgreSQL がインストールされないように設定を変更しておきます。/etc/yum.repos.d/CentOS-Base.repo をテキストエディタで開き、[base] セクションと [updates] セクション両方に以下の1行を追加します:
  :
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*

[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
  :

次に PostgreSQL 9.x 用の yum リポジトリを追加します。以下は 64bit 版での例です:
# yum -y localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

これで PostgreSQL 9.x をインストールする準備が出来ました。次のコマンドで PostgreSQL 9.4 Server をインストールします:
# yum -y install postgresql94-server

インストール後に、データベース初期化のコマンドを実行する必要があります:
# service postgresql-9.4 initdb

そして PosgreSQL サーバーを起動して、自動起動設定も加えておきます:
# service postgresql-9.4 start
# chkconfig postgresql-9.4 on


ここまでの手順で PostgreSQL サーバーが導入され、サービスとして起動し、利用できる状態になりました。では実際に使ってみましょう。

このインストールの手順の中で postgres という名前のユーザーがシステムに追加されているはずです。まずはこのユーザーのパスワードを設定します:
# passwd postgresql
ユーザー postgres のパスワードを変更。
新しいパスワード: (設定するパスワードを入力、表示されません)
新しいパスワードを再入力してください:(パスワードを再入力)
passwd: 全ての認証トークンが正しく更新できました。

その後、postgres ユーザーに切り替えて、psql コマンドを実行するとコマンドライン環境でローカルの PostgreSQL サーバーに接続します:
# su - postgres
$ psql
psql (9.4.5)
"help" でヘルプを表示します.

postgres=#

この状態から SQL を発行してデータベースやユーザーを作ったり、テーブルを定義したり、問い合わせをしたり、・・・といった一連の作業を行うことができます。

このコマンドライン PostgreSQL 環境から抜ける場合は \q を実行します:
postgres=# \q
$



(↓2015/Jan/26追記)
このブログエントリに書かれている内容は最新のものではありません。ご注意ください。
また、こちらの記事も参照ください:
Bluemix 内の MySQL データベースでサイズが選択可能になった
(↑2015/Jan/26追記)


IBM BlueMix からは数多くのデータストアサービスが提供されています。RDB(リレーショナルデータベース)だけでも4つあります(青っぽい背景のが IBM 提供サービス、白背景のがオープンソースベースのコミュニティ提供サービスです):

※2014年4月23日時点の情報を元に記述しています。詳しくは各リンク先を参照ください。
サービス価格最大サイズ説明
BLUEAccelerationベータ期間は無料標準で圧縮後1GB(最大10GBまで拡張可能)サイズは圧縮後のサイズなので、実際にはこれら以上のデータを格納可能
SQLDBベータ期間は無料1GB最大接続数は2(BlueMix環境からのみ)
MySQL無料10MB最大接続数は1000
PostgreSQL無料30MB最大接続数は1000

容量10MB のデータベースって・・・ (^^;

ちなみに RDB 以外のデータストアサービスについてはこんな感じです:

サービス価格最大サイズ説明
JSON DBベータ期間は無料1GBJSON ストア型
MongoDB無料250 MBJSON ストア型
Redis無料N/A (?)KEY-VALUE ストア型
Mobile Dataベータ期間は無料(?)ローカルネイティブのように使えるモバイル向けデータストア


それなりに実用的な量のデータを格納しようとすると、IBM 提供のデータストアサービスを選ぶことになるんでしょうかね。リレーションなしと割りきって JSON 型の MongoDB を選ぶという選択もあるけど。

この表だとわかりにくい差もあると思っています。例えば MySQL や MongoDB などは管理コンソールを別途 phpMyAdmin や phpMoAdmin などで用意する必要があります(参照)。一方、BLUAcceleration や SQLDB には標準で管理コンソール機能が付属していたりします。
2014042201


ちなみに BlueMix のサービスで MySQL を立ちあげた場合、BlueMix 内のアプリケーションサーバーからしか接続できません。ただし BlueMix の外で稼働している MySQL に BlueMix のアプリケーションサーバーから接続することは(パフォーマンスに目をつぶれば)可能でした。なので意地悪く特定のポートが閉じられている、とかいうことはなさそうです。どうしても MySQL で10MB以上のデータを使いたい、ということであればデータベースだけは外部接続にする、という選択肢もありますね(遅いし、PaaS のメリットも薄くなるのでオススメしませんけど)。



このページのトップヘ