How to Test DB Connectivity from the Astronomer Software Platform

Get the astronomer-bootstrap Connection string from Secret

kubectl get secret astronomer-bootstrap -o jsonpath='{.data.connection}' | base64 -d

Spin Up a new postgres pod.

kubectl apply -f https://raw.githubusercontent.com/nandlalyadav57/astro/main/new_pod.yml

newpod.yaml

apiVersion: v1
kind: Pod
metadata:
  name: postgres-client
  labels:
    app: postgres-client
spec:
  containers:
  - image: postgres:latest
    command:
      - sleep
      - "3600"
    imagePullPolicy: IfNotPresent
    name: postgres-client
  restartPolicy: Always

Login to Pod:

kubectl exec -it postgres-client -- sh -n astronomer

Add postgresql client:

apt update && apt install postgresql-client

connect to astronomer_houston

psql <step1 output from above commands>


psql postgres://airflow:pjqBDMwzcU3ED2T270qjmJlEMeqqeFzz@astrodb-1bfdde7e1c617119.cluster-cnuxtliiayqh.u

Or

Instead of the above commands.You can use this command as well for checking Houston DB connectivity: This can spin up a new pod & use secret information to connect directly

kubectl run psql --rm -it --restart=Never --namespace astronomer --image bitnami/postgresql --command -- psql $(kubectl get secret -n astronomer astronomer-houston-backend --template='{{.data.connection | base64decode }}' | sed 's/?.*//g')

Show Databases

\l (to list databases)
\c <releasename>_houston
astronomer_houston=> \dt;
set search_path="houston$default";    (Set search path )

Remove the Postgres-client pod

kubectl delete -f newpod.yaml -n astronomer
kubectl delete  pod psql -n astronomer