Where to get help

This document will help you get started with PostgreSQL, the DBMS that will be used for all the homeworks. For more information, check out the reference documentation.

Before running PostgreSQL

The homeworks will be graded on one of the GHC cluster machines ghcXX.ghc.andrew.edu.cmu, which you can access via SSH. We recommend you run your SQL statements on one of those machines before turning in your homework. These machines are running PostgreSQL version 8.4.20 or later.

Each student is assigned a ghc machine, number XX and port number YY, to minimize conflicts. Ports (YY) are unique; machines (XX) may be shared among 2-3 people. You’ll find these numbers on blackboard, under 'Course Content / HW2 port and machine assignments', and throughout this document we’ll refer to the machine as ghcXX as the port as 415YY.

Setting up a database

Before using PostgreSQL you’ll have to initialize a database structure and create a database. You only have to do this once, and you don’t have to do it again if you move from one cluster machine to another.

From your local machine command prompt, log into your cluster machine (remember to replace ghcXX with your assigned machine):

    $ ssh <your-andrew-id>@ghcXX.ghc.andrew.cmu.edu

On the cluster machine, run the following commands (remember to replace 415YY with your assigned port):

    $ export PGPORT=415YY   # sets the PORT to be used by PostgreSQL
    $ initdb db415          # initializes a database structure on the folder db415
    $ pg_ctl -D db415 start # starts the server on the port 415YY, using db415 as data folder
    $ createdb $USER        # creates a database, with the name being your andrew id

When you’re done, please stop the server by running:

    $ pg_ctl -D db415 stop

Using the database

From your local machine command prompt, log into your cluster machine:

    $ ssh <your-andrew-id>@ghcXX.ghc.andrew.cmu.edu

On the cluster machine, run the following commands:

    $ PGPORT=415YY pg_ctl -D db415 start    # starts the server on the port 415YY, using db415 as data folder
    $ psql -p 415YY                         # connects to the server running on the port 415YY

And you’ll be in the PostgreSQL prompt, from where you can run any SQL statements.

When you’re done, please stop the server by running:

    $ PGPORT=415YY pg_ctl -D db415 stop

Setting up for Homework 2

For homework 2, we’ll provide a dump[1] with all you’ll need. You need to import it into your own database so you can solve the homework problems.

Step 1) From your local machine command prompt, log into one your cluster machine:

    $ ssh <your-andrew-id>@ghcXX.ghc.andrew.cmu.edu

Step 2) Start the server and import the dump:

    $ PGPORT=415YY pg_ctl -D db415 start
    $ curl http://www.cs.cmu.edu/~christos/courses/dbms.S15/hws/HW2/dump.sql > dump.sql
    $ PGPORT=415YY psql $USER < dump.sql    # runs the commands in the dump.sql file into the <your-andrew-id> database
    $ psql -p 415YY                         # connects to the server running on the port 415YY

Step 3) From the PostgreSQL prompt you’ll now be in, run:

    # select * from games;

If you get a list of games back, you have successfully imported the dump and can get started on the homework.

Step 4) IMPORTANT: When you are done, please stop the server by running:

    $ PGPORT=415YY pg_ctl -D db415 stop

Stopping the server is important - otherwise, in your next run, there may be subtle, hard-to-detect problems (server running out of threads, etc).

[1] the dump is a text file with SQL commands to create the tables and import the data.

FAQ

A: No worries, it happens. Just use some other machine  in the range XX=26-46  (e.g. ghc(XX + 1)). Make sure you use it with your original port (YY).

A: Please post a question on blackboard ('HW2 forum'), or come to office hours —  see the course website for locations and hours.