Aks postgres

https://learn.microsoft.com/en-us/azure/aks/deploy-postgresql-ha

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

Bash

Copy
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
Validate that the secret was successfully created using the kubectl get command.

Bash

Copy
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 set environment variables for the PostgreSQL cluster using the following kubectl apply command:

Bash

Copy
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
Install the Prometheus PodMonitors
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.

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

Bash

Copy
helm repo add prometheus-community \
    https://prometheus-community.github.io/helm-charts
Upgrade the Prometheus Community Helm repo and install it on the primary cluster using the helm upgrade command with the --install flag.

Bash

Copy
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
Verify that the pod monitor is created.

Bash

Copy
kubectl --namespace $PG_NAMESPACE \
    --context $AKS_PRIMARY_CLUSTER_NAME \
    get podmonitors.monitoring.coreos.com \
    $PG_PRIMARY_CLUSTER_NAME \
    -o yaml
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.

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

Bash

Copy
export AKS_PRIMARY_CLUSTER_OIDC_ISSUER="$(az aks show \
    --name $AKS_PRIMARY_CLUSTER_NAME \
    --resource-group $RESOURCE_GROUP_NAME \
    --query "oidcIssuerProfile.issuerUrl" \
    --output tsv)"
Create a federated identity credential using the az identity federated-credential create command.

Bash

Copy
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).

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

Property    Definition
inheritedMetadata   Specific to the CNPG operator. Metadata is inherited by all objects related to the cluster.
annotations: service.beta.kubernetes.io/azure-dns-label-name    DNS label for use when exposing the read-write and read-only Postgres cluster endpoints.
labels: azure.workload.identity/use: "true" Indicates that AKS should inject workload identity dependencies into the pods hosting the PostgreSQL cluster instances.
topologySpreadConstraints   Require different zones and different nodes with label "workload=postgres".
resources   Configures a Quality of Service (QoS) class of Guaranteed. In a production environment, these values are key for maximizing usage of the underlying node VM and vary based on the Azure VM SKU used.
bootstrap   Specific to the CNPG operator. Initializes with an empty app database.
storage / walStorage    Specific to the CNPG operator. Defines storage templates for the PersistentVolumeClaims (PVCs) for data and log storage. It's also possible to specify storage for tablespaces to shard out for increased IOPs.
replicationSlots    Specific to the CNPG operator. Enables replication slots for high availability.
postgresql  Specific to the CNPG operator. Maps settings for postgresql.conf, pg_hba.conf, and pg_ident.conf config.
serviceAccountTemplate  Contains the template needed to generate the service accounts and maps the AKS federated identity credential to the UAMI to enable AKS workload identity authentication from the pods hosting the PostgreSQL instances to external Azure resources.
barmanObjectStore   Specific to the CNPG operator. Configures the barman-cloud tool suite using AKS workload identity for authentication to the Azure Blob Storage object store.
Deploy the PostgreSQL cluster with the Cluster CRD using the kubectl apply command.

Bash

Copy
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
    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: 2Gi
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 2Gi
      storageClassName: managed-csi-premium

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

  monitoring:
    enablePodMonitor: true

  postgresql:
    parameters:
      archive_timeout: '5min'
      auto_explain.log_min_duration: '10s'
      checkpoint_completion_target: '0.9'
      checkpoint_timeout: '15min'
      shared_buffers: '256MB'
      effective_cache_size: '512MB'
      pg_stat_statements.max: '1000'
      pg_stat_statements.track: 'all'
      max_connections: '400'
      max_prepared_transactions: '400'
      max_parallel_workers: '32'
      max_parallel_maintenance_workers: '8'
      max_parallel_workers_per_gather: '8'
      max_replication_slots: '32'
      max_worker_processes: '32'
      wal_keep_size: '512MB'
      max_wal_size: '1GB'
    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.windows.net/backups"
      azureCredentials:
        inheritFromAzureAD: true

    retentionPolicy: '7d'
EOF
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.

Bash

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

Output

Copy
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
Validate the Prometheus PodMonitor is running
The CNPG operator automatically creates a PodMonitor for the primary instance using the recording rules created during the Prometheus Community installation.

Validate the PodMonitor is running using the kubectl get command.

Bash

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

Output

Copy
 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:

Bash

Copy
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).

Bash

Copy
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