PostgreSQL

PostreSQL is a relational database management system, often known as “The World’s Most Advanced Open Source Relational Database”.

In IN5800, we will use PostgreSQL both for storing and interacting with relational data, but also as the backend for our triplestore.

Using Postgres in IN5800

USIT sets up PostgreSQL databases for students of IN5800, so all students of IN5800 should have recieved an email from USIT with a password for logging in to their personal database(s). The databases are behind UiO’s firewall, which means that one either needs to be physically at UiO (e.g. IFI), either with your own laptop (logged into UiO’s WiFi); or remote login to a machine at IFI.

Remote login

To log in remotely, you can e.g. use SSH from the command line as follows:

ssh <username>@login.ifi.uio.no

where <username> is your UiO-username. My username is leifhka so I would execute

ssh leifhka@login.ifi.uio.no

You will then be prompted for your UiO-password. Note: when typing in your password, it will seem like nothing is typed, but is just a security measure so that no one can see the length of your password.

Logging into PostgreSQL via psql

Once you are on IFIs Wifi (either remote of physically), you can connect to your database with psql as follows:

psql -d <database> -U <dbuser> -h dbpg-ifi-kurs03

where <database> is the name of the database (your personal database has name in5800_<username> where <username> is your UiO username); and <dbuser> is the username of the database user (your personal database has the user in5800_<username>_user where <username> is your UiO-username). I (with leifhka as username) would execute:

psql -d in5800_leifhka -U in5800_leifhka_user -h dbpg-ifi-kurs03

Logging into PostgreSQL via Edbit

It is also possible to log into your database directly from your laptop (also outside IFI’s WiFi) with a program called Edbit (written by Leif Harald Karlsen). You can download Edbit here.

To log into your database with Edbit, execute:

java -jar edbit.jar -d <database> -U <dbuser> -h dbpg-ifi-kurs03 --sshHost login.ifi.uio.no --sshUser <username>

where the flags are as described above. So, e.g. I would execute:

java -jar edbit.jar -d in5800_leifhka -U in5800_leifhka_user -h dbpg-ifi-kurs03 --sshHost login.ifi.uio.no --sshUser leifhka

If you are (physically) at Ole Johan Dahls Hus, you can drop the ssh-flags, and simply write:

java -jar edbit.jar -d <database> -U <dbuser> -h dbpg-ifi-kurs03

Executing SQL-scripts

To execute a SQL-script <script>, you need to use psql as follows:

psql -d <database> -U <dbuser> -h dbpg-ifi-kurs03 -f <script>

where the flags are as described above.

Note that <script> needs to be on an IFI-machine. If you are working on your peronal laptop, you can move a file <script> from your laptop to your UiO-home with scp as follows:

scp <script> <username>@login.ifi.uio.no:~/

If you then remotely log in as described above, you can exectute the above psql-command to execute the script over your database.

If the script is online, you can also start by logging in remotely, and then use wget to download the file directly to your UiO-home folder:

wget <url-to-script>

where <url-to-script> is the URL to the script you want to execute. E.g. if I were to execute the script at http://leifhka.org/in5800/data/species.sql, I would use the following commands (after remotely logging in):

wget http://leifhka.org/in5800/data/species.sql
psql -d in5800_leifhka -U in5800_leifhka_user -h dbpg-ifi-kurs03 -f species.sql

Guides and tutorials