\documentclass[11pt]{article}

\usepackage[margin=1in]{geometry}
\usepackage{hyperref}
\usepackage{parskip}
\usepackage{verbatim}

\title{DATA 503 Lab Handout\\Grafana Dashboards on Railway (PostgreSQL 18)}
\author{Lucas P. Cordova, Ph.D. --- Willamette University}
\date{Spring 2026}

\begin{document}
\maketitle

\section{How to use this handout}

Work through the steps in order. You may work \textbf{individually} or in \textbf{pairs}. If you hit a blocker you cannot clear quickly, pair up with a neighbor and compare connection strings, service names, and error messages before asking for help.

\textbf{Materials on Canvas:}

\begin{itemize}
  \item \texttt{railway\_dump.dump} --- custom-format backup for \texttt{pg\_restore} (built for PostgreSQL 18)
  \item \texttt{railway\_dump.sql} --- optional plain SQL you can run if your computer does not have \texttt{pg\_restore}
\end{itemize}

\section{Part A: Create a PostgreSQL 18 database on Railway}

\begin{enumerate}
  \item Go to \url{https://railway.app} and sign in.
  \item Click \textbf{New Project} $\rightarrow$ \textbf{Empty Project} (or use a dedicated class project if instructed).
  \item Click \textbf{Create} $\rightarrow$ \textbf{Database} $\rightarrow$ \textbf{Add PostgreSQL}.
  \item Open the PostgreSQL service $\rightarrow$ \textbf{Settings}. If there is a \textbf{Version} or image tag option, select \textbf{PostgreSQL 18} (or the version your instructor specifies).
  \item Wait until the database shows as deployed (healthy).
  \item Open the PostgreSQL service $\rightarrow$ \textbf{Variables} (or \textbf{Connect}). Copy the \textbf{public} database URL Railway gives for connections from your own computer (often the variable \texttt{DATABASE\_PUBLIC\_URL}, or the URL under \textbf{Public network} / \textbf{TCP Proxy} on the Connect tab). You will need host, port, database name (often \texttt{railway}), user, and password from that URL.
  \item Use this \textbf{public} URL for Beekeeper, pgAdmin, \texttt{psql}, and \texttt{pg\_restore}. Private hostnames such as \texttt{*.railway.internal} do not work from your laptop.
\end{enumerate}

\section{Part B: Connect with Beekeeper Studio or pgAdmin}

\subsection*{Beekeeper Studio}

\begin{enumerate}
  \item New connection $\rightarrow$ \textbf{Postgres}.
  \item Paste the full \textbf{public} database URL if the app supports it, or copy host, port, user, password, and database name from that URL.
  \item Test connection, then save.
\end{enumerate}

\subsection*{pgAdmin}

\begin{enumerate}
  \item Register $\rightarrow$ \textbf{Server}. On the \textbf{Connection} tab, enter host, port, maintenance database (often \texttt{railway}), username, and password from the \textbf{public} database URL.
  \item For connections through Railway's public endpoint, set SSL mode to \textbf{Require} unless Railway's Connect instructions say otherwise.
\end{enumerate}

\section{Part C: Load the class database from the dump}

\subsection*{Option 1: \texttt{pg\_restore} (preferred for \texttt{.dump} files)}

You need the PostgreSQL \textbf{client tools} installed (version 18 preferred; older clients may fail against newer servers).

\begin{enumerate}
  \item Download \texttt{railway\_dump.dump} from Canvas to a folder you can find.
  \item In a terminal, set your connection string (replace with your real URL from Railway):

\begin{verbatim}
export DATABASE_PUBLIC_URL="postgresql://USER:PASSWORD@PUBLIC_HOST:PORT/railway"
\end{verbatim}

  \item Run (flags avoid ownership errors on hosted Postgres):

\begin{verbatim}
pg_restore --verbose --no-owner --no-acl -d "$DATABASE_PUBLIC_URL" railway_dump.dump
\end{verbatim}

  \item Refresh Beekeeper or pgAdmin. You should see the class tables (for example census, survey, temperature tables) and the dashboard views (names starting with \texttt{v\_}, etc.).
\end{enumerate}

If \texttt{pg\_restore} prints errors about existing objects, ask before re-running with destructive flags. Your instructor may give a fresh empty database or a revised dump.

\subsection*{Option 2: Plain SQL file (no \texttt{pg\_restore})}

\begin{enumerate}
  \item Download \texttt{railway\_dump.sql} from Canvas.
  \item In Beekeeper: open the file or paste contents, ensure you are connected to your Railway database, and execute the script.
  \item In pgAdmin: use \textbf{Query Tool} $\rightarrow$ open file $\rightarrow$ Execute.
  \item Alternatively with \texttt{psql}:

\begin{verbatim}
psql "$DATABASE_PUBLIC_URL" -f railway_dump.sql
\end{verbatim}

\end{enumerate}

\section{Part D: Add Grafana on Railway}

Grafana needs its \emph{own} configuration database so dashboards survive restarts. Your \textbf{pipeline} database holds the class data; a separate database (commonly named \texttt{grafana}) holds Grafana metadata.

\begin{enumerate}
  \item In Beekeeper or pgAdmin, connected to your Postgres service, run:

\begin{verbatim}
CREATE DATABASE grafana;
\end{verbatim}

  \item In the same Railway \textbf{project}, click \textbf{Create} $\rightarrow$ \textbf{Template} and search for \textbf{Grafana}. Deploy a well-maintained template (your instructor may name a specific one).
  \item On the Grafana service, set environment variables (exact names can vary slightly by image; confirm with the template readme if something fails):

  \textbf{Authentication}

  \begin{itemize}
    \item \texttt{GF\_SECURITY\_ADMIN\_USER} = \texttt{admin}
    \item \texttt{GF\_SECURITY\_ADMIN\_PASSWORD} = a password you will remember
  \end{itemize}

  \textbf{Grafana config database (points at your Postgres service)}

  \begin{itemize}
    \item \texttt{GF\_DATABASE\_TYPE} = \texttt{postgres}
    \item \texttt{GF\_DATABASE\_HOST} = host and port from the \textbf{same public} Postgres URL you use in Beekeeper (for example \texttt{short-name.proxy.rlwy.net:PORT})
    \item \texttt{GF\_DATABASE\_NAME} = \texttt{grafana}
    \item \texttt{GF\_DATABASE\_USER} = often \texttt{postgres}
    \item \texttt{GF\_DATABASE\_PASSWORD} = the Postgres password from Railway variables
    \item \texttt{GF\_DATABASE\_SSL\_MODE} = usually \texttt{require} for the public endpoint (follow the template readme if the deploy fails)
  \end{itemize}

  \textbf{Public URL}

  \begin{itemize}
    \item After Grafana gets a public URL, set \texttt{GF\_SERVER\_ROOT\_URL} to that URL (including \texttt{https://}).
  \end{itemize}

  \item Redeploy Grafana if prompted. Enable \textbf{Serverless} on the Grafana service if you want it to sleep when idle (saves credits).
  \item Log into Grafana from the public URL. Under \textbf{Connections} $\rightarrow$ \textbf{Data sources}, add \textbf{PostgreSQL} for your \textbf{class data} database:
    \begin{itemize}
      \item Host / port: same \textbf{public} Postgres host and port as Beekeeper
      \item Database: the database name that contains the restored tables (often \texttt{railway})
      \item User / password: prefer a \textbf{read-only} user your instructor helps you create; never paste admin passwords into shared screenshots
      \item SSL: \textbf{require} (or whatever matches Railway's public Postgres settings)
    \end{itemize}
  \item Click \textbf{Save \& test}. Fix host, database name, or SSL until the check passes.
\end{enumerate}

\section{Part E: Lab scenarios (build a small dashboard)}

Create one dashboard with at least \textbf{four} panels. Use meaningful titles. Set a sensible refresh interval (for static class data, manual or 5--15 minutes is fine).

\begin{description}
  \item[Scenario 1 --- State population] Bar chart: top 15 states by \texttt{total\_pop} from \texttt{v\_state\_population\_summary}. Sort descending.

  \item[Scenario 2 --- Migration story] Horizontal bar chart from \texttt{v\_state\_migration\_rates} (for example top 20 by absolute migration rate). Use field overrides or value mappings so ``Gaining'' and ``Losing'' read clearly (color is optional).

  \item[Scenario 3 --- Growth categories] Pie or donut chart from \texttt{v\_county\_growth\_summary} using \texttt{growth\_category} and \texttt{num\_counties}, or stat panels for \texttt{total\_pop} per category.

  \item[Scenario 4 --- Establishments per capita] Bar chart from \texttt{v\_state\_estabs\_per\_capita} for \texttt{estabs\_per\_thousand} (limit to top 15 states).

  \item[Scenario 5 --- Seasons and stations] Grouped bar chart from \texttt{v\_seasonal\_temperatures}: \texttt{station\_name}, \texttt{season}, \texttt{avg\_max\_temp}.

  \item[Scenario 6 --- Population tiers] Table or bar chart from \texttt{v\_population\_tiers} comparing \texttt{tier}, \texttt{num\_counties}, and \texttt{pct\_of\_total\_pop}.

  \item[Scenario 7 --- Pivot-style table] Table panel: run the ice cream \texttt{crosstab()} query from lecture (offices as rows, flavors as columns). If the query fails, confirm \texttt{CREATE EXTENSION tablefunc;} was applied in your database.

  \item[Scenario 8 --- Temperature pivot] Table panel: run the median temperature by station and month \texttt{crosstab()} query from lecture.
\end{description}

\section{What to turn in}

Link to your dashboard in the Canvas assignment.

\end{document}
