acid-minimal-cluster2 Postgres Health

8 Troubleshooting Commands

Last updated 13 weeks ago

Contributed by stewartshea

---

Public Source Code

Private Configuration

Join Discussion

---

Troubleshooting Commands

List Resources Related to Postgres Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This command retrieves all resources with specific labels in a particular namespace, and then provides detailed information about a specific object within that namespace using a specific context.

Command

kubectl get all -l cluster-name=acid-minimal-cluster2 -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster && kubectl describe postgresql.acid.zalan.do acid-minimal-cluster2 -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

# First, we want to list all resources with specific labels in a certain namespace and context
kubectl get all -l ${RESOURCE_LABELS} -n ${NAMESPACE} --context ${CONTEXT}

# Then, we want to describe a specific object of a certain kind and name in the same namespace and context
kubectl describe ${OBJECT_KIND} ${OBJECT_NAME} -n ${NAMESPACE} --context ${CONTEXT}

In the comments, replace `${RESOURCE_LABELS}`, `${NAMESPACE}`, `${CONTEXT}`, `${OBJECT_KIND}`, and `${OBJECT_NAME}` with the actual values or variables you are using. This will make the command more readable and understandable for newer or less experienced devops engineers.

Helpful Links

• Retrieve all resources with specific labels in a particular namespace
• Detailed information about a specific object within a namespace

Get Postgres Pod Logs & Events for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This command retrieves the names of all pods in a specific namespace and context that have certain resource labels and are currently running. It outputs only the names of the pods as a result.

Command

kubectl get pods -l cluster-name=acid-minimal-cluster2 -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster -o=name --field-selector=status.phase=Running

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

# The following command retrieves the names of all pods with specific labels and running in a particular namespace, using a specific context.

kubectl get pods \  # Uses the kubectl command-line tool to interact with Kubernetes
  -l ${RESOURCE_LABELS} \  # Specifies the resource labels to filter the pods
  -n ${NAMESPACE} \  # Specifies the namespace where the pods are located
  --context ${CONTEXT} \  # Specifies the context for accessing the Kubernetes cluster
  -o=name \  # Formats the output to only display the names of the pods
  --field-selector=status.phase=Running  # Filters the pods based on their running status

Helpful Links

• Kubernetes CLI Cheat Sheet
• Kubernetes Namespaces
• Kubernetes Labels and Selectors
• Kubernetes Pods

Get Postgres Pod Resource Utilization for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This command uses kubectl to show resource usage of pods with specific labels, including individual container resource usage, in the specified namespace and context.

Command

kubectl top pods -l cluster-name=acid-minimal-cluster2 --containers -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

# This command is used to get the resource usage of pods that match certain labels in a specific namespace and context
# kubectl top - Display Resource (CPU/Memory) usage
# pods - The resource type to display
# -l ${RESOURCE_LABELS} - Labels to filter the pods by
# --containers - Include containers in the resource usage
# -n ${NAMESPACE} - Specify the namespace for the pods
# --context ${CONTEXT} - Specify the context for the Kubernetes cluster

kubectl top pods \ # Command to display resource usage of pods
  -l ${RESOURCE_LABELS} \ # Filter the pods by specified labels
  --containers \ # Include containers in the resource usage
  -n ${NAMESPACE} \ # Specify the namespace for the pods
  --context ${CONTEXT} # Specify the context for the Kubernetes cluster

Helpful Links

• kubectl Cheat Sheet
• Kubernetes Labels and Selectors
• Monitoring Kubernetes Resource Usage

Get Running Postgres Configuration for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This script is a bash script that performs configuration checks on two different types of PostgreSQL Operators: Crunchy and Zalando. It collects reports and issues related to the configurations and outputs them in a JSON format.

Command

DISTRIBUTION="Kubernetes" NAMESPACE="postgres-database" CONTEXT="gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster" KUBERNETES_DISTRIBUTION_BINARY="kubectl" WORKLOAD_NAME="-l application=spilo,cluster-name=acid-minimal-cluster2" RESOURCE_LABELS="cluster-name=acid-minimal-cluster2" OBJECT_API_VERSION="acid.zalan.do/v1" OBJECT_NAME="acid-minimal-cluster2" DATABASE_CONTAINER="postgres" OBJECT_KIND="postgresql.acid.zalan.do"  bash -c "$(curl -s https://raw.githubusercontent.com/runwhen-contrib/rw-cli-codecollection/main/codebundles/k8s-postgres-healthcheck/config_health.sh)" _

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

