How to Deploy PostgreSQL on GKE using Helm

By May 30, 2019 June 25th, 2019 Blog, Databases, Stateful Applications, storage

In this tutorial, we will deploy a Postgres database on Google Kubernetes Engine (GKE) using Helm and load data in the database. Before you start this tutorial, make sure you have installed ROBIN Storage on GKE.

Let us first install the PostgreSQL client so that we can use PostgreSQL once deployed.

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

Let’s create a Postgresql database using Helm and ROBIN Storage. In the Cloud Shell run the following command. The command will install Postgresql. We are setting the storageClass to robin-0-3 to benefit from data management capabilities ROBIN Storage brings.

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

Before proceeding, let us make sure all necessary workloads (pods) are running properly. Click on the “Workloads” tab on the left-hand panel, and wait for the status of all workloads to be shown as ”OK” with a green check next to them.

Advanced Data Management for Kubernetes - Deploy PostgreSQL on GKE using Helm | Get ROBIN Storage on Google Cloud Marketplace for Stateful workloads

Let’s confirm that our Database is deployed and all relevant Kubernetes resources are ready.

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

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

Advanced Data Management for Kubernetes - Deploy PostgreSQL on GKE using Helm | Get ROBIN Storage on Google Cloud Marketplace for Stateful workloads

Get Service IP address of our PostgreSQL database.

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

Get Password of our PostgreSQL database from Kubernetes Secret.

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

Adding data to the Postgresql database

We will use movie data to load data into our PostgreSQL database.

Let us create a database “testdb” and connect to “testdb”.

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

Let’s 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);"

Let’s 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');"

To see the movies added to the “movies” table, run the following command.

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

You should see an output similar to the following:

Advanced Data Management for Kubernetes - Deploy PostgreSQL on GKE using Helm | Get ROBIN Storage on Google Cloud Marketplace for Stateful workloads

We have now deployed a PostgreSQL database on GKE with a table and some sample data.

To benefit from data management capabilities ROBIN brings, such as taking snapshots, making clones, and creating backups, we have to register this PostgreSQL Helm release as an application with ROBIN.

Registering the PostgreSQL Helm release as an application

Registering this PostgreSQL Helm release as an application will enable 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 myfilms --app helm/films

Let’s verify ROBIN is now tracking our Postgresql Helm release as a single entity (app).

robin app list --name myfilms

You should see an output similar to this.

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

This concludes the Deploy PostgreSQL on GKE 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 Ramendra Singh

More posts by Ramendra Singh