Connect to a Postgres Cluster
pgo show user hippo
yields output similar to:
CLUSTER USERNAME PASSWORD EXPIRES STATUS ERROR ------- -------- -------------------------------- ------- ------ ----- hippo testuser securerandomlygeneratedpassword never ok
If you need to get the password of one of the system or privileged accounts, you will need to use the
--show-system-accounts flag, i.e.:
pgo show user hippo --show-system-accounts
CLUSTER USERNAME PASSWORD EXPIRES STATUS ERROR ------- ----------- -------------------------------- ------- ------ ----- hippo postgres B>xy}9+7wTVp)gkntf}X|H@N never ok hippo primaryuser ^zULckQy-\KPws:2UoC+szXl never ok hippo testuser securerandomlygeneratedpassword never ok
Let’s look at three different ways we can connect to the PostgreSQL cluster.
The PostgreSQL Operator creates a service with the same name as the cluster. See for yourself! Get a list of all of the Services available in the
kubectl -n pgo get svc NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE hippo ClusterIP 10.96.218.63 <none> 2022/TCP,5432/TCP 59m hippo-backrest-shared-repo ClusterIP 10.96.75.175 <none> 2022/TCP 59m postgres-operator ClusterIP 10.96.121.246 <none> 8443/TCP,4171/TCP,4150/TCP 71m
Let’s connect the
hippo cluster. First, in a different console window, set up a port forward to the
kubectl -n pgo port-forward svc/hippo 5432:5432
You can connect to the database with the following command, substituting
datalake for your actual password:
PGPASSWORD=datalake psql -h localhost -p 5432 -U testuser hippo
You should then be greeted with the PostgreSQL prompt:
psql (12.4) Type "help" for help. hippo=>
Connecting via pgAdmin 4
pgAdmin 4 is a graphical tool that can be used to manage and query a PostgreSQL database from a web browser. The PostgreSQL Operator provides a convenient integration with pgAdmin 4 for managing how users can log into the database.
To add pgAdmin 4 to
hippo, you can execute the following command:
pgo create pgadmin -n pgo hippo
It will take a few moments to create the pgAdmin 4 instance. The PostgreSQL Operator also creates a pgAdmin 4 service. See for yourself! Get a list of all of the Services available in the
kubectl -n pgo get svc NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE hippo ClusterIP 10.96.218.63 <none> 2022/TCP,5432/TCP 59m hippo-backrest-shared-repo ClusterIP 10.96.75.175 <none> 2022/TCP 59m hippo-pgadmin ClusterIP 10.96.165.27 <none> 5050/TCP 5m1s postgres-operator ClusterIP 10.96.121.246 <none> 8443/TCP,4171/TCP,4150/TCP 71m
Let’s connect to our
hippo cluster via pgAdmin 4! In a different terminal, set up a port forward to pgAdmin 4:
kubectl -n pgo port-forward svc/hippo-pgadmin 5050:5050
Navigate your browser to http://localhost:5050 and use your database username (
testuser) and password (e.g.
datalake) to log in. Though the prompt says “email address”, using your PostgreSQL username will work:
(There are occasions where the initial credentials do not properly get set in pgAdmin 4. If you have trouble logging in, try running the command
pgo update user -n pgo hippo --username=testuser --password=datalake).
Once logged into pgAdmin 4, you will be automatically connected to your database. Explore pgAdmin 4 and run some queries!
Connecting from a Kubernetes Application
Within a Kubernetes Cluster
Connecting a Kubernetes application that is within the same cluster that your PostgreSQL cluster is deployed in is as simple as understanding the default Kubernetes DNS system. A cluster created by the PostgreSQL Operator automatically creates a Service of the same name (e.g.
Following the example we’ve created, the hostname for our PostgreSQL cluster is
hippo.pgo.svc.cluster.local). To get your exact DNS resolution rules, you may need to consult with your Kubernetes administrator.
Knowing this, we can construct a Postgres URI that contains all of the connection info:
which breaks down as such:
postgres: the scheme, i.e. a Postgres URI
testuser: the name of the PostgreSQL user
securerandomlygeneratedpassword: the password for
hippo.jkatz.svc.cluster.local: the hostname
5432: the port
hippo: the database you want to connect to
If your application or connection driver cannot use the Postgres URI, the above should allow for you to break down the connection string into its appropriate components.
Outside a Kubernetes Cluster
To connect to a database from an application that is outside a Kubernetes cluster, you will need to set one of the following:
- A Service type of
- An Ingress. The PostgreSQL Operator does not provide any management for Ingress types.
To have the PostgreSQL Operator create a Service that is of type
NodePort, you can use the
--service-type flag as part of creating a PostgreSQL cluster, e.g.:
pgo create cluster hippo --service-type=LoadBalancer
You can also set the
ServiceType attribute of the PostgreSQL Operator configuration to provide a default Service type for all PostgreSQL clusters that are created.
We’ve created a cluster and we’ve connected to it! Now, let’s learn what customizations we can make as part of the cluster creation process.