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

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

タグ:postgresql

Node.js + PostgreSQL 環境で複数レコードのバルクインサートを、それも対象テーブルに UNIQUE 制約が存在している場合のバルクインサート方法について調べる機会があったので、ブログにまとめておきます。


【Node.js で PostgreSQL を使う】
まず、Node.js で PostgreSQL クライアント機能を実装する場合、よく使われているのが pg(node-postgres) という npm のライブラリだと思っています:
2022080601


こんな感じでコネクションプーリングを作って、、
  :
  :
var PG = require( 'pg' );
var pg = new PG.pool( 'postgres://localhost:5432/mydb' );
  :
  :


こんな感じで SQL (以下の例は "INSERT" 処理)を実行し、結果を取得します:
  :
  :
if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = "insert into table1( name, num ) values ( $1, $2 )";  //. SQL
      var query = { text: sql, values: [ "木村", 100 ] );             //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


今回もこの pg を使って紹介します。


【Node.js + PostgreSQL でバルクインサートを実装する】
上の例では一回の SQL 実行で1つのレコードをインサートする、という処理の例を紹介しました。複数のレコードをインサートする場合もこれを複数回続けて実行してもよいのですが、レコード数が多い場合に効率よくインサートできると便利です。このように「複数のレコードを1回の処理でまとめて作成する」ことを「バルクインサート」と呼びます。

Node.js + PostgreSQL でバルクインサートを実装する方法もいくつかありますが、今回は node-pg-format という npm ライブラリを使った例を紹介します:
2022080602


実際に使う場合はバルクインサートデータを配列で用意して、以下のように実装します:
  :
  :
var format = require( 'pg-format' );

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = format( "insert into table1( name, num ) values %L", records );  //. SQL
      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

上例のように、SQL 文全体を pg-format でインスタンス化しています。その際にインサートする複数レコードを配列データとして付与しています(conn.query() 実行時のパラメータは空です)。これで records 配列変数で指定された複数レコードを1回の SQL 実行でまとめてインサートすることができるようになります。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
ここからがこのブログエントリの本題に入っていきます。バルクインサートの方法は上で紹介しました。問題はこれではうまくいかないケースがあるのでした。具体的には対象テーブルに UNIQUE 制約が付与されていて、この制約にかかるレコードがバルクインサートの一部に含まれる場合です。

具体的に紹介します。例えば今回は table1 という名称のテーブルに複数レコードをバルクインサートする例を紹介していますが、この table1 テーブル作成時に以下のような指定がされていた可能性は排除できません:
create table table1( name varchar(50) default '', num int default 0 );
alter table table1 add unique( name, num );

1行目の "create table ..." はいいとして、問題は2行目です。この制約を与えたことによって name と num の組み合わせは一意(UNIQUE) でないといけない、という条件が付与されました。name は被ってもいいし、num も被ってかまわないのですが、name と num の組み合わせはユニークでないといけない(この条件に反するデータはインサートできない)ようになりました。

このような UNIQUE 制約が存在している前提で、改めて上例を見てみます。この例でバルクインサートしようとしているレコードは、
var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

という内容でした(配列の1つ目が name 、2つ目が num としてバルクインサートされます)。ここだけを見るとこの例に限っては(name と num の組み合わせは全て異なるので)問題なくバルクインサートできそうです。 ・・・が、もしこのバルクインサート実行前に既に { name: "木村", num: 100 }
のデータが記録済みであった場合
はどうでしょう?バルクインサートの最初のレコードは UNIQUE 制約にかかってしまいます。つまり実行エラーとなります。

そして更にこの問題をややこしくしている事情があります。それは「バルクインサートが UNIQUE 制約エラーで失敗するのはいいが、その時 UNIQUE 制約にかからなかったレコードはインサートされるべきか?否か? という問題です。この答はおそらくデータやアプリケーション次第、つまりケース・バイ・ケースです。まとめてインサートされないと意味がないレコードであった場合はまとめてエラー(UNIQUE 制約にかからなかったレコードもインサートされない)という挙動が正しい挙動となり、UNIQUE 制約にかかったレコードはインサートされないが、他のレコードはインサートされるべきデータであった場合はバルクインサートの一部だけが失敗し、残りのレコードは挿入されているべき、ということになります。 

ちなみに上例で紹介したバルクインサート実装の場合は前者の挙動となります。つまり「全てのレコードが制約違反にならなかった場合のみバルクインサートが成功し、1つでも制約違反のレコードがあった場合はそうでないレコードも含めて1つもインサートされずにエラーとなる」という挙動です(そもそも厳密には「バルクインサート」とはそのような挙動および処理を意味する言葉です)。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
長い前置きでした。ではここまでの背景を理解した上で UNIQUE 制約に違反しているレコードのみ挿入されず、違反していないレコードは正しく挿入されるようなバルクインサート(「バルクインサート」の定義としてはおかしいですけど・・)を実装するにはどのようにすればよいでしょうか? これが本ブログエントリの肝となる部分です。

