PatroniをサポートしたTanzu for Postgres on Kubernetes 4.0をインストールしてSpring Bootアプリからアクセスするメモ

Tanzu for Postgres on Kubernetes 4.0からついにHigh Availabilityの実現にPatroniが使用されるようになりました。
3.0まではpg_auto_failoverが使用されていました。

試してみたいと思います。

Tanzu for Postgres on Kubernetes Docker Registryのアクセストークン取得
Helm ChartとコンテナイメージをPrivate Registryへリロケート
Postgres Operatorのインストール
Postgresインスタンスの作成
サンプルアプリのデプロイ
Read Replicaに対するRoutingを設定

Tanzu for Postgres on Kubernetes Docker Registryのアクセストークン取得

まずはこのHelmチャートが配布されているDocker Registryにアクセスするためのアクセストークンを取得します。

Broadcom Supportにログインして、"My Downloads"を選択し、"postgres"で検索します。

検索結果から"VMware Tanzu for Postgres on Kubernetes"をクリックします。

バージョン"4.0.0"の"Token Download"アイコンをクリックします。

次のようなダイアログが現れます。

下にスクロールするとRegistryの情報の記載が見えます。

次のコマンドでtanzu-sql-postgres.packages.broadcom.comにログインします。認証情報を自分のものに置き換えてください。

BC_SUPPORT_USERNAME=toshiaki.maki@broadcom.com
BC_SUPPORT_PASSWORD=eyJ2ZXI*************************************
docker login tanzu-sql-postgres.packages.broadcom.com -u ${BC_SUPPORT_USERNAME} -p ${BC_SUPPORT_PASSWORD}

これで次のコマンドでtanzu-sql-postgres.packages.broadcom.comからHelm Chartをpullできます。

helm pull oci://tanzu-sql-postgres.packages.broadcom.com/vmware-sql-postgres-operator --version v4.0.0 --untar --untardir /tmp

ただし、このトークンは有効期限があり、期限が切れるたびにローテートするのが面倒くさいので、今回はこのRegistryから直接Pullするのではなく、内部ネットワーク内のPrivate Registryであるregistry.example.comにリロケートした上で使用することとします。
registry.example.comは自分の環境に変えてください。

Helm ChartとコンテナイメージをPrivate Registryへリロケート

次のコマンドでregistry.example.comにログインします。認証情報を自分のものに置き換えてください。

REGISTRY_USERNAME=username
REGISTRY_PASSWORD=changme
docker login registry.example.com -u ${REGISTRY_USERNAME} -p ${REGISTRY_PASSWORD}

リロケートにはimgpkgを使用します。

imgpkg copy -i tanzu-sql-postgres.packages.broadcom.com/vmware-sql-postgres-operator:v4.0.0 --to-repo registry.example.com/tanzu-sql-postgres/vmware-sql-postgres-operator

リロケート後のHelm Chartにアクセスして設定可能なvaluesを確認します。

$ helm show values oci://registry.example.com/tanzu-sql-postgres/vmware-sql-postgres-operator --version v4.0.0
Pulled: registry.example.com/tanzu-sql-postgres/vmware-sql-postgres-operator:v4.0.0
Digest: sha256:52fdabfa581ea319491b5649e607a8034dc689381ae0c39e62080caa92bd6b23
---
# specify the url for the docker image for the operator, e.g. gcr.io/<my_project>/postgres-operator
operatorImage: tanzu-sql-postgres.packages.broadcom.com/postgres-operator:v4.0.0

# specify the docker image for postgres instance, e.g. gcr.io/<my_project>/postgres-instance
postgresImage: tanzu-sql-postgres.packages.broadcom.com/postgres-instance:v4.0.0

# specify the name of the docker-registry secret to allow the cluster to authenticate with the container registry for pulling images
dockerRegistrySecretName: regsecret

# override the default self-signed cert-manager cluster issuer
certManagerClusterIssuerName: postgres-operator-ca-certificate-cluster-issuer

# override the namespace of the cert-manager installation namespace
certManagerNamespace: cert-manager

# set the resources for the postgres operator deployment
resources: { }
#  limits:
#    cpu: 500m
#    memory: 300Mi
#  requests:
#    cpu: 500m
#    memory: 300Mi

# enabled security context for the postgres-operator deployment and the managed instances, typically disabled on OpenShift clusters
enableSecurityContext: true

operatorImageとpostgresImageも次のコマンドでtanzu-sql-postgres.packages.broadcom.comからregistry.example.comにリロケートします。

imgpkg copy -i tanzu-sql-postgres.packages.broadcom.com/postgres-operator:v4.0.0 --to-repo registry.example.com/tanzu-sql-postgres/postgres-operator
imgpkg copy -i tanzu-sql-postgres.packages.broadcom.com/postgres-instance:v4.0.0 --to-repo registry.example.com/tanzu-sql-postgres/postgres-instance

Postgres Operatorのインストール

ここではpostgres-system namespaceにPostgres Operatorをインストールします。
Docker Pull Secretの名前はデフォルトでregsecretです。

次のコマンドでnamespaceとsecretを作成します。

kubectl create namespace postgres-system
kubectl create secret docker-registry regsecret \
    --docker-server=registry.example.com \
    --docker-username=${REGISTRY_USERNAME} \
    --docker-password="${REGISTRY_PASSWORD}" \
    -n  postgres-system

Postgres Operatorの前提条件としてcert-managerが必要なので、cert-managerが未インストールの場合は次のコマンドでインストールします。

kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.17.0/cert-manager.yaml

次のコマンドでPostgres Operatorをインストールします。リロケート後のChartとコンテナイメージを指定します。

helm upgrade --install \
  -n postgres-system \
  postgres-operator \
  oci://registry.example.com/tanzu-sql-postgres/vmware-sql-postgres-operator \
  --version v4.0.0 \
  --set operatorImage=registry.example.com/tanzu-sql-postgres/postgres-operator:v4.0.0 \
  --set postgresImage=registry.example.com/tanzu-sql-postgres/postgres-instance:v4.0.0 \
  --wait

しばらくするとインストールが完了します。helm listでインストール状態を確認できます。

$ helm list -n postgres-system
NAME             	NAMESPACE      	REVISION	UPDATED                             	STATUS  	CHART                              	APP VERSION
postgres-operator	postgres-system	1       	2025-03-21 15:00:12.782425 +0900 JST	deployed	vmware-sql-postgres-operator-v4.0.0	v4.0.0

次のコマンドで、Postgres OperatorのPodの状態を確認します。

