Skip to content
Hands-on IT IT Drills

Day 16: CNPG: High Availability

Goal: You measure how long pg-notes is not writable during a Pod failure, a node failure, a switchover and maintenance, enable synchronous replication and fencing, roll out configuration and a minor update, and upgrade a cluster from 17 to 18. · Duration: approx. 90 min · Prerequisite: Day 10, Day 15

Briefing

  • Failover: unplanned, the operator promotes the replica with the most recent LSN. Switchover: planned, first shut down cleanly, then promote.
  • The yardstick is not “Pod is running”, but: how long do INSERTs fail?
  • CNPG rolls out every change that needs a restart: replicas first, the primary last – via restart or switchover (primaryUpdateMethod).
  • Since CNPG 1.26, a major upgrade works in place with pg_upgrade --link – offline.
mkdir -p ~/pg/tag16 && cp ~/.kube/config ~/pg/tag16/.kc
export KUBECONFIG=~/pg/tag16/.kc
kubectl create namespace tag16

Drill 1: Build the measuring device

A Pod schreiber inserts a row every 0.2 s; the database itself writes the timestamp and the server IP. Gaps in the table = downtime. You need the client Pod from Day 14 for the analysis:

kubectl apply -f ~/pg/tag14/psql-client.yaml
kubectl wait --for=condition=Ready pod/psql-client -n tag14
kubectl exec -n tag14 psql-client -- psql -c "CREATE TABLE ha_test (
  id bigserial PRIMARY KEY, t timestamptz NOT NULL DEFAULT clock_timestamp(),
  srv inet DEFAULT inet_server_addr())"
cat > ~/pg/tag16/schreiber.yaml <<'EOF'
apiVersion: v1
kind: Pod
metadata:
  name: schreiber
  namespace: tag14
spec:
  containers:
  - name: psql
    image: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
    command:
    - sh
    - -c
    - while true; do psql -Atqc "INSERT INTO ha_test DEFAULT VALUES"; sleep 0.2; done
    env:
    - name: PGCONNECT_TIMEOUT
      value: "2"
    - name: PGHOST
      valueFrom: {secretKeyRef: {name: pg-notes-app, key: host}}
    - name: PGDATABASE
      valueFrom: {secretKeyRef: {name: pg-notes-app, key: dbname}}
    - name: PGUSER
      valueFrom: {secretKeyRef: {name: pg-notes-app, key: username}}
    - name: PGPASSWORD
      valueFrom: {secretKeyRef: {name: pg-notes-app, key: password}}
    resources:
      requests: {cpu: 10m, memory: 16Mi}
      limits: {memory: 64Mi}
EOF
kubectl apply -f ~/pg/tag16/schreiber.yaml

Expected:

pod/psql-client created
pod/psql-client condition met
CREATE TABLE
pod/schreiber created

You need the analysis in every drill – two queries, each time preceded by TRUNCATE ha_test:

kubectl exec -n tag14 psql-client -- psql -c "SELECT srv, count(*),
  min(t)::time(1), max(t)::time(1) FROM ha_test GROUP BY srv ORDER BY 3" \
  -c "SELECT t::time(1) AS nach, gap FROM (SELECT t, t - lag(t) OVER (ORDER BY t)
  AS gap FROM ha_test) x ORDER BY gap DESC NULLS LAST LIMIT 1"

Warning: Don’t use kubectl exec … while … as the write loop: exec goes through the kubelet. If the kubelet fails in Drill 3, the session aborts with websocket: close 1006 – and the measurement with it.

Save your progress? Sign up for free

Drill 2: Failover on Pod failure

kubectl exec -n tag14 psql-client -- psql -qc 'TRUNCATE ha_test'
kubectl get cluster pg-notes -n tag14
P=$(kubectl get cluster pg-notes -n tag14 -o jsonpath='{.status.currentPrimary}')
kubectl delete pod $P -n tag14 --wait=false
while sleep 2; do echo "$(date +%T) $(kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}')"; done | uniq -f1

Ctrl+C after about 20 s, then run the analysis.

Expected (abridged; your Pod names, IPs and timeline may differ):

