Warning
This article was automatically translated by OpenAI API (claude-sonnet-4-20250514).It may be edited eventually, but please be aware that it may contain incorrect information at this time.
Tanzu for Postgres on Kubernetes 4.0 finally started using Patroni to achieve High Availability.
Up to version 3.0, pg_auto_failover was used.
Let's give it a try.
Table of Contents
- Obtaining Access Token for Tanzu for Postgres on Kubernetes Docker Registry
- Relocating Helm Chart and Container Images to Private Registry
- Installing Postgres Operator
- Creating Postgres Instance
- Deploying Sample Application
- Configuring Routing to Read Replica
Obtaining Access Token for Tanzu for Postgres on Kubernetes Docker Registry
First, let's obtain an access token to access the Docker Registry where this Helm chart is distributed.
Log in to Broadcom Support, select "My Downloads", and search for "postgres".
Click on "VMware Tanzu for Postgres on Kubernetes" from the search results.
Click the "Token Download" icon for version "4.0.0".
The following dialog will appear.
Scroll down to see the Registry information.
Log in to tanzu-sql-postgres.packages.broadcom.com with the following command. Replace the credentials with your own.
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}
Now you can pull the Helm Chart from tanzu-sql-postgres.packages.broadcom.com with the following command.
helm pull oci://tanzu-sql-postgres.packages.broadcom.com/vmware-sql-postgres-operator --version v4.0.0 --untar --untardir /tmp
However, since this token has an expiration date and it's troublesome to rotate it every time it expires, this time we'll relocate it to a private registry registry.example.com within the internal network and use it from there instead of pulling directly from this registry.
Replace registry.example.com with your own environment.
Relocating Helm Chart and Container Images to Private Registry
Log in to registry.example.com with the following command. Replace the credentials with your own.
REGISTRY_USERNAME=username
REGISTRY_PASSWORD=changme
docker login registry.example.com -u ${REGISTRY_USERNAME} -p ${REGISTRY_PASSWORD}
We'll use imgpkg for relocation.
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
Access the relocated Helm Chart to check the configurable 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
Relocate operatorImage and postgresImage from tanzu-sql-postgres.packages.broadcom.com to registry.example.com with the following commands.
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
Installing Postgres Operator
Here we'll install the Postgres Operator in the postgres-system namespace.
The Docker Pull Secret name is regsecret by default.
Create the namespace and secret with the following commands.
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
cert-manager is required as a prerequisite for the Postgres Operator, so if cert-manager is not installed, install it with the following command.
kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.17.0/cert-manager.yaml
Install the Postgres Operator with the following command. Specify the relocated chart and container images.
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
The installation will complete after a while. You can check the installation status with 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
Check the status of the Postgres Operator Pod with the following command.
$ kubectl get pod -n postgres-system
NAME READY STATUS RESTARTS AGE
postgres-operator-5986868f44-qlswf 1/1 Running 0 2m6s
Check the custom resources available with this Postgres Operator with the following command.
$ 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
Check the available Postgres versions with the following command.
$ 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
Creating Postgres Instance
Create a Postgres instance in the demo namespace using the Postgres Operator.
Create the namespace and docker pull secret with the following commands.
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
Create the Postgres instance with the following command.
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
After a while, the following resources will be created.
$ 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
Access the created Postgres instance with psql from inside the container.
$ 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=#
The initial state is as follows.
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.
The available extensions are as follows. pgvector for vector search and h3, a geographic indexing system used by Uber, are available.
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 once.
demo=# \q
Next, check the Patroni cluster information. You can see that demo-0 is a Read Replica and demo-1 is the 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 | |
+--------+------------------------------------------+--------------+-----------+----+-----------+
Increase the number of Read Replicas to 2 with the following command.
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
Check the resources with the following command. It's still in the process of increasing replicas.
$ 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
During the intermediate state, Patroni is in the following state.
$ 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 |
+--------+------------------------------------------+--------------+------------------+----+-----------+
After a while, the cluster update completes.
$ 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
The Patroni cluster is now in the following state.
$ 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 |
+--------+------------------------------------------+--------------+-----------+----+-----------+
Check the IP of each Postgres instance with the following command.
$ 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>
Let's try accessing this cluster from outside the cluster. Check the connection information with the following command.
$ 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"
}
Prepare to use psql from outside the cluster with the following command.
$ kubectl run pg-client --rm -it --image=postgres:alpine -- bash
If you don't see a command prompt, try pressing enter.
pg-client:/#
Using the obtained connection information, access the cluster with psql as follows. First, access the read-write endpoint.
psql postgresql://pgappuser:Scor1vyN2a9u9xez1LdRM1V88f5S8u@demo.demo:5432/demo
Display the server information of the connection destination with the following command. Since the IP of demo-1 is returned, you can see that it's connected to the 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)
Execute the following SQL to insert test data.
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');
Search for the inserted data.
demo=> select organization_id,organization_name from organization;
organization_id | organization_name
-----------------+-------------------
1 | foo
2 | bar
(2 rows)
Quit once.
demo=> \q
Next, connect to the read-only endpoint.
psql postgresql://pgappuser:Scor1vyN2a9u9xez1LdRM1V88f5S8u@demo-read-only.demo:5432/demo
Display the server information of the connection destination with the following command. Since the IP of demo-0 is returned, you can see that it's connected to a 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)
Confirm that the data inserted earlier can be searched with the following command.
demo=> select organization_id,organization_name from organization;
organization_id | organization_name
-----------------+-------------------
1 | foo
2 | bar
(2 rows)
Attempting to insert results in an error. This is expected since it's 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
Exit from the container that ran psql.
pg-client:/# exit
Delete the created Postgres instance temporarily.
kubectl delete -f /tmp/demo-postgres.yaml -n demo
Deploying Sample Application
Next, let's try connecting to the Postgres instance from an application. Here we'll use https://github.com/categolj/room-reservation as a sample application. 80% of this app was created by AI Agent ;)
The confirmed revision is 7e728adbff9781d0f4a864cc98c202402226f82b.
Create a Postgres instance for this app with the following command.
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
Confirm that the Postgres instance has been created with the following command.
$ 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
Next, deploy the app. First, simply access the read-write endpoint.
Since room-reservation-db-app-user-db-secret supports the Service Binding Specification for Kubernetes, Spring Cloud Bindings automatically configures spring.datasource.* properties from the mounted information.
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
The following Pod and Service are created.
$ 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
Access this app via port-forwarding to the Service.
kubectl port-forward -n demo svc/room-reservation 8080:8080
Confirm that room reservations can be made.
Get this room reservation information via REST API. (Please change the date and roomId parameters.)
$ 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"}]
Let's put load on this API using vegeta. vegeta can be installed with the following command.
brew install vegeta
Put load with the following command.
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
It's OK if all return with status code 200.
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:
Next, let's try rolling restart of this Patroni cluster while putting load on it. Put load for 80 seconds with the following command.
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
Restart the Patroni cluster in another terminal.
kubectl rollout restart -n demo sts/room-reservation-db
The vegeta load test results are as follows. 500 errors can be seen.
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
The following error logs were confirmed.
{
"@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"
}
The first error is due to a connection being suddenly cut by the DB side during SQL execution. This is a so-called "momentary disconnection". It's thought to be an error caused by the restart of the Primary node being accessed.
The second error is an error that PostgreSQL cannot be connected to. This error probably occurred during the time when the Read Replica was being promoted to Primary.
Visualize the load test results with the following command.
cat results.bin | vegeta plot > plot.html
Since errors occurred at two timings,
- primary disconnects
- read replica promotes to primary
- promoted primary disconnects
- read replica promotes to primary
errors occurred at these timings. Even so, the failover time seems to be much shorter than with pg_auto_failover.
Configuring Routing to Read Replica
To further reduce application downtime, configure the application side so that read-only access is routed to Read Replicas.
This app includes the following definitions:
- 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
Similar to spring.datasource.* properties, when read-replica.datasource.* properties are configured, code with the @Transactional(readOnly = true) annotation will access Read Replicas.
So while continuing to auto-configure spring.datasource.* properties with Spring Cloud Binding,
read-replica.datasource.urlread-replica.datasource.usernameread-replica.datasource.password
Set environment variables so that properties are configured with connection information for read-only endpoints.
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
This app was using datasource-proxy, but datasource routing didn't work as expected, so we set jdbc.datasource-proxy.enabled=false to disable the proxy.
After the configuration change, apply load again with 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
Restart the Patroni cluster in another terminal.
kubectl rollout restart -n demo sts/room-reservation-db
The vegeta load test results are as follows. 500 errors were reduced to 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
The graph is as follows.
The following error logs were confirmed.
{
"@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"
}
It seems like only one "momentary disconnection" occurred where the connection being accessed was cut due to restart. Incidentally, this can be rescued with retry.
The recovery of connections to read replicas seems to be instantaneous.
Although it's only for read operations, downtime has been significantly improved.
I tried Tanzu for Postgres on Kubernetes using Patroni. Depending on the application-side configuration, it seems possible to significantly reduce downtime during Postgres updates.