これは実際には使っている RDB やライブラリ、プログラミング言語によって異なるようですが、PostgreSQL 9.5 以上で、Node.js + node-postgres を使っている場合であれば以下の方法で実現できるようです:
  :
  :

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      //. UNIQUE 制約に違反したデータを無視するような SQL を作る
      var sql = "insert into table1( name, num ) ";
      var selects = [];
      for( var i = 0; i < records.length; i ++ ){
        selects.push( "select '" + records[i][0] + "', " + records[i][1] );
      }
      sql += selects.join( " union all" ) + " on conflict ( name, num ) do nothing";

      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


この例ではプログラミングによって、以下のような SQL 文を生成して実行しています:
insert into table1( name, num )
  select '木村', 100 union all
  select '鈴木', 101 union all
  select '木村', 102
  on conflict ( name, num ) do nothing

厳密にはバルクインサートではないのですが、1回の SQL 実行で複数のレコードをインサートし、"conflict"(UNIQUE 制約違反)が発生した場合は "do nothing"(無視)するよう指定しています。その結果、UNIQUE 制約違反のないレコードのみがまとめてインサートされる、という処理が実現できています。

バルクインサートで1度に挿入するレコード数が莫大になるようなケース(SQL 文自体が膨大で複雑になるケース)には向かないかもしれませんが、自分の手元では一応期待どおりに動くことを確認しました。ちょうどこういう処理を行いたいアプリを作っていて調べたのですが、他の人の参考になれば。


【参照】
https://stackoverflow.com/questions/53722405/how-to-insert-bulk-rows-and-ignore-duplicates-in-postgresql-9-3










初めて Heroku Connect を使ったアプリケーションを作ってみました。普段使わない環境や手順もあったことに加え、日本語資料をあまり多く見つけられなかったので、自分の備忘録も兼ねて一連の手順をまとめてみました:
2022022700



【Heroku Connect とは】
Heroku Connect は Heroku のアプリケーションリソースの1つで、SalesForce のデータと Heroku Postgres( PostgreSQL データベース)との双方向同期機能です。これによって SalesForce 上のデータを Postgres データベースのデータとして読み書きできるようになる(Heroku アプリケーションからは PostgreSQL DB に接続することで SalesForce のデータを読み書きできるようになる)というものです。ある意味で閉じられた SalesForce データを、オープンな Heroku アプリケーション環境の一部として取り扱うことができるようになります。

なお Heroku Connect にも Heroku Postgres にも無料枠があり、一定条件内であれば無料で動作確認程度はできるものです。

Heroku Connect について、詳しくはこちらも参照ください:
https://devcenter.heroku.com/articles/heroku-connect


【Heroku Connect の設定】
実際に Heroku アプリケーション上で Heroku Connect を有効に設定し、SQL でデータを取り出す、という手順を行うまでの設定手順を紹介します。

前提条件として、Heroku のアカウントはもちろんですが、SalesForce.com でオブジェクト開発のできるアカウントが必要です。無料の Developer Edition も用意されているので、アカウントを持っていない場合はまずはアカウントを作成しておいてください。Developer Edition はこちらから:
https://developer.salesforce.com/ja


順序としてはもう少し後でもいいと思うのですが、SalesForce.com 側の話になっているこのタイミングで Heroku Connect で読み書きするデータオブジェクトを決めておきます。私自身が SalesForce.com にあまり詳しくないので標準オブジェクトから1つ選択しますが、ここでの対象はカスタムオブジェクトでも構わないはずです。

SalesForce.com (Developer Edition)にログインし、「オブジェクトマネージャ」と書かれた箇所をクリックします:
2022022701


標準で用意されている(と、カスタムオブジェクトを追加した場合はカスタムオブジェクトも含めた)オブジェクトの一覧が表示されます。この中から Heroku Connect で取り出す対象を1つ決めます。私はよく分かっていないこともあって、標準オブジェクトでサンプルデータもはじめからいくつか格納済みの「取引先」オブジェクトを対象にする前提で以下を紹介します。別のオブジェクトを使うこともできると思いますが、適宜読み替えてください:
2022022702


オブジェクトの表示を絞り込む場合は「クイック検索」フィールドに名前を一部入力すると、オブジェクトのラベルでフィルタリングされます。下の例では「取引先」でフィルタリングした結果です。「取引先」オブジェクトは API 参照名が "Account" となっていることがわかります。この名称は後で使うことになるのでメモしておきましょう:
2022022703


SalesForce.com 側の準備はこれだけです。Heroku Connect で同期するオブジェクトデータが決まっていれば Heroku 側の準備にとりかかります。

