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

Creating a PostgreSQL user

To use PostgreSQL on you ULYSSIS account, you first need to create a PostgreSQL user on UCC. In the PostgreSQL section of the panel, you will be suggested to click a link to create a user. Then fill in the password for the database user. It is highly recommended that this password differs from the password you use for your account. Finally, click Create user and you are ready to go.

Creating a PostgreSQL database

After having created a PostgreSQL user, you can click Add database in UCC and enter the name for your new database. It will automatically will be prefixed by your username.

Deleting a PostgreSQL database

If you no longer need a database, you can easily remove it by clicking on PostgreSQL in UCC and then pressing on Remove database next to the database you would like to delete. After confirming, the database and all its tables and other content will be gone. For security reasons, you can't remove databases using an SQL query like DROP DATABASE, but have to go through UCC.

Using PostgreSQL for your website or application

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

For example, if my username is foobar, I made a database called website and I were to create a PHP website I would use something like:

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

or

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

Accessing PostgreSQL with PHPPgAdmin or Adminer

Using your username and password you can easily manage your PostgreSQL databases, tables and records on https://phppgadmin.ulyssis.org and https://adminer.ulyssis.org. Documentation is also available to aid in using PHPPgAdmin and using Adminer.

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. SSH-tunneling makes an encrypted connection between 2 devices, which we will use to map a port on the database server onto a local port on your device, so it behaves and can be used like a local port.

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

While this command is running, you can connect to the PostgreSQL server with host 'localhost' and port '5400', To terminate the port forwarding, press ctrl-C in the terminal.

If you prefer the command to be run in the background, you can add -f to your command. To close this background process, you can use pgrep to acquire the pid of the process:

pgrep --list-full ssh | grep pgsql.ulyssis.org
12657 ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N

We can then kill the process using kill with the pid we acquired:

kill 12657

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.