$ kubectl get pod -n postgres-system 
NAME                                 READY   STATUS    RESTARTS   AGE
postgres-operator-5986868f44-qlswf   1/1     Running   0          2m6s

次のコマンドで、このPostgres Operatorで利用可能なカスタムリソースを確認します。

$ kubectl api-resources --api-group=sql.tanzu.vmware.com
NAME                      SHORTNAMES   APIVERSION                NAMESPACED   KIND
postgres                  pg           sql.tanzu.vmware.com/v1   true         Postgres
postgresbackuplocations                sql.tanzu.vmware.com/v1   true         PostgresBackupLocation
postgresbackups                        sql.tanzu.vmware.com/v1   true         PostgresBackup
postgresbackupschedules                sql.tanzu.vmware.com/v1   true         PostgresBackupSchedule
postgresmigrations                     sql.tanzu.vmware.com/v1   true         PostgresMigration
postgresrestores                       sql.tanzu.vmware.com/v1   true         PostgresRestore
postgresversions                       sql.tanzu.vmware.com/v1   false        PostgresVersion
postgresversionupgrades                sql.tanzu.vmware.com/v1   true         PostgresVersionUpgrade

次のコマンドで、利用可能なPostgresのバージョンを確認します。

$ kubectl get postgresversion
NAME          DB VERSION
postgres-11   11.22
postgres-12   12.22
postgres-13   13.18
postgres-14   14.15
postgres-15   15.10
postgres-16   16.6

Postgresインスタンスの作成

Postgres Operatorを使ってdemo namespaceにPostgresインスタンスの作成します。

次のコマンドでnamespaceとdocker pull secretを作成します。

kubectl create namespace demo
kubectl create secret docker-registry regsecret \
    --docker-server=registry.example.com \
    --docker-username=${REGISTRY_USERNAME} \
    --docker-password="${REGISTRY_PASSWORD}" \
    -n  demo

次のコマンドでPostgresインスタンスを作成します。

cat <<EOF > /tmp/demo-postgres.yaml
---
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: demo
spec:
  postgresVersion:
    name: postgres-16
  storageSize: 1Gi
  highAvailability:
    enabled: true
---
EOF

kubectl apply -f /tmp/demo-postgres.yaml -n demo

しばらくすると次のリソースが作成されます。

$ kubectl get postgres,sts,pod,svc,secret,certificate,pvc -n demo -l app=postgres 
NAME                                 STATUS    DB VERSION   BACKUP LOCATION   AGE
postgres.sql.tanzu.vmware.com/demo   Running   16.6                           85s

NAME                    READY   AGE
statefulset.apps/demo   2/2     84s

NAME         READY   STATUS    RESTARTS   AGE
pod/demo-0   4/4     Running   0          84s
pod/demo-1   4/4     Running   0          84s

NAME                     TYPE        CLUSTER-IP        EXTERNAL-IP   PORT(S)    AGE
service/demo             ClusterIP   192.168.194.223   <none>        5432/TCP   85s
service/demo-agent       ClusterIP   None              <none>        <none>     85s
service/demo-read-only   ClusterIP   192.168.194.192   <none>        5432/TCP   85s

NAME                                    TYPE                           DATA   AGE
secret/demo-additional-db-creds         Opaque                         1      85s
secret/demo-app-user-db-secret          servicebinding.io/postgresql   8      85s
secret/demo-db-secret                   Opaque                         5      85s
secret/demo-empty-secret                Opaque                         0      85s
secret/demo-internal-ssl-secret         kubernetes.io/tls              3      85s
secret/demo-metrics-secret              Opaque                         4      85s
secret/demo-metrics-tls-secret          kubernetes.io/tls              3      83s
secret/demo-pgbackrest-secret           Opaque                         3      85s
secret/demo-read-only-user-db-secret    servicebinding.io/postgresql   8      85s
secret/demo-read-write-user-db-secret   servicebinding.io/postgresql   8      85s
secret/demo-replication-ssl-secret      kubernetes.io/tls              3      85s

NAME                                                           READY   SECRET                        AGE
certificate.cert-manager.io/demo-internal-ssl-certificate      True    demo-internal-ssl-secret      85s
certificate.cert-manager.io/demo-metrics-tls-certificate       True    demo-metrics-tls-secret       85s
certificate.cert-manager.io/demo-replication-ssl-certificate   True    demo-replication-ssl-secret   85s

NAME                                       STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   VOLUMEATTRIBUTESCLASS   AGE
persistentvolumeclaim/demo-pgdata-demo-0   Bound    pvc-f6e307e6-fce3-4889-9c79-551f3ca578cf   1Gi        RWO            local-path     <unset>                 84s
persistentvolumeclaim/demo-pgdata-demo-1   Bound    pvc-e4894901-17d4-45cc-a0c2-68ba04842eae   1Gi        RWO            local-path     <unset>                 84s

作成したPostgresインスタンスにコンテナの中からpsqlでアクセスします。

$ kubectl exec -it -n demo demo-0 -c pg-container -- psql demo
psql (16.6 (VMware Postgres 16.6.0))
Type "help" for help.

demo=#

初期状態は次の通りです。

demo=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |      Access privileges       
-----------+----------+----------+-----------------+---------+---------+------------+-----------+------------------------------
 demo      | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | postgres=CTc/postgres       +
           |          |          |                 |         |         |            |           | postgres_exporter=c/postgres+
           |          |          |                 |         |         |            |           | pgappuser=CTc/postgres      +
           |          |          |                 |         |         |            |           | pgrouser=c/postgres         +
           |          |          |                 |         |         |            |           | pgrwuser=c/postgres
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | postgres=CTc/postgres       +
           |          |          |                 |         |         |            |           | postgres_exporter=c/postgres
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres                 +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres                 +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(4 rows)

demo=# \d
Did not find any relations.

利用可能なExtensionは次の通りです。ベクトル検索のためのpgvectorやUberで使われている地理インデックスシステムであるh3などが利用可能です。

postgres=# SELECT * FROM pg_available_extensions ORDER BY name;
             name             | default_version | installed_version |                                                                                                                          comment                                                                                                                          