改めて Heroku にログインし、アプリケーションを1つ作成します。以下の例では "forceobject" というアプリケーションに対して Heroku Connect を設定する想定で紹介しているので、アプリケーション名は自分のアプリケーション名に読み替えてください。またこの時点では Heroku Connect を含めたアドオンは1つも設定していないものとします:
2022022701


このアプリケーションに Heroku Connect をアドオンします。上記画面の "Configure Add-ons" と書かれた箇所をクリックします:
2022022702


アプリケーションのリソース画面に移動します。この "Add-ons" と書かれたフィールドに "Heroku Connect" と入力すると "Heroku Connect" が見つかります。見つかった名前の部分をクリックします:
2022022703


アプリケーションに Heroku Connect を追加する確認ダイアログが表示されます。使用条件と、プランが "Demo Edition - Free"(無料)となっていることを確認して "Submit Order Form" ボタンをクリックします※1:
2022022704


※1 Heroku Connect Demo Edition には以下の制約があるようです:
 ・最大 10,000 行のデータまで同期
 ・同期間隔の最小値は 10 分
 ・ログは7日間保持


アプリケーションに Heroku Connect がアドオンされました:
2022022701


続けて同期を行うデータベースである Heroku Postgres をアドオンします。先程と同様に "Add-ons" フィールドに "Heroku Postgres" と入力して、候補として見つかる "Heroku Postgres" をクリックします:
2022022701


アプリケーションに Heroku Postgres を追加する確認ダイアログが表示されます。使用条件と、プランが "Hobby Dev - Free"(無料)となっていることを確認して "Submit Order Form" ボタンをクリックします:
2022022702


これで Heroku Postgres もアドオンとして追加できました。次にこの2つ(Heroku Connect と Heroku Postgres)を接続するための設定が必要ですが、これにはスキーマと呼ばれる DB の情報が必要になります。スキーマを確認するため、画面上部の "Settings" と書かれたタブをクリックします:
2022022703


"Config Vars" 節の "Reveal Config Vars" ボタンをクリックして環境変数を確認します:
2022022702


すると環境変数 DATABASE_URL が設定されていることがわかります。その値を確認するため、 "DATABASE_URL" と書かれた行の右にある鉛筆ボタンをクリックします:
2022022703


以下のようなダイアログが表示され、"Value" と書かれたフィールドに環境変数 DATABASE_URL に設定された値を確認できます:
2022022704


この値は以下のような形式になっているはずです。この最後の "/" 文字から右にある部分がスキーマです。このスキーマ値を確認した上で Heroku Connect の設定に移ります:
postgres://*****:*******@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com:5432/(スキーマ)


改めて Heroku Connect の設定を続けるため、"Heroku Connect" と書かれた箇所をクリックします:
2022022701



Heroku Connect の設定画面が表示されます。"Setup Connection" ボタンをクリックします:
2022022702


以下のような画面に切り替わります。"Enter schema name" と書かれたフィールド(デフォルトでは "salesforce" と入力されたフィールド)に先程確認したスキーマ名を入力します。正しく入力できたら画面右上の "Next" ボタンをクリック:
2022022701


ここで SalesForce.com のアカウント認証が必要になります。画面右上の "Authorize" ボタンをクリック:
2022022702


SalesForce.com のログイン画面が表示されるので、ID とパスワードを入力してログインします:
2022022703


正しく認証が行われると Heroku Connection の接続が行われ、以下のような画面になります:
2022022704


Heroku Connect 側の準備としての最後にマッピングを行います。画面上部の "Mapping" タブを選択し、右下の "Create Mapping" ボタンをクリックします:
2022022701


SalesForce 側のオブジェクトの一覧が表示されます。今回は「取引先」を対象に同期したいので、"Account" オブジェクトを選択します:
2022022702


Account オブジェクトのマッピング条件を指定する画面が表示されます。デフォルトでは10分おきに SalesForce からデータベースへの同期のみが有効になっていますが、その条件を変えたい場合はここで指定します。また画面下部には Account オブジェクトの中のどの属性値を同期の対象とするかを指定する表があります:
2022022703


デフォルトでもいくつか指定されていて、そのままでもいいと思います。とりあえず取引先名称となる Name がチェックされていることを確認しておきましょう:
2022022704


画面上部に戻り、最後に "Save" ボタンをクリックして、この条件を保存します:
2022022703


指定された条件が保存され、最初の同期が行われます。少し待つと Status が "OK" となります。これで SalesForce の取引先情報が Heroku Postgres のデータベースに格納されました。Heroku Connect の設定が完了です:
2022022701



【アプリケーションから同期されたデータを参照する】
ここまでの設定ができていれば、アプリケーションから(PostgreSQL を参照して)SalesForce のデータを取り出すことができます。といっても、ここまでの設定が済んでいれば該当の PostgreSQL サーバーに接続して、以下の SQL を実行するだけ(この SQL を実行するプログラムを書くだけ)です:
select * from (スキーマ名).Account

