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

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

タグ:postgresql

コンテナ環境が広まっていくと、アプリケーションやサービスが安定運用できるようになる、という話を耳にします。それはそれで事実だと思うのですが、本当にサービスを安定運用するにはアプリケーションが使うバックエンドも安定運用が必要で、そのあたりの話が飛ばされていることが多いと感じることもあります。 今回のブログエントリはそういう話の例として、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 環境が構築できました。


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 が実行できるようになる、というものです。


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



このページのトップヘ