This topic tells you how to connect to the PostgreSQL database. To connect to the PostgreSQL database, you need the following values:
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.