1. はじめに

EDB Postgresはオープンソース(OSS)データベースのPostgreSQLにOracle互換機能や運用管理機能などを追加したもので、OSSを好む企業などで使われています。IBM社はDb2に加えてEDB Postgresも提供して、要件に合わせてデータベースを選べるようにしています。

本記事の導入編でパソコン上のminikube（1ノード）にEDB Postgres for Kubernetesを導入しました。そのオペレーター(EDB Postgres for Kubernetes operator)はOSS版オペレーターCloudNativePGに機能追加したもので、IBM PowerやOpenshiftをサポートしています。またEDB Postgresのデータベース・サーバーであるEDB Postgres Advanced Server (EPAS)とPostgreSQLデータベース・サーバーの両者をサポートします。

本記事の目的はオペレーターの高可用性機能とPostgreSQL 15/EDB Postgres 15の新機能の中で次の3点を確認することです。

Primary pod削除後のフェイルオーバー
MERGEコマンド（PostgreSQL 15/EDB Postgres 15の両者で利用可）
透過的データベース暗号化(TDE)（EDB Postgres 15で利用可、PostgreSQL 15で利用不可）

これらの内容は他の筆者による記事／ブログと重複しますが、本記事では動作確認結果を容易に再現できるように導入編と合わせて手順を詳細化しています。

2. 前提

次の記事で説明した環境(Ubuntu/Docker/minikube)を用います。
パソコンにEDB Postgres for Kubernetesを導入して無料トライアル（導入編）
実行例で使うUbuntuのホスト名をkuma、ユーザー名をyamaとしましたが、各人の環境に合わせて読み替えてください。

3. Primary pod削除後のフェイルオーバー

導入編で作成したEPAS (EDB Postgres Advanced Server)クラスターcluster-example-tdeにおいてpodが3個稼働しています。1個がprimary、残り2個がreplica (hot standby)です（参考：PostgreSQL architecture）。

yama@kuma:~$ kubectl get pods -n edb-ns
NAME                                                       READY   STATUS    RESTARTS        AGE
cluster-example-tde-1                                      1/1     Running   0               141m
cluster-example-tde-2                                      1/1     Running   0               141m
cluster-example-tde-3                                      1/1     Running   0               141m

yama@kuma:~$ kubectl get cluster -n edb-ns
NAME                  AGE   INSTANCES   READY   STATUS                     PRIMARY
cluster-example-tde   19h   3           3       Cluster in healthy state   cluster-example-tde-1

Primary podのcluster-example-tde-1を削除します。

yama@kuma:~$ kubectl delete pod cluster-example-tde-1 -n edb-ns
pod "cluster-example-tde-1" deleted

次のように削除したpodが約10秒で再起動し、primaryはcluster-example-tde-2に交代しました。

yama@kuma:~$ kubectl get pods -n edb-ns
NAME                                                       READY   STATUS    RESTARTS       AGE
cluster-example-tde-1                                      0/1     Running   0              3s

yama@kuma:~$ kubectl get pods -n edb-ns
NAME                                                       READY   STATUS    RESTARTS       AGE
cluster-example-tde-1                                      1/1     Running   0              13s

yama@kuma:~$ kubectl get cluster -n edb-ns
NAME                  AGE   INSTANCES   READY   STATUS                     PRIMARY
cluster-example-tde   19h   3           3       Cluster in healthy state   cluster-example-tde-2

補足）EDB Postgres for Kubernetesオペレーターは次の順でpodを操作することにより、primaryを切り替えるときにreplicaでWAL(write ahead log)の受信が正常に終了し、かつ再起動したpodがWAL送信を再開しない、といった整合性を保ちます。（参考：Automated failover v1）
1. delete podコマンドによってcluster-example-tde-1から応答がなくなります。
2. オペレーターは無応答を検知してcluster-example-tde-1をシャットダウンします。
3. Replica（cluster-example-tde-2とcluster-example-tde-3）でWAL受信が止まります。
4. オペレーターはreplicaの2台からprimaryの候補を1台選び、primaryに昇格させます。
5. cluster-example-tde-1が再起動しreplicaとなります。

4. MERGEコマンド

PostgreSQL 14/EDB Postgres 14以前においてMERGE文を移行する場合、INSERT ON CONFLICT等を使って書き換える必要がありました。PostgreSQL 15/EDB Postgres 15にMERGEコマンドが導入されて、書き換えの手間が減りました。

コマンドライン・インターフェースpsqlを用いて例を作成します。更新対象と更新元のキーの値に応じてINSERTまたはUPDATEする処理をMERGEコマンドで記述します（参考：MERGE v15）。まず更新対象targetと更新元sourceの表を作成します。

yama@kuma:~$ kubectl cnp psql cluster-example-tde -n edb-ns
psql (15.4.1, server 15.4.1)
postgres=# create table target (tid integer, name nvarchar(10), balance integer, primary key(tid));
postgres=# insert into target values (1,'山',10),(2,'川',20);
postgres=# select * from target;
 tid | name | balance
-----+------+---------
   1 | 山  |      10
   2 | 川  |      20