NAME       AGE   INSTANCES   READY   STATUS                     PRIMARY
pg-notes   45m   3           3       Cluster in healthy state   pg-notes-1
pod "pg-notes-1" deleted from tag14 namespace
05:36:13 pg-notes-1 Cluster in healthy state
05:36:15 pg-notes-1 Failing over
05:36:25 pg-notes-3 Failing over
05:36:27 pg-notes-3 Waiting for the instances to become active
    srv    | count |    min     |    max
-----------+-------+------------+------------
 10.0.1.54 |    14 | 05:36:08.3 | 05:36:13.8
 10.0.1.57 |    60 | 05:36:25.7 | 05:36:53.4
    nach    |       gap
------------+-----------------
 05:36:25.7 | 00:00:11.887219

About 12 s without writes. kubectl logs schreiber -n tag14 shows the errors during the gap: FATAL: the database system is shutting down, then Connection refused, until -rw points to the new primary.

Save your progress? Sign up for free

Drill 3: Failover on node failure

A dead node no longer reports anything. Simulated: stop the kubelet and kill the postgres container hard. On cp1:

kubectl exec -n tag14 psql-client -- psql -qc 'TRUNCATE ha_test'
P=$(kubectl get cluster pg-notes -n tag14 -o jsonpath='{.status.currentPrimary}')
N=$(kubectl get pod $P -n tag14 -o jsonpath='{.spec.nodeName}')
NIP=$(kubectl get node $N -o jsonpath='{.status.addresses[0].address}')
echo $P $N $NIP
kubectl get pods -n tag14 -o wide | grep pg-notes

Expected (abridged; your Pod names, IPs and timeline may differ):

pg-notes-2 worker2 172.16.0.83
pg-notes-1    2/2     Running   0     ...   worker1   ...
pg-notes-2    2/2     Running   2     ...   worker2   ...
pg-notes-3    2/2     Running   0     ...   worker1   ...

The primary is alone on its node – exactly the case we want here (more on the opposite case shortly):

ssh $NIP "sudo systemctl stop kubelet && sudo crictl stop \
  \$(sudo crictl ps --name '^postgres\$' --label io.kubernetes.pod.name=$P -q)"
while sleep 3; do echo "$(date +%T) $(kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}')"; done | uniq -f1

Expected (abridged, Ctrl+C after about 90 s; names may differ):

29450d03f010...
05:47:05 pg-notes-2 Cluster in healthy state
05:47:55 pg-notes-2 Failing over
05:48:17 pg-notes-1 Failing over

For about 50 s nothing happens: that’s how long Kubernetes waits until the node is NotReady and the Pods there become Ready=False. Only then does the operator promote. The phase stays Failing over until the old primary is reachable again. The analysis now runs through an instance on the healthy node (psql-client may be on the dead one):

kubectl exec -n tag14 pg-notes-1 -c postgres -- psql -d notes \
  -c "SELECT t::time(1) AS nach, gap FROM (SELECT t, t - lag(t) OVER (ORDER BY t)
  AS gap FROM ha_test) x ORDER BY gap DESC NULLS LAST LIMIT 1" \
  -c 'SELECT timeline_id FROM pg_control_checkpoint()'
ssh $NIP sudo systemctl start kubelet

Expected (abridged; your timeline may differ):

    nach    |       gap
------------+-----------------
 05:48:19.7 | 00:01:14.471558
 timeline_id
-------------
           6

1:14 min without writes. After the kubelet starts, the old primary comes back as a replica after about 20 s (Cluster in healthy state).

Warning: If the primary and a replica are on the dead node, CNPG waits longer. The operator log then only shows Cannot extract Pod status and Waiting for the Kubelet to refresh the readiness probe – it cannot check the LSN of the lost replica. The failover only follows once Kubernetes evicts the Pods after 5 min (tolerationSeconds: 300). In the lab: a 6:18 min gap. With only two workers this is unavoidable; in production, three instances belong on three nodes.

Save your progress? Sign up for free

Drill 4: Switchover

A planned change, for example before maintenance:

kubectl exec -n tag14 psql-client -- psql -qc 'TRUNCATE ha_test'
kubectl cnpg promote pg-notes pg-notes-2 -n tag14
kubectl cnpg status pg-notes -n tag14 | grep -E 'Primary instance|Timeline'

Expected (abridged, analysis after about 30 s):

Node pg-notes-2 in cluster pg-notes will be promoted
Primary instance:        pg-notes-2
Current Write LSN:       0/1000DAE8 (Timeline: 7 - WAL File: 000000070000000000000010)
    srv     | count |    min     |    max
------------+-------+------------+------------
 10.0.1.145 |     9 | 05:50:24.4 | 05:50:27.8
 10.0.2.14  |    49 | 05:50:40.5 | 05:51:02.2
    nach    |       gap
------------+-----------------
 05:50:40.5 | 00:00:12.758206

A switchover also costs about 13 s here: the old primary shuts down (fast, time limit switchoverDelay), the replica fetches the last WAL, gets promoted, and -rw is repointed. In return, no confirmed commit is lost. The timeline increases with every change.

Save your progress? Sign up for free

Drill 5: Synchronous replication and fencing

You must not set synchronous_standby_names yourself; postgresql.synchronous does that:

kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"synchronous":{"method":"any","number":1}}}}'
kubectl cnpg psql pg-notes -n tag14 -- -Atc 'SHOW synchronous_standby_names' \
  -c 'SELECT application_name, sync_state FROM pg_stat_replication ORDER BY 1'

Expected (after about 15 s, without a restart):

cluster.postgresql.cnpg.io/pg-notes patched
ANY 1 ("pg-notes-1","pg-notes-3","pg-notes-2")
pg-notes-1|quorum
pg-notes-3|quorum

any = quorum: a commit waits until any one replica has the WAL. Fencing stops PostgreSQL in an instance, and the Pod stays around for investigation. Fence both replicas:

kubectl cnpg fencing on pg-notes pg-notes-1 -n tag14
kubectl cnpg fencing on pg-notes pg-notes-3 -n tag14
kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.metadata.annotations.cnpg\.io/fencedInstances}{"\n"}'
time kubectl exec -n tag14 psql-client -- psql -c 'INSERT INTO ha_test DEFAULT VALUES'

Expected (abridged): the INSERT hangs.

pg-notes-1 fenced
pg-notes-3 fenced
["pg-notes-1","pg-notes-3"]

In a second terminal on cp1:

export KUBECONFIG=~/pg/tag16/.kc
kubectl get cluster pg-notes -n tag14
kubectl cnpg psql pg-notes -n tag14 -- -Atc "SELECT wait_event, now() - query_start,
  left(query, 40) FROM pg_stat_activity WHERE wait_event = 'SyncRep'"
kubectl exec -n tag14 pg-notes-1 -c postgres -- pg_isready
kubectl cnpg fencing off pg-notes pg-notes-3 -n tag14

Expected (abridged):

NAME       AGE   INSTANCES   READY   STATUS                     PRIMARY
pg-notes   62m   3           1       Cluster in healthy state   pg-notes-2
SyncRep|00:02:11.207495|INSERT INTO ha_test DEFAULT VALUES
SyncRep|00:02:00.210755|INSERT INTO ha_test DEFAULT VALUES
/controller/run:5432 - no response
command terminated with exit code 2
pg-notes-3 unfenced

Both writers wait on SyncRep. As soon as pg-notes-3 is back, the first terminal shows INSERT 0 1 with real 2m22s. The cluster reports “healthy” despite only one ready instance – fencing is intentional. This is dataDurability: required (the default): better to stand still than to risk data. The alternative:

kubectl cnpg fencing off pg-notes pg-notes-1 -n tag14
kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"synchronous":{"dataDurability":"preferred"}}}}'
kubectl cnpg fencing on pg-notes pg-notes-1 -n tag14
kubectl cnpg fencing on pg-notes pg-notes-3 -n tag14

Wait about 30 s – that’s how long the operator needs to take the fenced replicas off the required list – then:

time kubectl exec -n tag14 psql-client -- psql -c 'INSERT INTO ha_test DEFAULT VALUES'
kubectl cnpg fencing off pg-notes pg-notes-1 -n tag14
kubectl cnpg fencing off pg-notes pg-notes-3 -n tag14

