Test and validate PostgreSQL database on AKS

Article
2025-03-28

In this article, you will perform various testing and validation steps on your newly deployed PostgreSQL database.

If you haven't already deployed PostgreSQL, follow the steps in Deploy a highly available PostgreSQL database on AKS with Azure CLI to get set up, and then you can return to this article.

Important

Open-source software is mentioned throughout AKS documentation and samples. Software that you deploy is excluded from AKS service-level agreements, limited warranty, and Azure support. As you use open-source technology alongside AKS, consult the support options available from the respective communities and project maintainers to develop a plan.

For example, the Ray GitHub repository describes several platforms that vary in response time, purpose, and support level.

Microsoft takes responsibility for building the open-source packages that we deploy on AKS. That responsibility includes having complete ownership of the build, scan, sign, validate, and hotfix process, along with control over the binaries in container images. For more information, see Vulnerability management for AKS and AKS support coverage.

Inspect the deployed PostgreSQL cluster

Validate that PostgreSQL is spread across multiple availability zones by retrieving the AKS node details using the kubectl get command.

kubectl get nodes \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE \
    --output json | jq '.items[] | {node: .metadata.name, zone: .metadata.labels."failure-domain.beta.kubernetes.io/zone"}'

Your output should resemble the following example output with the availability zone shown for each node:

{
    "node": "aks-postgres-15810965-vmss000000",
    "zone": "westus3-1"
}
{
    "node": "aks-postgres-15810965-vmss000001",
    "zone": "westus3-2"
}
{
    "node": "aks-postgres-15810965-vmss000002",
    "zone": "westus3-3"
}
{
    "node": "aks-systempool-26112968-vmss000000",
    "zone": "westus3-1"
}
{
    "node": "aks-systempool-26112968-vmss000001",
    "zone": "westus3-2"
}

Connect to PostgreSQL and create a sample dataset

In this section, you create a table and insert some data into the app database that was created in the CNPG Cluster CRD you deployed earlier. You use this data to validate the backup and restore operations for the PostgreSQL cluster.

Create a table and insert data into the app database using the following commands:

kubectl cnpg psql $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE

# Run the following PSQL commands to create a small dataset
# postgres=#

CREATE TABLE datasample (id INTEGER,name VARCHAR(255));
INSERT INTO datasample (id, name) VALUES (1, 'John');
INSERT INTO datasample (id, name) VALUES (2, 'Jane');
INSERT INTO datasample (id, name) VALUES (3, 'Alice');
SELECT COUNT(*) FROM datasample;

# Type \q to exit psql

Your output should resemble the following example output:

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
count
-------
    3
(1 row)

Connect to PostgreSQL read-only replicas

Connect to the PostgreSQL read-only replicas and validate the sample dataset using the following commands:

kubectl cnpg psql --replica $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE

#postgres=#
SELECT pg_is_in_recovery();

Example output

# pg_is_in_recovery
#-------------------
# t
#(1 row)

#postgres=#
SELECT COUNT(*) FROM datasample;

Example output

# count
#-------
#     3
#(1 row)

# Type \q to exit psql

Set up on-demand and scheduled PostgreSQL backups using Barman

Validate that the PostgreSQL cluster can access the Azure storage account specified in the CNPG Cluster CRD and that Working WAL archiving reports as OK using the following command:

kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME 1 \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

Example output

Continuous Backup status
First Point of Recoverability:  Not Available
Working WAL archiving:          OK
WALs waiting to be archived:    0
Last Archived WAL:              00000001000000000000000A   @   2024-07-09T17:18:13.982859Z
Last Failed WAL:                -

Deploy an on-demand backup to Azure Storage, which uses the AKS workload identity integration, using the YAML file with the kubectl apply command.

export BACKUP_ONDEMAND_NAME="on-demand-backup-1"

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -v 9 -f -
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: $BACKUP_ONDEMAND_NAME
spec:
  method: barmanObjectStore
  cluster:
    name: $PG_PRIMARY_CLUSTER_NAME