例えば Node.js の Express フレームワークを使ったウェブアプリとして実装するとこんな感じになります:
//. app.js
var express = require( 'express' ),
    app = express();

var PG = require( 'pg' );
PG.defaults.ssl = true;
var database_url = 'DATABASE_URL' in process.env ? process.env.DATABASE_URL : ''; 
var schema = '';
if( database_url.indexOf( '/' ) > -1 ){
  var tmp = database_url.split( '/' );
  schema = tmp[tmp.length-1];
}

var pg = null;
if( database_url ){
  console.log( 'database_url = ' + database_url );
  pg = new PG.Pool({
    connectionString: database_url,
    ssl: { require: true, rejectUnauthorized: false },
    idleTimeoutMillis: ( 3 * 86400 * 1000 )
  });
}


app.get( '/', async function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );

  try{
    var conn = await pg.connect();
    var sql = 'select * from ' + schema + '.Account';
    var query = { text: sql, values: [] };
    conn.query( query, function( err, result ){
      if( err ){
        console.log( err );
        res.status( 400 );
        res.write( JSON.stringify( err, null, 2 ) );
        res.end();
      }else{
        res.write( JSON.stringify( result, null, 2 ) );
        res.end();
      }
    });
  }catch( e ){
    console.log( e );
    res.status( 400 );
    res.write( JSON.stringify( e, null, 2 ) );
    res.end();
  }finally{
    if( conn ){
      conn.release();
    }
  }

});

var port = process.env.PORT || 8080;
app.listen( port );
console.log( "server starting on " + port + " ..." );

(サンプルはこちら)
https://github.com/dotnsf/forceobject


上述の環境変数 DATABASE_URL を指定して、以下のように実行します:
$ DATABASE_URL=postgres://*****:*******@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com:5432/ssssss node app

するとピンク色の部分で DATABASE_URL からスキーマを取り出し、青字の部分で SQL 文を生成して実行する、というものです。実行後にウェブブラウザで http://localhost:8080/ にアクセスすると以下のような SQL 実行結果を得ることができます:
2022022700


実行結果の JSON オブジェクト内の rows キーの配列値として SQL の実行結果が格納されています。各要素に含まれる属性はマッピング時に指定したものになっていて、特に name 属性には取り出したオブジェクト名称(つまり取引先の名称)が格納されていることが確認できます。


これを有効活用するには、まず自分自身がもう少し SalesForce 自体に詳しくなる必要がありそうですが、今は SalesForce 傘下にある Heroku らしいビジネス色の濃い機能が、Heroku の機能にうまく統合されて提供されているようでした。設定段階が比較的面倒な気もしていますが、一度設定できてしまえば後は普通に PostgreSQL を操作する感覚で使えるし、双方向同期を有効にすればウェブアプリ側から SalesForce のデータを活用して作成・更新・削除といった変更処理もできるようになると思います。


 

コンテナ環境が広まっていくと、アプリケーションやサービスが安定運用できるようになる、という話を耳にします。それはそれで事実だと思うのですが、本当にサービスを安定運用するにはアプリケーションが使うバックエンドも安定運用が必要で、そのあたりの話が飛ばされていることが多いと感じることもあります。 今回のブログエントリはそういう話の例として、Node.js からリレーショナルデータベース(今回は PostgreSQL)を使うアプリケーションでどういった点を考慮すべきか、という例を紹介します。


Node.js から PostgreSQL に接続する、というコード自体は node-postgres という npm パッケージを使うことで簡単に実現できます。具体的な方法もググって容易に見つけることができます。

一方、特にクラウドやコンテナ環境においてはマイクロサービス化を踏まえた設計になっていることも珍しくないと思っています。そのようなケースでは「PostgreSQL がメンテナンス状態になる(接続が切れる)」ことを想定する必要があります。アプリケーションとしては「データベース接続が切断される可能性があり、切断されたら再接続する(それも失敗したら再接続を続ける)」という挙動になるような、ちと面倒な実装が求められます:
20211027



そのような具体的に動くサンプルコードを探していたのですが、ピンポイントで見つけることができず、色々試しながら自分で作ってみました。docker で PostgreSQL イメージを起動し、コンテナを止めたり再スタートすることでアプリも再接続することを確認することができるものです:
https://github.com/dotnsf/postgresql_reconnect


なお、以下の内容は PostgreSQL で(一般的なアプリではこれが普通だと思っていますが)コネクションプーリングを使う前提での接続や SQL 実行を想定したコードを紹介します。


【動作確認】
以下 Node.js が導入された PC と、ローカルの docker を使ってアプリケーションの動作確認する場合の手順を紹介します。

