You are here

PostgreSQL teaching and research databases

Printer-friendly versionPrinter-friendly version

PostgreSQL is a database service. This page explains our PostgreSQL software, accounts, extensions and using the database with your own code. To find out more, click one of those keywords.

PostgreSQL software

What PostgreSQL resources are available?

The school maintains two PostgreSQL servers, one for teaching and one for research. Staff and students can be granted access to either server as appropriate.

Generally there is no limit on research database use, but where consumption of resources becomes detrimental to other users of the server we may request that you source dedicated server hardware.

What versions are installed?

It depends!

On DICE desktops and servers we usually carry a few versions of PostgreSQL: one older version as maintained by our upstream Linux distribution, and a newer versionmaintained for use by servers and clients connecting to those servers. We also install other versions and will do this on request.

  • On DICE SL7 (most servers, excluding teaching): we provide 9.2 as default, and 9.6 / 11 via psql96 / psql11.
  • On DICE Ubuntu Focal (most teaching machines): we provide postgresql 11 as default.

    You can see which versions are available with something like:

     # this is an example - your versions might differ
     $ ls /usr/bin/psql*
     /usr/bin/psql  /usr/bin/psql96
    
     $ psql --version
     psql (PostgreSQL) 9.2.24
    
     $ psql96 --version
     psql (PostgreSQL) 9.6.17
    
    

    The same convention is used for differing versions of the other PostgreSQL tools:

     $ ls /usr/bin/pg_dump*
     /usr/bin/pg_dump /usr/bin/pg_dumpall 
     /usr/bin/pg_dump96 /usr/bin/pg_dumpall96
    

    note that while clients are usually compatible with servers of different versions, a warning is given:

    $ psql -h pgteach
      WARNING: psql version 9.2, server version 9.6.
               Some psql features might not work.
      SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
      Type "help" for help.
    

    or in the case of tools like pg_dump, an error:

     $ pg_dump -h servername mydatabase
     pg_dump: server version: 9.4.7; pg_dump version: 9.2.15
     pg_dump: aborting because of server version mismatch
    

    in this case you should use the matching version where possible, replacing psql, pg_dump, or whatever command with the appropriate version, e.g. psqlXX or pg_dumpXX.

    PostgreSQL accounts

    How do I get a PostgreSQL account / database?

    For most students and teaching users this is entirely automatic. You will receive an account if you are taking any courses which requires one. Once you drop this course, your access will be suspended and, ultimately, any stored data removed (unless you request otherwise). You can check if you have an account by following the instructions below. If you do not have an account but feel you are entitled to one, please get in touch with support noting any course or supervisor details you think are relevant.

    If you are a member of staff and would like an account, you may request one using the support form. Please state whether the account is to be used for teaching or research, and if it is to be shared with anybody.

    How long can I keep my account?

    If you are an undergraduate student, and have been granted a postgresql database as part of a course you are taking, your account and database will be made available in time for your coursework, and removed when it ends. If you wish to retain your account for longer than this, please contact support.

    How do I access my database?

    From any DICE machine (except certain research group servers), simply type the following:

    psql -h pgteach

    or

    psql -h pgresearch

    You will be connected to your database on the database server. If you have been given access to a named database (for example a shared group database) then you may access it by specifying the name of the database at the shell:

    psql -h pgresearch databasename

    If you are not on a DICE machine, the only recommended connection method is to use SSH to connect to a DICE machine, and then to use psql.

    I tried typing psql, but got an error

    Here are a few common cases:

    1. While attempting to connect to your database you may receive the following error:

      psql: krb5_sendauth: Ticket expired

      This is most likely because you have been logged in for a long time, or perhaps left your computer locked overnight. The solution is simply to issue the following command at a terminal:

      renc

      You will be prompted for your DICE password.

    2. A similar error can be found if you clear out the contents of /tmp on your machine:

      psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found

      This can be avoided by not deleting files named /tmp/krb5cc_*, and is solved by the renc procedure described above.

      If you have any other problems, feel free to contact support. Copy or write the error message into the form if possible.

    3. If you're attempting to use your scripting password (see below) and receive an error relating to GSSAPI it's likely that you've not disabled SSL. See Why is my script's SSL connection breaking? below.

    PostgreSQL extensions

    Can I install my own extensions?

    No, but we will be able to install some extensions on your behalf. Generally we should be able to add any of the standard "contrib" extensions on a per-account basis. We might also grant permission to install some third-party extensions where we're confident that they will not be detrimental to the server. Extensions won't typically be installed for students unless needed for a course or project.

    Can I use PostGIS?

    Yes, PostGIS is a good example of a well-supported third-party extension. We will install this on request.

    Are there any catches with third-party extensions?

    The continued operation and compatibility of third-party extensions can't be guaranteed across major database upgrades (these occur less than once per academic year on average). If we encounter problems with third-party extensions on a shared server we may have to arrange alternative hosting with you.

    Using your own code with PostgreSQL

    How do I use my database with scripts or web applications?

    Your regular database access is authenticated using Kerberos; this means that, while using PSQL, you will not need to enter your password to access databases. For convenience however we also permit access with a password, for scripted access where it is not possible or convenient to use stored Kerberos credentials.

    If you have not set a password, your account will have have none. This denies all password attempts. To create a password, follow the instructions for resetting your password below.


    Why is my script's SSL connection breaking?

    Sorry, but at present, SSL is only allowed for Kerberos-authenticated clients (typically, just interactive psql sessions). If you are connecting with your password, you must use a regular, unsecured connection (with a few specific exceptions).

    Exceptions have been made for a number of web servers, including Groups and Homepages, so that both SSL and non-SSL connections will work.

    You can force PHP not to use SSL by adding "sslmode=disable" to your list of pg_connect options, for example using PHP:

    <?php
        $dbconn = pg_connect ("host=$server dbname=$db user=$username password=$password sslmode=disable")
    >
    

    If you are using the interactive psql client in a script, setting the $PGSSLMODE environment variable to 'disable' before running psql will have a similar effect.

    The sslmode parameter and $PGSSLMODE environment variable are amongst the standard connection parameters defined in libpq, the PostgreSQL client library on which most clients are based. You'll find that this trick also works for the likes of popular python PostgreSQL module psycopg2, for example.

    I'm using a shared database. Can I give access to another user?

    You can do this using the SQL GRANT and REVOKE commands to give access to other users, either on an individual table basis or en masse using the "ALL IN SCHEMA" clause.

    PostgreSQL provides documentation on all of the above: a good starting point might be the GRANT command.

    Some examples:

    -- Check who has permission to access your tables using 
    -- the postgresql \z and \dp commands.
    
    myuser=> \z
                    Access privileges
     Schema |    Name     | Type  | Access privileges
    --------+-------------+-------+-------------------
     public | foo         | table |
     public | bar         | table |
    
    -- Remember that in this example myuser has implicit access 
    --  to the database: it's the owner.
    
    myuser=> GRANT INSERT, DELETE, UPDATE, SELECT ON foo TO another;
    GRANT
    myuser=> GRANT SELECT ON foo TO another;
    GRANT
    
    myuser=> \z
                       Access privileges
     Schema |    Name     | Type  |    Access privileges
    --------+-------------+-------+-------------------------
     public | foo         | table | another=arwd/myuser
     public | bar         | table | another=r/myuser
    
    -- Using the schema noted above (default in postgresql is 'public')
    -- it is simple to revoke privileges from all tables, if you wish:
    
    myuser=> REVOKE ALL ON ALL TABLES IN SCHEMA public FROM another;
    REVOKE
    
    

    I am trying to empty a table but it is taking ages. Should I just keep trying until the database server breaks?

    This shouldn't happen, but massive DELETE FROM ... statements can take ages due to a quirk of the combination of PostgreSQL version and file system used on DICE servers.

    It will help you (and please Support) greatly if you empty very large tables in a more efficient way:

      myuser=> TRUNCATE TABLE tablename
    

    But it would be wise to read the postgresql documentation on the TRUNCATE command and to please make sure you have a copy of any important data before deciding to TRUNCATE it.

    I left my scripting password on a post-it note in a lab/at a conference. How can I change it?

    Note your password is your responsibility, and should be secret. Don't write it down.

    Most users can change their own passwords in postgres. (note the quotes in the commands below; they are important)

    • Log in to your account on pgteach/pgresearch from any desktop DICE machine. You will not be required to enter a password.
    • Now change your main account password to match:
      myuser=> ALTER ROLE username WITH password 'new password here';

    Alternatively, if you have no Kerberos access to your account, and no password, support can reset the password on your behalf. Be sure to change it (using the above) as soon as you have received the new password.

    I've forgotten my password, and lost the post-it note. How can I reset it?

    Your password is not stored in accessible form anywhere, but you don't need to know your password to change it. Simply reset it as above.

    I'm still stuck. How can I get help?

    Please visit the Informatics support form. Ensure you have read all relevant FAQs before continuing.

  • Last reviewed: 
    19/10/2020

    System Status

    Home dirs (AFS)
    Network
    Mail
    Other services
    University services
    Scheduled downtime

    Choose a topic