EOF

Validate the status of the on-demand backup using the kubectl describe command.

kubectl describe backup $BACKUP_ONDEMAND_NAME \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

Example output

Type    Reason     Age   From                   Message
 ----    ------     ----  ----                   -------
Normal  Starting   6s    cloudnative-pg-backup  Starting backup for cluster pg-primary-cnpg-r8c7unrw
Normal  Starting   5s    instance-manager       Backup started
Normal  Completed  1s    instance-manager       Backup completed

Validate that the cluster has a first point of recoverability using the following command:

kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME 1 \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

Example output

Continuous Backup status
First Point of Recoverability:  2024-06-05T13:47:18Z
Working WAL archiving:          OK

Configure a scheduled backup for every hour at 15 minutes past the hour using the YAML file with the kubectl apply command.

export BACKUP_SCHEDULED_NAME="scheduled-backup-1"

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -v 9 -f -
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: $BACKUP_SCHEDULED_NAME
spec:
  # Backup once per hour
  schedule: "0 15 * ? * *"
  backupOwnerReference: self
  cluster:
    name: $PG_PRIMARY_CLUSTER_NAME
EOF

Validate the status of the scheduled backup using the kubectl describe command.

kubectl describe scheduledbackup $BACKUP_SCHEDULED_NAME \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

View the backup files stored on Azure blob storage for the primary cluster using the az storage blob list command.

az storage blob list \
    --account-name $PG_PRIMARY_STORAGE_ACCOUNT_NAME \
    --container-name backups \
    --query "[*].name" \
    --only-show-errors

Your output should resemble the following example output, validating the backup was successful:

[
  "pg-primary-cnpg-r8c7unrw/base/20240605T134715/backup.info",
  "pg-primary-cnpg-r8c7unrw/base/20240605T134715/data.tar",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000001",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000002",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000003",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000003.00000028.backup",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000004",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000005",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000005.00000028.backup",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000006",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000007",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000008",
  "pg-primary-cnpg-r8c7unrw/wals/0000000100000000/000000010000000000000009"
]

Restore the on-demand backup to a new PostgreSQL cluster

In this section, you restore the on-demand backup you created earlier using the CNPG operator into a new instance using the bootstrap Cluster CRD. A single instance cluster is used for simplicity. Remember that the AKS workload identity (via CNPG inheritFromAzureAD) accesses the backup files, and that the recovery cluster name is used to generate a new Kubernetes service account specific to the recovery cluster.

You also create a second federated credential to map the new recovery cluster service account to the existing UAMI that has "Storage Blob Data Contributor" access to the backup files on blob storage.

Create a second federated identity credential using the az identity federated-credential create command.

export PG_PRIMARY_CLUSTER_NAME_RECOVERED="$PG_PRIMARY_CLUSTER_NAME-recovered-db"

az identity federated-credential create \
    --name $PG_PRIMARY_CLUSTER_NAME_RECOVERED \
    --identity-name $AKS_UAMI_CLUSTER_IDENTITY_NAME \
    --resource-group $RESOURCE_GROUP_NAME \
    --issuer "${AKS_PRIMARY_CLUSTER_OIDC_ISSUER}" \
    --subject system:serviceaccount:"${PG_NAMESPACE}":"${PG_PRIMARY_CLUSTER_NAME_RECOVERED}" \
    --audience api://AzureADTokenExchange