Expected (abridged):

INSERT 0 1

real    0m5.165s

With preferred, the operator takes replicas that aren’t ready off the required list; writing continues, without a synchronous copy if necessary. Back to asynchronous – the following days assume it:

kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"synchronous":null}}}'
kubectl cnpg psql pg-notes -n tag14 -- -Atc \
  'SELECT application_name, sync_state FROM pg_stat_replication'

Expected:

cluster.postgresql.cnpg.io/pg-notes patched
pg-notes-1|async
pg-notes-3|async

Save your progress? Sign up for free

Drill 6: Rolling update of the configuration

A parameter that only needs a reload is active immediately:

kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"parameters":{"work_mem":"8MB"}}}}'
kubectl cnpg psql pg-notes -n tag14 -- -Atc 'SHOW work_mem'
kubectl get pods -n tag14 | grep pg-notes

Expected (abridged):

cluster.postgresql.cnpg.io/pg-notes patched
8MB
pg-notes-1    2/2     Running   1 (6m11s ago)   11m
pg-notes-2    2/2     Running   2 (9m36s ago)   43m
pg-notes-3    2/2     Running   0               11m

max_connections needs a restart. The default is primaryUpdateMethod: restart (the primary simply restarts, as you saw on Day 15). With switchover, the primary first moves to an already updated replica:

kubectl exec -n tag14 psql-client -- psql -qc 'TRUNCATE ha_test'
kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{
  "primaryUpdateMethod":"switchover",
  "postgresql":{"parameters":{"max_connections":"120"}}}}'
while sleep 3; do echo "$(date +%T) $(kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}')"; done | uniq -f1

Expected (abridged, Ctrl+C after about 90 s):

cluster.postgresql.cnpg.io/pg-notes patched
05:56:49 pg-notes-2 Cluster in healthy state
05:56:52 pg-notes-2 Applying configuration
05:56:55 pg-notes-2 Waiting for the instances to become active
05:57:18 pg-notes-2 Applying configuration
05:57:21 pg-notes-2 Waiting for the instances to become active
05:57:48 pg-notes-2 Upgrading cluster
05:57:57 pg-notes-3 Upgrading cluster
05:58:14 pg-notes-3 Cluster in healthy state
kubectl get events -n tag14 --field-selector involvedObject.kind=Cluster \
  --sort-by=.lastTimestamp | tail -3
kubectl cnpg psql pg-notes -n tag14 -- -Atc 'SHOW max_connections'

Expected (abridged, analysis: gap of about 13.6 s):

...  UpgradingInstance   cluster/pg-notes   Upgrading instance pg-notes-1
...  UpgradingInstance   cluster/pg-notes   Upgrading instance pg-notes-3
...  Switchover          cluster/pg-notes   Initiating switchover to pg-notes-3 to
                                            upgrade pg-notes-2
120

First pg-notes-1, then pg-notes-3, then the switchover, and finally the old primary. Downtime for writers: only the switchover.

Save your progress? Sign up for free

Drill 7: Node drain and PodDisruptionBudget

Two PDBs protect the cluster: pg-notes (replicas, at most one gone at a time) and pg-notes-primary (0 disruptions). A drain of the primary’s node therefore triggers a switchover. In the shared lab you only evict the CNPG Pods (--pod-selector); a real drain leaves that out:

kubectl get pods -n tag14 -o wide -L cnpg.io/instanceRole | grep pg-notes
kubectl exec -n tag14 psql-client -- psql -qc 'TRUNCATE ha_test'
kubectl drain worker1 --pod-selector cnpg.io/cluster=pg-notes \
  --delete-emptydir-data --ignore-daemonsets --timeout 90s
kubectl get pods -n tag14 -o wide | grep pg-notes
kubectl get pdb -n tag14

Pick the node with the primary.

Expected (abridged):