#!/bin/bash

# Arrays to collect reports and issues
CONFIG_REPORTS=()
ISSUES=()

# Function to generate an issue in JSON format and add to ISSUES array
generate_issue() {
  issue=$(cat <<EOF
{
  "title": "Configuration issue for Postgres Cluster \`$OBJECT_NAME\` in \`$NAMESPACE\`",
  "description": "$1",
  "parameter": "$2",
  "current_value": "$3",
  "expected_value": "$4"
}
EOF
)
  ISSUES+=("$issue")
}

# Function to display configuration and perform sanity checks for CrunchyDB PostgreSQL Operator
display_crunchy_config() {
  POD_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get pods -n "$NAMESPACE" --context "$CONTEXT" -l postgres-operator.crunchydata.com/role=master -o jsonpath="{.items[0].metadata.name}")

  CONFIG=$(${KUBERNETES_DISTRIBUTION_BINARY} exec -n "$NAMESPACE" "$POD_NAME" --context "$CONTEXT" -c "$DATABASE_CONTAINER" -- psql -U postgres -c "SHOW ALL")

  echo "CrunchyDB PostgreSQL Configuration:"
  echo "$CONFIG"

  CONFIG_REPORTS+=("CrunchyDB PostgreSQL Configuration:\n$CONFIG")

  # Sanity Checks
  echo "Performing sanity checks..."
  if [[ "$CONFIG" == *"shared_buffers"* ]]; then
    echo "shared_buffers setting is present."
  else
    generate_issue "Missing critical configuration parameter" "shared_buffers" "None" "Expected to be present"
  fi

  if [[ "$CONFIG" == *"max_connections"* ]]; then
    echo "max_connections setting is present."
  else
    generate_issue "Missing critical configuration parameter" "max_connections" "None" "Expected to be present"
  fi

  # Example additional sanity check for max_connections
  MAX_CONNECTIONS=$(echo "$CONFIG" | grep -i max_connections | awk '{print $3}')
  if (( MAX_CONNECTIONS < 100 )); then
    generate_issue "max_connections is set to less than 100" "max_connections" "$MAX_CONNECTIONS" ">= 100"
  else
    echo "max_connections setting is adequate."
  fi

  # Check if PgBouncer is deployed in the status
  PGB_READY=$(${KUBERNETES_DISTRIBUTION_BINARY} get postgresclusters.postgres-operator.crunchydata.com "$OBJECT_NAME" -n "$NAMESPACE" --context "$CONTEXT" -o jsonpath="{.status.proxy.pgBouncer.readyReplicas}")
  if [[ "$PGB_READY" -gt 0 ]]; then
    PGB_CONFIGMAP_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get configmap -l postgres-operator.crunchydata.com/role=pgbouncer  -n "$NAMESPACE" --context "$CONTEXT" -o jsonpath="{.items[0].metadata.name}")
    display_pgbouncer_config "$PGB_CONFIGMAP_NAME"
  else {
    echo "PgBouncer is not deployed for CrunchyDB in the namespace $NAMESPACE."
  }
  fi
}

# Function to display configuration and perform sanity checks for Zalando PostgreSQL Operator
display_zalando_config() {
  POD_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get pods -n "$NAMESPACE" --context "$CONTEXT" -l application=spilo -o jsonpath="{.items[0].metadata.name}")

  CONFIG=$(${KUBERNETES_DISTRIBUTION_BINARY} exec -n "$NAMESPACE" "$POD_NAME" --context "$CONTEXT" -c "$DATABASE_CONTAINER" -- psql -U postgres -c "SHOW ALL")

  echo "Zalando PostgreSQL Configuration:"
  echo "$CONFIG"

  CONFIG_REPORTS+=("Zalando PostgreSQL Configuration:\n$CONFIG")

  # Sanity Checks
  echo "Performing sanity checks..."
  if [[ "$CONFIG" == *"shared_buffers"* ]]; then
    echo "shared_buffers setting is present."
  else
    generate_issue "Missing critical configuration parameter" "shared_buffers" "None" "Expected to be present"
  fi

  if [[ "$CONFIG" == *"max_connections"* ]]; then
    echo "max_connections setting is present."
  else
    generate_issue "Missing critical configuration parameter" "max_connections" "None" "Expected to be present"
  fi

  # Example additional sanity check for max_connections
  MAX_CONNECTIONS=$(echo "$CONFIG" | grep -i max_connections | awk '{print $3}')
  if (( MAX_CONNECTIONS < 100 )); then
    generate_issue "max_connections is set to less than 100" "max_connections" "$MAX_CONNECTIONS" ">= 100"
  else
    echo "max_connections setting is adequate."
  fi

  # Check for PgBouncer
  PGB_LABEL="application=pgbouncer"
  display_pgbouncer_config "$PGB_LABEL"
}