Restore the on-demand backup using the Cluster CRD with the kubectl apply command.

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -v 9 -f -
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: $PG_PRIMARY_CLUSTER_NAME_RECOVERED
spec:

  inheritedMetadata:
    annotations:
      service.beta.kubernetes.io/azure-dns-label-name: $AKS_PRIMARY_CLUSTER_PG_DNSPREFIX
    labels:
      azure.workload.identity/use: "true"

  instances: 1

  affinity:
    nodeSelector:
      workload: postgres

  # Point to cluster backup created earlier and stored on Azure Blob Storage
  bootstrap:
    recovery:
      source: clusterBackup

  storage:
    size: 2Gi
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 2Gi
      storageClassName: managed-csi-premium
      volumeMode: Filesystem

  walStorage:
    size: 2Gi
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 2Gi
      storageClassName: managed-csi-premium
      volumeMode: Filesystem

  serviceAccountTemplate:
    metadata:
      annotations:
        azure.workload.identity/client-id: "$AKS_UAMI_WORKLOAD_CLIENTID"
      labels:
        azure.workload.identity/use: "true"

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        destinationPath: https://${PG_PRIMARY_STORAGE_ACCOUNT_NAME}.blob.core.windows.net/backups
        serverName: $PG_PRIMARY_CLUSTER_NAME
        azureCredentials:
          inheritFromAzureAD: true
        wal:
          maxParallel: 8
EOF

Connect to the recovered instance, then validate that the dataset created on the original cluster where the full backup was taken is present using the following command:

kubectl cnpg psql $PG_PRIMARY_CLUSTER_NAME_RECOVERED --namespace $PG_NAMESPACE

postgres=# SELECT COUNT(*) FROM datasample;

Example output

# count
#-------
#     3
#(1 row)

# Type \q to exit psql

Delete the recovered cluster using the following command:

kubectl cnpg destroy $PG_PRIMARY_CLUSTER_NAME_RECOVERED 1 \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

Delete the federated identity credential using the az identity federated-credential delete command.

az identity federated-credential delete \
    --name $PG_PRIMARY_CLUSTER_NAME_RECOVERED \
    --identity-name $AKS_UAMI_CLUSTER_IDENTITY_NAME \
    --resource-group $RESOURCE_GROUP_NAME \
    --yes

Expose the PostgreSQL cluster using a public load balancer

In this section, you configure the necessary infrastructure to publicly expose the PostgreSQL read-write and read-only endpoints with IP source restrictions to the public IP address of your client workstation.

You also retrieve the following endpoints from the Cluster IP service:

One primary read-write endpoint that ends with *-rw.
Zero to N (depending on the number of replicas) read-only endpoints that end with *-ro.
One replication endpoint that ends with *-r.

Get the Cluster IP service details using the kubectl get command.
```
kubectl get services \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE \
    -l cnpg.io/cluster=$PG_PRIMARY_CLUSTER_NAME
```
Example output
```
NAME                          TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
pg-primary-cnpg-sryti1qf-r    ClusterIP   10.0.193.27    <none>        5432/TCP   3h57m
pg-primary-cnpg-sryti1qf-ro   ClusterIP   10.0.237.19    <none>        5432/TCP   3h57m
pg-primary-cnpg-sryti1qf-rw   ClusterIP   10.0.244.125   <none>        5432/TCP   3h57m
```
Note

There are three services: namespace/cluster-name-ro mapped to port 5433, namespace/cluster-name-rw, and namespace/cluster-name-r mapped to port 5433. It’s important to avoid using the same port as the read/write node of the PostgreSQL database cluster. If you want applications to access only the read-only replica of the PostgreSQL database cluster, direct them to port 5433. The final service is typically used for data backups but can also function as a read-only node.

Get the service details using the kubectl get command.

export PG_PRIMARY_CLUSTER_RW_SERVICE=$(kubectl get services \
    --namespace $PG_NAMESPACE \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    -l "cnpg.io/cluster" \
    --output json | jq -r '.items[] | select(.metadata.name | endswith("-rw")) | .metadata.name')

echo $PG_PRIMARY_CLUSTER_RW_SERVICE

export PG_PRIMARY_CLUSTER_RO_SERVICE=$(kubectl get services \
    --namespace $PG_NAMESPACE \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    -l "cnpg.io/cluster" \
    --output json | jq -r '.items[] | select(.metadata.name | endswith("-ro")) | .metadata.name')

echo $PG_PRIMARY_CLUSTER_RO_SERVICE