pg-notes-1    2/2   Running   ...   worker1   ...   replica
pg-notes-2    2/2   Running   ...   worker2   ...   replica
pg-notes-3    2/2   Running   ...   worker1   ...   primary
node/worker1 cordoned
evicting pod tag14/pg-notes-1
evicting pod tag14/pg-notes-3
error when evicting pods/"pg-notes-3" -n "tag14" (will retry after 5s): Cannot evict
pod as it would violate the pod's disruption budget.
pod/pg-notes-1 evicted
evicting pod tag14/pg-notes-3
pod/pg-notes-3 evicted
node/worker1 drained
pg-notes-1    0/2     Pending   0     ...   <none>    ...
pg-notes-2    2/2     Running   3     ...   worker2   ...
NAME               MIN AVAILABLE   MAX UNAVAILABLE   ALLOWED DISRUPTIONS   AGE
pg-notes           1               N/A               0                     70m
pg-notes-primary   1               N/A               0                     70m

The PDB holds up the eviction of pg-notes-3 until the operator has switched over to pg-notes-2 (gap in the lab: 13 s). The corresponding event: Current primary is running on unschedulable node worker1, switching over from pg-notes-3 .... pg-notes-1 stays Pending: local-path volumes are bound to the node (didn't match PersistentVolume's node affinity). Meanwhile the cluster runs with one instance. Maintenance done:

kubectl uncordon worker1
kubectl get cluster pg-notes -n tag14

Expected (after about 30 s):

node/worker1 uncordoned
NAME       AGE   INSTANCES   READY   STATUS                     PRIMARY
pg-notes   71m   3           3       Cluster in healthy state   pg-notes-2

Tip: Single-instance clusters block every drain (no switchover possible). For development clusters there is enablePDB: false.

Save your progress? Sign up for free

Drill 8: Minor and major upgrade

A test cluster with PostgreSQL 17.11 in tag16:

cat > ~/pg/tag16/pg-alt.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-alt
  namespace: tag16
spec:
  instances: 2
  imageName: ghcr.io/cloudnative-pg/postgresql:17.11-minimal-trixie
  primaryUpdateMethod: switchover
  storage:
    size: 1Gi
  resources:
    requests: {cpu: 50m, memory: 128Mi}
    limits: {memory: 256Mi}
  bootstrap:
    initdb:
      database: app
      owner: app
EOF
kubectl apply -f ~/pg/tag16/pg-alt.yaml
kubectl wait --for=condition=Ready cluster/pg-alt -n tag16 --timeout=180s
kubectl cnpg psql pg-alt -n tag16 -- -d app -c "CREATE TABLE t AS
  SELECT i, md5(i::text) FROM generate_series(1,100000) i" -Atc 'SHOW server_version'

Expected (abridged):

cluster.postgresql.cnpg.io/pg-alt created
cluster.postgresql.cnpg.io/pg-alt condition met
SELECT 100000
17.11 (Debian 17.11-1.pgdg13+2)

Minor update = new image:

kubectl patch cluster pg-alt -n tag16 --type merge \
  -p '{"spec":{"imageName":"ghcr.io/cloudnative-pg/postgresql:17.12-minimal-trixie"}}'
kubectl get pods -n tag16 -w -L cnpg.io/instanceRole

Expected (abridged, Ctrl+C after about 2 min):

pg-alt-2   1/1     Terminating       0          43s     replica
pg-alt-2   0/1     Init:0/1          0          1s
pg-alt-2   1/1     Running           0          31s     replica
pg-alt-1   1/1     Running           0          2m12s   unhealthy
pg-alt-1   0/1     Running           1 (5s ago) 2m18s   replica
pg-alt-2   1/1     Running           0          41s     primary
pg-alt-1   1/1     Terminating       1 (19s ago) 2m32s  replica
pg-alt-1   0/1     Init:0/1          0          0s
pg-alt-1   1/1     Running           0          30s     replica

Replica recreated with 17.12, switchover, old primary recreated – like Drill 6. Now the major upgrade to 18 – also just a new image (same distribution trixie, otherwise CNPG rejects it):

kubectl patch cluster pg-alt -n tag16 --type merge \
  -p '{"spec":{"imageName":"ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie"}}'
kubectl get pods -n tag16 -w

Expected (abridged, Ctrl+C after about 2 min):