------------------------------+-----------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.0           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.0           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 advanced_password_check      | 1.4             |                   | Advanced Password Check
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 bool_plperl                  | 1.0             |                   | transform between bool and plperl
 bool_plperlu                 | 1.0             |                   | transform between bool and plperlu
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 h3                           | 4.1.3           |                   | H3 bindings for PostgreSQL
 h3_postgis                   | 4.1.3           |                   | H3 PostGIS integration
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 hstore_plperl                | 1.0             |                   | transform between hstore and plperl
 hstore_plperlu               | 1.0             |                   | transform between hstore and plperlu
 hstore_plpython3u            | 1.0             |                   | transform between hstore and plpython3u
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 jsonb_plperl                 | 1.0             |                   | transform between jsonb and plperl
 jsonb_plperlu                | 1.0             |                   | transform between jsonb and plperlu
 jsonb_plpython3u             | 1.0             |                   | transform between jsonb and plpython3u
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 ltree_plpython3u             | 1.0             |                   | transform between ltree and plpython3u
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 orafce                       | 4.10            |                   | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_cron                      | 1.6             |                   | Job scheduler for PostgreSQL
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_graphql                   | 1.5.8           |                   | pg_graphql: GraphQL support
 pg_hint_plan                 | 1.6.0           |                   | 
 pg_partman                   | 5.1.0           |                   | Extension to manage partitioned tables by time or ID
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_monitor              | 2.0             |                   | The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.
 pg_stat_statements           | 1.10            | 1.10              | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             |                   | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgagent                      | 4.2             |                   | A PostgreSQL job scheduler
 pgaudit                      | 16.0            |                   | provides auditing functionality
 pgcrypto                     | 1.3             |                   | cryptographic functions
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plperl                       | 1.0             |                   | PL/Perl procedural language
 plperlu                      | 1.0             |                   | PL/PerlU untrusted procedural language
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 plpython3u                   | 1.0             |                   | PL/Python3U untrusted procedural language
 postgis                      | 3.4.0           |                   | PostGIS geometry and geography spatial types and functions
 postgis_raster               | 3.4.0           |                   | PostGIS raster types and functions
 postgis_tiger_geocoder       | 3.4.0           |                   | PostGIS tiger geocoder and reverse geocoder
 postgis_topology             | 3.4.0           |                   | PostGIS topology spatial types and functions
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 set_user                     | 4.1.0           |                   | similar to SET ROLE but with added logging
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 uuid-ossp                    | 1.1             |                   | generate universally unique identifiers (UUIDs)
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
(78 rows)

一旦quitします。

demo=# \q

次にPatroniのクラスタ情報を確認します。demo-0がRead Replica、demo-1がPrimaryであることがわかります。

$ kubectl exec -it -n demo demo-0 -c pg-container -- patronictl -c pgsql/patroni.yaml list

+ Cluster: demo (7485181581897678935) --------------+--------------+-----------+----+-----------+
| Member | Host                                     | Role         | State     | TL | Lag in MB |
+--------+------------------------------------------+--------------+-----------+----+-----------+
| demo-0 | demo-0.demo-agent.demo.svc.cluster.local | Sync Standby | streaming |  1 |         0 |
| demo-1 | demo-1.demo-agent.demo.svc.cluster.local | Leader       | running   |  1 |           |
+--------+------------------------------------------+--------------+-----------+----+-----------+

次のコマンドでRead Replicaの数を2に増やします。

cat <<EOF > /tmp/demo-postgres.yaml
---
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: demo
spec:
  postgresVersion:
    name: postgres-16
  storageSize: 1Gi
  highAvailability:
    readReplicas: 2
    enabled: true
---
EOF

kubectl apply -f /tmp/demo-postgres.yaml -n demo

次のコマンドでリソースを確認します。まだReplicaを増やしている途中です。

$ kubectl get postgres,sts,pod,svc,secret,certificate,pvc -n demo -l app=postgres
NAME                                 STATUS    DB VERSION   BACKUP LOCATION   AGE
postgres.sql.tanzu.vmware.com/demo   Pending   16.6                           5m23s

NAME                    READY   AGE
statefulset.apps/demo   2/3     5m22s

NAME         READY   STATUS    RESTARTS   AGE
pod/demo-0   4/4     Running   0          5m22s
pod/demo-1   4/4     Running   0          5m22s
pod/demo-2   3/4     Running   0          18s

NAME                     TYPE        CLUSTER-IP        EXTERNAL-IP   PORT(S)    AGE
service/demo             ClusterIP   192.168.194.223   <none>        5432/TCP   5m23s
service/demo-agent       ClusterIP   None              <none>        <none>     5m23s
service/demo-read-only   ClusterIP   192.168.194.192   <none>        5432/TCP   5m23s

NAME                                    TYPE                           DATA   AGE
secret/demo-additional-db-creds         Opaque                         1      5m23s
secret/demo-app-user-db-secret          servicebinding.io/postgresql   8      5m23s
secret/demo-db-secret                   Opaque                         5      5m23s
secret/demo-empty-secret                Opaque                         0      5m23s
secret/demo-internal-ssl-secret         kubernetes.io/tls              3      5m23s
secret/demo-metrics-secret              Opaque                         4      5m23s
secret/demo-metrics-tls-secret          kubernetes.io/tls              3      5m21s
secret/demo-pgbackrest-secret           Opaque                         3      5m23s
secret/demo-read-only-user-db-secret    servicebinding.io/postgresql   8      5m23s
secret/demo-read-write-user-db-secret   servicebinding.io/postgresql   8      5m23s
secret/demo-replication-ssl-secret      kubernetes.io/tls              3      5m23s

NAME                                                           READY   SECRET                        AGE
certificate.cert-manager.io/demo-internal-ssl-certificate      True    demo-internal-ssl-secret      5m23s
certificate.cert-manager.io/demo-metrics-tls-certificate       True    demo-metrics-tls-secret       5m23s
certificate.cert-manager.io/demo-replication-ssl-certificate   True    demo-replication-ssl-secret   5m23s

NAME                                       STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   VOLUMEATTRIBUTESCLASS   AGE
persistentvolumeclaim/demo-pgdata-demo-0   Bound    pvc-f6e307e6-fce3-4889-9c79-551f3ca578cf   1Gi        RWO            local-path     <unset>                 5m22s
persistentvolumeclaim/demo-pgdata-demo-1   Bound    pvc-e4894901-17d4-45cc-a0c2-68ba04842eae   1Gi        RWO            local-path     <unset>                 5m22s
persistentvolumeclaim/demo-pgdata-demo-2   Bound    pvc-bb3e5ac3-4891-420f-b841-56bb2c19848a   1Gi        RWO            local-path     <unset>                 18s

途中の状態ではPatroniは次の状態になっています。

$ kubectl exec -it -n demo demo-0 -c pg-container -- patronictl -c pgsql/patroni.yaml list 
+ Cluster: demo (7485181581897678935) --------------+--------------+------------------+----+-----------+
| Member | Host                                     | Role         | State            | TL | Lag in MB |
+--------+------------------------------------------+--------------+------------------+----+-----------+
| demo-0 | demo-0.demo-agent.demo.svc.cluster.local | Sync Standby | streaming        |  1 |         0 |
| demo-1 | demo-1.demo-agent.demo.svc.cluster.local | Leader       | running          |  1 |           |
| demo-2 | demo-2.demo-agent.demo.svc.cluster.local | Replica      | creating replica |    |   unknown |
+--------+------------------------------------------+--------------+------------------+----+-----------+

