03-install-postgres.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c81323e8",
   "metadata": {},
   "source": [
    "## About the PostgreSQL relational database in the PEcAn Platform\n",
    "\n",
    "> A SQL statement goes into a restaurant, walks up to two tables, and asks \"may I join you?\" One of the members at one of the tables responds, \"Are we related?\"\n",
    "\n",
    "A relational database like PostgreSQL is where live persistent data in an application belongs. \n",
    "The PEcAn platform uses two combined tools for storing  data, including a BETYdb and PostgreSQL relational database.\n",
    "Users with access to a BETYdb PostgreSQL database can access data through R packages—in particular PEcAn.DB, which is designed for use in PEcAn—or through any programming language a scientist is likely to use. \n",
    "\n",
    "PostgreSQL is the very best transactional database with support for ecological forecasting data because it handles geolocation, unstructured, and structured data with confidence and ACID compliance. \n",
    "ACID compliance is a set of database characteristics consisting of Atomicity, Consistency, Isolation, and Durability that ensure that database transactions are completed efficiently. \n",
    "We take advantage of PostgreSQL in the PEcAn Platform and BETYdb at the same time. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1814347f-b17a-4f7c-ab67-75a531232d6e",
   "metadata": {},
   "source": [
    "# Install PostgreSQL with helm\n",
    "\n",
    "Run the commands below to install PostgreSQL relational database with helm. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5721ad20-c855-40b2-8817-386085a460c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "helm upgrade --install postgresql oci://registry-1.docker.io/bitnamicharts/postgresql \\\n",
    "  --set image.tag=14.5.0 \\\n",
    "  --set volumePermissions.securityContext.runAsUser=\"auto\" \\\n",
    "  --set fullnameOverride=pecan-postgresql \\\n",
    "  --set serviceAccount.enabled=true \\\n",
    "  --set auth.postgresqlPassword=supersecret \\\n",
    "  --set auth.postgresPassword=supersecret\n",
    "echo DONE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dda9e44",
   "metadata": {},
   "source": [
    "### View PostgreSQL pod details\n",
    "After deploying the Helm Chart, it will take a minute before the PostgreSQL pod is up and running. Run the command below until the PostgreSQL pod health checks are `READY 1/1` and `STATUS Running`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "478e168d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "oc get pod -l app.kubernetes.io/name=postgresql\n",
    "oc wait pod -l app.kubernetes.io/name=postgresql --for=condition=Ready --timeout=2m\n",
    "oc get pod -l app.kubernetes.io/name=postgresql\n",
    "echo DONE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d78c5c4",
   "metadata": {},
   "source": [
    "### View PostgreSQL pod logs\n",
    "If your PostgreSQL pod does not reach the STATUS Running, you can run the command below to view the pod logs of PostgreSQL and check for other errors that may have occured. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3304d956",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "oc logs -l app.kubernetes.io/name=postgresql\n",
    "echo DONE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "933fa199",
   "metadata": {},
   "source": [
    "## Check the version of PostgreSQL deployed\n",
    "\n",
    "PostgreSQL is an open source project rolling out new stable versions of the database all the time. \n",
    "You can check on the image version of PostgreSQL by checking the `image` of the `StatefulSet/pecan-postgresql` below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c859961b-57fc-410a-b305-50f2b9d2d14f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "oc get statefulset/pecan-postgresql -o yaml | grep image:\n",
    "echo DONE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e796d1b",
   "metadata": {},
   "source": [
    "The image you discovered above can also be found in DockerHub here: \n",
    "\n",
    "https://hub.docker.com/layers/bitnami/postgresql/14.5.0/images/sha256-2092a1b0bed244fbfacea836da72a84bfa47a1e7b4f2b4a82dda28dc3065d6de?context=explore"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e1db52e",
   "metadata": {},
   "source": [
    "## More information about deploying PostgreSQL\n",
    "\n",
    "To learn more about deploying PostgreSQL using the Bitnami Helm Chart see: \n",
    "\n",
    "https://artifacthub.io/packages/helm/bitnami/postgresql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f66f5fd",
   "metadata": {},
   "source": [
    "## Next...\n",
    "I hope that answers your questions about PostgreSQL in the PEcAn Platform. \n",
    "- If you have additional questions or issues, please [create an issue for the course here](https://github.com/computate-org/pecan-unconstrained-forecast-course/issues). \n",
    "- Otherwise, please continue to the next notebook [04-install-rabbitmq.ipynb](04-install-rabbitmq.ipynb). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a724ba49",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.16",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}