まずはソースコード一式を入手してください。上述の Github リポジトリから git clone するか、ダウンロード&展開して postgresql_reconnect/ プロジェクトを手元に用意します。

まずは docker で PostgreSQL を動かします。起動時に DB を作成しますが、特にテーブルやデータを作ることもなく、単に起動するだけ、です:
$ docker run -d --name postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=P@ssw0rd -e POSTGRES_DB=mydb -p 5432:5432 postgres

↑このコマンドはローカルホストに docker エンジンが導入されている前提で、
 ・ユーザー名: admin
 ・パスワード: P@ssw0rd
 ・データベース名: mydb
 ・公開ポート番号: 5432
でオフィシャル PostgreSQL イメージをコンテナとして起動するように指示するコマンドです。各指定オプションを変更して動かすことも可能ですが、後述するサンプルソースコードはこの内容で PostgreSQL インスタンスが生成される前提で記述されているため、ここから変更する場合はこの後に紹介するサンプルコードの内容も変更内容に合わせて適宜編集してから実行してください:
2021102606



まずは切断時の再接続を考慮しないコード: oldapp.js を実行してみます。ちなみに oldapp.js の内容は以下のようになっています(青字部分が PostgreSQL の接続情報赤字部分が接続部分、そしてピンクが SQL 実行部分です):
//. oldapp.js
var express = require( 'express' ),
    app = express();

var PG = require( 'pg' );

//. PostgreSQL
var pg_hostname = 'localhost';
var pg_port = 5432;
var pg_database = 'mydb';
var pg_username = 'admin';
var pg_password = 'P@ssw0rd';

var pg_clinet = null;
var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.connect( function( err, client ){
  if( err ){
    //. 初回起動時に DB が動いていない
    console.log( 'no db on startup', err.code );
  }else{
    console.log( 'connected.' );
    pg_client = client;
  }
});

//. top
app.get( '/', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  res.write( JSON.stringify( { status: true }, null, 2 ) );
  res.end();
});

//. ping
app.get( '/ping', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var sql = 'select 1';
  var query = { text: sql, values: [] };
  pg_client.query( query, function( err, result ){
    if( err ){
      console.log( { err } );
      res.status( 400 );
      res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
      res.end();
    }else{
      //console.log( { result } );
      res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
      res.end();
    }
  });
});


var port = process.env.PORT || 8080;
app.listen( port );
console.log( "server starting on " + port + " ..." );

実際に Node.js で動かす場合、まず初回のみ依存ライブラリをインストールするため(Node.js 導入済みの環境で)以下のコマンドを実行します:
$ npm install

そして以下のコマンドで oldapp.js を起動します:
$ node oldapp

起動すると "server starting on 8080 ..." と表示され、サーバーが 8080 番ポートでリクエスト待ち状態になります。また、この oldapp.js の場合は起動直後に PostgreSQL に接続を試みるので "connected." と表示されます:
$ node oldapp
server starting on 8080 ...
connected.

上述の oldapp.js の内容からもわかるのですが、このアプリケーションは "GET /" と "GET /ping" という2つの REST API を処理します。前者は単に { status: true } という JSON を返すだけのものです。また後者は接続した PostgreSQL に対して "SELECT 1" という SQL を実行し、その実行結果を返すものです(PostgreSQL に接続できていればなんらかの結果が返るものです)。

試しに前者を実行してみます。ウェブブラウザで "http://localhost:8080/" にアクセスし、{ status: true } が表示されることを確認します:
2021102601


また後者も実行してみます。同様にウェブブラウザで "http://localhost:8080/ping" にアクセスし、{ status: true, result: { .... } } という文字列が表示されることを確認します:
2021102602



ここまでは普通に成功するはずです。
ここからが本番です。この状態でアプリケーションを動かしたまま PostgreSQL サーバーを止めてみます。docker コマンドで止める場合は
$ docker stop postgres

を実行します(docker デスクトップを使っている場合は稼働中のコンテナの STOP ボタンを押すと止まります):
2021102603



この状態で改めてウェブブラウザで各ページにアクセスするとどうなるか? 期待している挙動という意味では以下のようになると思っています:
・GET / へのリクエストについては(DB を使わないので){ status: true } を返す
・GET /ping へのリクエストについては(DB が止まっているので)「DB エラー」を返す
・(更に)DB が再稼働したら自動的に再接続して、GET /ping に対して SQL 実行結果を返す


ところが、実はこの時点でサーバーはクラッシュしています。$ node oldapp を実行したターミナルには Exception が表示された上にアプリケーションは終了し、プロンプトが表示されてしまっています:
2021102604


つまりサーバーがクラッシュしています。したがって GET /ping どころか、GET / へのリクエストもエラーとなってしまうし、稼働していないので自動再接続もできません:
2021102605


