The first step is to remove older version of PostGIS if any.
sudo apt-get purge postgis
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
sudo apt-get update && sudo apt-get install postgresql-9.3-postgis-2.1 postgresql-contrib-9.3 pgadmin3 -f
Now, let’s see how we can create a PostGIS enabled database. We have two ways of doing this, in which the first one is the latest and simple one:
sudo -u postgres psql
CREATE EXTENSION adminpack;
Find out where core files are located. You may need to edit to /etc/postgresql/9.3/main/pg_hba.conf and/or pg_ident.conf, postgresql.conf to allow external access - for example if you need external access, may add a line like this to allow all with md5 password encrypt authentication (right after the local rules)
hostssl all all 0.0.0.0/0 md5
You'll also want to edit the postgresql.conf file and change listen_addresses line to a specific ip of the server or '*' to listen on all ips. If you change ip or port, you need to do a service restart.
service postgresql restart
after (can also do from postgres psql console with below - only for changes that don't require retart)
SELECT pg_reload_conf();
SELECT name, setting FROM pg_settings where category='File Locations';
Which will output something like:
name | setting
-------------------+------------------------------------------
config_file | /etc/postgresql/9.3/main/postgresql.conf
data_directory | /var/lib/postgresql/9.3/main
external_pid_file | /var/run/postgresql/9.3-main.pid
hba_file | /etc/postgresql/9.3/main/pg_hba.conf
ident_file | /etc/postgresql/9.3/main/pg_ident.conf
(5 rows)
Quit psql:
\q
You can create a new database super user to use instead of the default postgres user.
While in terminal, run:
sudo su - postgres
createuser -d -E -i -l -P -r -s yourUserName
(be sure to change the username to your desired username)
Enter your new password when prompted
Log out as postgres user:
exit
This is as simple as running a query in the database where you want to enable PostGIS:
sudo -u postgres psql
CREATE EXTENSION postgis;
OR
This is an old method of doing the same. Creating a template for PostGIS will make it easy to enable PostGIS for every new database you create:
createdb -E UTF8 template_postgis2.1
psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis2.1'"
Make sure you have logged in to postgres if not:
sudo -u postgres psql
Now, we have to run an SQL script comes along with PostGIS in the template:
psql -d template_postgis2.1 -f /usr/share/postgresql/9.3/extension/postgis--2.1.5.sql
psql -d template_postgis2.1 -c "GRANT ALL ON geometry_columns TO PUBLIC;"
psql -d template_postgis2.1 -c "GRANT ALL ON geography_columns TO PUBLIC;"
psql -d template_postgis2.1 -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
Let’s test the PostGIS installation by creating a test database:
createdb test_db -T template_postgis2.1
In test_db you can run the following statement to make sure that you installed and configured PostGIS correctly:
test_db=# select postgis_version();
postgis_version---------------------------------------
2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
If you don’t want to use the CREATE EXTENSION statement and want to enable PostGIS for an already existing database. It is simple enough, you just need to run the PostGIS 2.1 script in your database:
psql -d test_db -f /usr/share/postgresql/9.3/extension/postgis--2.1.5.sql
inspiration http://technobytz.com/install-postgis-postgresql-9-3-ubuntu.html