OpenShift 4.X Foundations - Access to a database service from outside the cluster using port-forwarding

When you're developing a cloud-native application on any PaaS, static provisioning of persistent volumes existed. One of the benefits of OpenShift over a traditional PaaS is that you have access to dynamic provisioning of persistent volumes. In this week's article, I will fix a technical challenge of my hypothetical customer Alex. Alex has a backend application outside of his OpenShift cluster and is trying to access a database within OpenShift.

Creating an initial project

The first step is to log in using the developer perspective and create a new project. The following commands take care of this:

# log in from developer perspective
oc login -u developer -p developer


# creating a new project called 'myproject'
oc new-project myproject

Deploying a PostgreSQL Database

The next step is to add a PostgreSQL database application to "myproject":

# Creating a postgresql-ephemeral application with following parameters:

oc new-app postgresql-ephemeral --name database --param DATABASE_SERVICE_NAME=database --param POSTGRESQL_DATABASE=sampledb --param POSTGRESQL_USER=username --param POSTGRESQL_PASSWORD=password

This will start up an instance of a PostgreSQL database. Although a database would normally be paired with a persistent volume, I only want to demonstrate how to access the database from a remote application in this article (without persistent volumes). Persistent volume on OpenShift will be covered in detail in a later article. The database instance created here will only store the database in the filesystem local to the container. This means that if the database were restarted, any changes would be lost. To monitor the deployment status of the database application:

# Monitor rollout of the database deployment
oc rollout status dc/database

# A successful rollout will include the following message
# replication controller "database-1" successfully rolled out

Remote shell to the database container

During this step, I find out the pod details of the database container, capture the name of the pod in an environment, remote shell to the pod and execute PostgreSQL commands. To learn more about the remote shell command, visit this page.

# This will output details of the pod which is running the database
oc get pods --selector app=database

# Capturing name of the pod in an environment variable for easy reference
POD=`oc get pods --selector app=database -o custom-columns=name:.metadata.name --no-headers`; echo $POD

# Creating an interactive shell within the same container
oc rsh $POD

# Checking all running processes owned by the user
ps -x 

The output of running ps -x will be something like this:

PID TTY      STAT   TIME COMMAND
      1 ?        Ss     0:00 postgres
     60 ?        Ss     0:00 postgres: logger process
     62 ?        Ss     0:00 postgres: checkpointer process
     63 ?        Ss     0:00 postgres: writer process
     64 ?        Ss     0:00 postgres: wal writer process
     65 ?        Ss     0:00 postgres: autovacuum launcher process
     66 ?        Ss     0:00 postgres: stats collector process
     67 ?        Ss     0:00 postgres: bgworker: logical replication
    
    146 pts/0    R+     0:00 ps -x

Executing psql commands locally

During this step, I connect to the sampledb using username username (try to choose a less confusing username ;) )

# Invoking PostgreSQL prompt
psql sampledb username

I could now dynamically create database tables, add data, or modify existing data. \q exits the database prompt and exit command takes me out of the interactive shell of the database container.

Remote connection to database application using port forwarding

In order to access the database from a remote machine/application, it will be necessary to expose the database service outside of the OpenShift cluster. Typically, Routes would be used to allow access to the database from another service within the OpenShift cluster. Since the application in question is outside the cluster, I'll be using port forwarding. The act of setting up port forwarding creates a port on the local machine which one can then use to connect to the database using a database administration tool.

To setup port forwarding between a remote machine and the database running on OpenShift, I use the oc port-forward command. & keeps is as a running background process.

# Creating a connection to the PostgreSQL database
# port on actual database container 5432
# port on remote machine from where connection is made, is 15432

oc port-forward $POD 15432:5432 &

With the port forwarding in place, I can now run psql again. This time it is being run from a remote machine, and not inside of the container. Because the forwarded connection is using port 15432 on the remote machine, I need to explicitly tell it to use that port rather than the default database port.

# Connecting to the database from remote machine
psql sampledb username --host=127.0.0.1 --port=15432

This will connect to the sampledb database; from a remote machine:

Execute \q to exit from the PostgreSQL prompt and kill %1 to kill the background process.

Congratulations! You just learned how to set up a connection between a service running inside of OpenShift and a remote application/machine. That's it for this week. See you next Monday with another OpenShift 4.X article.

Reference commands

oc rsh <pod-name>: Start an interactive shell in the specified pod.

oc port-forward <pod-name> <local-port>:<remote-port>: Forward connections between your local machine and an application running in OpenShift. The remote port is the port the application running in OpenShift accepts connections. The local port is the port on your local machine you wish to make it available as, and to which any client application running on your local machine would connect to.

oc port-forward <pod-name> :<remote-port>: Forward connections between your local machine and an application running in OpenShift. The remote port is the port the application running in OpenShift accepts connections. As a local port to use is not specified, a random local port is used, with the port number being displayed. Any client application running on your local machine would connect to the randomly assigned port.