Sep 29: SSH and PostgreSQL

Learning Objectives

After today's class, you should be able to:

• Configure an SSH tunnel and connect to data.cs.jmu.edu using pgAdmin.
• Compare PostgreSQL data types and constraints with those of SQLite.

Lesson Outline

SSH Tunnel [20 min]

• Setting up an SSH tunnel
• Why: our server is behind the JMU firewall, and you will work from off campus

PostgreSQL [5 min]

• Pronounced "post-gres-Q-L" – see https://www.postgresql.org/about/
• SQLite is a file on your laptop; PostgreSQL is a server that many users share
  • Your GP2 schema already says database_type: 'PostgreSQL'
  • DB-Engines Ranking – where the two stand

pgAdmin [15 min]

• pgAdmin – PostgreSQL Tools
  • Register the server through the tunnel (localhost)
  • Tour: databases, your schema, the SQL tab
  • jmudb warmup queries

Group Activity [35 min]

• SQLite vs. PostgreSQL
  • Part 1: predict, then run, in both databases
  • Part 2: port your HW2 tables, and save hw2_pg.sql for Thursday

Your To-Do List

• Submit the Week 6 reading ← due Wednesday
• Get started working on GP2 ← due Monday