Deploy a highly available PostgreSQL database on Azure Kubernetes Service (AKS)

In this article, you deploy a highly available PostgreSQL database on AKS.

• If you still need to create the required infrastructure for this deployment, follow the steps in Create infrastructure for deploying a highly available PostgreSQL database on AKS to get set up, and then return to this article.

Create secret for bootstrap app user

1. Generate a secret to validate the PostgreSQL deployment by interactive login for a bootstrap app user using the kubectl create secret command.

PG_DATABASE_APPUSER_SECRET=$(echo -n | openssl rand -base64 16)

kubectl create secret generic db-user-pass \
    --from-literal=username=app \
     --from-literal=password="${PG_DATABASE_APPUSER_SECRET}" \
     --namespace $PG_NAMESPACE \
     --context $AKS_PRIMARY_CLUSTER_NAME

1. Validate that the secret was successfully created using the kubectl get command.

   kubectl get secret db-user-pass --namespace $PG_NAMESPACE --context $AKS_PRIMARY_CLUSTER_NAME
   

Set environment variables for the PostgreSQL cluster

• Deploy a ConfigMap to configure the CNPG operator using the following kubectl apply command. These values replace the legacy ENABLE_AZURE_PVC_UPDATES toggle, which is no longer required, and help stagger upgrades and speed up replica reconnections. Before rolling this configuration into production, validate that any existing DRAIN_TAINTS settings you rely on remain compatible with your Azure environment.

  cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME -n $PG_NAMESPACE -f -
  apiVersion: v1
  kind: ConfigMap
  metadata:
      name: cnpg-controller-manager-config
  data:
      CLUSTERS_ROLLOUT_DELAY: '120'
      STANDBY_TCP_USER_TIMEOUT: '10'
  EOF
  

Install the Prometheus PodMonitors

Prometheus scrapes CNPG using the recording rules stored in the CNPG GitHub samples repo. Because the operator-managed PodMonitor is being deprecated, create and manage the PodMonitor resource yourself so you can tailor it to your monitoring stack.

1. Add the Prometheus Community Helm repo using the helm repo add command.

   helm repo add prometheus-community \
       https://prometheus-community.github.io/helm-charts
   

2. Upgrade the Prometheus Community Helm repo and install it on the primary cluster using the helm upgrade command with the --install flag.

   helm upgrade --install \
       --namespace $PG_NAMESPACE \
       -f https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/docs/src/samples/monitoring/kube-stack-config.yaml \
       prometheus-community \
       prometheus-community/kube-prometheus-stack \
       --kube-context=$AKS_PRIMARY_CLUSTER_NAME
   

3. Create a PodMonitor for the cluster. The CNPG team is deprecating the operator-managed PodMonitor, so you now manage it directly:

   cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -f -
   apiVersion: monitoring.coreos.com/v1
   kind: PodMonitor
   metadata:
     name: $PG_PRIMARY_CLUSTER_NAME
     namespace: ${PG_NAMESPACE}
     labels:
       cnpg.io/cluster: ${PG_PRIMARY_CLUSTER_NAME}
   spec:
     selector:
       matchLabels:
         cnpg.io/cluster: ${PG_PRIMARY_CLUSTER_NAME}
     podMetricsEndpoints:
       - port: metrics
   EOF
   

Create a federated credential

In this section, you create a federated identity credential for PostgreSQL backup to allow CNPG to use AKS workload identity to authenticate to the storage account destination for backups. The CNPG operator creates a Kubernetes service account with the same name as the cluster named used in the CNPG Cluster deployment manifest.

1. Get the OIDC issuer URL of the cluster using the az aks show command.

   export AKS_PRIMARY_CLUSTER_OIDC_ISSUER="$(az aks show \
       --name $AKS_PRIMARY_CLUSTER_NAME \
       --resource-group $RESOURCE_GROUP_NAME \
       --query "oidcIssuerProfile.issuerUrl" \
       --output tsv)"
   

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

   az identity federated-credential create \
       --name $AKS_PRIMARY_CLUSTER_FED_CREDENTIAL_NAME \
       --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}" \
       --audience api://AzureADTokenExchange
   

Deploy a highly available PostgreSQL cluster

In this section, you deploy a highly available PostgreSQL cluster using the CNPG Cluster custom resource definition (CRD).

Cluster CRD parameters

The following table outlines the key properties set in the YAML deployment manifest for the Cluster CRD:

To further isolate PostgreSQL workloads, you can add a taint (for example, node-role.kubernetes.io/postgres=:NoSchedule) to your data plane nodes and replace the sample nodeSelector/tolerations with the values recommended by CloudNativePG. If you take this approach, label the nodes accordingly and confirm the AKS autoscaler policies align with your topology.

