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

# 目次
1. はじめに
2. 前提
3. Primary pod削除後のフェイルオーバー
4. MERGEコマンド
5. 透過的データベース暗号化(TDE)無しの場合
6. 透過的データベース暗号化(TDE)有りの場合
7. 参考資料
8. 類似テーマを扱ったQiita記事


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

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

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

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

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


# 2. 前提
- 次の記事で説明した環境(Ubuntu/Docker/minikube)を用います。
    [パソコンにEDB Postgres for Kubernetesを導入して無料トライアル（導入編）](https://qiita.com/yamasakk/items/3b63a3bd1c8834dd498c)

- 実行例で使うUbuntuのホスト名を`kuma`、ユーザー名を`yama`としましたが、各人の環境に合わせて読み替えてください。


# 3. Primary pod削除後のフェイルオーバー
[導入編](https://qiita.com/yamasakk/items/3b63a3bd1c8834dd498c)で作成したEPAS (EDB Postgres Advanced Server)クラスター`cluster-example-tde`においてpodが3個稼働しています。1個がprimary、残り2個がreplica (hot standby)です（参考：[PostgreSQL architecture](https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/architecture/#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](https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/failover/)）
    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](https://www.enterprisedb.com/docs/epas/latest/reference/oracle_compatibility_reference/epas_compat_sql/65a_merge/#name)）。まず更新対象`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](https://www.postgresql.org/docs/16/sql-insert.html)）、上の例ではレコード`(1,'山',100)`が該当します。


# 5. 透過的データベース暗号化(TDE)無しの場合
## 5.1 PostgreSQLのクラスターをデプロイ
[導入編](https://qiita.com/yamasakk/items/3b63a3bd1c8834dd498c)においてEPAS (EDB Postgres Advanced Server)のクラスターをデプロイし、その際TDEを有効にしました（注：定義ファイルの`tde`で設定）。TDE無しの状態で暗号化の状況を確認するために、PostgreSQLのクラスターを新たにデプロイします。次のマニュアルを参考にします。
- [Quickstart v1](https://www.enterprisedb.com/docs/postgres_for_kubernetes/latest/quickstart/)

まず、クラスターの定義ファイルをダウンロードします。
```
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をクリックしてダッシュボードを表示します（注：手順は[導入編](https://qiita.com/yamasakk/items/3b63a3bd1c8834dd498c)に記載）。画面上部中央付近にclusterを指定する欄があり、そこで`cluster-example`を選択します。
- http://localhost:3000/
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/75204979-a8fd-b339-e2b5-209d8665ecbb.png)
PostgreSQL 16のダッシュボードが表示されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/ee67b916-a97f-bd54-ad02-7e39f719b14e.png)


## 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](https://www.enterprisedb.com/docs/tde/latest/)）
    - 対象：ディスクに保存された表やインデックスなどユーザーデータ、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