postgres=# create table source (sid integer, name nvarchar(10), diff integer, primary key(sid));
postgres=# insert into source values (1,'山',100),(3,'海',300);
postgres=# select * from source;
 sid | name | diff
-----+------+------
   1 | 山  |  100
   3 | 海  |  300

次のMERGE文によってtid=1のレコードがUPDATEされ、tid=3のレコードがINSERTされます。

postgres=# MERGE INTO target t
postgres-# USING source s
postgres-# ON (t.tid = s.sid)
postgres-# WHEN MATCHED THEN
postgres-#     UPDATE SET balance = balance + s.diff
postgres-# WHEN NOT MATCHED THEN
postgres-#     INSERT VALUES (s.sid, s.name, s.diff);
MERGE 2

postgres=# select * from target order by tid;
 tid | name | balance
-----+------+---------
   1 | 山  |     110
   2 | 川  |      20
   3 | 海  |     300

MERGEコマンドを使わず、INSERT ON CONFLICTコマンドを使って実行する例は次のようになります。

postgres=# select * from target;
 tid | name | balance
-----+------+---------
   1 | 山  |      10
   2 | 川  |      20

postgres=# select * from source;
 sid | name | diff
-----+------+------
   1 | 山  |  100
   3 | 海  |  300

postgres=# INSERT INTO target (tid, name, balance)
postgres-#     SELECT sid, name, diff FROM source
postgres-#     ON CONFLICT (tid)
postgres-#     DO UPDATE
postgres-#     SET target.balance = target.balance + excluded.balance;
INSERT 0 2

postgres=# select * from target order by tid;
 tid | name | balance
-----+------+---------
   1 | 山  |     110
   2 | 川  |      20
   3 | 海  |     300

MERGEコマンドと同じ結果を得ました。INSERT ON CONFLICTで使ったexcludedはsourceとtargetの間でキーが重複してINSERTの対象外となったレコードを指していて（参考：INSERT）、上の例ではレコード(1,'山',100)が該当します。

5. 透過的データベース暗号化(TDE)無しの場合

5.1 PostgreSQLのクラスターをデプロイ

導入編においてEPAS (EDB Postgres Advanced Server)のクラスターをデプロイし、その際TDEを有効にしました（注：定義ファイルのtdeで設定）。TDE無しの状態で暗号化の状況を確認するために、PostgreSQLのクラスターを新たにデプロイします。次のマニュアルを参考にします。

Quickstart v1

まず、クラスターの定義ファイルをダウンロードします。

yama@kuma:~$ wget https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/samples/cluster-example.yaml
...
2023-10-31 20:36:24 (97.9 MB/s) - ‘cluster-example.yaml’ saved [141/141]

このファイルにmonitoringを追加してダッシュボードの表示対象となるようにします。

cluster-example.yaml

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3

  storage:
    size: 1Gi

  monitoring:
    enablePodMonitor: true

PostgreSQLクラスターcluster-exampleをデプロイします。なお下の実行例に表示されているcluster-example-tdeはEPASクラスターです。

yama@kuma:~$ kubectl apply -f cluster-example.yaml -n edb-ns
cluster.postgresql.k8s.enterprisedb.io/cluster-example created

yama@kuma:~$ kubectl get pods -n edb-ns
NAME                                                       READY   STATUS    RESTARTS        AGE
alertmanager-prometheus-community-kube-alertmanager-0      2/2     Running   6 (7h40m ago)   11h
cluster-example-1                                          1/1     Running   0               69s
cluster-example-2                                          1/1     Running   0               50s
cluster-example-3                                          1/1     Running   0               33s
cluster-example-tde-1                                      1/1     Running   0               170m
cluster-example-tde-2                                      1/1     Running   0               5h12m
cluster-example-tde-3                                      1/1     Running   0               5h12m
prometheus-community-grafana-57cc58669f-gdpm2              3/3     Running   9 (7h40m ago)   11h
prometheus-community-kube-operator-55ccb78dbb-qq7x6        1/1     Running   6 (7h35m ago)   11h
prometheus-community-kube-state-metrics-755fc69c9b-h49x2   1/1     Running   6 (7h35m ago)   11h
prometheus-prometheus-community-kube-prometheus-0          2/2     Running   6 (7h40m ago)   11h

yama@kuma:~$ kubectl get cluster -n edb-ns
NAME                  AGE   INSTANCES   READY   STATUS                     PRIMARY
cluster-example       10m   3           3       Cluster in healthy state   cluster-example-1
cluster-example-tde   9h    3           3       Cluster in healthy state   cluster-example-tde-2

次のURLをクリックしてダッシュボードを表示します（注：手順は導入編に記載）。画面上部中央付近にclusterを指定する欄があり、そこでcluster-exampleを選択します。

http://localhost:3000/

PostgreSQL 16のダッシュボードが表示されました。

5.2 データが暗号化されていないことを確認

PostgreSQLクラスターcluster-exampleでコマンドライン・インターフェースpsqlを起動し、表を作成してレコードを挿入します。