これでは困ってしまいます。簡単なデモ程度が目的の実装であれば oldapp.js の内容でも(とりあえず動くので)いいと思いますが、ある程度安定した連続稼働が求められる実運用を想定するとちょっと心細い状況と言えます。



では oldapp.js と同じ内容で、DB を止めてもアプリケーションが死ぬこともなく、DB が復活したら自動再接続して再び SQL が実行できるようになるような実装はどのようにすればよいでしょうか? その例が newapp.js です:
//. newapp.js
var express = require( 'express' ),
    app = express();

var PG = require( 'pg' );

//. PostgreSQL
var pg_hostname = 'localhost';
var pg_port = 5432;
var pg_database = 'mydb';
var pg_username = 'admin';
var pg_password = 'P@ssw0rd';

var retry_ms = 5000;  //. retry every 5 sec

var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
console.log( 'connecting...' );
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.on( 'error', function( err ){
  console.log( 'db error on starting', err );
  if( err.code && err.code.startsWith( '5' ) ){
    //. terminated by admin?
    try_reconnect( retry_ms );
  }
});

function try_reconnect( ts ){
  setTimeout( function(){
    console.log( 'reconnecting...' );
    pg = new PG.Pool({
      connectionString: connectionString
    });
    pg.on( 'error', function( err ){
      console.log( 'db error on working', err );
      if( err.code && err.code.startsWith( '5' ) ){
        //. terminated by admin?
        try_reconnect( ts );
      }
    });
  }, ts );
}

//. top
app.get( '/', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  res.write( JSON.stringify( { status: true }, null, 2 ) );
  res.end();
});

//. ping
app.get( '/ping', async function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var conn = null;
  try{
    conn = await pg.connect();
    var sql = 'select 1';
    var query = { text: sql, values: [] };
    conn.query( query, function( err, result ){
      if( err ){
        console.log( { err } );
        res.status( 400 );
        res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
        res.end();
      }else{
        //console.log( { result } );
        res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
        res.end();
      }
    });
  }catch( e ){
    res.status( 400 );
    res.write( JSON.stringify( { status: false, error: e }, null, 2 ) );
    res.end();
  }finally{
    if( conn ){
      conn.release();
    }
  }
});


var port = process.env.PORT || 8080;
app.listen( port );
console.log( "server starting on " + port + " ..." );

青字部分が PostgreSQL の接続情報赤字部分が接続部分、そしてピンクが SQL 実行部分です(青字部分は全く同じです)。違いを紹介する前にまずは挙動を確認してみましょう。PostgreSQL を再び稼働状態に戻します:
2021102606


この状態で "$ node newapp" を実行して newapp.js を起動します:
$ node newapp
server starting on 8080 ...
connecting...

同じようなメッセージが表示されて、リクエスト待ち状態になります。まずは先ほど同様に GET / や GET /ping を実行します(実行結果自体は先ほどと同じです):
2021102601

2021102602


ではここでも同様に PostgreSQL を強制停止してみましょう。先ほどはアプリケーションがクラッシュしてリクエスト待ち状態ではなくなってしまいましたが、今回はプロンプトには戻らず、引き続き待ち受け状態が続くはずです:
2021102601


この状態で改めて GET / や GET /ping にアクセスしてみます。GET / は変わらず { status: true } を返し、GET /ping は(DB にアクセスできないので) { status: false, error: ... } という内容になりますが、ちゃんとレスポンスを返すことができています:
2021102601

2021102602


そして止まっていた PostgreSQL を再度スタートします:
2021102606



するとアプリケーションが自動的に再接続を行い、少ししてから GET /ping を実行すると、再び SQL 実行が成功した時の画面が表示されます:
2021102602


これでデータベースにメンテナンスが入っても自動再接続して稼働する、という実用的な挙動が実現できました。


【コード説明】
改めて2つのソースコードを比較します。といっても青字部分は共通なので比較の対象からははずし、赤字の接続部分と、ピンク字の SQL 実行部分を比較します。

まずは前者の自動再接続しない方。接続部分と SQL 実行部分は以下のようでした:
var pg_clinet = null;
var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.connect( function( err, client ){
  if( err ){
    //. 初回起動時に DB が動いていない
    console.log( 'no db on startup', err.code );
  }else{
    console.log( 'connected.' );
    pg_client = client;
  }
});

  :
  :

//. ping
app.get( '/ping', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var sql = 'select 1';
  var query = { text: sql, values: [] };
  pg_client.query( query, function( err, result ){
    if( err ){
      console.log( { err } );
      res.status( 400 );
      res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
      res.end();
    }else{
      //console.log( { result } );
      res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
      res.end();
    }
  });
});

接続処理では単純に接続文字列を生成してコネクションプーリングを生成し、直後に connect() を実行してクライアントを1つ取り出しています。そしてこのクライアントをこの後の SQL 実行時に使いまわしています。