Configure the load balancer service with the following YAML files using the kubectl apply command.

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME -f -
apiVersion: v1
kind: Service
metadata:
  annotations:
    service.beta.kubernetes.io/azure-load-balancer-resource-group: $AKS_PRIMARY_CLUSTER_NODERG_NAME
    service.beta.kubernetes.io/azure-pip-name: $AKS_PRIMARY_CLUSTER_PUBLICIP_NAME
    service.beta.kubernetes.io/azure-dns-label-name: $AKS_PRIMARY_CLUSTER_PG_DNSPREFIX
  name: cnpg-cluster-load-balancer-rw
  namespace: "${PG_NAMESPACE}"
spec:
  type: LoadBalancer
  ports:
  - protocol: TCP
    port: 5432
    targetPort: 5432
  selector:
    cnpg.io/instanceRole: primary
    cnpg.io/podRole: instance
  loadBalancerSourceRanges:
  - "$MY_PUBLIC_CLIENT_IP/32"
EOF

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME -f -
apiVersion: v1
kind: Service
metadata:
  annotations:
    service.beta.kubernetes.io/azure-load-balancer-resource-group: $AKS_PRIMARY_CLUSTER_NODERG_NAME
    service.beta.kubernetes.io/azure-pip-name: $AKS_PRIMARY_CLUSTER_PUBLICIP_NAME
    service.beta.kubernetes.io/azure-dns-label-name: $AKS_PRIMARY_CLUSTER_PG_DNSPREFIX
  name: cnpg-cluster-load-balancer-ro
  namespace: "${PG_NAMESPACE}"
spec:
  type: LoadBalancer
  ports:
  - protocol: TCP
    port: 5433
    targetPort: 5432
  selector:
    cnpg.io/instanceRole: replica
    cnpg.io/podRole: instance
  loadBalancerSourceRanges:
  - "$MY_PUBLIC_CLIENT_IP/32"
EOF

Get the service details using the kubectl describe command.

kubectl describe service cnpg-cluster-load-balancer-rw \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

kubectl describe service cnpg-cluster-load-balancer-ro \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    --namespace $PG_NAMESPACE

export AKS_PRIMARY_CLUSTER_ALB_DNSNAME="$(az network public-ip show \
    --resource-group $AKS_PRIMARY_CLUSTER_NODERG_NAME \
    --name $AKS_PRIMARY_CLUSTER_PUBLICIP_NAME \
    --query "dnsSettings.fqdn" --output tsv)"

echo $AKS_PRIMARY_CLUSTER_ALB_DNSNAME

Validate public PostgreSQL endpoints

In this section, you validate that the Azure Load Balancer is properly set up using the static IP that you created earlier and routing connections to the primary read-write and read-only replicas and use the psql CLI to connect to both.

Remember that the primary read-write endpoint maps to TCP port 5432 and the read-only replica endpoints map to port 5433 to allow the same PostgreSQL DNS name to be used for readers and writers.

Note

You need the value of the app user password for PostgreSQL basic auth that was generated earlier and stored in the $PG_DATABASE_APPUSER_SECRET environment variable.

Validate the public PostgreSQL endpoints using the following psql commands:

echo "Public endpoint for PostgreSQL cluster: $AKS_PRIMARY_CLUSTER_ALB_DNSNAME"

# Query the primary, pg_is_in_recovery = false

psql -h $AKS_PRIMARY_CLUSTER_ALB_DNSNAME \
    -p 5432 -U app -d appdb -W -c "SELECT pg_is_in_recovery();"

Example output

pg_is_in_recovery
-------------------
 f
(1 row)

echo "Query a replica, pg_is_in_recovery = true"

psql -h $AKS_PRIMARY_CLUSTER_ALB_DNSNAME \
    -p 5433 -U app -d appdb -W -c "SELECT pg_is_in_recovery();"

Example output

# Example output

pg_is_in_recovery
-------------------
t
(1 row)

