Perform CRUD operations using Crunchy PostgreSQL for Kubernetes Operator

In this tutorial, we demonstrate how to perform CRUD operations using the Crunchy PostgreSQL for Kubernetes Operator hosted on Red Hat Marketplace using a Python runtime and a Jupyter notebook.

Any developer who wants to develop a database application will have to use CRUD operations. The Crunchy PostgreSQL for Kubernetes operator from Red Hat Marketplace ensures that the database will work in a hybrid cloud environment using Red Hat OpenShift.

About Crunchy PostgreSQL for Kubernetes Operator

Crunchy PostgreSQL for OpenShift Container Platform (OCP) includes Crunchy PostgreSQL Operator and Crunchy PostgreSQL Container Suite supporting hybrid cloud, open source PostgreSQL-as-a-Service. Learn more. The postgres-operator is a controller that runs within a Kubernetes cluster that provides a means to deploy and manage PostgreSQL clusters.

Learning objectives

After completing this tutorial, you will understand how to:

  • Deploy a Crunchy PostgreSQL Operator on an OpenShift cluster
  • Perform Create, Read, Update and Delete (CRUD) operations on an OpenShift cluster

Estimated time

Completing this tutorial should take about 30 minutes.

Prerequisites

To complete the steps in this tutorial, you need:

  1. Red Hat OpenShift version 4.3 is required to use with software in Red Hat Marketplace. 1.Create an account on Red Hat Marketplace.
  2. Access to a Jupyter Notebook. You can install a Jupyter Notebook from python-pip or use a tool such as Anaconda to open the Jupyter Notebook

Step 1: Deploy a Crunchy PostgreSQL for Kubernetes Operator on an OpenShift cluster

Follow the steps in this tutorial to Deploy a Crunchy PostgreSQL Operator from Red Hat Marketplace on an OpenShift cluster.

Step 2: Perform CRUD operations on Crunchy PostgreSQL for Kubernetes Operator using Python

Once you have the Crunchy PostgreSQL for Kubernetes Operator up and running and you’ve created a user and database, you can now explore the CRUD operations on Crunchy PostgreSQL in a Python runtime using a Jupyter Notebook.

  1. In your terminal, run the following command to port forward the 5432 port from the OpenShift cluster. This port is used in our Jupyter Notebook to establish a connection with the Crunchy PostgreSQL instance.

     $ kubectl port-forward -n pgo svc/hippo 5432:5432
    
     Forwarding from 127.0.0.1:5432 -> 5432
     Forwarding from [::1]:5432 -> 5432
    
  2. Download and open the Jupyter notebook CrunchyDB CRUD Operations.ipynb on your local machine.

  3. Click on the Cell tab and select Run All.

    Run all button

    You can now follow the notebook instructions for more details on what is happening in each cell.

  4. After you execute the Jupyter notebook, you can verify the table in Crunchy Data’s pgAdmin 4 console. To access pgAdmin 4, you can set up a port-forward to the Crunchy Data service (Console), which follows the pattern <clusterName>-pgadmin, to port 5050:

     $ kubectl port-forward -n pgo svc/hippo-pgadmin 5050:5050
    
     Forwarding from 127.0.0.1:5050 -> 5050
     Forwarding from [::1]:5050 -> 5050
    
  5. In the pgAdmin 4 console, expand the following attributes:

    • your cluster — for example, in our case ,cpdemo
    • Databases
    • database_name
    • Schemas
    • username (eg: in our case hippo)
    • Tables

      Under Tables, you can now see the accounts table that we created from the notebook. Right click on the accounts table and select View/Edit Data > All Rows, and the table with all rows will be displayed.

    See the table you created

Note: You can view this table after each CRUD operation performed in a notebook in order to visualize the changes.

Visualize your changes in a Jupyter notebook

Summary

In this tutorial, you have learned the basics of how to use the Crunchy PostgreSQL for Kubernetes operator deployed on OpenShift Cluster and walked through a quick exercise of how to perform CRUD operations using the Crunchy PostgreSQL for Kubernetes operator in Python.

Reference

Access the documentation for Crunchy PostgreSQL Operator.