Now we need to install PostGIS to add geographic objects to PostgreSQL. PostGIS is pretty much equivalent to SDE in the ESRI world but follows the OpenGIS "Simple Features Specification for SQL". It is really nice that it subscribes to an open standard like this so it can interoperate with so many other systems. We will eventually install Geoserver and configure it to connect to PostgreSQL/PostGIS to get data and utilize funstionality. You can do quite a bit of serious web mapping and GIS with this combination of technologies.
Anyway, lets get PostGIS installed and configured here now...
$ sudo apt-get install postgis postgresql-8.4-postgis
Now we need to create a new database, add support for the PL/pgSQL database language to that database, and then run 3 scripts that add all the geographic database objects to the database.
$ sudo -u postgres createdb my_db $ sudo -u postgres createlang plpgsql my_db $ sudo cd /usr/share/postgresql/8.4/contrib/ $ sudo -u postgres psql -d my_db -f postgis.sql $ sudo -u postgres psql -d my_db -f spatial_ref_sys.sql $ sudo -u postgres psql -d my_db -f postgis_comments.sql
Notice that I am giving the postgres user superuser rights to run these commands. By default you have to run the psql command as postgres since that is the only user with a database account, but we need to give the OS account permission to do these things as well. This is a bit annoying but it is better than spending a bunch of time changing permissions and settings on the postgres account. That would be less secure as well.
Here are a few other useful programs that may come in handy. These are all programs not commands executed inside psql.
sudo -u postgres createdb dropdb <database>