しばらくするとクラスタの更新が完了します。

$ kubectl get postgres,sts,pod,svc,secret,certificate,pvc -n demo -l app=postgres
NAME                                 STATUS    DB VERSION   BACKUP LOCATION   AGE
postgres.sql.tanzu.vmware.com/demo   Running   16.6                           8m28s

NAME                    READY   AGE
statefulset.apps/demo   3/3     8m27s

NAME         READY   STATUS    RESTARTS   AGE
pod/demo-0   4/4     Running   0          8m27s
pod/demo-1   4/4     Running   0          8m27s
pod/demo-2   4/4     Running   0          3m23s

NAME                     TYPE        CLUSTER-IP        EXTERNAL-IP   PORT(S)    AGE
service/demo             ClusterIP   192.168.194.223   <none>        5432/TCP   8m28s
service/demo-agent       ClusterIP   None              <none>        <none>     8m28s
service/demo-read-only   ClusterIP   192.168.194.192   <none>        5432/TCP   8m28s

NAME                                    TYPE                           DATA   AGE
secret/demo-additional-db-creds         Opaque                         1      8m28s
secret/demo-app-user-db-secret          servicebinding.io/postgresql   8      8m28s
secret/demo-db-secret                   Opaque                         5      8m28s
secret/demo-empty-secret                Opaque                         0      8m28s
secret/demo-internal-ssl-secret         kubernetes.io/tls              3      8m28s
secret/demo-metrics-secret              Opaque                         4      8m28s
secret/demo-metrics-tls-secret          kubernetes.io/tls              3      8m26s
secret/demo-pgbackrest-secret           Opaque                         3      8m28s
secret/demo-read-only-user-db-secret    servicebinding.io/postgresql   8      8m28s
secret/demo-read-write-user-db-secret   servicebinding.io/postgresql   8      8m28s
secret/demo-replication-ssl-secret      kubernetes.io/tls              3      8m28s

NAME                                                           READY   SECRET                        AGE
certificate.cert-manager.io/demo-internal-ssl-certificate      True    demo-internal-ssl-secret      8m28s
certificate.cert-manager.io/demo-metrics-tls-certificate       True    demo-metrics-tls-secret       8m28s
certificate.cert-manager.io/demo-replication-ssl-certificate   True    demo-replication-ssl-secret   8m28s

NAME                                       STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   VOLUMEATTRIBUTESCLASS   AGE
persistentvolumeclaim/demo-pgdata-demo-0   Bound    pvc-f6e307e6-fce3-4889-9c79-551f3ca578cf   1Gi        RWO            local-path     <unset>                 8m27s
persistentvolumeclaim/demo-pgdata-demo-1   Bound    pvc-e4894901-17d4-45cc-a0c2-68ba04842eae   1Gi        RWO            local-path     <unset>                 8m27s
persistentvolumeclaim/demo-pgdata-demo-2   Bound    pvc-bb3e5ac3-4891-420f-b841-56bb2c19848a   1Gi        RWO            local-path     <unset>                 3m23s

Patroniクラスタは次の状態になっています。

$ kubectl exec -it -n demo demo-0 -c pg-container -- patronictl -c pgsql/patroni.yaml list 
+ Cluster: demo (7485181581897678935) --------------+--------------+-----------+----+-----------+
| Member | Host                                     | Role         | State     | TL | Lag in MB |
+--------+------------------------------------------+--------------+-----------+----+-----------+
| demo-0 | demo-0.demo-agent.demo.svc.cluster.local | Sync Standby | streaming |  1 |         0 |
| demo-1 | demo-1.demo-agent.demo.svc.cluster.local | Leader       | running   |  1 |           |
| demo-2 | demo-2.demo-agent.demo.svc.cluster.local | Replica      | streaming |  1 |         0 |
+--------+------------------------------------------+--------------+-----------+----+-----------+

次のコマンドで各PostgresインスタンスのIPを確認します。

$ kubectl get pod -n demo -owide -l app=postgres
NAME     READY   STATUS    RESTARTS   AGE     IP               NODE       NOMINATED NODE   READINESS GATES
demo-0   4/4     Running   0          10m     192.168.194.91   orbstack   <none>           <none>
demo-1   4/4     Running   0          10m     192.168.194.90   orbstack   <none>           <none>
demo-2   4/4     Running   0          5m20s   192.168.194.93   orbstack   <none>           <none>

クラスタの外から、このクラスタにアクセスしてみます。次のコマンドで接続情報を確認します。

$ kubectl get secret -n demo demo-app-user-db-secret -ojson | jq '.data | map_values(@base64d)'
{
  "database": "demo",
  "host": "demo.demo",
  "password": "Scor1vyN2a9u9xez1LdRM1V88f5S8u",
  "port": "5432",
  "provider": "vmware",
  "type": "postgresql",
  "uri": "postgresql://pgappuser:Scor1vyN2a9u9xez1LdRM1V88f5S8u@demo.demo:5432/demo",
  "username": "pgappuser"
}

次のコマンドでクラスタの外からpsqlを使用する準備をします。

$ kubectl run pg-client --rm -it --image=postgres:alpine -- bash

If you don't see a command prompt, try pressing enter.
pg-client:/#

取得した接続情報を使い、次のようにpsqlでクラスタにアクセスします。まずはread-writeなエンドポイントにアクセスします。

psql postgresql://pgappuser:Scor1vyN2a9u9xez1LdRM1V88f5S8u@demo.demo:5432/demo

次のコマンドで接続先のサーバー情報を表示します。demo-1のIPが返るので、Primaryに接続していることがわかります。

demo=> SELECT inet_server_addr() AS server_ip, inet_server_port() AS server_port;
   server_ip    | server_port 
----------------+-------------
 192.168.194.90 |        5432
(1 row)

次のSQLを実行し、テストデータをinsertします。

CREATE TABLE IF NOT EXISTS organization
(
    organization_id   BIGINT PRIMARY KEY,
    organization_name VARCHAR(255) NOT NULL
);
INSERT INTO organization(organization_id, organization_name) VALUES(1, 'foo');
INSERT INTO organization(organization_id, organization_name) VALUES(2, 'bar');

insertしたデータを検索します。

demo=> select organization_id,organization_name from organization;
 organization_id | organization_name 