PostgreSQL performance parameters

PostgreSQL performance heavily depends on your cluster's underlying resources and workload. The following table provides baseline guidance for a three-node cluster running on Standard D4s v3 nodes (16-GiB memory). Treat these values as a starting point and adjust them once you understand your workload profile:

Deploying PostgreSQL

2. Validate that the primary PostgreSQL cluster was successfully created using the kubectl get command. The CNPG Cluster CRD specified three instances, which can be validated by viewing running pods once each instance is brought up and joined for replication. Be patient as it can take some time for all three instances to come online and join the cluster.

   kubectl get pods --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -l cnpg.io/cluster=$PG_PRIMARY_CLUSTER_NAME
   

   Example output

   NAME                         READY   STATUS    RESTARTS   AGE
   pg-primary-cnpg-r8c7unrw-1   1/1     Running   0          4m25s
   pg-primary-cnpg-r8c7unrw-2   1/1     Running   0          3m33s
   pg-primary-cnpg-r8c7unrw-3   1/1     Running   0          2m49s
   

kubectl cnpg destroy [cnpg-cluster-name] [instance-number]  

Validate the Prometheus PodMonitor is running

The manually created PodMonitor ties the kube-prometheus-stack scrape configuration to the CNPG pods you deployed earlier.

Validate the PodMonitor is running using the kubectl get command.

kubectl --namespace $PG_NAMESPACE \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    get podmonitors.monitoring.coreos.com \
    $PG_PRIMARY_CLUSTER_NAME \
    --output yaml

Example output

kind: PodMonitor
metadata:
  labels:
    cnpg.io/cluster: pg-primary-cnpg-r8c7unrw
  name: pg-primary-cnpg-r8c7unrw
  namespace: cnpg-database
spec:
  podMetricsEndpoints:
  - port: metrics
  selector:
    matchLabels:
      cnpg.io/cluster: pg-primary-cnpg-r8c7unrw

If you're using Azure Monitor for Managed Prometheus, you need to add another pod monitor using the custom group name. Managed Prometheus doesn't pick up the custom resource definitions (CRDs) from the Prometheus community. Aside from the group name, the CRDs are the same. That design lets pod monitors for Managed Prometheus run alongside pod monitors that use the community CRD. If you're not using Managed Prometheus, you can skip this section. Create a new pod monitor:

cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME --namespace $PG_NAMESPACE -f -
apiVersion: azmonitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: cnpg-cluster-metrics-managed-prometheus
  namespace: ${PG_NAMESPACE}
  labels:
    azure.workload.identity/use: "true"
    cnpg.io/cluster: ${PG_PRIMARY_CLUSTER_NAME}
spec:
  selector:
    matchLabels:
      azure.workload.identity/use: "true"
      cnpg.io/cluster: ${PG_PRIMARY_CLUSTER_NAME}
  podMetricsEndpoints:
    - port: metrics
EOF

Verify that the pod monitor is created (note the difference in the group name).

kubectl --namespace $PG_NAMESPACE \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    get podmonitors.azmonitoring.coreos.com \
    -l cnpg.io/cluster=$PG_PRIMARY_CLUSTER_NAME \
    -o yaml

Option A - Azure Monitor workspace

After you deploy the Postgres cluster and the pod monitor, you can view the metrics using the Azure portal in an Azure Monitor workspace.

Option B - Managed Grafana

Alternatively, after you deploy the Postgres cluster and pod monitors, you can create a metrics dashboard on the Managed Grafana instance created by the deployment script to visualize the metrics exported to the Azure Monitor workspace. You can access the Managed Grafana via the Azure portal. Navigate to the Managed Grafana instance created by the deployment script and select the Endpoint link as shown here:

Selecting the Endpoint link opens a new browser window where you can create dashboards on the Managed Grafana instance. Following the instructions to configure an Azure Monitor data source, you can then add visualizations to create a dashboard of metrics from the Postgres cluster. After setting up the data source connection, from the main menu, select the Data sources option. You should see a set of data source options for the data source connection as shown here:

On the Managed Prometheus option, select the option to build a dashboard to open the dashboard editor. After the editor window opens, select the Add visualization option then select the Managed Prometheus option to browse the metrics from the Postgres cluster. After you select the metric you want to visualize, select the Run queries button to fetch the data for the visualization as shown here:

Select the Save icon to add the panel to your dashboard. You can add other panels by selecting the Add button in the dashboard editor and repeating this process to visualize other metrics. Adding the metrics visualizations, you should have something that looks like this:

Select the Save icon to save your dashboard.

Next steps

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 for reviewing earlier drafts of this document and offering technical improvements.