# Function to display configuration and perform sanity checks for PgBouncer
display_pgbouncer_config() {
  CONFIGMAP_NAME=$1

  if [ -z "$CONFIGMAP_NAME" ]; then
    echo "PgBouncer ConfigMap is not found in the namespace $NAMESPACE."
    return
  fi

  CONFIG=$(${KUBERNETES_DISTRIBUTION_BINARY} get configmap "$CONFIGMAP_NAME" -n "$NAMESPACE" --context "$CONTEXT" -o jsonpath='{.data.pgbouncer\.ini}')

  echo "PgBouncer Configuration:"
  echo "$CONFIG"

  CONFIG_REPORTS+=("PgBouncer Configuration:\n$CONFIG")

  # Sanity Checks
  echo "Performing sanity checks..."
  if grep -q "max_client_conn" <<< "$CONFIG"; then
    echo "max_client_conn setting is present."
  else
    generate_issue "Missing critical configuration parameter" "max_client_conn" "None" "Expected to be present"
  fi

  # Example additional sanity check for max_client_conn
  MAX_CLIENT_CONN=$(grep -i max_client_conn <<< "$CONFIG" | awk -F'=' '{print $2}' | tr -d ' ')
  if (( MAX_CLIENT_CONN < 100 )); then
    generate_issue "max_client_conn is set to less than 100" "max_client_conn" "$MAX_CLIENT_CONN" ">= 100"
  else
    echo "max_client_conn setting is adequate."
  fi
}

if [[ "$OBJECT_API_VERSION" == *"crunchydata.com"* ]]; then
  display_crunchy_config
elif [[ "$OBJECT_API_VERSION" == *"zalan.do"* ]]; then
  display_zalando_config
else
  echo "Unsupported API version. Please specify a valid API version containing 'crunchydata.com' or 'zalan.do'."
fi

# Print the configuration reports and issues
OUTPUT_FILE="../config_report.out"

echo "Configuration Report:" > "$OUTPUT_FILE"
for report in "${CONFIG_REPORTS[@]}"; do
  echo -e "$report" >> "$OUTPUT_FILE"
done

echo "" >> "$OUTPUT_FILE"
echo "Issues:" >> "$OUTPUT_FILE"
echo "[" >> "$OUTPUT_FILE"
for issue in "${ISSUES[@]}"; do
  echo "$issue," >> "$OUTPUT_FILE"
done
# Remove the last comma and close the JSON array
sed -i '$ s/,$//' "$OUTPUT_FILE"
echo "]" >> "$OUTPUT_FILE"

echo "Configuration report and issues have been written to $OUTPUT_FILE."

These comments provide explanations, contexts, examples, and clarifications that would help newer or less experienced devops engineers understand the purpose and functionality of each part of the script.

Helpful Links

• Bash scripting guide
• PostgreSQL Operator documentation (Crunchy)
• PostgreSQL Operator documentation (Zalando)

Get Patroni Output and Add to Report for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This command uses kubectl to execute a patronictl command in the specified namespace and context, targeting a specific database container within a workload. It is often used for troubleshooting or managing database clusters in Kubernetes.

Command

kubectl exec $(kubectl get pods -l application=spilo,cluster-name=acid-minimal-cluster2 -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster -o jsonpath='{.items[0].metadata.name}') -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster -c postgres -- patronictl list

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

# Retrieve the name of the pod associated with the specified workload in the given namespace and context
POD_NAME=$(kubectl get pods ${WORKLOAD_NAME} -n ${NAMESPACE} --context ${CONTEXT} -o jsonpath='{.items[0].metadata.name}')

# Execute a command inside the specified pod, namespace, and context, targeting a specific container
kubectl exec $POD_NAME -n ${NAMESPACE} --context ${CONTEXT} -c ${DATABASE_CONTAINER} -- patronictl list

In the first line, we use `kubectl get pods` to retrieve information about the pods associated with the specified workload, namespace, and context. We then use `jsonpath` to extract the name of the pod and store it in the variable `POD_NAME`.

In the second line, we use `kubectl exec` to execute a command inside the specified pod, namespace, and context. We also specify the target container using the `-c` flag, and then run the command `patronictl list` within that container.

