Using PostgreSQL

Creating a database

You can create one or more PostgreSQL databases in UCC.

The first database you create will have the same name as your username. Subsequent databases will be prefixed with "username_".

Managing your database

• The easiest way to manage your database is using phpPgAdmin.
• You can also access it via the command line with:

psql -h pgsql.ulyssis.org

Connecting to the database

You can connect to the database with the following details:

• Host: pgsql.ulyssis.org
• Login: your ULYSSIS username
• Password: the PostgreSQL password you chose in UCC
• Database: the database you created in UCC

Note that some CMSs will assume that you use "localhost" as the host, and will hide the host option under advanced settings in the installation process. If you can't find anywhere to enter the hostname, look under advanced settings.

Connection string

If you are programming your application yourself, PostgreSQL often uses the following connection format:

host=$host user=$username password=$password db=$dbname

Where you replace the parts starting with $ with the above connection details.

pg_connect

If you are using PHP's pg_connect to connect to the database, and your username is "foo", your password is "password" and your database name is "foo":

$connection = pg_connect("host=pgsql.ulyssis.org user=foo password=password db=foo");

PDO

If you want to connect using PDO, your username is "foo", your password is "password" and your database is "foo":

$pdo = new PDO('pgsql:host=pgsql.ulyssis.org;dbname=foo', 'foo', 'password');

Accessing PostgreSQL from outside of our network

To prevent unnecessary load on our database server by hackers and bots it is only available inside of our network. If you wish to access it externally the easiest way is to use an SSH-tunnel. On Linux, Mac OSX and other Unix-like Operating Systems it is easily possible to use the following command to create a tunnel to a local port (in this case 5400):

ssh username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N

You can use "ssh -f username@ssh2.ulyssis.org -L 3300:mysql.ulyssis.org:3306 -N": the f-argument will put the command in the background.

PostGIS

If necessary for an application or website you wish to use on ULYSSIS, we can always enable PostGIS for the specific database you wish to use it on. Contact us at ulyssis@ulyssis.org and let us know you would like to get PostGIS enabled and on which specific database.