-----------------+-------------------
               1 | foo
               2 | bar
(2 rows)

いったんquitします。

demo=> \q

次にread-onlyなエンドポイントに接続します。

psql postgresql://pgappuser:Scor1vyN2a9u9xez1LdRM1V88f5S8u@demo-read-only.demo:5432/demo

次のコマンドで接続先のサーバー情報を表示します。demo-0のIPが返るので、Read Replicaに接続していることがわかります。

demo=> SELECT inet_server_addr() AS server_ip, inet_server_port() AS server_port;
   server_ip    | server_port 
----------------+-------------
 192.168.194.91 |        5432
(1 row)

次のコマンドで、先ほどinsertしたデータが検索できることを確認します。

demo=> select organization_id,organization_name from organization;
 organization_id | organization_name 
-----------------+-------------------
               1 | foo
               2 | bar
(2 rows)

insertを試みるとエラーになります。read-onlyなので期待通りです。

demo=> INSERT INTO organization(organization_id, organization_name) VALUES(3, 'baz');
ERROR:  cannot execute INSERT in a read-only transaction

quitします。

demo=> \q

psqlを実行したコンテナからexitします。

pg-client:/# exit

作成したPostgresインスタンスを一旦削除します。

kubectl delete -f /tmp/demo-postgres.yaml -n demo

サンプルアプリのデプロイ

次にPostgresインスタンスにアプリから接続してみます。ここではサンプルアプリとして https://github.com/categolj/room-reservation を使用します。このアプリの80%はAI Agentによって作成されました;)
動作確認したrevisionは7e728adbff9781d0f4a864cc98c202402226f82bです。

次のコマンドでこのアプリ向けのPostgresインスタンスを作成します。

cat <<EOF > /tmp/room-reservation-db.yaml
---
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: room-reservation-db
spec:
  postgresVersion:
    name: postgres-16
  storageSize: 1Gi
  highAvailability:
    readReplicas: 1
    enabled: true
---
EOF

kubectl apply -f /tmp/room-reservation-db.yaml -n demo

次のコマンドでPostgresインスタンスが作成されたことを確認します。

$ kubectl get postgres,pod,secret -n demo -l app=postgres
NAME                                                STATUS    DB VERSION   BACKUP LOCATION   AGE
postgres.sql.tanzu.vmware.com/room-reservation-db   Running   16.6                           2m22s

NAME                        READY   STATUS    RESTARTS   AGE
pod/room-reservation-db-0   4/4     Running   0          2m22s
pod/room-reservation-db-1   4/4     Running   0          2m22s

NAME                                                   TYPE                           DATA   AGE
secret/room-reservation-db-additional-db-creds         Opaque                         1      2m22s
secret/room-reservation-db-app-user-db-secret          servicebinding.io/postgresql   8      2m22s
secret/room-reservation-db-db-secret                   Opaque                         5      2m22s
secret/room-reservation-db-empty-secret                Opaque                         0      2m22s
secret/room-reservation-db-internal-ssl-secret         kubernetes.io/tls              3      2m22s
secret/room-reservation-db-metrics-secret              Opaque                         4      2m22s
secret/room-reservation-db-metrics-tls-secret          kubernetes.io/tls              3      2m22s
secret/room-reservation-db-pgbackrest-secret           Opaque                         3      2m22s
secret/room-reservation-db-read-only-user-db-secret    servicebinding.io/postgresql   8      2m22s
secret/room-reservation-db-read-write-user-db-secret   servicebinding.io/postgresql   8      2m22s
secret/room-reservation-db-replication-ssl-secret      kubernetes.io/tls              3      2m22s

次にアプリをデプロイします。まずはシンプルにread-writeなエンドポイントにアクセスします。

room-reservation-db-app-user-db-secretはService Binding Specification for Kubernetesに対応しているので、Spring Cloud Bindingsにより、マウントした情報から自動でspring.datasource.*プロパティを設定します。

cat <<'EOF' > /tmp/room-reservation.yaml
---
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: room-reservation
  name: room-reservation
spec:
  replicas: 1
  selector:
    matchLabels:
      app: room-reservation
  template:
    metadata:
      labels:
        app: room-reservation
    spec:
      containers:
      - name: room-reservation
        image: ghcr.io/categolj/room-reservation:jvm
        imagePullPolicy: Always
        ports:
        - containerPort: 8080
        env:
        - name: SERVICE_BINDING_ROOT
          value: /bindings
        - name: spring.datasource.hikari.data-source-properties.sslmode
          value: require
        - name: logging.structured.format.console
          value: ecs
        resources:
          limits:
            memory: 786Mi
          requests:
            memory: 786Mi
        securityContext:
          allowPrivilegeEscalation: false
          capabilities:
            drop:
            - ALL
          runAsNonRoot: true
          runAsUser: 1002
          seccompProfile:
            type: RuntimeDefault
        volumeMounts:
        - name: room-reservation-db
          mountPath: /bindings/room-reservation-db
          readOnly: true
        readinessProbe:
          httpGet:
            path: /actuator/health/readiness
            port: 8080
            scheme: HTTP
        livenessProbe:
          httpGet:
            path: /actuator/health/liveness
            port: 8080
            scheme: HTTP
      volumes:
      - name: room-reservation-db
        secret:
          secretName: room-reservation-db-app-user-db-secret
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: room-reservation
  name: room-reservation
spec:
  ports:
  - name: http
    port: 8080
    targetPort: 8080
  selector:
    app: room-reservation
---
EOF

kubectl apply -f /tmp/room-reservation.yaml -n demo

次のPodとServiceが作成されます。

$ kubectl get pod,svc -n demo
NAME                                    READY   STATUS    RESTARTS   AGE
pod/room-reservation-6f597cbf58-lzc4d   1/1     Running   0          94s
pod/room-reservation-db-0               4/4     Running   0          4m17s
pod/room-reservation-db-1               4/4     Running   0          4m17s

NAME                                    TYPE        CLUSTER-IP        EXTERNAL-IP   PORT(S)    AGE
service/room-reservation                ClusterIP   192.168.194.197   <none>        8080/TCP   94s
service/room-reservation-db             ClusterIP   192.168.194.145   <none>        5432/TCP   4m17s
service/room-reservation-db-agent       ClusterIP   None              <none>        <none>     4m17s
service/room-reservation-db-read-only   ClusterIP   192.168.194.180   <none>        5432/TCP   4m17s

Serviceに対するport-forwardingでこのアプリにアクセスします。

kubectl port-forward -n demo svc/room-reservation 8080:8080

部屋の予約ができることを確認します。