pg-alt-1                       1/1     Terminating       0          62s
pg-alt-2                       1/1     Terminating       0          119s
pg-alt-2-major-upgrade-d5xhz   0/1     Init:1/2          0          2s
pg-alt-2-major-upgrade-d5xhz   1/1     Running           0          6s
pg-alt-2-major-upgrade-d5xhz   0/1     Completed         0          51s
pg-alt-2                       1/1     Running           0          12s
pg-alt-1-join-8nnjj            0/1     Completed         0          12s
pg-alt-1                       1/1     Running           0          12s

All instances stop (downtime!), a Job runs pg_upgrade --link on the primary’s PVC, and the replica is cloned anew – its PVC is fresh:

kubectl cnpg psql pg-alt -n tag16 -- -d app -Atc 'SHOW server_version' \
  -c 'SELECT count(*) FROM t' -c 'SELECT n_live_tup FROM pg_stat_user_tables'
kubectl get cluster pg-alt -n tag16 -o jsonpath='{.status.pgDataImageInfo}{"\n"}'
kubectl get pvc -n tag16

Expected (abridged):

18.7 (Debian 18.7-1.pgdg13+2)
100000
0
{"image":"ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie","majorVersion":18}
NAME       STATUS   ...   AGE
pg-alt-1   Bound    ...   83s
pg-alt-2   Bound    ...   5m55s

n_live_tup is 0 – the counters of the cumulative statistics start over. Since 18, pg_upgrade carries over the planner statistics; vacuumdb --analyze-in-stages --missing-stats-only adds missing ones (Day 13). Backups and WAL from before the upgrade are useless for PITR across the major version boundary: take a new base backup right away. If the upgrade fails, you set the old image back; the data is unchanged.

kubectl delete cluster pg-alt -n tag16

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show the current timeline of pg-notes. Target time: 15 s
  2. Fence pg-notes-3, show the annotation, lift the fence again. Target time: 45 s
  3. Restart only the replica pg-notes-1. Target time: 20 s
  4. Do a switchover to a replica of your choice. Target time: 20 s

Solution

kubectl cnpg status pg-notes -n tag14 | grep Timeline
kubectl cnpg fencing on pg-notes pg-notes-3 -n tag14
kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.metadata.annotations.cnpg\.io/fencedInstances}{"\n"}'
kubectl cnpg fencing off pg-notes pg-notes-3 -n tag14
kubectl cnpg restart pg-notes pg-notes-1 -n tag14
kubectl cnpg promote pg-notes pg-notes-3 -n tag14

Expected (abridged):

Current Write LSN:       0/15035130 (Timeline: 9 - WAL File: 000000090000000000000015)
pg-notes-3 fenced
["pg-notes-3"]
pg-notes-3 unfenced
instance pg-notes-1 restarted
Node pg-notes-3 in cluster pg-notes will be promoted

For a replica, kubectl cnpg restart deletes the Pod; for the primary, it restarts it in place. Without an instance, it rolls through the whole cluster.

Save your progress? Sign up for free

Checkpoint

pg-notes stays in place with three asynchronous instances, primaryUpdateMethod: switchover, max_connections=120 and work_mem=8MB for Days 17 to 19.

kubectl get cluster pg-notes -n tag14

Expected: 3 instances, 3 ready, Cluster in healthy state.

kubectl get cluster pg-notes -n tag14 -o jsonpath='{.spec.primaryUpdateMethod}{"\n"}'

Expected: switchover.

kubectl cnpg psql pg-notes -n tag14 -- -Atc 'SELECT sync_state FROM pg_stat_replication'

Expected: async twice.

kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.metadata.annotations.cnpg\.io/fencedInstances}{"\n"}'

Expected: empty – no instance fenced.

kubectl get nodes

Expected: all nodes Ready, none SchedulingDisabled.

Challenge

Upgrade a PostgreSQL 17 cluster to 18 without downtime for the source: create pg-17 (one instance, 17.12, database app with one table) and build a new cluster pg-18 with 18.7 from it via import.

Hint: bootstrap.initdb.import with type: microservice and an entry in externalClusters; the Secret pg-17-app is enough for access.

Solution