Helpful Links

• kubectl Overview
• patronictl Documentation
• Kubernetes Namespaces
• Kubernetes Contexts
• Managing Stateful Applications in Kubernetes

Fetch Patroni Database Lag for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This command is using kubectl to execute a patronictl list command in a specific pod and container within a Kubernetes cluster. It's pulling information about the pods and containers from the specified namespace and context.

Command

kubectl exec $(kubectl get pods -l application=spilo,cluster-name=acid-minimal-cluster2 -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster -o jsonpath='{.items[0].metadata.name}') -n postgres-database --context gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster -c postgres -- patronictl list -f json

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

# Set variables for better readability and reusability
WORKLOAD_NAME="your-workload-name"
NAMESPACE="your-namespace"
CONTEXT="your-context"
DATABASE_CONTAINER="your-database-container"

# Get the pod name of the specified workload in the specified namespace and context
POD_NAME=$(kubectl get pods ${WORKLOAD_NAME} -n ${NAMESPACE} --context ${CONTEXT} -o jsonpath='{.items[0].metadata.name}')

# Execute a command inside the specified pod, namespace, and context, targeting the specified database container
kubectl exec $POD_NAME -n ${NAMESPACE} --context ${CONTEXT} -c ${DATABASE_CONTAINER} -- patronictl list -f json

Helpful Links

• kubectl command overview
• patronictl documentation
• Kubernetes namespaces
• Kubernetes contexts

Check Database Backup Status for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This script is a Bash shell script that checks the health of backups for Postgres clusters managed by two different Kubernetes operators - CrunchyDB and Zalando PostgreSQL. It generates a report on the status of the backups and any issues encountered, based on the maximum acceptable age for the backup.

Command

DISTRIBUTION="Kubernetes" NAMESPACE="postgres-database" CONTEXT="gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster" KUBERNETES_DISTRIBUTION_BINARY="kubectl" WORKLOAD_NAME="-l application=spilo,cluster-name=acid-minimal-cluster2" RESOURCE_LABELS="cluster-name=acid-minimal-cluster2" OBJECT_API_VERSION="acid.zalan.do/v1" OBJECT_NAME="acid-minimal-cluster2" DATABASE_CONTAINER="postgres" OBJECT_KIND="postgresql.acid.zalan.do"  bash -c "$(curl -s https://raw.githubusercontent.com/runwhen-contrib/rw-cli-codecollection/main/codebundles/k8s-postgres-healthcheck/backup_health.sh)" _

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

#!/bin/bash

# Set the maximum acceptable age of the backup (in seconds) based on BACKUP_MAX_AGE environment variable
MAX_AGE=$((BACKUP_MAX_AGE * 3600))
# Arrays to store backup reports and issues
BACKUP_REPORTS=()
ISSUES=()

# Function to generate an issue in JSON format
generate_issue() {
  cat <<EOF
{
    "title": "Backup health issue for Postgres Cluster \`$OBJECT_NAME\` in \`$NAMESPACE\`",
    "description": "$1",
    "backup_completion_time": "$2",
    "backup_age_hours": "$3"
}
EOF
}