この部屋の予約情報をREST APIで取得します。(dateとroomIdパラメータを変更してください。)

$ curl "http://localhost:8080/api/reservations?date=2025-03-24&roomId=018422b2-4843-7a62-935b-b4e65649de3f"

[{"reservationId":"0195c5e1-c292-788b-96ec-1cb3b3568454","roomId":"018422b2-4843-7a62-935b-b4e65649de3f","date":"2025-03-24","startTime":"08:00:00","endTime":"12:00:00","purpose":"Block","userId":"018422b2-4843-7a62-935b-b4e65649de46"}]

このAPIに対して、vegetaを使って負荷をかけてみます。vegetaは次のコマンドでインストールできます。

brew install vegeta

次のコマンドで負荷をかけます。

echo "GET http://localhost:8080/api/reservations?date=2025-03-24&roomId=018422b2-4843-7a62-935b-b4e65649de3f" | vegeta attack -duration=10s | tee results.bin | vegeta report

全てステータスコード200で返ればOKです。

Requests      [total, rate, throughput]         500, 50.10, 50.08
Duration      [total, attack, wait]             9.984s, 9.98s, 3.616ms
Latencies     [min, mean, 50, 90, 95, 99, max]  1.903ms, 4.813ms, 4.261ms, 6.654ms, 8.229ms, 14.717ms, 37.522ms
Bytes In      [total, mean]                     118000, 236.00
Bytes Out     [total, mean]                     0, 0.00
Success       [ratio]                           100.00%
Status Codes  [code:count]                      200:500  
Error Set:

次に負荷をかけている最中にこのPatroniクラスタをrolling restartしてみます。次のコマンドで80秒負荷をかけます。

echo "GET http://localhost:8080/api/reservations?date=2025-03-24&roomId=018422b2-4843-7a62-935b-b4e65649de3f" | vegeta attack -duration=80s | tee results.bin | vegeta report

別のターミナルでPatroniクラスタを再起動します。

kubectl rollout restart -n demo sts/room-reservation-db

vegetaの負荷テスト結果は次の通りです。500エラーが見受けられます。

Requests      [total, rate, throughput]         4000, 50.01, 49.17
Duration      [total, attack, wait]             1m20s, 1m20s, 2.146ms
Latencies     [min, mean, 50, 90, 95, 99, max]  1.324ms, 89.38ms, 3.257ms, 8.017ms, 731.317ms, 2.004s, 2.018s
Bytes In      [total, mean]                     936027, 234.01
Bytes Out     [total, mean]                     0, 0.00
Success       [ratio]                           98.32%
Status Codes  [code:count]                      200:3933  500:67  
Error Set:
500

次のようなエラーログを確認できました。

{
  "@timestamp": "2025-03-24T02:05:56.631734872Z",
  "log.level": "ERROR",
  "process.pid": 1,
  "process.thread.name": "tomcat-handler-701",
  "service.name": "room-reservation",
  "service.version": "0.0.1.7e728adbff9781d0f4a864cc98c202402226f82b",
  "log.logger": "org.springframework.transaction.interceptor.TransactionInterceptor",
  "message": "Application exception overridden by rollback exception",
  "traceId": "67e0be042b0a83d6df08bb7469670237",
  "spanId": "fe7f1902c2f5ae8c",
  "error.type": "org.springframework.dao.DataAccessResourceFailureException",
  "error.message": "PreparedStatementCallback; SQL [SELECT reservation_id,\n    room_id,\n    date,\n    start_time,\n    end_time,\n    purpose,\n    user_id\nFROM reservation_view\nWHERE room_id = ? AND date = ?]; FATAL: terminating connection due to administrator command",
  "error.stack_trace": "org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [SELECT reservation_id,\n    room_id,\n    date,\n    start_time,\n    end_time,\n    purpose,\n    user_id\nFROM reservation_view\nWHERE room_id = ? AND date = ?]; FATAL: terminating connection due to administrator command\n\tat org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:121)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)\n\tat org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)\n\tat org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218)\n\tat org.springframework.jdbc.core.simple.DefaultJdbcClient.list(DefaultJdbcClient.java:366)\n\tat com.example.reservation.query.ReservationViewRepository.findByRoomIdAndDate(ReservationViewRepository.java:49)\n\tat java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.query.ReservationViewRepository58755SpringCGLIB587550.findByRoomIdAndDate(<generated>)\n\tat com.example.reservation.web.ReservationController.findReservation(ReservationController.java:49)\n\tat java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.modulith.observability.ModuleEntryInterceptor.invoke(ModuleEntryInterceptor.java:91)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.web.ReservationController58755SpringCGLIB587550.findReservation(<generated>)\n\tat java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:191)\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891)\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.zalando.logbook.servlet.LogbookFilter.doFilter(LogbookFilter.java:76)\n\tat org.zalando.logbook.servlet.HttpFilter.doFilter(HttpFilter.java:32)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:114)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)\n\tat org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:731)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)\n\tat org.apache.coyote.AbstractProtocol.process(AbstractProtocol.java:905)\n\tat org.apache.tomcat.util.net.NioEndpoint.doRun(NioEndpoint.java:1743)\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)\n\tat java.base/java.lang.VirtualThread.run(Unknown Source)\nCaused by: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command\n\tat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)\n\tat org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)\n\tat org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)\n\tat org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat net.ttddyy.dsproxy.proxy.StatementProxyLogic.performProxyLogic(StatementProxyLogic.java:287)\n\tat net.ttddyy.dsproxy.proxy.ProxyLogicSupport.proceedMethodExecution(ProxyLogicSupport.java:103)\n\tat net.ttddyy.dsproxy.proxy.StatementProxyLogic.invoke(StatementProxyLogic.java:119)\n\tat net.ttddyy.dsproxy.proxy.jdk.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:37)\n\tat jdk.proxy2/jdk.proxy2..executeQuery(Unknown Source)\n\tat org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:732)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)\n\t... 82 common frames omitted\n",
  "ecs.version": "8.11"
}
{
  "@timestamp": "2025-03-24T02:05:58.706140580Z",
  "log.level": "ERROR",
  "process.pid": 1,
  "process.thread.name": "tomcat-handler-707",
  "service.name": "room-reservation",
  "service.version": "0.0.1.7e728adbff9781d0f4a864cc98c202402226f82b",
  "log.logger": "org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[\/].[dispatcherServlet]",
  "message": "Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction] with root cause",
  "error.type": "java.net.ConnectException",
  "error.message": "Connection refused",
  "error.stack_trace": "java.net.ConnectException: Connection refused\n\tat java.base\/sun.nio.ch.Net.pollConnect(Native Method)\n\tat java.base\/sun.nio.ch.Net.pollConnectNow(Unknown Source)\n\tat java.base\/sun.nio.ch.NioSocketImpl.timedFinishConnect(Unknown Source)\n\tat java.base\/sun.nio.ch.NioSocketImpl.connect(Unknown Source)\n\tat java.base\/java.net.SocksSocketImpl.connect(Unknown Source)\n\tat java.base\/java.net.Socket.connect(Unknown Source)\n\tat org.postgresql.core.PGStream.createSocket(PGStream.java:260)\n\tat org.postgresql.core.PGStream.<init>(PGStream.java:121)\n\tat org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:140)\n\tat org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:268)\n\tat org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)\n\tat org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273)\n\tat org.postgresql.Driver.makeConnection(Driver.java:446)\n\tat org.postgresql.Driver.access$100(Driver.java:63)\n\tat org.postgresql.Driver$ConnectThread.run(Driver.java:355)\n\tat java.base\/java.lang.Thread.run(Unknown Source)\n",
  "ecs.version": "8.11"
}

