Connecting to a Database Using Port Forwarding in OpenShift

OpenShift and Databases

One of the benefits of OpenShift over a traditional Platform-as-a-Service (PaaS) is that you can have access to persistent volumes. This means you can attach storage to your web applications or run applications such as databases.

Databases deployed to OpenShift will typically be used to support the operations of a front-end web application, and therefore only need to be accessible by other applications running in the same OpenShift cluster.

To manage a database, it can be convenient to use a database client running on your own local machine. This could be a command line client or a GUI-based application.

In this post, you will learn how to access a database using an interactive shell, and also how to use port forwarding to temporarily expose a database outside of OpenShift, allowing you to access it from a database tool running on your own local machine.

Deploying a PostgreSQL Database

To create a database that you can then connect to, run the following command:

    oc new-app postgresql-persistent --name database --param

    DATABASE_SERVICE-NAME=database --param POSTGRESQL_DATABASE=sampledb --param

    POSTGRESQL_USER=username --param POSTGRESQL_PASSWORD=password

This will start up a persistent instance of a PostgreSQL database.

To monitor progress as the database is deployed and made ready, run this command:

    oc rollout status dc/database

This command will exit once the database is ready to be used.

Starting an Interactive Shell

In order to know where the database is running so that we can connect to it, use this command:

    oc get pods --selector app=database

This will output the details of the pod that is running the database.

    NAME               READY   STATUS      RESTARTS    AGE

    database-1-9xv8n    1/1     Running     0           1m

To create an interactive shell within the same container running the database, you can use the oc rsh command, supplying it the name of the pod.

    oc rsh database-1-9xv8n

You could also access an interactive terminal session via a web browser by visiting the pod details from the web console.

You can see that you are in the container running the database by running:

    ps x

This will display an output similar to:

    PID    TTY     STAT    TIME    COMMAND

    1     ?       Ss      0:00    postgres

    71     ?       Ss      0:00    postgres: logger process

    73     ?       Ss      0:00    postgres: checkpointer process

    74     ?       Ss      0:00    postgres: writer process

    75     ?       Ss      0:00    postgres: wal writer process

    76     ?       Ss      0:00    postgres: autovacuum launcher process

    77     ?       Ss      0:00    postgres: stats collector process

    940     ?       Ss      0:00    /bin/sh

    957     ?       R+      0:00    ps x

Because you are in the same container, you could at this point run the database client for the database if provided in the container. For PostgreSQL, you would use the psql command.

    psql sampledb username

This will present you with the prompt for running the database operations via psql.

    psql (9.5.4)

    Type "help" for help.
 sampledb=>
 

You could now dynamically create database tables, add data, or modify existing data.

To exit psql enter:

    /q

Then, to exit the interactive shell, run:

    exit

Anything you want to do to the database could be done through any database admin tool included in the container. However, this will be limited to console-based tools, and you would not be able to use a GUI-based tool that runs from your local machine, as the database is still not exposed outside of the OpenShift cluster at this point.

If you need to run database script files to perform operations on the database, you would also need to first copy those files into the database container using the oc rsync command.

Creating a Remote Connection

In order to access the database from a database administration tool running on your own local machine, it will be necessary to expose the database service outside of the OpenShift cluster.

When a web application is made visible outside of the OpenShift cluster a route is created. This enables a user to use a URL to access the web application from a web browser. A route is usually used for web applications which use the HTTP protocol. A route cannot be used to expose a database, as they would typically use their own distinct protocol, and routes would not be able to work with the database protocol.

There are ways of permanently exposing a database service outside of an OpenShift cluster. However, the need to do that would be an exception and not the norm. If you're only wanting to access the database to perform administrative tasks on it, you can instead create a temporary connection back to your local machine using port forwarding. The act of setting up port forwarding creates a port on your local machine that you can then use to connect to the database using a database administration tool.

To setup port forwarding between a local machine and the database running on OpenShift, you can use the oc port-forward command. You will need to pass the name of the pod and details of the port the database service is using, as well as the local port to use.

The format for the command is:

    oc port-forward <pod-name> <local-port>:<remote:port>

To create a connection to the PostgreSQL database, which uses port 5432, and expose it as port 15432 on the local machine where oc is being run, use:

    oc port-forward database-1-9xv8n 15432:5432

Port 15432 is used here for the local machine, rather than using 5432, in case an instance of PostgreSQL was also running on the local machine. If an instance of PostgreSQL was running on the local machine and the same port was used, setting up the connection would fail as the port would already be in use.

If you do not know what ports may be available, you can instead use the following format for the command:

    oc port-forward <pod-name> :<remote-port>

In this form, the local port is left off, resulting in a random available port being used. You would need to look at the output from the command to work out what port number was used for the local port, and use that.

When the oc port-forward command is run and the connection setup, it will stay running until the command is interrupted. You can then use a separate terminal window to run the administration tool, which will connect via the forwarded connection.

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

    psql sampledb username --host=127.0.0.1 --port=15432

This will again present you with the prompt for running database operations via psql.

    Handling connection for 5432

    psql (9.2.18, server 9.5.4)

        Type "help" for help.
 sampledb=>
 

You could now dynamically create database tables, add data, or modify existing data.

To exit psql, enter:

    /q

We can now stop oc port-forward by using ctrl-C to interrupt the process.

In these examples, we used psql. However, you could also use a GUI-based database administration tool running on your local machine.

Summary

In this post, we explored oc commands you would use for setting up a temporary connection between your local machine and a service running inside of OpenShift.

You can find a summary of the key commands covered below. To see more information on each oc command, run it with the --help option in your terminal.

• oc rsh <pod-name>: This starts an interactive shell in the specified pod.

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

• oc port-forward <pod-name> :<remote-port>: This forwards connections between your local machine and an application running in OpenShift. The remote port is the port the application running in OpenShift uses to accept 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.

Now that you've gotten started with port-forwarding in OpenShift, what other topics would you like to see covered? Leave a comment letting us know!