また SQL 実行処理でも SQL 文字列を定義して↑で取り出したクライアントを使って実行しています。処理そのものはわかりやすいのですが、一方でこれといった例外発生を考慮した内容でもありません(そのため DB が止まってしまうケースが想定できておらず、アプリケーションのクラッシュを引き起こしてしまう内容でした)。


一方の後者、例外発生を考慮して、切断後に自動再接続できるようにした方の接続部分と SQL 実行部分は以下のようでした:
var retry_ms = 5000;  //. retry every 5 sec

var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
console.log( 'connecting...' );
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.on( 'error', function( err ){
  console.log( 'db error on starting', err );
  if( err.code && err.code.startsWith( '5' ) ){
    //. terminated by admin?
    try_reconnect( retry_ms );
  }
});

function try_reconnect( ts ){
  setTimeout( function(){
    console.log( 'reconnecting...' );
    pg = new PG.Pool({
      connectionString: connectionString
    });
    pg.on( 'error', function( err ){
      console.log( 'db error on working', err );
      if( err.code && err.code.startsWith( '5' ) ){
        //. terminated by admin?
        try_reconnect( ts );
      }
    });
  }, ts );
}

  :
  :

//. ping
app.get( '/ping', async function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var conn = null;
  try{
    conn = await pg.connect();
    var sql = 'select 1';
    var query = { text: sql, values: [] };
    conn.query( query, function( err, result ){
      if( err ){
        console.log( { err } );
        res.status( 400 );
        res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
        res.end();
      }else{
        //console.log( { result } );
        res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
        res.end();
      }
    });
  }catch( e ){
    res.status( 400 );
    res.write( JSON.stringify( { status: false, error: e }, null, 2 ) );
    res.end();
  }finally{
    if( conn ){
      conn.release();
    }
  }
});

まず接続処理ではコネクションプーリングを生成するまでは同じですが、ここではそのまま終了します。connect() を実行してクライアントを取り出す、のは実際に SQL を実行する直前の処理に変更しています。またこのコネクションプーリングを管理する変数 pg を使ってエラーハンドリングを行い、DB 切断時に正しくハンドリングできるようにしています(具体的には数秒待ってから再びコネクションプーリングを生成し、新たに生成したコネクションプーリングに対してもエラーハンドリングを行う、という内容です)。

また SQL 実行時には以下のような処理を加えています:
(1)処理全体を try{ .. }catch{ .. }finally{ .. } で括り、どこで切断しても例外処理できるようにする
(2)try{ .. } 内の実際に SQL を実行する直前に pg.connect でクライアントを取り出す
(3)finally{ .. } 内でクライアントをリリースしてコネクションプーリングに戻す

この3つの処理を加えておくことで DB が突然死んでも正しくハンドリングして再接続(再びコネクションプーリングを作る)を試みるようにしています。また結果的に再接続に時間がかかってしまう場合であってもアプリケーションそのものはクラッシュせずに生き続けるので、(DB にアクセスはできないけど)利用者からのリクエストに返答できるようにしています。



・・・というわけで、公開&提供しているのはあくまでサンプルですが、この考え方で PostgreSQL サーバーの切断時でも自動接続して連続稼働ができるようになると思っています。また PostgreSQL 以外のコネクションを使う RDB を利用する際にも応用できる内容です。 利用するインフラプラットフォームによってはこういったメンテナンス時の再接続方法について特殊な機能が用意されていることもあるので、必ずしもこういう方法をとらないといけない、というわけではないのですが、一つのベストプラクティス的な内容だと思っています。

自分が痛い目にあったことに関わる内容でもあるので、困っている方のお役に立てば何より。


IBM Cloud から提供されている 30 日間無料 Kubernetes サービスIBM Kubernetes Service 、以下 "IKS")環境を使って利用することのできるコンテナイメージを1日に1個ずつ 30 日間連続で紹介していきます。

環境のセットアップや制約事項については Day0 のこちらの記事を参照してください。

Day 6 からはデータベース系コンテナとその GUI ツールを中心に紹介してます。Day 8 では Day 6 で紹介した MySQL と並んで多くの利用者を持つデータベース・サーバー PostgreSQL イメージをデプロイする例を紹介します。
20180804134032



【イメージの概要】
今更改めて説明する必要もないくらい有名で、かつ広く使われているオープンソースのリレーショナル・データベース・サーバーだと思っています。個人的には MySQL と PostgreSQL が2大オープンソース RDB という印象を持っています。



【イメージのデプロイ】
まずはこちらのファイルを自分の PC にダウンロードしてください:
https://raw.githubusercontent.com/dotnsf/yamls_for_iks/main/postgresql.yaml


