--- title: PatroniをサポートしたTanzu for Postgres on Kubernetes 4.0をインストールしてSpring Bootアプリからアクセスするメモ tags: ["Kubernetes", "Tanzu", "PostgreSQL", "Spring Boot", "Helm", "Patroni"] categories: ["Dev", "CaaS", "Kubernetes", "TanzuSQL", "PostgreSQL"] date: 2025-03-24T06:30:01Z updated: 2025-03-24T08:17:17Z --- [Tanzu for Postgres on Kubernetes 4.0](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-for-postgres-on-kubernetes/4-0/tnz-postgres-k8s/release-notes.html)からついにHigh Availabilityの実現に[Patroni](https://github.com/patroni/patroni)が使用されるようになりました。 3.0までは[pg_auto_failover](https://pg-auto-failover.readthedocs.io/en/main/intro.html)が使用されていました。 試してみたいと思います。 **目次** ### Tanzu for Postgres on Kubernetes Docker Registryのアクセストークン取得 まずはこのHelmチャートが配布されているDocker Registryにアクセスするためのアクセストークンを取得します。 [Broadcom Support](https://support.broadcom.com/)にログインして、"My Downloads"を選択し、"postgres"で検索します。 検索結果から"VMware Tanzu for Postgres on Kubernetes"をクリックします。 image バージョン"4.0.0"の"Token Download"アイコンをクリックします。 image 次のようなダイアログが現れます。 image 下にスクロールするとRegistryの情報の記載が見えます。 image 次のコマンドで`tanzu-sql-postgres.packages.broadcom.com`にログインします。認証情報を自分のものに置き換えてください。 ```bash 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できます。 ```bash 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`にログインします。認証情報を自分のものに置き換えてください。 ```bash REGISTRY_USERNAME=username REGISTRY_PASSWORD=changme docker login registry.example.com -u ${REGISTRY_USERNAME} -p ${REGISTRY_PASSWORD} ``` リロケートには[imgpkg](https://carvel.dev/imgpkg/)を使用します。 ```bash 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を確認します。 ```yaml $ 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//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//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`にリロケートします。 ```bash 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を作成します。 ```bash 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](https://github.com/cert-manager)が必要なので、cert-managerが未インストールの場合は次のコマンドでインストールします。 ```bash kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.17.0/cert-manager.yaml ``` 次のコマンドでPostgres Operatorをインストールします。リロケート後のChartとコンテナイメージを指定します。 ```bash 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`でインストール状態を確認できます。 ```bash $ 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の状態を確認します。 ```bash $ kubectl get pod -n postgres-system NAME READY STATUS RESTARTS AGE postgres-operator-5986868f44-qlswf 1/1 Running 0 2m6s ``` 次のコマンドで、このPostgres Operatorで利用可能なカスタムリソースを確認します。 ```bash $ 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のバージョンを確認します。 ```bash $ 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を作成します。 ```bash 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インスタンスを作成します。 ```yaml cat < /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 ``` しばらくすると次のリソースが作成されます。 ```bash $ 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 5432/TCP 85s service/demo-agent ClusterIP None 85s service/demo-read-only ClusterIP 192.168.194.192 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 84s persistentvolumeclaim/demo-pgdata-demo-1 Bound pvc-e4894901-17d4-45cc-a0c2-68ba04842eae 1Gi RWO local-path 84s ``` 作成したPostgresインスタンスにコンテナの中から`psql`でアクセスします。 ```bash $ 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](https://github.com/pgvector/pgvector)やUberで使われている地理インデックスシステムである[h3](https://github.com/zachasme/h3-pg)などが利用可能です。 ``` 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であることがわかります。 ```bash $ 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に増やします。 ```yaml cat < /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を増やしている途中です。 ```bash $ 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 5432/TCP 5m23s service/demo-agent ClusterIP None 5m23s service/demo-read-only ClusterIP 192.168.194.192 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 5m22s persistentvolumeclaim/demo-pgdata-demo-1 Bound pvc-e4894901-17d4-45cc-a0c2-68ba04842eae 1Gi RWO local-path 5m22s persistentvolumeclaim/demo-pgdata-demo-2 Bound pvc-bb3e5ac3-4891-420f-b841-56bb2c19848a 1Gi RWO local-path 18s ``` 途中の状態ではPatroniは次の状態になっています。 ```bash $ 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 | +--------+------------------------------------------+--------------+------------------+----+-----------+ ``` しばらくするとクラスタの更新が完了します。 ```bash $ 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 5432/TCP 8m28s service/demo-agent ClusterIP None 8m28s service/demo-read-only ClusterIP 192.168.194.192 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 8m27s persistentvolumeclaim/demo-pgdata-demo-1 Bound pvc-e4894901-17d4-45cc-a0c2-68ba04842eae 1Gi RWO local-path 8m27s persistentvolumeclaim/demo-pgdata-demo-2 Bound pvc-bb3e5ac3-4891-420f-b841-56bb2c19848a 1Gi RWO local-path 3m23s ``` Patroniクラスタは次の状態になっています。 ```bash $ 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を確認します。 ```bash $ 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 demo-1 4/4 Running 0 10m 192.168.194.90 orbstack demo-2 4/4 Running 0 5m20s 192.168.194.93 orbstack ``` クラスタの外から、このクラスタにアクセスしてみます。次のコマンドで接続情報を確認します。 ```bash $ 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`を使用する準備をします。 ```bash $ 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なエンドポイントにアクセスします。 ```bash 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します。 ```sql 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なエンドポイントに接続します。 ```bash 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します。 ```bash pg-client:/# exit ``` 作成したPostgresインスタンスを一旦削除します。 ```bash kubectl delete -f /tmp/demo-postgres.yaml -n demo ``` ### サンプルアプリのデプロイ 次にPostgresインスタンスにアプリから接続してみます。ここではサンプルアプリとして https://github.com/categolj/room-reservation を使用します。このアプリの80%はAI Agentによって作成されました;) 動作確認したrevisionは[`7e728adbff9781d0f4a864cc98c202402226f82b`](https://github.com/categolj/room-reservation/tree/7e728adbff9781d0f4a864cc98c202402226f82b)です。 次のコマンドでこのアプリ向けのPostgresインスタンスを作成します。 ```yaml cat < /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インスタンスが作成されたことを確認します。 ```bash $ 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](https://github.com/servicebinding/spec)に対応しているので、[Spring Cloud Bindings](https://github.com/spring-cloud/spring-cloud-bindings)により、マウントした情報から自動で`spring.datasource.*`プロパティを設定します。 ```yaml 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が作成されます。 ```bash $ 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 8080/TCP 94s service/room-reservation-db ClusterIP 192.168.194.145 5432/TCP 4m17s service/room-reservation-db-agent ClusterIP None 4m17s service/room-reservation-db-read-only ClusterIP 192.168.194.180 5432/TCP 4m17s ``` Serviceに対するport-forwardingでこのアプリにアクセスします。 ```bash kubectl port-forward -n demo svc/room-reservation 8080:8080 ``` Image 部屋の予約ができることを確認します。 Image この部屋の予約情報を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`](https://github.com/tsenart/vegeta)を使って負荷をかけてみます。`vegeta`は次のコマンドでインストールできます。 ```bash 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秒負荷をかけます。 ```bash 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クラスタを再起動します。 ```bash 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 ``` 次のようなエラーログを確認できました。 ```json { "@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()\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()\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.(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.(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に昇格するまでの間このエラーが発生したと思われます。 次のコマンドで負荷テストの結果をグラフ化します。 ```bash cat results.bin | vegeta plot > plot.html ``` Image 2回のタイミングでエラーが発生しているので、 1. primaryが切断 2. read replicaがprimaryに昇格 3. 昇格したprimaryが切断 4. read replicaがprimaryに昇格 のタイミングでエラーが発生していたと思われます。これでもpg_auto_failoverよりもフェースオーバーの時間がかなり短くなった印象です。 ### Read Replicaに対するRoutingを設定 アプリのダウンタイムをより短くするため、参照形のアクセスはRead Replicaにroutingされるようにアプリ側を設定します。 このアプリには次の定義が含まれています。 * https://github.com/categolj/room-reservation/blob/7e728adbff9781d0f4a864cc98c202402226f82b/src/main/java/com/example/config/ReadOnlyTransactionRoutingDataSource.java * https://github.com/categolj/room-reservation/blob/7e728adbff9781d0f4a864cc98c202402226f82b/src/main/java/com/example/config/DataSourceConfig.java `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なエンドポイントに対する接続情報が設定されるように環境変数を設定します。 ```yaml 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](https://jdbc-observations.github.io/datasource-proxy/docs/snapshot/user-guide/index.html)を使用していましたが、データソースのroutingが想定通りに動作しなかったため、`jdbc.datasource-proxy.enabled=false`を設定してProxyを無効にしました。 設定変更後に、再びvegetaで負荷を与えます。 ```bash 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クラスタを再起動します。 ```bash 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 ``` グラフは次の通りです。 Image 次のエラーログを確認できました。 ```json { "@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()\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()\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()\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()\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()\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()\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アップデート時のダウンタイムをかなり小さく抑えられそうです。