sed -e 's/name: pg-alt$/name: pg-17/' -e 's/17.11-minimal/17.12-minimal/' \
  -e 's/instances: 2/instances: 1/' ~/pg/tag16/pg-alt.yaml > ~/pg/tag16/pg-17.yaml
kubectl apply -f ~/pg/tag16/pg-17.yaml
kubectl wait --for=condition=Ready cluster/pg-17 -n tag16 --timeout=180s
kubectl cnpg psql pg-17 -n tag16 -- -d app -c "CREATE TABLE t AS
  SELECT i, md5(i::text) FROM generate_series(1,100000) i" \
  -c 'ALTER TABLE t OWNER TO app'
cat > ~/pg/tag16/pg-18.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-18
  namespace: tag16
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
  storage:
    size: 1Gi
  resources:
    requests: {cpu: 50m, memory: 128Mi}
    limits: {memory: 256Mi}
  bootstrap:
    initdb:
      database: app
      owner: app
      import:
        type: microservice
        databases: [app]
        source:
          externalCluster: pg-17
  externalClusters:
  - name: pg-17
    connectionParameters:
      host: pg-17-rw
      user: app
      dbname: app
      sslmode: verify-full
    sslRootCert:
      name: pg-17-ca
      key: ca.crt
    password:
      name: pg-17-app
      key: password
EOF
kubectl apply -f ~/pg/tag16/pg-18.yaml
kubectl wait --for=condition=Ready cluster/pg-18 -n tag16 --timeout=180s
kubectl cnpg psql pg-18 -n tag16 -- -d app -Atc 'SHOW server_version' \
  -c 'SELECT count(*) FROM t' \
  -c "SELECT tableowner FROM pg_tables WHERE tablename = 't'"

Expected (abridged):

cluster.postgresql.cnpg.io/pg-17 created
SELECT 100000
ALTER TABLE
cluster.postgresql.cnpg.io/pg-18 created
18.7 (Debian 18.7-1.pgdg13+2)
100000
app

The import runs with pg_dump -Fd and pg_restore --no-acl --no-owner, followed by ANALYZE. The source stays readable – but writes after the dump are missing in the target. Logical replication (Day 11) does it without any gap at all.

Save your progress? Sign up for free

Cleanup

kubectl delete cluster pg-17 pg-18 -n tag16
kubectl delete pod schreiber -n tag14
kubectl exec -n tag14 psql-client -- psql -c 'DROP TABLE ha_test'
kubectl delete pod psql-client -n tag14
kubectl delete namespace tag16

Expected (abridged):

cluster.postgresql.cnpg.io "pg-17" deleted from tag16 namespace
cluster.postgresql.cnpg.io "pg-18" deleted from tag16 namespace
pod "schreiber" deleted from tag14 namespace
DROP TABLE
pod "psql-client" deleted from tag14 namespace
namespace "tag16" deleted

Interview Note

  • “How long does a failover take with CNPG?” – A Pod failure or switchover takes about 10–15 s; a node failure adds Kubernetes’ detection time (about 50 s), in the worst case up to the Pod eviction after 5 min. Measure, don’t guess.
  • “Synchronous or asynchronous?” – Quorum any 1 loses no confirmed commit; dataDurability: required stops writers when no replica is available, preferred keeps writing.
  • “How do you do a major upgrade?” – In place via a new image (pg_upgrade --link, offline, replicas get cloned anew) or blue/green via import or logical replication; take a backup before, and run ANALYZE and a new base backup after.

Enterprise Note

  • Three instances on three nodes or zones: by default, anti-affinity is only preferred; with affinity.podAntiAffinityType: required (and topologyKey zone) a node failure never hits the primary and a replica at the same time.
  • Faster node detection via shorter tolerationSeconds or node health checks (e.g. Node Problem Detector + remediation) – with care.
  • postgresql.synchronous.failoverQuorum: true, together with synchronous replication, prevents promotions that would lose confirmed commits.
  • Updates via GitOps with primaryUpdateStrategy: supervised for critical clusters: the operator waits for your kubectl cnpg promote before touching the primary.
  • Rehearse major upgrades on a clone from the backup first (Day 15) and measure the duration of pg_upgrade plus re-cloning the replicas.