Installing Postgis - Revision history

Tykling at 13:02, 9 May 2010

2010-05-09T13:02:20Z

← Older revision		Revision as of 13:02, 9 May 2010
Line 10:		Line 10:

	The steps to install Postgres is outlined on the [[Installing Postgres]] page. After following that, I follow the steps below to add a user and a database, and Postgis enable that database.		The steps to install Postgres is outlined on the [[Installing Postgres]] page. After following that, I follow the steps below to add a user and a database, and Postgis enable that database.

			== Installing Postgis ==
			As always, FreeBSD ports makes this step easy:
			<pre>
			$ sudo portmaster /usr/ports/databases/postgis
			</pre>

	== Adding a user to Postgres ==		== Adding a user to Postgres ==
	Postgres has added a user to the system, ~~you~~ need to ''su'' to that user to make changes to Postgres:		Postgres has added a user to the system, I need to ''su'' to that user to make changes to Postgres:
	<pre>		<pre>
	[tykling@wackbox163 ~]$ sudo su pgsql		[tykling@wackbox163 ~]$ sudo su pgsql
Line 33:		Line 39:
	</pre>		</pre>

	So far so good, nothing Postgis related yet.. but the database needs to be Postgis enabled, and to do that we need a couple of the files that the Postgis port installed:		So far so good, nothing Postgis related yet.. but the database needs to be Postgis enabled, and to do that I need a couple of the files that the Postgis port installed:

	<pre>		<pre>

Tykling at 12:39, 9 May 2010

2010-05-09T12:39:52Z

@@ Line 9: / Line 9: @@
 Not sure what all that means, but a friend of mine is works with GIS and I just setup a jail with postgres and postgis for him. I had a few minor glitches along the way. Here is the procedure I used to install Postgres and Postgis, and create a Postgis-enabled database.
-== Install the ports ==
+The steps to install Postgres is outlined on the [[Installing Postgres]] page. After following that, I follow the steps below to add a user and a database, and Postgis enable that database.
 == Adding a user to Postgres ==

Tykling at 09:23, 20 December 2009

2009-12-20T09:23:39Z

← Older revision		Revision as of 09:23, 20 December 2009
Line 1:		Line 1:
	[[category:miscellaneous]]		[[category:miscellaneous]]
			[[category:postgres]]
			[[category:postgis]]
	From the Postgis homepage:		From the Postgis homepage:

Tykling at 23:27, 19 December 2009

2009-12-19T23:27:14Z

