Access Postgres CLI via Port Forward

This documentation will help to connect to managed Postgres via psql using port forward.

CLI Setup

The first step is to setup psql on your local machine. For this, go through following steps. If you already have CLI setup, you can skip this section.

1. Run following command in terminal:

   sudo apt-get install -y postgresql-client
   

2. Run following command to verify CLI installation:

   psql --version
   

Create Port Forwarding to Postgres Service

Before running psql command we need to port forward to Postgres Pod running in cluster. To achieve this, perform following steps:

1. In OpenShift console, click on your username on top right corner and click Copy login command as shown below:

   

2. Click on Display Token and then copy oc login command.

3. Run this command inside your terminal. Now you have access to interact with your cluster using terminal.

4. Run following command to locate the pod to which we need to create port forward.

   PG_CLUSTER_PRIMARY_POD=$(oc get pod -n <postgres-namespace> -o name -l postgres-operator.crunchydata.com/cluster=<postgres-cluster>,postgres-operator.crunchydata.com/role=master)
   

   Here <Postgres-namespace> is the name of namespace where Postgres is deployed (In current case its value is crunchy-postgres-instance). This name might be different for you. You can ask about this to cluster admin. <Postgres-cluster> is the name of cluster in Postgres that usually get created at time of deployment (In current case its value is postgres). You can ask about its name from cluster admin as well.

5. Upon printing the value stored in variable mentioned above, you'll get an output similar to one shown below:

   

6. Run the following command to create port forward.

   oc port-forward "${PG_CLUSTER_PRIMARY_POD}" <port> -n <postgres-namespace>
   

   • <port> is the port number for this pod to which we need to create port forward. In current case its value is 5432

   • This should result in following output:

     

7. At this moment, you need to open a second terminal (while keeping this running) and run following command:

   psql -h localhost -U $PGUSER -d $PGDATABASE
   

   • $PGUSER is a reference to a variable that must be set (or you can replace this with username) used to connect to Postgres. This can easily be retrieved from Vault.

   • $PGDATABASE contains name of the database that you want to connect to. This can also be retrieved from Vault.

   • This should result in following output:

     

   • Paste in the password that you can get from Vault.

8. At this moment, you should have access to psql. You can run \q at anytime to exit out of this terminal access.

Caveat

When using oc port-forward, the connection can be fragile. This has been reported as an issue. A workaround is to disable SSL mode.