Documentation

Difference between revisions of "Using PostgreSQL"

 
Line 1: Line 1:
= Creating a database =
+
== Creating a database ==
  
 
You can create one or more PostgreSQL databases in [https://ucc.ulyssis.org UCC].
 
You can create one or more PostgreSQL databases in [https://ucc.ulyssis.org UCC].
Line 5: Line 5:
 
The first database you create will have the same name as your username. Subsequent databases will be prefixed with "''username''_".
 
The first database you create will have the same name as your username. Subsequent databases will be prefixed with "''username''_".
  
= Managing your database =
+
== Managing your database ==
  
 
* The easiest way to manage your database is using [https://phppgadmin.ulyssis.org phpPgAdmin].
 
* The easiest way to manage your database is using [https://phppgadmin.ulyssis.org phpPgAdmin].
Line 11: Line 11:
 
  psql -h pgsql.ulyssis.org
 
  psql -h pgsql.ulyssis.org
  
= Connecting to the database =
+
== Connecting to the database ==
  
 
You can connect to the database with the following details:
 
You can connect to the database with the following details:
Line 22: Line 22:
 
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.
 
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 ==
+
=== Connection string ===
  
 
If you are programming your application yourself, PostgreSQL often uses the following connection format:
 
If you are programming your application yourself, PostgreSQL often uses the following connection format:
Line 29: Line 29:
 
Where you replace the parts starting with <code>$</code> with the above connection details.
 
Where you replace the parts starting with <code>$</code> with the above connection details.
  
== pg_connect ==
+
=== pg_connect ===
  
 
If you are using PHP's [http://www.php.net/manual/en/function.pg-connect.php pg_connect] to connect to the
 
If you are using PHP's [http://www.php.net/manual/en/function.pg-connect.php pg_connect] to connect to the
Line 36: Line 36:
 
$connection = pg_connect("host=pgsql.ulyssis.org user=foo password=password db=foo");
 
$connection = pg_connect("host=pgsql.ulyssis.org user=foo password=password db=foo");
 
</syntaxhighlight>
 
</syntaxhighlight>
== PDO ==
+
=== PDO ===
  
 
If you want to connect using [http://be2.php.net/manual/en/intro.pdo.php PDO], your username is "foo", your password is "password" and your database is "foo":
 
If you want to connect using [http://be2.php.net/manual/en/intro.pdo.php PDO], your username is "foo", your password is "password" and your database is "foo":
Line 43: Line 43:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=Accessing PostgreSQL from outside of our network=
+
==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):
 
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
 
  ssh username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N
Line 49: Line 49:
 
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.
 
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 =
+
== 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.
 
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.
  
 
[[Category:Databases]]
 
[[Category:Databases]]

Latest revision as of 01:52, 26 October 2019

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.