How to Deploy PostgreSQL on OpenShift in Ten Minutes!

Red Hat OpenShift is a popular enterprise Kubernetes application platform. Openshift has been traditionally considered a platform for stateless applications. However, many developers are exploring the possibility of running stateful workloads, such as PostgreSQL, on OpenShift. If you are considering extending OpenShift for stateful workloads, this tutorial will help you experiment on your existing OpenShift environment by providing step-by-step instructions.

Deploy PostgreSQL on OpenShift Using Helm

In this tutorial, we will deploy a PostgreSQL database on OpenShift using Helm with a single command, load data in the database, and register the PostgreSQL Helm release as an application with ROBIN Storage.

Before you begin this tutorial, install ROBIN Storage on your existing OpenShift cluster.

After you have installed ROBIN, let us install the PostgreSQL client as the first step, so that we can use the PostgreSQL database once deployed.

yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm
yum install -y postgresql10

Let us now confirm that OpenShift cluster is up and running.

oc get nodes

You should see an output similar to below, with the list of nodes and their status as “Ready”.

Deploy PostgreSQL on OpenShift

Let us confirm that ROBIN is up and running. Run the following command to verify that ROBIN Storage is ready.

oc get robincluster -n robinio
Confirm ROBIN is deployed

Let us now setup helm. ROBIN has helper utilities to initialize helm.

robin k8s deploy-tiller-objects
robin k8s helm-setup
helm repo add stable https://kubernetes-charts.storage.googleapis.com

Let us now create a PostgreSQL database using Helm and OBIN Storage. The following command will install PostgreSQL. We are setting the storageClass to robin-0-3 to benefit from the data management capabilities that ROBIN Storage brings.

helm install stable/postgresql --name movies --tls --set
persistence.storageClass=robin-0-3 --namespace default --tiller-namespace default

Run the following command to verify our database called “movies” is deployed and all relevant Kubernetes resources are ready.

helm list -c movies --tls --tiller-namespace default

You should be able to see an output showing the status of your Postgresql database.

PostgreSQL on OpenShift database status

You would also want to make sure Postgres database services are running before proceeding further. Run the following command to verify the services are running.

oc get service | grep movies
PostgreSQL on OpenShift - Deploy

Now that we know the PostgreSQL services are up and running, let us get the Service IP address of our database.

export IP_ADDRESS=$(kubectl get service films-postgresql -o jsonpath={.spec.clusterIP})

Let us now get the Password of our Postgres database from Kubernetes Secret.

export POSTGRES_PASSWORD=$(kubectl get secret --namespace default
movies-postgresql -o jsonpath="{.data.postgresql-password}" |
base64 --decode)

Adding data to the PostgreSQL database

We’ll use movie data to load data into our PostgreSQL database. Let’s create a database “testdb” and connect to this “testdb”.

PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U postgres -c
"CREATE DATABASE testdb;"

For the purpose of this tutorial, let us create a table named “movies”.

PGPASSWORD=”$POSTGRES_PASSWORD” psql -h $IP_ADDRESS -U postgres -d testdb -c
“CREATE TABLE movies (movieid TEXT, year INT, title TEXT, genre TEXT);”

To perform various operations, we need some sample data. Let us add 9 movies to the “movies” table.

PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U postgres -d testdb -c "INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0360556', 2018, 'Fahrenheit 451', 'Drama');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0365545', 2018, 'Nappily Ever After', 'Comedy');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0427543', 2018, 'A Million Little Pieces', 'Drama');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0432010', 2018, 'The Queen of Sheba Meets the Atom Man', 'Comedy');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0825334', 2018, 'Caravaggio and My Mother the Pope', 'Comedy');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0859635', 2018, 'Super Troopers 2', 'Comedy');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0862930', 2018, 'Dukun', 'Horror');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0891581', 2018, 'RxCannabis: A Freedom Tale', 'Documentary');INSERT INTO movies (movieid, year, title, genre) VALUES ('tt0933876', 2018, 'June 9', 'Horror');"

Let us verify the data was added to the “movies” table by running the following command.

PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U
postgres -d testdb -c "SELECT * from movies;"

You should see an output with the “movies” table and the nine rows in it as follows:

Deploy PostgreSQL on OpenShift - Load data

We now have a PostgreSQL database with a table and some sample data. Now, let’s take a look at the data management capabilities ROBIN brings, such as taking snapshots, making clones, and creating backups.

Registering the PostgreSQL Helm release as an application

To benefit from the data management capabilities, we’ll register our PostgreSQL database with ROBIN. Doing so will let ROBIN map and track all resources associated with the Helm release for this PostgreSQL database.

Let us first get the ‘robin’ client utility and set it up to work with this OpenShift cluster.

To get the link to download robin client do:

oc describe robinclusters -n robinioa

You should see an output similar to below:

Deploy PostgreSQL on OpenShift - Register Helm Release

Find the field ‘Get _ Robin _ Client’ and run the corresponding command to get the ROBIN client.

curl -k https://10.9.40.125:29451/api/v3/robin_server/client/linux -o robin

In the same output above notice the field ‘Master _ Ip’ and use it to set up your ROBIN client to work with your OpenShift cluster, by running the following command.

export ROBIN_SERVER=10.9.40.125

Now you can register the Helm release as an application with ROBIN. Doing so will let ROBIN map and track all resources associated with the Helm release for this PostgreSQL database. To register the Helm release as an application, run the following command:

robin app register movies --app helm/movies
Deploy PostgreSQL on OpenShift Register Helm Release

Let us now verify that ROBIN is tracking our PostgreSQL Helm release as a single entity (app).

robin app status --app movies

You should see an output similar to this:

Deploy PostgreSQL on OpenShift Register Helm

We have successfully registered our Helm release as an app called “movies”.

This concludes the Deploy PostgreSQL on OpenShift tutorial. Now that we have deployed the PostgreSQL database, loaded data, and registered the PostgreSQL Helm release with ROBIN, we can create a PostgreSQL database snapshot and create a PostgreSQL clone including app plus data.

mm

Author Ripul Patel, Sr Software Engineer

More posts by Ripul Patel, Sr Software Engineer