Connect to a Postgres Cluster

It's one thing to create a Postgres cluster; it's another thing to connect to it. Let's explore how PGO makes it possible to connect to a Postgres cluster!

Background: Services, Secrets, and TLS

PGO creates a collection of Kubernetes Services to provide stable endpoints for connecting to your Postgres databases. These endpoints make it easy to provide a consistent way for your application to maintain connectivity to your data. To inspect what services are available, you can run the following command:

kubectl -n postgres-operator get svc --selector=postgres-operator.crunchydata.com/cluster=hippo

which will yield something similar to:

NAME              TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
hippo-ha          ClusterIP   10.103.73.92   <none>        5432/TCP   3h14m
hippo-ha-config   ClusterIP   None           <none>        <none>     3h14m
hippo-pods        ClusterIP   None           <none>        <none>     3h14m
hippo-primary     ClusterIP   None           <none>        5432/TCP   3h14m
hippo-replicas    ClusterIP   10.98.110.215  <none>        5432/TCP   3h14m

You do not need to worry about most of these Services, as they are used to help manage the overall health of your Postgres cluster. For the purposes of connecting to your database, the Service of interest is called hippo-primary. Thanks to PGO, you do not need to even worry about that, as that information is captured within a Secret!

When your Postgres cluster is initialized, PGO will bootstrap a database and create a Postgres user that your application can use to access the database. This information is stored in a Secret named with the pattern <clusterName>-pguser-<userName>. For our hippo cluster, this Secret is called hippo-pguser-hippo. This Secret contains the information you need to connect your application to your Postgres database:

user: The name of the user account.
password: The password for the user account.
dbname: The name of the database that the user has access to by default.
host: The name of the host of the database. This references the Service of the primary Postgres instance.
port: The port that the database is listening on.
uri: A PostgreSQL connection URI that provides all the information for logging into the Postgres database.
jdbc-uri: A PostgreSQL JDBC connection URI that provides all the information for logging into the Postgres database via the JDBC driver.

All connections are over TLS. PGO provides its own certificate authority (CA) to allow you to securely connect your applications to your Postgres clusters. This allows you to use the verify-full "SSL mode" of Postgres, which provides eavesdropping protection and prevents MITM attacks. You can also choose to bring your own CA, which is described later in this tutorial in the Customize Cluster section.

Modifying Service Type, NodePort Value and Metadata

By default, PGO deploys Services with the ClusterIP Service type. Based on how you want to expose your database, you may want to modify the Services to use a different Service type and NodePort value.

You can modify the Services that PGO manages from the following attributes:

spec.service - this manages the Service for connecting to a Postgres primary.
spec.proxy.pgBouncer.service - this manages the Service for connecting to the PgBouncer connection pooler.
spec.userInterface.pgAdmin.service - this manages the Service for connecting to the pgAdmin management tool.

For example, say you want to set the Postgres primary to use a NodePort service, a specific nodePort value, and set a specific annotation and label, you would add the following to your manifest:

spec:
  service:
    metadata:
      annotations:
        my-annotation: value1
      labels:
        my-label: value2
    type: NodePort
    nodePort: 32000

For our hippo cluster, you would see the Service type and nodePort modification as well as the annotation and label. For example:

kubectl -n postgres-operator get svc --selector=postgres-operator.crunchydata.com/cluster=hippo

will yield something similar to:

NAME              TYPE           CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
hippo-ha          NodePort       10.105.57.191   <none>        5432:32000/TCP   48s
hippo-ha-config   ClusterIP      None            <none>        <none>           48s
hippo-pods        ClusterIP      None            <none>        <none>           48s
hippo-primary     ClusterIP      None            <none>        5432/TCP         48s
hippo-replicas    ClusterIP      10.106.18.99    <none>        5432/TCP         48s

and the top of the output from running

kubectl -n postgres-operator describe svc hippo-ha

will show our custom annotation and label have been added:

NAME:              hippo-ha
Namespace:         postgres-operator
Labels:            my-label=value2
                   postgres-operator.crunchydata.com/cluster=hippo
                   postgres-operator.crunchydata.com/patroni=hippo-ha
Annotations:       my-annotation: value1

Note that setting the nodePort value is not allowed when using the (default) ClusterIP type, and it must be in-range and not otherwise in use or the operation will fail. Additionally, be aware that any annotations or labels provided here will win in case of conflicts with any annotations or labels a user configures elsewhere.

Finally, if you are exposing your Services externally and are relying on TLS verification, you will need to use the custom TLS features of PGO).

