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

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

タグ:postgresql

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


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



これまで 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
$



このページのトップヘ