Proposed Pull Request Change

📄 Document Links

View on GitHub View on Microsoft Learn

--- title: 'Validate and test a PostgreSQL database deployment on AKS' description: Validate the deployment of a PostgreSQL database on AKS and learn how to conduct various day-2 operations ms.topic: how-to ms.date: 06/18/2025 author: kenkilty ms.author: kkilty ms.custom: 'innovation-engine, aks-related-content, stateful-workloads' # Customer intent: "As a database administrator, I want to test and validate a highly available PostgreSQL database on AKS, so that I can ensure its reliability and performance in a production environment." --- # Validate and test a PostgreSQL database deployment on Azure Kubernetes Service (AKS) In this article, you perform various testing and validation steps on a PostgreSQL database deployed on AKS. This includes verifying the deployment, connecting to the database, and testing failover scenarios. * If you haven't already deployed PostgreSQL, follow the steps in [Deploy a highly available PostgreSQL database on AKS with Azure CLI][deploy-postgresql-ha] to get set up, and then you can return to this article. [!INCLUDE [open source disclaimer](./includes/open-source-disclaimer.md)] ## Inspect the deployed PostgreSQL cluster Validate that PostgreSQL is spread across multiple availability zones by retrieving the AKS node details using the [`kubectl get`][kubectl-get] command. ```bash 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: ```output { "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: ```bash kubectl cnpg psql $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE ``` ```sql -- Create a small dataset 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 when finished. Your output should resemble the following example output: ```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: ```bash kubectl cnpg psql --replica $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE ``` ```sql SELECT pg_is_in_recovery(); ``` Example output ```output pg_is_in_recovery ------------------- t (1 row) ``` ```sql SELECT COUNT(*) FROM datasample; ``` Example output ```output count ------- 3 (1 row) ``` ## Set up on-demand and scheduled PostgreSQL backups using Barman > [!NOTE] > CloudNativePG is expected to deprecate native Barman Cloud support in favor of the [Barman Cloud plugin](https://cloudnative-pg.io/plugin-barman-cloud/docs/intro/) in an upcoming 1.29 release. The steps in this guide continue to work today, but plan to migrate to the plugin once it stabilizes. 1. 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: ```bash kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME 1 \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE ``` Example output ```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: - ``` 1. Deploy an on-demand backup to Azure Storage, which uses the AKS workload identity integration, using the YAML file with the [`kubectl apply`][kubectl-apply] command. ```bash 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 ``` 1. Validate the status of the on-demand backup using the [`kubectl describe`][kubectl-describe] command. ```bash kubectl describe backup $BACKUP_ONDEMAND_NAME \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE ``` Example output ```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 ``` 1. Validate that the cluster has a first point of recoverability using the following command: ```bash kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME 1 \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE ``` Example output ```output Continuous Backup status First Point of Recoverability: 2024-06-05T13:47:18Z Working WAL archiving: OK ``` 1. Configure a scheduled backup for _every hour at 15 minutes past the hour_ using the YAML file with the [`kubectl apply`][kubectl-apply] command. ```bash 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 ``` 1. Validate the status of the scheduled backup using the [`kubectl describe`][kubectl-describe] command. ```bash kubectl describe scheduledbackup $BACKUP_SCHEDULED_NAME \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE ``` 1. View the backup files stored on Azure blob storage for the primary cluster using the [`az storage blob list`][az-storage-blob-list] command. ```bash 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: ```output [ "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. 1. Create a second federated identity credential using the [`az identity federated-credential create`][az-identity-federated-credential-create] command. ```bash 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 ``` 1. Restore the on-demand backup using the Cluster CRD with the [`kubectl apply`][kubectl-apply] command. ```bash 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 ``` 1. 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: ```bash kubectl cnpg psql $PG_PRIMARY_CLUSTER_NAME_RECOVERED --namespace $PG_NAMESPACE ``` ```sql SELECT COUNT(*) FROM datasample; ``` Example output ```output count ------- 3 (1 row) Type \q to exit psql ``` 1. Delete the recovered cluster using the following command: ```bash kubectl cnpg destroy $PG_PRIMARY_CLUSTER_NAME_RECOVERED 1 \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE ``` 1. Delete the federated identity credential using the [`az identity federated-credential delete`][az-identity-federated-credential-delete] command. ```bash 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`. 1. Get the Cluster IP service details using the [`kubectl get`][kubectl-get] command. ```bash kubectl get services \ --context $AKS_PRIMARY_CLUSTER_NAME \ --namespace $PG_NAMESPACE \ -l cnpg.io/cluster=$PG_PRIMARY_CLUSTER_NAME ``` Example output ```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. 1. Get the service details using the [`kubectl get`][kubectl-get] command. ```bash 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 ``` 1. Configure the load balancer service with the following YAML files using the [`kubectl apply`][kubectl-apply] command. ```bash 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 ``` 1. Get the service details using the [`kubectl describe`][kubectl-describe] command. ```bash 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: ```bash 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 ```output pg_is_in_recovery ------------------- f (1 row) ``` ```bash 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 ```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. 1. Check the status of the running pod instances using the following command: ```bash kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE ``` Example output ```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 ``` 1. Delete the primary pod using the [`kubectl delete`][kubectl-delete] command. ```bash 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 ``` 1. Validate that the `pg-primary-cnpg-sryti1qf-2` pod instance is now the primary using the following command: ```bash kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE ``` Example output ```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 ``` 1. Reset the `pg-primary-cnpg-sryti1qf-1` pod instance as the primary using the following command: ```bash kubectl cnpg promote $PG_PRIMARY_CLUSTER_NAME 1 --namespace $PG_NAMESPACE ``` 1. Validate that the pod instances have returned to their original state before the unplanned failover test using the following command: ```bash kubectl cnpg status $PG_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE ``` Example output ```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`][az-group-delete] command. ```bash 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 use AKS for your workloads, see [What is Azure Kubernetes Service (AKS)?][what-is-aks] To learn more about Azure Database for PostgreSQL, see [What is Azure Database for PostgreSQL?](/azure/postgresql/flexible-server/overview) ## 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 ## Acknowledgment This documentation was jointly developed with EnterpriseDB, the maintainers of the CloudNativePG operator. We thank [Gabriele Bartolini](https://cloudnative-pg.io/authors/gbartolini/) for reviewing earlier drafts of this document and offering technical improvements. <!-- LINKS --> [kubectl-get]: https://kubernetes.io/docs/reference/kubectl/generated/kubectl_get/ [kubectl-apply]: https://kubernetes.io/docs/reference/kubectl/generated/kubectl_apply/ [az-identity-federated-credential-create]: /cli/azure/identity/federated-credential#az-identity-federated-credential-create [kubectl-describe]: https://kubernetes.io/docs/reference/kubectl/generated/kubectl_describe/ [az-storage-blob-list]: /cli/azure/storage/blob/#az-storage-blob-list [az-identity-federated-credential-delete]: /cli/azure/identity/federated-credential#az-identity-federated-credential-delete [kubectl-delete]: https://kubernetes.io/docs/reference/kubectl/generated/kubectl_delete/ [az-group-delete]: /cli/azure/group#az-group-delete [what-is-aks]: ./what-is-aks.md [deploy-postgresql-ha]: ./deploy-postgresql-ha.md