Deploy a highly available PostgreSQL database on AKS

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

• If you haven't already created 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 you can return to this article.

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
   

2. 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
   

• Deploy a ConfigMap to set environment variables for the PostgreSQL cluster using the following kubectl apply command:

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

Prometheus creates PodMonitors for the CNPG instances using a set of default recording rules stored on the CNPG GitHub samples repo. In a production environment, these rules would be modified as needed.

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
   

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
   

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

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

PostgreSQL performance heavily depends on your cluster's underlying resources. The following table provides some suggestions on how to calculate key parameters for high performance:

1. Deploy the PostgreSQL cluster with the Cluster CRD using the kubectl apply command.

   cat <<EOF | kubectl apply --context $AKS_PRIMARY_CLUSTER_NAME -n $PG_NAMESPACE -v 9 -f -
   apiVersion: postgresql.cnpg.io/v1
   kind: Cluster
   metadata:
     name: $PG_PRIMARY_CLUSTER_NAME
   spec:
     inheritedMetadata:
       annotations:
         service.beta.kubernetes.io/azure-dns-label-name: $AKS_PRIMARY_CLUSTER_PG_DNSPREFIX
         acstor.azure.cn/accept-ephemeral-storage: "true"
       labels:
         azure.workload.identity/use: "true"
   
     instances: 3
     startDelay: 30
     stopDelay: 30
     minSyncReplicas: 1
     maxSyncReplicas: 1
     replicationSlots:
       highAvailability:
         enabled: true
       updateInterval: 30
   
     topologySpreadConstraints:
     - maxSkew: 1
       topologyKey: topology.kubernetes.io/zone
       whenUnsatisfiable: DoNotSchedule
       labelSelector:
         matchLabels:
           cnpg.io/cluster: $PG_PRIMARY_CLUSTER_NAME
   
     affinity:
       nodeSelector:
         workload: postgres
   
     resources:
       requests:
         memory: '8Gi'
         cpu: 2
       limits:
         memory: '8Gi'
         cpu: 2
   
     bootstrap:
       initdb:
         database: appdb
         owner: app
         secret:
           name: db-user-pass
         dataChecksums: true
   
     storage:
       size: 32Gi
       pvcTemplate:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 32Gi
         storageClassName: $POSTGRES_STORAGE_CLASS
   
     walStorage:
       size: 32Gi
       pvcTemplate:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 32Gi
         storageClassName: $POSTGRES_STORAGE_CLASS
   
     monitoring:
       enablePodMonitor: true
   
     postgresql:
       parameters:
         wal_compression: lz4
         max_wal_size: 6GB
         checkpoint_timeout: 15min
         checkpoint_flush_after: 2MB
         wal_writer_flush_after: 2MB
         min_wal_size: 4GB
         shared_buffers: 4GB
         effective_cache_size: 12GB
         work_mem: 62MB
         maintenance_work_mem: 1GB
         autovacuum_vacuum_cost_limit: "2400"
         random_page_cost: "1.1"
         effective_io_concurrency: "64"
         maintenance_io_concurrency: "64"
       pg_hba:
         - host all all all scram-sha-256
   
     serviceAccountTemplate:
       metadata:
         annotations:
           azure.workload.identity/client-id: "$AKS_UAMI_WORKLOAD_CLIENTID"
         labels:
           azure.workload.identity/use: "true"
   
     backup:
       barmanObjectStore:
         destinationPath: "https://${PG_PRIMARY_STORAGE_ACCOUNT_NAME}.blob.core.chinacloudapi.cn/backups"
         azureCredentials:
           inheritFromAzureAD: true
       retentionPolicy: '7d'
   EOF
   

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
   

The CNPG operator automatically creates a PodMonitor for the primary instance using the recording rules created during the Prometheus Community installation.

1. 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:
     annotations:
       cnpg.io/operatorVersion: 1.23.1
   ...
   

If you are using Azure Monitor for Managed Prometheus, you will need to add another pod monitor using the custom group name. Managed Prometheus does not pick up the custom resource definitions (CRDs) from the Prometheus community. Aside from the group name, the CRDs are the same. This allows pod monitors for Managed Prometheus to exist side-by-side those that use the community pod monitor. If you are not using Managed Prometheus, you can skip this. 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

Once you have deployed the Postgres cluster and the pod monitor, you can view the metrics using the Azure portal in an Azure Monitor workspace.

Alternatively, Once you have deployed 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 click on the Endpoint link as shown here:

Clicking on the Endpoint link will cause a new browser window to open 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, click the Data sources option and you should see a set of data source options for the data source connection as shown here:

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

Click the Save button to add the panel to your dashboard. You can add other panels by clicking 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:

Click the Save icon to save your dashboard.

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