yama@kuma:~$ kubectl cnp psql cluster-example -n edb-ns
psql (16.0)
postgres=# create table sample (id int, name varchar(10));
postgres=# insert into sample values (1,'aaa'),(2,'bbb');
postgres=# select * from sample;
 id | name
----+------
  1 | aaa
  2 | bbb

表単位でデータが保存されていて、そのファイル名を確認します。

postgres=# select pg_relation_filepath('sample');
 pg_relation_filepath
----------------------
 base/5/16391

postgres=# \q

Podのシェルを起動して上のファイルをダンプすると、挿入したレコードに含まれる文字列aaaとbbbが見えます（注：表の内容がファイルに反映されるまで私の環境では待つ必要がありました）。データが平文で保存されていることがわかりました。

yama@kuma:~$ kubectl exec -it cluster-example-1 -n edb-ns -- bash
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
bash-4.4$ cd /var/lib/postgresql/data/pgdata/
bash-4.4$ hexdump -C base/5/16391
...
00001fc0  ea 02 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fd0  02 00 02 00 02 09 18 00  02 00 00 00 09 62 62 62  |.............bbb|
00001fe0  ea 02 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001ff0  01 00 02 00 02 09 18 00  01 00 00 00 09 61 61 61  |.............aaa|
00002000
bash-4.4$ exit

6. 透過的データベース暗号化(TDE)有りの場合

EPAS (EDB Postgres Advanced Server)のクラスターcluster-example-tdeで前節と同様に表を作成してレコードを挿入します。

yama@kuma:~$ kubectl cnp psql cluster-example-tde -n edb-ns
psql (15.4.1, server 15.4.1)
postgres=# create table sample (id int, name varchar(10));
postgres=# insert into sample values (1,'aaa'),(2,'bbb');
postgres=# select * from sample;
 id | name
----+------
  1 | aaa
  2 | bbb

表が保存されているファイルの名前を確認します。

postgres=# select pg_relation_filepath('sample');
 pg_relation_filepath
----------------------
 base/5/16403

postgres=# \q

Podのシェルを起動して上のファイルをダンプします。表に挿入した文字列aaaとbbbを検索してもヒットせず、データが暗号化されていることがわかります。

yama@kuma:~$ kubectl exec -it cluster-example-tde-1 -n edb-ns -- bash
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
[postgres@cluster-example-tde-1 /]$ cd /var/lib/postgresql/data/pgdata/
[postgres@cluster-example-tde-1 pgdata]$ hexdump -C base/5/16403
00000000  1c df 8a 63 57 6c 9c e7  27 b0 09 5d 84 88 21 df  |...cWl..'..]..!.|
00000010  a5 c1 c6 2c c1 cc c7 7b  c7 af b7 1e cb 3c 95 f9  |...,...{.....<..|
00000020  20 44 d9 5f 13 25 62 06  ec 81 ed f2 33 08 79 50  | D._.%b.....3.yP|
...
00001fd0  37 78 80 78 d4 7a 1e 02  2a 4c 2d a5 ed 22 c3 6f  |7x.x.z..*L-..".o|
00001fe0  37 b4 aa 33 f5 06 31 f3  53 99 1c 9a 33 c2 6e 64  |7..3..1.S...3.nd|
00001ff0  6a 87 15 fb 18 b7 47 79  d5 b6 60 23 d3 c0 fa ec  |j.....Gy..`#....|
00002000

[postgres@cluster-example-tde-1 pgdata]$ hexdump -C base/5/16403 | grep aaa
[postgres@cluster-example-tde-1 pgdata]$ hexdump -C base/5/16403 | grep bbb
[postgres@cluster-example-tde-1 pgdata]$ exit

補足）TDEを有効にした状態で、暗号化の対象は次のようになります。（参考：Transparent Data Encryption）
- 対象：ディスクに保存された表やインデックスなどユーザーデータ、WAL、一時ファイル、バックアップ
- 対象外：メモリー上のデータ、ディスク上のファイル名、外部表、診断ログ、設定ファイルなど

7. 参考資料

EDB Postgres for Kubernetes
- パソコンにEDB Postgres for Kubernetesを導入して無料トライアル（導入編）
  https://qiita.com/yamasakk/items/3b63a3bd1c8834dd498c
- PostgreSQL architecture
  https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/architecture/#postgresql-architecture
- The EDB Postgres for Kubernetes operator
  https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/
- Automated failover v1
  https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/failover/
- Transparent Data Encryption (TDE)
  https://www.enterprisedb.com/docs/tde/latest/
- Quickstart v1
  https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/quickstart/
SQLコマンド
- MERGE
  - https://www.postgresql.org/docs/16/sql-merge.html
  - https://www.enterprisedb.com/docs/epas/latest/reference/oracle_compatibility_reference/epas_compat_sql/65a_merge/
- INSERT
  https://www.postgresql.org/docs/16/sql-insert.html

8. 類似テーマを扱ったQiita記事

EDB Cloud Native PostgreSQL on OpenShift 高可用性検証
https://qiita.com/bata12/items/49c529b650ffe5358db1

パソコンにEDB Postgres for Kubernetesをインストールして無料トライアル（EPAS 15.4の機能確認）

目次