一つ目のエラーはSQL実行中のコネクションがDB側で突然切られたことによるエラーです。いわゆる"瞬断"です。アクセス中のPrimaryノードが再起動したことによって発生したエラーだと思われます。
二つ目のエラーはPostgreSQLに接続できないというエラーです。おそらくRead ReplicaがPrimaryに昇格するまでの間このエラーが発生したと思われます。

次のコマンドで負荷テストの結果をグラフ化します。

cat results.bin | vegeta plot > plot.html

2回のタイミングでエラーが発生しているので、

primaryが切断
read replicaがprimaryに昇格
昇格したprimaryが切断
read replicaがprimaryに昇格

のタイミングでエラーが発生していたと思われます。これでもpg_auto_failoverよりもフェースオーバーの時間がかなり短くなった印象です。

Read Replicaに対するRoutingを設定

アプリのダウンタイムをより短くするため、参照形のアクセスはRead Replicaにroutingされるようにアプリ側を設定します。

このアプリには次の定義が含まれています。

spring.datasource.*プロパティ同様にread-replica.datasource.*プロパティを設定すると@Transactional(readOnly = true)アノテーションが設定されたコードはRead Replicaにアクセスするようになります。

そこでspring.datasource.*プロパティは引き続きSpring Cloud Bindingで自動設定させつつ、

read-replica.datasource.url
read-replica.datasource.username
read-replica.datasource.password

プロパティはread-onlyなエンドポイントに対する接続情報が設定されるように環境変数を設定します。

cat <<'EOF' > /tmp/room-reservation.yaml
---
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: room-reservation
  name: room-reservation
spec:
  replicas: 1
  selector:
    matchLabels:
      app: room-reservation
  template:
    metadata:
      labels:
        app: room-reservation
    spec:
      containers:
      - name: room-reservation
        image: ghcr.io/categolj/room-reservation:jvm
        imagePullPolicy: Always
        ports:
        - containerPort: 8080
        env:
        - name: SERVICE_BINDING_ROOT
          value: /bindings
        - name: read-replica.datasource.url
          value: jdbc:postgresql://${k8s.bindings.room-reservation-db.database}-read-only:${k8s.bindings.room-reservation-db.port}/${k8s.bindings.room-reservation-db.database}
        - name: read-replica.datasource.username
          value: ${spring.datasource.username}
        - name: read-replica.datasource.password
          value: ${spring.datasource.password}
        - name: jdbc.datasource-proxy.enabled
          value: "false"
        - name: spring.datasource.hikari.data-source-properties.sslmode
          value: require
        - name: logging.structured.format.console
          value: ecs
        resources:
          limits:
            memory: 786Mi
          requests:
            memory: 786Mi
        securityContext:
          allowPrivilegeEscalation: false
          capabilities:
            drop:
            - ALL
          runAsNonRoot: true
          runAsUser: 1002
          seccompProfile:
            type: RuntimeDefault
        volumeMounts:
        - name: room-reservation-db
          mountPath: /bindings/room-reservation-db
          readOnly: true
        readinessProbe:
          httpGet:
            path: /actuator/health/readiness
            port: 8080
            scheme: HTTP
        livenessProbe:
          httpGet:
            path: /actuator/health/liveness
            port: 8080
            scheme: HTTP
      volumes:
      - name: room-reservation-db
        secret:
          secretName: room-reservation-db-app-user-db-secret
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: room-reservation
  name: room-reservation
spec:
  ports:
  - name: http
    port: 8080
    targetPort: 8080
  selector:
    app: room-reservation
---
EOF

kubectl apply -f /tmp/room-reservation.yaml -n demo

Tip

このアプリはdatasource-proxyを使用していましたが、データソースのroutingが想定通りに動作しなかったため、jdbc.datasource-proxy.enabled=falseを設定してProxyを無効にしました。

設定変更後に、再びvegetaで負荷を与えます。

echo "GET http://localhost:8080/api/reservations?date=2025-03-24&roomId=018422b2-4843-7a62-935b-b4e65649de3f" | vegeta attack -duration=80s | tee results.bin | vegeta report

別のターミナルでPatroniクラスタを再起動します。

kubectl rollout restart -n demo sts/room-reservation-db

vegetaの負荷テスト結果は次の通りです。500エラーが1件になりました。

Requests      [total, rate, throughput]         4000, 50.01, 50.00
Duration      [total, attack, wait]             1m20s, 1m20s, 5.527ms
Latencies     [min, mean, 50, 90, 95, 99, max]  1.072ms, 4.315ms, 3.317ms, 7.783ms, 8.597ms, 11.599ms, 111.286ms
Bytes In      [total, mean]                     943881, 235.97
Bytes Out     [total, mean]                     0, 0.00
Success       [ratio]                           99.98%
Status Codes  [code:count]                      200:3999  500:1  
Error Set:
500

グラフは次の通りです。

次のエラーログを確認できました。