← Older revision		Revision as of 23:27, 19 December 2009
Line 134:		Line 134:
	Now that that is sorted all that remains is to run the two sql files in the database I want Postgis enabled:		Now that that is sorted all that remains is to run the two sql files in the database I want Postgis enabled:
	<pre>		<pre>
	[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/~~lwpostgis~~.sql > ~~lwpostgis~~.sql.log 2>&1		[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/contrib/postgis.sql > postgis.sql.log 2>&1
	[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/spatial_ref_sys.sql > spatial_ref_sys.log 2>&1		[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/contrib/spatial_ref_sys.sql > spatial_ref_sys.log 2>&1
	[pgsql@wackbox163 ~]$		[pgsql@wackbox163 ~]$
	</pre>		</pre>

	A quick look at the logfiles afterwards and I see nothing alarming. Thanks to #postgis on freenode for pointing me in the right direction on this one. The website [http://www.bostongis.com/?content_name=postgis_tut01 BostonGIS] also helped a lot.		A quick look at the logfiles afterwards and I see nothing alarming. Thanks to #postgis on freenode for pointing me in the right direction on this one. The website [http://www.bostongis.com/?content_name=postgis_tut01 BostonGIS] also helped a lot.

Tykling: Postgis moved to Installing Postgis

2009-06-21T15:18:25Z

Postgis moved to Installing Postgis

← Older revision	Revision as of 15:18, 21 June 2009
(No difference)

Tykling at 15:16, 21 June 2009

2009-06-21T15:16:44Z

← Older revision		Revision as of 15:16, 21 June 2009
Line 1:		Line 1:
			[[category:miscellaneous]]
	From the Postgis homepage:		From the Postgis homepage:

Tykling: New page: From the Postgis homepage: ''PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowi...

2009-06-21T15:15:32Z

New page: From the Postgis homepage: ''PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowi...

New page

From the Postgis homepage:

''PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension.''

Not sure what all that means, but a friend of mine is works with GIS and I just setup a jail with postgres and postgis for him. I had a few minor glitches along the way. Here is the procedure I used to install Postgres and Postgis, and create a Postgis-enabled database.

== Install the ports ==
Install the ports with default options:

<pre>
[tykling@wackbox163 ~]$ portmaster /usr/ports/databases/postgresql83-server/
...snip...
[tykling@wackbox163 ~]$ portmaster /usr/ports/databases/postgis
...snip...
</pre>

And as always, remember to enable Postgres in rc.conf:
<pre>
[tykling@wackbox163 ~]$ grep postgres /etc/rc.conf
postgresql_enable="YES"
</pre>

== Initializing Postgres ==
When you install the Postgres server, the post-install message says (among other things):
<pre>
To initialize the database, run

/usr/local/etc/rc.d/postgresql initdb

You can then start PostgreSQL by running:

/usr/local/etc/rc.d/postgresql start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~pgsql/data/postgresql.conf for more info
</pre>

When I tried calling the rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that Postgres is running in a jail. I was getting some errors about shared memory:

<pre>
creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Function not implemented
</pre>

I found the solution on the always great [http://www.freebsddiary.org/jail-multiple.php The FreeBSD Diary]. I needed to change a sysctl on the jail host - not in the jail itself ofcourse - to allow the shared memory operation. Use:

<pre>
[tykling@wackbox ~]$ sudo sysctl security.jail.sysvipc_allowed=1
</pre>

to change the current value, and add:
<pre>
security.jail.sysvipc_allowed=1
</pre>

to ''/etc/sysctl.conf'' to ensure it stays that way after the next reboot.

I didn't need to restart the jail or anything after this change, it just immediately worked when I tried going back to rerun the ''initdb'' command.

After the database is initialized, start Postgres using the rc.d script, and you are ready to go.

== Adding a user to Postgres ==
Postgres has added a user to the system, you need to ''su'' to that user to make changes to Postgres:
<pre>
[tykling@wackbox163 ~]$ sudo su pgsql
[pgsql@wackbox163 /usr/home/tykling]$ cd ~
[pgsql@wackbox163 ~]$
</pre>

Postgres uses the commands ''createuser'' and ''createdb'' to create users and databases. Since my friend is the only one on this server, I added him as a superuser:
<pre>
[pgsql@wackbox163 ~]$ createuser -P myfriend
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
</pre>

Then I added a database for him:
<pre>
[pgsql@wackbox163 ~]$ createdb -O myfriend gistestdb
[pgsql@wackbox163 ~]$
</pre>

So far so good, nothing Postgis related yet.. but the database needs to be Postgis enabled, and to do that we need a couple of the files that the Postgis port installed:

<pre>
[tykling@wackbox163 ~]$ pkg_info -L -x postgis
Information for postgis-1.3.6:

Files:
/usr/local/man/man1/pgsql2shp.1.gz
/usr/local/man/man1/shp2pgsql.1.gz
/usr/local/bin/pgsql2shp
/usr/local/bin/shp2pgsql
/usr/local/lib/liblwgeom.so
/usr/local/lib/liblwgeom.so.1
/usr/local/lib/postgresql/liblwgeom.so.1
/usr/local/share/postgis/lwpostgis.sql
/usr/local/share/postgis/lwpostgis_upgrade.sql
/usr/local/share/postgis/spatial_ref_sys.sql
/usr/local/share/postgis/utils/postgis_restore.pl
/usr/local/share/postgis/utils/profile_intersects.pl
/usr/local/share/postgis/utils/test_estimation.pl
/usr/local/share/postgis/utils/test_joinestimation.pl
/usr/local/share/postgis/utils/create_undef.pl
/usr/local/share/postgis/utils/README
/usr/local/share/doc/postgis/README.postgis
/usr/local/share/doc/postgis/postgis.html
</pre>

We need to run the two .sql files ''/usr/local/share/postgis/lwpostgis.sql'' and ''/usr/local/share/postgis/spatial_ref_sys.sql'' in that order. To do that we need [http://en.wikipedia.org/wiki/PL/pgSQL plpgsql] language support in Postgres, so before running them go to the database and run the following command:
<pre>
[pgsql@wackbox163 ~]$ psql gistestdb
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

android2=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
android2=# \q
[pgsql@wackbox163 ~]$
</pre>

Note how the Freebsd ''pgsql'' user is permitted access to the database (and any database) with no password. This is because postgres is installed with ''trust'' for the local machine, meaning that anyone with access to the local machine can access any database.

Now that that is sorted all that remains is to run the two sql files in the database I want Postgis enabled:
<pre>
[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/lwpostgis.sql > lwpostgis.sql.log 2>&1
[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/spatial_ref_sys.sql > spatial_ref_sys.log 2>&1
[pgsql@wackbox163 ~]$
</pre>

A quick look at the logfiles afterwards and I see nothing alarming. Thanks to #postgis on freenode for pointing me in the right direction on this one. The website [http://www.bostongis.com/?content_name=postgis_tut01 BostonGIS] also helped a lot.