IDB: Software (1)

Using the PostgreSQL installation on DICE

Prerequisites

The following instructions require the execution of commands in a shell:

• in Windows, you can use the Windows PowerShell program,
• if you are on a Mac or Linux (e.g., Ubuntu), you can use the Terminal application.

Also, you will need a DICE account (see DICE desktops and servers and Computing accounts); your EASE credentials will not work. You should have gotten a DICE account automatically; if you do not remember the credentials, or you believe you do not have a DICE account, please get in touch with Informatics computing support.
 

Connecting

Once you are on the shell command line, proceed as indicated below.

1. Connect to the student ssh server using the following command:

ssh UUN@student.ssh.inf.ed.ac.uk

where UUN is your University username (e.g., s1234567).

Note: You will need a VPN client to use it (either the University VPN or the School VPN).

2. Connect to the student login server:

ssh student.login

3. Connect to your PostgreSQL database with the following command:

psql -h pgteach

Note: the client and server versions may not match, but this is not a problem.

4. You are now in the psql command prompt and you can type SQL statements.
 

Working with psql

psql is an extremely powerful command-line application to interact with a PostgreSQL server; it can be run interactively (as described in step 4 above) or in a batch way (so it can be included in scripts). Once you are in front of the psql prompt (at the end of the procedure described above), you can execute SQL statements, which must be terminated by a semicolon ";" (this allows one to break down long statements into multiple lines). There are many other internal commands (which are not SQL) accepted by psql; these start with a backslash "\" and are not terminated by semicolon. The most common ones are:

• \q quits the psql prompt and returns to the shell command line
• \d lists the tables stored in the database
• \d+ <tablename> gives details about the table
• \i </absolute/path/to/filename> reads and executes SQL statements from the specified file
• \ir <relative/path/to/filename> same as the above, but the file path is relative to the one from which psql was invoked
• \? shows help on blackslash commands
   

You can (and should!) read the full psql documentation at https://www.postgresql.org/docs/current/app-psql.html (select the appropriate version).

Disconnecting

1. Exit the psql prompt by issuing the \q backslash command.
2. Issue the exit command in the shell twice to log out of the login and ssh servers, respectively.