Connecting to the PostgreSQL Database

This topic tells you how to connect to the PostgreSQL database. To connect to the PostgreSQL database, you need the following values:

  • database name
  • user name
  • password
  • database host
  • database port
  • database CA certificate

  • To obtain the database name, user name, password, and CA certificate, run these commands:

    db_name=$(kubectl get secret postgres-db-secret -n metadata-store -o json | jq -r '.data.POSTGRES_DB' | base64 -d)
    db_username=$(kubectl get secret postgres-db-secret -n metadata-store -o json | jq -r '.data.POSTGRES_USER' | base64 -d)
    db_password=$(kubectl get secret postgres-db-secret -n metadata-store -o json | jq -r '.data.POSTGRES_PASSWORD' | base64 -d)
    
    db_ca_dir=$(mktemp -d -t ca-cert-XXXX)
    db_ca_path="$db_ca_dir/ca.crt"
    kubectl get secrets postgres-db-tls-cert -n metadata-store -o json | jq -r '.data."ca.crt"' | base64 -d > $db_ca_path
    

    If the password was auto-generated, the earlier password command returns an empty string. Instead, run:

    db_password=$(kubectl get secret postgres-db-password -n metadata-store -o json | jq -r '.data.DB_PASSWORD' | base64 -d)
    
  • In a separate terminal, run:

    kubectl port-forward service/metadata-store-db 5432:5432 -n metadata-store
    
  • Set the database host and port values on the first terminal by running:

    db_host="localhost"
    db_port=5432
    
  • To port forward to a different local port number, use this command template:

    kubectl port-forward service/metadata-store-db <LOCAL_PORT>:5432 -n metadata-store
    

With this setup, you can now connect to the database and make queries as in the following example:

$ psql "host=$db_host port=$db_port user=$db_username dbname=$db_name sslmode=verify-ca \
sslrootcert=$db_ca_path" -c "SELECT * FROM images"

You can also use GUI clients, such as Postico or DBeaver to interact with the database.

check-circle-line exclamation-circle-line close-line
Scroll to top icon