When successfully connected to the primary read-write endpoint, the PostgreSQL function returns f for false, indicating that the current connection is writable.

When connected to a replica, the function returns t for true, indicating the database is in recovery and read-only.

Simulate an unplanned failover

In this section, you trigger a sudden failure by deleting the pod running the primary, which simulates a sudden crash or loss of network connectivity to the node hosting the PostgreSQL primary.

Check the status of the running pod instances using the following command:

kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE

Example output

Name                        Current LSN Rep role        Status  Node
--------------------------- ----------- --------        ------- -----------
pg-primary-cnpg-sryti1qf-1  0/9000060   Primary         OK      aks-postgres-32388626-vmss000000
pg-primary-cnpg-sryti1qf-2  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000001
pg-primary-cnpg-sryti1qf-3  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000002

Delete the primary pod using the kubectl delete command.

PRIMARY_POD=$(kubectl get pod \
    --namespace $PG_NAMESPACE \
    --no-headers \
    -o custom-columns=":metadata.name" \
    -l role=primary)

kubectl delete pod $PRIMARY_POD --grace-period=1 --namespace $PG_NAMESPACE

Validate that the pg-primary-cnpg-sryti1qf-2 pod instance is now the primary using the following command:

kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE

Example output

pg-primary-cnpg-sryti1qf-2  0/9000060   Primary         OK      aks-postgres-32388626-vmss000001
pg-primary-cnpg-sryti1qf-1  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000000
pg-primary-cnpg-sryti1qf-3  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000002

Reset the pg-primary-cnpg-sryti1qf-1 pod instance as the primary using the following command:
```
kubectl cnpg promote $PG_PRIMARY_CLUSTER_NAME 1 --namespace $PG_NAMESPACE
```

Validate that the pod instances have returned to their original state before the unplanned failover test using the following command:

kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE

Example output

Name                        Current LSN Rep role        Status  Node
--------------------------- ----------- --------        ------- -----------
pg-primary-cnpg-sryti1qf-1  0/9000060   Primary         OK      aks-postgres-32388626-vmss000000
pg-primary-cnpg-sryti1qf-2  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000001
pg-primary-cnpg-sryti1qf-3  0/9000060   Standby (sync)  OK      aks-postgres-32388626-vmss000002

Clean up resources

Once you're finished reviewing your deployment, delete all the resources you created in this guide using the az group delete command.
```
az group delete --resource-group $RESOURCE_GROUP_NAME --no-wait --yes
```

Next steps

In this how-to guide, you learned how to:

Use Azure CLI to create a multi-zone AKS cluster.
Deploy a highly available PostgreSQL cluster and database using the CNPG operator.
Set up monitoring for PostgreSQL using Prometheus and Grafana.
Deploy a sample dataset to the PostgreSQL database.
Simulate a cluster interruption and PostgreSQL replica failover.
Perform a backup and restore of the PostgreSQL database.

To learn more about how you can leverage AKS for your workloads, see What is Azure Kubernetes Service (AKS)? To learn more about Azure Database for PostgreSQL, see What is Azure Database for PostgreSQL?

Contributors

Microsoft maintains this article. The following contributors originally wrote it:

Ken Kilty | Principal TPM
Russell de Pina | Principal TPM
Adrian Joian | Senior Customer Engineer
Jenny Hayes | Senior Content Developer
Carol Smith | Senior Content Developer
Erin Schaffer | Content Developer 2
Adam Sharif | Customer Engineer 2

Share via

Test and validate PostgreSQL database on AKS

Inspect the deployed PostgreSQL cluster

Connect to PostgreSQL and create a sample dataset

Connect to PostgreSQL read-only replicas

Set up on-demand and scheduled PostgreSQL backups using Barman

Restore the on-demand backup to a new PostgreSQL cluster

Expose the PostgreSQL cluster using a public load balancer

Validate public PostgreSQL endpoints

Simulate an unplanned failover

Clean up resources

Next steps

Contributors

Additional resources