Connect via `psql` in the Terminal

Connect Directly

If you are on the same network as your PostgreSQL cluster, you can connect directly to it using the following command:

psql $(kubectl -n postgres-operator get secrets hippo-pguser-hippo -o go-template='{{.data.uri | base64decode}}')

Connect Using a Port-Forward

In a new terminal, create a port forward. If you are using Bash, you can run the following commands:

PG_CLUSTER_PRIMARY_POD=$(kubectl get pod -n postgres-operator -o name -l postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master)
kubectl -n postgres-operator port-forward "${PG_CLUSTER_PRIMARY_POD}" 5432:5432

For Powershell environments:

$env:PG_CLUSTER_PRIMARY_POD=(kubectl get pod -n postgres-operator -o name -l postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master)
kubectl -n postgres-operator port-forward "$env:PG_CLUSTER_PRIMARY_POD" 5432:5432

Establish a connection to the PostgreSQL cluster. If you are using Bash, you can run:

PG_CLUSTER_USER_SECRET_NAME=hippo-pguser-hippo

PGPASSWORD=$(kubectl get secrets -n postgres-operator "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.password | base64decode}}') \ 
PGUSER=$(kubectl get secrets -n postgres-operator "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.user | base64decode}}') \ 
PGDATABASE=$(kubectl get secrets -n postgres-operator "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.dbname | base64decode}}') \ 
psql -h localhost

For Powershell environments:

$env:PG_CLUSTER_USER_SECRET_NAME="hippo-pguser-hippo" 

$env:PGPASSWORD=(kubectl get secrets -n postgres-operator "$env:PG_CLUSTER_USER_SECRET_NAME" -o go-template='{{.data.password | base64decode}}') 
$env:PGUSER=(kubectl get secrets -n postgres-operator "$env:PG_CLUSTER_USER_SECRET_NAME" -o go-template='{{.data.user | base64decode}}') 
$env:PGDATABASE=(kubectl get secrets -n postgres-operator "$env:PG_CLUSTER_USER_SECRET_NAME" -o go-template='{{.data.dbname | base64decode}}') 
psql -h localhost

Connecting With pgAdmin

Crunchy Postgres for Kubernetes also provides a pgAdmin image for users who prefer working with a graphical user interface. For more information, see our documentation on pgAdmin 4.

Connect an Application

For this tutorial, we are going to connect Keycloak, an open source identity management application. Keycloak can be deployed on Kubernetes and is backed by a Postgres database. While we provide an example of deploying Keycloak and a PostgresCluster in the Postgres Operator examples repository, the manifest below deploys it using our hippo cluster that is already running:

kubectl apply --filename=- <<EOF
apiVersion: apps/v1
kind: Deployment
metadata:
  name: keycloak
  namespace: postgres-operator
  labels:
    app.kubernetes.io/name: keycloak
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: keycloak
  template:
    metadata:
      labels:
        app.kubernetes.io/name: keycloak
    spec:
      containers:
      - image: quay.io/keycloak/keycloak:latest
        args: ["start-dev"]
        name: keycloak
        env:
        - name: DB_VENDOR
          value: "postgres"
        - name: DB_ADDR
          valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: host } }
        - name: DB_PORT
          valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: port } }
        - name: DB_DATABASE
          valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: dbname } }
        - name: DB_USER
          valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: user } }
        - name: DB_PASSWORD
          valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: password } }
        - name: KEYCLOAK_ADMIN
          value: "admin"
        - name: KEYCLOAK_ADMIN_PASSWORD
          value: "admin"
        - name: KC_PROXY
          value: "edge"
        ports:
        - name: http
          containerPort: 8080
        - name: https
          containerPort: 8443
        readinessProbe:
          httpGet:
            path: /realms/master
            port: 8080
      restartPolicy: Always
EOF

Notice this part of the manifest:

- name: DB_ADDR
  valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: host } }
- name: DB_PORT
  valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: port } }
- name: DB_DATABASE
  valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: dbname } }
- name: DB_USER
  valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: user } }
- name: DB_PASSWORD
  valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: password } }

The above manifest shows how all of these values are derived from the hippo-pguser-hippo Secret. This means that we do not need to know any of the connection credentials or have to insecurely pass them around -- they are made directly available to the application!

Using this method, you can tie an application directly into your GitOps pipeline that connects to Postgres without any prior knowledge of how PGO will deploy Postgres: all of the information your application needs is propagated into the Secret!

Next Steps

Now that we have seen how to connect an application to a cluster, let's learn how to create a high availability Postgres cluster!