Connecting to the Database

— Alex Reinhart and Christopher Genovese

For this class, we have a database server running PostgreSQL. You can use this server for all your homework assignments and projects – you do not need to install Postgres on your own computer.

To connect, open a terminal. You can use the terminal built in to Visual Studio Code, or use Git Bash on Windows, Terminal on a Mac, and your favorite terminal on Linux.

Also look up the email you received from Carl Skipper giving your database username and password.

To connect:

  1. Run this command:

    ssh yourusername@sculptor.stat.cmu.edu

    The first time you do this, you’ll see this message:

    The authenticity of host 'sculptor.stat.cmu.edu (128.2.24.140)' can't be established.
    ECDSA key fingerprint is SHA256:JyXIHb98TaWbXxGBpVbEBjnMWGkWBUKrdgDeUuNq0Jk.
    Are you sure you want to continue connecting (yes/no)?

    Answer “yes”. You’ll then be asked for your password, as provided in Carl’s email. Type it in. (You will not see any stars or text as you type – that’s normal!)

  2. Once you log in, you’ll see a prompt like this:

    [yourusername@sculptor ~]$

    This is a bash terminal, just like you use on your own computer. You can use commands like ls and cd to move around.

    1. Now run the command psql. You should see this:
    psql (10.5)
    Type "help" for help.
    
    yourusername=>

    This is a Postgres shell. It does not accept commands like ls and cd: it reads in SQL queries and prints out their results. It also has various features to describe database tables, read in input files, and do other useful things; check out the psql documentation.

  3. When you’re done, type \q to exit. This will put you back in the Bash terminal, where you can type exit to disconnect from sculptor.

Troubleshooting #

Getting files onto the server #

The SQL server is a computer in a data center on campus. It has its own hard drive. That means that if you have a file on your computer – say, a data file – that you want to load into Postgres, you can’t just log in to sculptor and use that file. You need to copy it to the server first.

To do this, open a terminal on your computer – don’t log in to sculptor yet. Find the file you’d like to copy to sculptor. Then run this command:

scp thefileyouwanttocopy.txt yourusername@sculptor.stat.cmu.edu:~/

This will copy the file into your home directory on sculptor, so it will be there when you log in.

Note: Many of the homework data files are already on sculptor for your convenience; run ls /home/areinhar on sculptor to see what’s available.

Postgres isn’t responding to my queries! #

Suppose you’re in psql, typed a query, hit Enter, and Postgres did nothing. No output. What’s going on? You type some more queries and Postgres still does nothing.

Hint: Look carefully at the prompt! If it looks like this:

yourusername'>

that apostrophe ' means that you typed a quotation mark somewhere and did not close it. Postgres is waiting for you to close the quotation mark.

If you see

yourusername(>

it’s the same problem, but you opened a parenthesis and did not close it.

Finally, if you see

yourusername->

the - means that you have not typed a semicolon to end your query yet, so psql is waiting for a semicolon. Type one and hit Enter.