Using PostgreSQL - Revision history

Erico: typos

2022-09-30T08:09:26Z

typos

← Older revision		Revision as of 10:09, 30 September 2022
Line 48:		Line 48:

	If you prefer the command to be run in the background, you can add <code>-f</code> to your command. To close this background process, you can use pgrep to acquire the pid of the process:		If you prefer the command to be run in the background, you can add <code>-f</code> to your command. To close this background process, you can use pgrep to acquire the pid of the process:
	pgrep --list-full ssh \| grep ~~psgql~~.ulyssis.org		pgrep --list-full ssh \| grep pgsql.ulyssis.org
	12657 ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N		12657 ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N

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

Erico: replace PHPMyAdmin with PHPPgAdmin

2022-09-30T08:05:59Z

replace PHPMyAdmin with PHPPgAdmin

← Older revision		Revision as of 10:05, 30 September 2022
Line 37:		Line 37:

	=== Accessing PostgreSQL with PHPPgAdmin or Adminer ===		=== 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~~\|using PHPMyAdmin~~]] and [[using 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===		===Accessing PostgreSQL from outside of our network===

Erico: replace PHPMyAdmin with PHPPgAdmin

2022-09-30T08:05:22Z

replace PHPMyAdmin with PHPPgAdmin

← Older revision		Revision as of 10:05, 30 September 2022
Line 36:		Line 36:
	</syntaxhighlight>		</syntaxhighlight>

	=== Accessing PostgreSQL with ~~PHPMyAdmin~~ or Adminer ===		=== 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\|using PHPMyAdmin]] and [[using 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\|using PHPMyAdmin]] and [[using Adminer]].

Erico at 08:03, 30 September 2022

2022-09-30T08:03:36Z

← Older revision		Revision as of 10:03, 30 September 2022
Line 37:		Line 37:

	=== Accessing PostgreSQL with PHPMyAdmin or Adminer ===		=== Accessing PostgreSQL with PHPMyAdmin or Adminer ===
	Using your username and password you can easily manage your PostgreSQL databases, tables and records on https://~~phpmyadmin~~.ulyssis.org and https://adminer.ulyssis.org. Documentation is also available to aid in [[using PHPMyAdmin]] and [[using 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\|using PHPMyAdmin]] and [[using Adminer]].

	===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.		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):		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):
Line 47:		Line 47:
	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.		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.

	~~You~~ can use "ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N": the ~~f-argument will put~~ the ~~command in the background.~~		If you prefer the command to be run in the background, you can add <code>-f</code> to your command. To close this background process, you can use pgrep to acquire the pid of the process:
			pgrep --list-full ssh \| grep psgql.ulyssis.org
			12657 ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N

			Then we can then kill the process using kill with the pid we acquired:
			kill 12657

	== PostGIS ==		== PostGIS ==

Erico: Changes zoals uitgelegd op wikipagina documentatie herschrijven, nog wat meer nodig bij ssh

2022-09-29T14:30:29Z

Changes zoals uitgelegd op wikipagina documentatie herschrijven, nog wat meer nodig bij ssh

← Older revision		Revision as of 16:30, 29 September 2022
Line 11:		Line 11:
	psql -h pgsql.ulyssis.org		psql -h pgsql.ulyssis.org

	== ~~Connecting~~ to the database ==		=== Creating a PostgreSQL user ===
			To use PostgreSQL on you ULYSSIS account, you first need to create a PostgreSQL user on [https://ucc,ulyssis,org 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 [https://ucc.ulyssis.org 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 [https://ucc.ulyssis.org 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:		You can connect to the database with the following details:
Line 20:		Line 29:
	* Database: the database you created 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.~~		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:<syntaxhighlight lang="php">

	~~=== 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 <code>$</code> with the above connection details.~~

	~~=== pg_connect ===~~

	~~If you are using PHP's [http://www.php.net/manual/en/function.pg-connect.php pg_connect] to connect to the~~
	~~database, and your~~ username is ~~"foo"~~, ~~your password is "password"~~ and ~~your database name is "foo"~~:
	<syntaxhighlight lang="php">
	$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>or
	~~=== 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":~~
	<syntaxhighlight lang="php">		<syntaxhighlight lang="php">
	$pdo = new PDO('pgsql:host=pgsql.ulyssis.org;dbname=foo', 'foo', 'password');		$pdo = new PDO('pgsql:host=pgsql.ulyssis.org;dbname=foo', 'foo', 'password');
	</syntaxhighlight>		</syntaxhighlight>

	==Accessing PostgreSQL from outside of our network==		=== Accessing PostgreSQL with PHPMyAdmin or Adminer ===
	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):		Using your username and password you can easily manage your PostgreSQL databases, tables and records on https://phpmyadmin.ulyssis.org and https://adminer.ulyssis.org. Documentation is also available to aid in [[using PHPMyAdmin]] 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.

			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

	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.		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.

			You can use "ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N": the f-argument will put the command in the background.

	== PostGIS ==		== PostGIS ==

Bert at 00:52, 26 October 2019

2019-10-26T00:52:02Z

← Older revision		Revision as of 02:52, 26 October 2019
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]]

Bert at 15:20, 28 August 2019

2019-08-28T15:20:04Z

← Older revision		Revision as of 17:20, 28 August 2019
Line 51:		Line 51:
	= 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]]

Andreas: /* Accessing PostgreSQL from outside of our network */

2018-11-05T11:37:37Z

Accessing PostgreSQL from outside of our network

← Older revision		Revision as of 13:37, 5 November 2018
Line 45:		Line 45:
	=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 -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N		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 =		= 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.

Bert at 02:34, 8 February 2017

2017-02-08T02:34:01Z

← Older revision		Revision as of 04:34, 8 February 2017
Line 46:		Line 46:
	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 -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N		ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N

			= 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.

Rockinroel: /* Accessing PostgreSQL from outside of our network */

2014-05-11T10:16:45Z

Accessing PostgreSQL from outside of our network

← Older revision		Revision as of 12:16, 11 May 2014
Line 44:		Line 44:

	=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 -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N		ssh -f username@ssh2.ulyssis.org -L 5400:pgsql.ulyssis.org:5432 -N