{
  "@timestamp": "2025-03-24T03:03:33.938327024Z",
  "log.level": "WARN",
  "process.pid": 1,
  "process.thread.name": "tomcat-handler-1244",
  "service.name": "room-reservation",
  "service.version": "0.0.1.7e728adbff9781d0f4a864cc98c202402226f82b",
  "log.logger": "com.zaxxer.hikari.pool.ProxyConnection",
  "message": "read-replica-pool - Connection org.postgresql.jdbc.PgConnection@6cfd08e9 marked as broken because of SQLSTATE(57P01), ErrorCode(0)",
  "traceId": "67e0cb8565fa49f08358a19f9c975409",
  "spanId": "3355110873e2ed7d",
  "error.type": "org.postgresql.util.PSQLException",
  "error.message": "FATAL: terminating connection due to administrator command",
  "error.stack_trace": "org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command\n\tat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)\n\tat org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)\n\tat org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)\n\tat org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)\n\tat org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218)\n\tat org.springframework.jdbc.core.simple.DefaultJdbcClient$DefaultStatementSpec$NamedParamMappedQuerySpec.list(DefaultJdbcClient.java:366)\n\tat com.example.reservation.query.ReservationViewRepository.findByRoomIdAndDate(ReservationViewRepository.java:49)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.query.ReservationViewRepository$$SpringCGLIB$$0.findByRoomIdAndDate(<generated>)\n\tat com.example.reservation.web.ReservationController.findReservation(ReservationController.java:49)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.modulith.observability.ModuleEntryInterceptor.invoke(ModuleEntryInterceptor.java:91)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.web.ReservationController$$SpringCGLIB$$0.findReservation(<generated>)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:191)\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891)\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.zalando.logbook.servlet.LogbookFilter.doFilter(LogbookFilter.java:76)\n\tat org.zalando.logbook.servlet.HttpFilter.doFilter(HttpFilter.java:32)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:114)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)\n\tat org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:731)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)\n\tat java.base\/java.lang.VirtualThread.run(Unknown Source)\n",
  "ecs.version": "8.11"
}
{
  "@timestamp": "2025-03-24T03:03:33.944662167Z",
  "log.level": "ERROR",
  "process.pid": 1,
  "process.thread.name": "tomcat-handler-1244",
  "service.name": "room-reservation",
  "service.version": "0.0.1.7e728adbff9781d0f4a864cc98c202402226f82b",
  "log.logger": "org.springframework.transaction.interceptor.TransactionInterceptor",
  "message": "Application exception overridden by rollback exception",
  "traceId": "67e0cb8565fa49f08358a19f9c975409",
  "spanId": "3355110873e2ed7d",
  "error.type": "org.springframework.dao.DataAccessResourceFailureException",
  "error.message": "PreparedStatementCallback; SQL [SELECT reservation_id,\n    room_id,\n    date,\n    start_time,\n    end_time,\n    purpose,\n    user_id\nFROM reservation_view\nWHERE room_id = ? AND date = ?]; FATAL: terminating connection due to administrator command",
  "error.stack_trace": "org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [SELECT reservation_id,\n    room_id,\n    date,\n    start_time,\n    end_time,\n    purpose,\n    user_id\nFROM reservation_view\nWHERE room_id = ? AND date = ?]; FATAL: terminating connection due to administrator command\n\tat org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:121)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)\n\tat org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)\n\tat org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)\n\tat org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218)\n\tat org.springframework.jdbc.core.simple.DefaultJdbcClient$DefaultStatementSpec$NamedParamMappedQuerySpec.list(DefaultJdbcClient.java:366)\n\tat com.example.reservation.query.ReservationViewRepository.findByRoomIdAndDate(ReservationViewRepository.java:49)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.query.ReservationViewRepository$$SpringCGLIB$$0.findByRoomIdAndDate(<generated>)\n\tat com.example.reservation.web.ReservationController.findReservation(ReservationController.java:49)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.modulith.observability.ModuleEntryInterceptor.invoke(ModuleEntryInterceptor.java:91)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.web.ReservationController$$SpringCGLIB$$0.findReservation(<generated>)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:191)\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891)\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.zalando.logbook.servlet.LogbookFilter.doFilter(LogbookFilter.java:76)\n\tat org.zalando.logbook.servlet.HttpFilter.doFilter(HttpFilter.java:32)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:114)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)\n\tat org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:731)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)\n\tat java.base\/java.lang.VirtualThread.run(Unknown Source)\nCaused by: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command\n\tat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)\n\tat org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)\n\tat org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)\n\tat org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)\n\t... 82 common frames omitted\n",
  "ecs.version": "8.11"
}
{
  "@timestamp": "2025-03-24T03:03:33.947225216Z",
  "log.level": "ERROR",
  "process.pid": 1,
  "process.thread.name": "tomcat-handler-1244",
  "service.name": "room-reservation",
  "service.version": "0.0.1.7e728adbff9781d0f4a864cc98c202402226f82b",
  "log.logger": "org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[\/].[dispatcherServlet]",
  "message": "Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: java.lang.reflect.UndeclaredThrowableException] with root cause",
  "error.type": "java.sql.SQLException",
  "error.message": "Connection is closed",
  "error.stack_trace": "java.sql.SQLException: Connection is closed\n\tat com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:503)\n\tat jdk.proxy3\/jdk.proxy3.$Proxy107.rollback(Unknown Source)\n\tat com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:386)\n\tat com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat com.example.config.ReadOnlyTransactionRoutingDataSource.lambda$getConnection$0(ReadOnlyTransactionRoutingDataSource.java:38)\n\tat jdk.proxy2\/jdk.proxy2.$Proxy106.rollback(Unknown Source)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:443)\n\tat jdk.proxy2\/jdk.proxy2.$Proxy108.rollback(Unknown Source)\n\tat org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:352)\n\tat org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:897)\n\tat org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:866)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:716)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:384)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.query.ReservationViewRepository$$SpringCGLIB$$0.findByRoomIdAndDate(<generated>)\n\tat com.example.reservation.web.ReservationController.findReservation(ReservationController.java:49)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.modulith.observability.ModuleEntryInterceptor.invoke(ModuleEntryInterceptor.java:91)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)\n\tat com.example.reservation.web.ReservationController$$SpringCGLIB$$0.findReservation(<generated>)\n\tat java.base\/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)\n\tat java.base\/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:191)\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891)\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)\n\tat jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.zalando.logbook.servlet.LogbookFilter.doFilter(LogbookFilter.java:76)\n\tat org.zalando.logbook.servlet.HttpFilter.doFilter(HttpFilter.java:32)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:114)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)\n\tat org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:731)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)\n\tat java.base\/java.lang.VirtualThread.run(Unknown Source)\n",
  "ecs.version": "8.11"
}

アクセスしているコネクションが再起動によって切断された"瞬断"が一件発生しただけのようです。ちなみにこれはretryで救済可能です。
read replicaへのコネクションの復旧は瞬時に行われているようです。

参照系のみですが、ダウンタイムはかなり改善されました。

Patroniを使ったTanzu for Postgres on Kubernetesを試しました。アプリ側の設定次第ではPostgresアップデート時のダウンタイムをかなり小さく抑えられそうです。

IK.AM