# Function to check CrunchyDB PostgreSQL Operator backup
check_crunchy_backup() {
  POSTGRES_CLUSTER_JSON=$(${KUBERNETES_DISTRIBUTION_BINARY} get postgresclusters.postgres-operator.crunchydata.com $OBJECT_NAME -n "$NAMESPACE" --context "$CONTEXT" -o json)
  LATEST_BACKUP_TIME=$(echo "$POSTGRES_CLUSTER_JSON" | jq -r '.status.pgbackrest.scheduledBackups | max_by(.completionTime) | .completionTime')

  LATEST_BACKUP_TIMESTAMP=$(date -d "$LATEST_BACKUP_TIME" +%s)
  CURRENT_TIMESTAMP=$(date +%s)
  BACKUP_AGE=$((CURRENT_TIMESTAMP - LATEST_BACKUP_TIMESTAMP))
  BACKUP_AGE_HOURS=$(awk "BEGIN {print $BACKUP_AGE/3600}")

  BACKUP_REPORTS+=("CrunchyDB Backup completed at $LATEST_BACKUP_TIME with age $BACKUP_AGE_HOURS hours.")

  if [ "$BACKUP_AGE" -gt "$MAX_AGE" ]; then
    ISSUES+=("$(generate_issue "The latest backup for the CrunchyDB PostgreSQL cluster \`$OBJECT_NAME\` is older than the acceptable limit of $BACKUP_MAX_AGE hour(s)." "$LATEST_BACKUP_TIME" "$BACKUP_AGE_HOURS")")
  else
    BACKUP_REPORTS+=("CrunchyDB Backup is healthy. Latest backup completed at $LATEST_BACKUP_TIME.")
  fi
}

# Function to check Zalando PostgreSQL Operator backup
check_zalando_backup() {
  # Assuming that we need to log in to the database to check backup status
  POD_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get pods -n "$NAMESPACE" --context "$CONTEXT" -l application=spilo -o jsonpath="{.items[0].metadata.name}")

  LATEST_BACKUP_TIME=$(${KUBERNETES_DISTRIBUTION_BINARY} exec -n "$NAMESPACE" "$POD_NAME" --context "$CONTEXT" -c "$DATABASE_CONTAINER" -- bash -c 'psql -U postgres -t -c "SELECT MAX(backup_time) FROM pg_stat_archiver;"')
  LATEST_BACKUP_TIMESTAMP=$(date -d "$LATEST_BACKUP_TIME" +%s)
  CURRENT_TIMESTAMP=$(date +%s)
  BACKUP_AGE=$((CURRENT_TIMESTAMP - LATEST_BACKUP_TIMESTAMP))
  BACKUP_AGE_HOURS=$(awk "BEGIN {print $BACKUP_AGE/3600}")

  BACKUP_REPORTS+=("Zalando Backup completed at $LATEST_BACKUP_TIME with age $BACKUP_AGE_HOURS hours.")

  if [ "$BACKUP_AGE" -gt "$MAX_AGE" ]; then
    ISSUES+=("$(generate_issue "The latest backup for the Zalando PostgreSQL cluster is older than the acceptable limit of $BACKUP_MAX_AGE hour(s)." "$LATEST_BACKUP_TIME" "$BACKUP_AGE_HOURS")")
  else
    BACKUP_REPORTS+=("Zalando Backup is healthy. Latest backup completed at $LATEST_BACKUP_TIME.")
  fi
}

# Check the backup based on API version
if [[ "$OBJECT_API_VERSION" == *"crunchydata.com"* ]]; then
  check_crunchy_backup
elif [[ "$OBJECT_API_VERSION" == *"zalan.do"* ]]; then
  check_zalando_backup
else
  echo "Unsupported API version: $OBJECT_API_VERSION. Please specify a valid API version containing 'postgres-operator.crunchydata.com' or 'acid.zalan.do'."
fi

OUTPUT_FILE="../backup_report.out"
rm -f $OUTPUT_FILE

# Print the backup reports and issues
echo "Backup Report:" > "$OUTPUT_FILE"
echo "Maximum age for last backup is set to: $BACKUP_MAX_AGE hour(s)"  >> "$OUTPUT_FILE"
for report in "${BACKUP_REPORTS[@]}"; do
  echo "$report" >> "$OUTPUT_FILE"
done

echo "" >> "$OUTPUT_FILE"
echo "Issues:" >> "$OUTPUT_FILE"
echo "[" >> "$OUTPUT_FILE"
for issue in "${ISSUES[@]}"; do
  echo "$issue," >> "$OUTPUT_FILE"
done
# Remove the last comma and close the JSON array
sed -i '$ s/,$//' "$OUTPUT_FILE"
echo "]" >> "$OUTPUT_FILE"

Helpful Links

• Bash shell scripting tutorial
• Postgres backup commands documentation
• Kubernetes operator documentation

Run DB Queries for Cluster acid-minimal-cluster2 in Namespace postgres-database

What does it do?

This script is designed to execute health queries for different versions of PostgreSQL operators in a Kubernetes environment. It generates a report of query results and any issues encountered during execution, and then writes the report to an output file in JSON format.

Command

DISTRIBUTION="Kubernetes" NAMESPACE="postgres-database" CONTEXT="gke_runwhen-nonprod-sandbox_us-central1_sandbox-cluster-1-cluster" KUBERNETES_DISTRIBUTION_BINARY="kubectl" WORKLOAD_NAME="-l application=spilo,cluster-name=acid-minimal-cluster2" RESOURCE_LABELS="cluster-name=acid-minimal-cluster2" OBJECT_API_VERSION="acid.zalan.do/v1" OBJECT_NAME="acid-minimal-cluster2" DATABASE_CONTAINER="postgres" OBJECT_KIND="postgresql.acid.zalan.do"  bash -c "$(curl -s https://raw.githubusercontent.com/runwhen-contrib/rw-cli-codecollection/main/codebundles/k8s-postgres-healthcheck/dbquery.sh)" _

Copy to clipboard Copied to clipboard

Learn more

This multi-line content is auto-generated and used for educational purposes. Copying and pasting the multi-line text might not function as expected.

#!/bin/bash

# Arrays to collect reports and issues
QUERY_REPORTS=()
ISSUES=()

# Function to sanitize a string for JSON compatibility
sanitize_string() {
  echo "$1" | sed 's/["]/ /g' | tr '\n' ' ' | tr '\r' ' '
}

# Function to generate an issue in JSON format and add to ISSUES array
generate_issue() {
  local description=$(sanitize_string "$1")
  local query=$(sanitize_string "$2")
  local error=$(sanitize_string "$3")

  issue=$(cat <<EOF
{
  "title": "Health Query Issue",
  "description": "$1",
  "description": "$description",
  "query": "$query",
  "error": "$error"
}
EOF
)
  ISSUES+=("$issue")
}

# Function to execute health queries for CrunchyDB PostgreSQL Operator
execute_crunchy_queries() {
  # Getting the name of the pod
  POD_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get pods -n "$NAMESPACE" --context "$CONTEXT" -l postgres-operator.crunchydata.com/role=master -o jsonpath="{.items[0].metadata.name}")

  # Iterating through each health query and executing it
  while IFS= read -r query; do
    echo "Executing query: $query"
    result=$(${KUBERNETES_DISTRIBUTION_BINARY} exec -n "$NAMESPACE" "$POD_NAME" --context "$CONTEXT" --container "$DATABASE_CONTAINER" -- bash -c "psql -U postgres -c \"$query\"" 2>&1)
    if [ $? -eq 0 ]; then
      echo "Query executed successfully."
      QUERY_REPORTS+=("Query: $query\nResult:\n$result\n")
    else
      echo "Query execution failed."
      generate_issue "Failed to execute health query" "$query" "$result"
    fi
  done <<< "$HEALTH_QUERIES"
}

# Function to execute health queries for Zalando PostgreSQL Operator
execute_zalando_queries() {
  # Getting the name of the pod
  POD_NAME=$(${KUBERNETES_DISTRIBUTION_BINARY} get pods -n "$NAMESPACE" --context "$CONTEXT" -l application=spilo -o jsonpath="{.items[0].metadata.name}")

  # Iterating through each health query and executing it
  while IFS= read -r query; do
    echo "Executing query: $query"
    result=$(${KUBERNETES_DISTRIBUTION_BINARY} exec -n "$NAMESPACE" "$POD_NAME" --context "$CONTEXT" --container "$DATABASE_CONTAINER" -- bash -c "psql -U postgres -c \"$query\"" 2>&1)
    if [ $? -eq 0 ]; then
      echo "Query executed successfully."
      QUERY_REPORTS+=("Query: $query\nResult:\n$result\n")
    else
      echo "Query execution failed."
      generate_issue "Failed to execute health query" "$query" "$result"
    fi
  done <<< "$HEALTH_QUERIES"
}

# Setting up health queries based on the OBJECT_API_VERSION
HEALTH_QUERIES=$QUERY

if [[ "$OBJECT_API_VERSION" == *"crunchydata.com"* ]]; then
  execute_crunchy_queries
elif [[ "$OBJECT_API_VERSION" == *"zalan.do"* ]]; then
  execute_zalando_queries
else
  echo "Unsupported API version. Please specify a valid API version containing 'crunchydata.com' or 'zalan.do'."
fi

# Print the query reports and issues
OUTPUT_FILE="../health_query_report.out"

echo "Health Query Report:" > "$OUTPUT_FILE"
for report in "${QUERY_REPORTS[@]}"; do
  echo -e "$report" >> "$OUTPUT_FILE"
done

echo "" >> "$OUTPUT_FILE"
echo "Issues:" >> "$OUTPUT_FILE"
echo "[" >> "$OUTPUT_FILE"
for issue in "${ISSUES[@]}"; do
  echo "$issue," >> "$OUTPUT_FILE"
done
# Remove the last comma and close the JSON array
sed -i '$ s/,$//' "$OUTPUT_FILE"
echo "]" >> "$OUTPUT_FILE"

echo "Health query report and issues have been written to $OUTPUT_FILE."

Helpful Links

• PostgreSQL Operators in Kubernetes
• Kubernetes Health Checks
• JSON Output Format

---