Carnegie Mellon University
15-826 Multimedia Databases and Data Mining
  Spring 2017 - C. Faloutsos

Getting Started with PostgreSQL

Instructions

This document will help you get started with PostgreSQL, the DBMS that will be used for the default/non-default project. For more information, check out the reference documentation.

SQL implementations of the project will be graded on one of the GHC cluster machines ghcXX.ghc.andrew.cmu.edu, running PostgreSQL version 9.2.4. You may develop your code anywhere, but make sure it runs correctly on a ghc machine.

Each student is assigned with a ghc machine, number XX and port number YYYYY, to avoid conflicts. Ports (YYYYY) are unique; machines (XX) are shared among 2-3 people. You'll find these numbers on blackboard, under 'MyGrades / Machine and Port numbers', and throughout this document we'll refer to the machine as ghcXX as the port as YYYYY.

Initialization

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. We provide set up commands in BASH - modify accordingly, for other shells.

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 YYYYY with your assigned port):

$ export PGPORT=YYYYY   # sets the PORT to be used by PostgreSQL
$ export PGHOST=/tmp    # sets the directory for the socket files
$ initdb $HOME/826prj    # initializes a database structure on the folder $HOME/826prj
$ pg_ctl -D $HOME/826prj -o '-k /tmp' start # starts the server on the port YYYYY, using $HOME/826prj as data folder
$ createdb $USER        # creates a database, with your andrew id as its name

IMPORTANT: When you're done, please stop the server (or add this line to your .logout file):

$ pg_ctl -D $HOME/826prj stop

Using the Database

Log into your assigned cluster machine:

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

To work with the database, set up the environment variables, and start the server:

$ export PGPORT=YYYYY; export PGHOST=/tmp;    # set env. vars
$ pg_ctl -D $HOME/826prj -o '-k /tmp' start    # starts the server on port YYYYY, using 826prj as data folder

To get the SQL prompt:

$ psql -d $USER                               # enter sql commands, now

And, again, when done, stop the server:

$ pg_ctl -D $HOME/826prj stop

Important - Stop the Server

Please remember to stop the Postgres server, whenever you are done working on the assignment. Otherwise, when you resume your work there may be subtle, hard-to-detect problems (server running out of threads, etc). You can automatically do this by adding the following command to your .logout file.

$ pg_ctl -D $HOME/826prj stop

Frequently Asked Questions

Q: My assigned machine doesn't work!
Our apologies - please use some other machine in the range XX=25-86. Make sure you use it with your original port number (i.e., YYYYY).

Q: Help, I'm stuck!
Please post a question on blackboard ('Default project forum'), or come to office hours.

Created: Feb. 1, 2017, by Yuang Liu