Connecting to the Database

The department (intermittently) hosts a database server running PostgreSQL. You may be able to use this server, but it would be best if you install Postgres on your own computer.

Either way, here’s how you connect:

Connecting #

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

If you are using the PostgreSQL installed on your computer #

1. If your install initialized a default database location then you can skip this step and use that. (For example, homebrew does so and tells you with brew info postgresql.)

   Pick a directly like /usr/local/var/postgres to store your data; it can be anything /usr/local/pgsql/data is another popular choice.

   initdb --locale=C -E UTF-8 /usr/local/var/postgres
   

2. Start the database server

   pg_ctl start -D /usr/local/var/postgres -l /tmp/pg.log &
   

   Use the same directory after -D as you used in step 1. The log file can be anywhere you like.

3. Create a database with your user name for general use. (You may want a special database for your project.)

   Type the following replacing USERNAME with your username:

   createdb USERNAME
   psql
   

   This should start a REPL and show you something like the following but with your name, et cetera.

   psql (14.5 (Homebrew))
   Type "help" for help.
   
   genovese=#
   

   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.

   Type help or \? for help on commands and more. See also the psql documentation.

   When finished, type \q to quit.

4. We will discuss connecting from a program later.

If you want to use the remote server #

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. 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.

3. Now run the command psql. You should see this:

   psql (14.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.

   Type help or \? for help on commands and more.

4. 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 #

If you are using the remote server, then postgreSQL is running on a computer in a data center on campus. It has its own hard drive, so if you want to access any files from SQL, you need to move those files there.

We will copy files from your laptop to the server machine using the scp command or rsync commands. These are just like the cp command at the shell except they can make remote copies to other machines – and require a password.

For example, to copy a file from your current directory on your laptop to your home directory on the server, do this:

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

You can copy multiple files at a time, even whole directoreis. For example:

rsync -a my-hw-data yourusername@sculptor.stat.cmu.edu:~/data/

This will recursively copy my-data-directory and its contents into the data directory on the server machine.

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.