次にこのファイルをテキストエディタで開いてパラメータを編集します。具体的には "POSTGRES_" で始まる3箇所の env.name の value 値を変更してください。それぞれの具体的な意味は以下の通りです(初期値として指定されている値のまま動かすことも可能ですが、安全のためなるべく変更してください):
・POSTGRES_PASSWORD : 管理者パスワード(初期値 P@ssw0rd)
・POSTGRES_DB : デプロイと同時に作成するデータベースの名前(初期値 mydb)
・POSTGRES_USER : デプロイと同時に作成するデータベースを利用するユーザー名(初期値 admin)

ではこのダウンロード&編集した postgresql.yaml ファイルを指定してデプロイします。以下のコマンドを実行する前に Day 0 の内容を参照して ibmcloud CLI ツールで IBM Cloud にログインし、クラスタに接続するまでを済ませておいてください。

そして以下のコマンドを実行します:
$ kubectl apply -f postgresql.yaml

以下のコマンドで PostgreSQL 関連の Deployment, Service, Pod, Replicaset が1つずつ生成されたことと、サービスが 30432 番ポートで公開されていることを確認します:
$ kubectl get all

NAME                            READY   STATUS    RESTARTS   AGE
pod/postgres-558dc49c46-wkdlp   1/1     Running   0          20s

NAME                     TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
service/kubernetes       ClusterIP   172.21.0.1      <none>        443/TCP          26d
service/postgresserver   NodePort    172.21.82.102   <none>        5432:30432/TCP   21s

NAME                       READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/postgres   1/1     1            1           21s

NAME                                  DESIRED   CURRENT   READY   AGE
replicaset.apps/postgres-558dc49c46   1         1         1       21s

この後に実際にサービスを利用するため、以下のコマンドでワーカーノードのパブリック IP アドレスを確認します(以下の例であれば 161.51.204.190):
$ ibmcloud ks worker ls --cluster=mycluster-free
OK
ID                                                       パブリック IP    プライベート IP   フレーバー   状態     状況    ゾーン   バージョン
kube-c3biujbf074rs3rl76t0-myclusterfr-default-000000df   169.51.204.190   10.144.185.144    free         normal   Ready   mil01    1.20.7_1543*

つまりこの時点で(上述の結果であれば)アプリケーションは 169.51.204.190:30432 で稼働している、ということになります。早速確認してみます、と言いたいところなのですが、Day 6 の MySQL 同様、これまでのようにウェブブラウザで確認できるものではなく、専用の CLI コマンドが使えると確認できるのですが・・・ ここでは動作確認を Day 9 で行うことにして、今回はこのままにしておきます。




【YAML ファイルの解説】
YAML ファイルはこちらを使っています(編集する前の状態です):
apiVersion: v1
kind: Service
metadata:
  name: postgresserver
spec:
  selector:
    app: postgres
  ports:
  - port: 5432
    protocol: TCP
    targetPort: 5432
    nodePort: 30432
  type: NodePort
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres
        env:
        - name: POSTGRES_PASSWORD
          value: "P@ssw0rd"
        - name: POSTGRES_DB
          value: "mydb"
        - name: POSTGRES_USER
          value: "admin"
        ports:
        - containerPort: 5432

Deployment 1つと、Service 1つのごくごくシンプルな YAML ファイルですが、一応解説を加えておきます。アプリケーションそのものは 5432 番ポートで動作するように作られているため、NodePort 30432 番を指定して、外部からは 30432 番ポートでアクセスできるようにしています(NodePort として指定可能な番号の範囲は 30000 ~ 32767 です、指定しない場合は空いている番号がランダムに割り振られます)。また ReplicaSet は1つだけで作りました(データベースなので、別途クラスタ構成の準備をしない限りはこの数値だけを増やしてもあまり意味ないと思います)。


デプロイしたコンテナイメージを削除する場合はデプロイ時に使った YAML ファイルを再度使って、以下のコマンドを実行します。不要であれば削除しておきましょう(ちなみにこの PostgreSQL コンテナは明日の Day 9 でも使う予定なので、削除するのはそのあとの方がいいかもしれません):
$ kubectl delete -f postgresql.yaml


【紹介したイメージ】
https://hub.docker.com/_/postgres


【紹介記録】
Dayカテゴリーデプロイ内容
0準備準備作業
1ウェブサーバーhostname
2Apache HTTP
3Nginx
4Tomcat
5Websphere Liberty
6データベースMySQL
7phpMyAdmin
8PostgreSQL
9pgAdmin4
10MongoDB
11Mongo-Express
12Redis
13RedisCommander
14ElasticSearch
15Kibana
16CouchDB
17CouchBase
18HATOYA
19プログラミングNode-RED
20Scratch
21Eclipse Orion
22Swagger Editor
23R Studio
24Jenkins
25アプリケーションFX
262048
27DOS Box
28VNC Server(Lubuntu)
29Drupal
30WordPress

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

なお、以下の内容は 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 環境が構築できました。


このページのトップヘ