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

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, we only want to demonstrate how to access the database in this course. The database instance we create here, will therefore only store the database in the filesystem local to the container. This means that if the database were restarted, any changes would be lost. When you deploy a database to be used with your own applications, you would want to look at using persistent volumes.

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

oc rollout status dc/database

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

When using a database with your front end web application, you will need to configure the web application to know about the database. We are going to skip that in this course.

In order to know where the database is running to connect to, run the command:

oc get pods --selector name=database

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

NAME               READY     STATUS    RESTARTS   AGE
database-1-9xv8n   1/1       Running   0          1m

To make it easier to reference the name of the pod, capture the name of the pod in an environment variable by running:

POD=`oc get pods --selector name=database -o custom-columns=NAME:.metadata.name --no-headers`; echo $POD

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 $POD

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 output similar to:

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 launcher
193 pts/0    Ss     0:00 /bin/sh
257 pts/0    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 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

To exit the interactive shell run:

exit

Anything you want to do to the database could be be done through any database admin tool included in the container. This will though be limited to console based tools and you would not be able to use a GUI based tool which 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.

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 only 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 only wanting to access the database to perform administration 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 which 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 use the oc port-forward command. You 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 on the local machine where oc is being run, as port 15432, use:

oc port-forward $POD 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 would then use a separate terminal window to run the administration tool which could connect via the forwarded connection. In this case, as we only have the one terminal window, we ran the oc port-forward command as a background job.

You can see that it is still running using:

jobs

With the port forwarding in place, you can now run psql again. This time it is being run from the local machine, and not 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)
WARNING: psql version 9.2, server version 9.5.
         Some psql features might not work.
Type "help" for help.

sampledb=>

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

To exit psql enter:

\q

Because we ran the oc port-forward command as a background process, we can kill it when done using:

kill %1

Running jobs again we can see it is terminated.

jobs

In this exercise we used psql, however you could also use a GUI based database administration tool running on your local machine as well.