OpenGeo offered a bundle package of all the three (PostgreSQL, PostGIS and OpenGeo) for Ubuntu. But unfortunately, the tomcat6 package required seems having some problems in Ubuntu repository. “Broken package” error occurred whenever trying installing it via either “apt-get” or “synaptic” package management tool. Having no time to figure out the root causes, I turned to install the three one by one individually.
0. [updates]: Postgre-PostGIS now have a better and clean installation procedure as below:
PostgreSQL and PostGIS installation procedures
1. Install PostgreSQL Ubuntu repository provided an almost latest version of PostrgSQL, version 9.1, only one version older than the newest 9.3. To avoid unnecessary compatibility issue, I chose to use the repository version. Will consider upgrade to newer version when needed. Installed postreSQL and related development and client packages using “synaptic”.
To install the latest/specific version, follow the instructions here….
Create user and database
sudo -u postgres createuser -P USER_NAME_HERE
sudo -u postgres createdb -O USER_NAME_HERE DATABASE_NAME_HERE
2. Install PostGIS The version available in Ubuntu repository is 1.5, seems way out of date comparing with the newest 2.1. I installed 1.5 using “synaptic” first, but decided to upgrade to 2.1 via source build following below procedures:
2.1 Install prerequisite packages
sudo apt-get install build-essential libxml2-dev libgdal-dev libproj-dev libjson0-dev xsltproc docbook-xsl docbook-mathml
2.2 Build and Install GEOS3.4.x PostGIS 2.1 is best used with GEOS >= 3.4 for several new features, however Ubuntu 13.10 only has GEOS 3.3.3 available in packages, so it needs to be built from source to include the new features not provided in libgeos-dev package.
wget http://download.osgeo.org/geos/geos-3.4.2.tar.bz2
tar xfj geos-3.4.2.tar.bz2%MCEPASTEBIN%
cd geos-3.4.2
./configure
make
sudo make install
cd ..
2.3 Build and Install PostGIS
Note: make sure the following packages are installed:
postgresql-client-9.3 – client libraries and client binaries
postgresql-9.3 – core database server
postgresql-contrib-9.3 – additional supplied modules
libpq-dev – libraries and headers for C language frontend development
postgresql-server-dev-9.3 – libraries and headers for C language backend development
pgadmin3 – pgAdmin III graphical administration utility
wget http://download.osgeo.org/postgis/source/postgis-2.1.2.tar.gz
tar xfz postgis-2.1.2.tar.gz
cd postgis-2.1.2
./configure
make
sudo make install
sudo ldconfig
sudo make comments-install
sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/shp2pgsql
sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp
sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/raster2pgsql
2.5 Enable GIS Connect to database using psql, and use the commands to add the PostgreSQL extensions. To add PostGIS with raster support: type at the command line under user “postgres”.
sudo -u postgres psql
then execute the following commands:
CREATE EXTENSION postgis;
To add topology support, a second extension can be created on the database:
CREATE EXTENSION postgis_topology;
2.6. To use the database, it is necessary to change the PostgreSQL postgres user password; The server will not be accessible otherwise. As the “postgres” Linux user, execute the following psql command. And change the password
sudo -u postgres psql postgres
\password postgres
To create a new postgre user:
execute
sudo su - postgres
under terminal shell, and then execute
createuser -d -E -i -l -P -r -s yourUserName
2.7 Install GUI Administration tool
pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:
sudo apt-get install pgadmin3
sudo -u postgres psql CREATE EXTENSION "adminpack"
to satrt pgadmin3
pgadmin3
use the username and password created in 1 or 2.6 to establish DB connection.
There is one more step required in order to allow pgAdmin III to connect to the server, editing pg_hba.conf file and change the authentication method from peer to md5 (Will not work if you have not set the password.):
sudo nano /etc/postgresql/9.3/main/pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres peer
change peer to md5, and add
host all all 0.0.0.0/0 md5
sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql start
If access Postsql server from a remote client,
A. You need to configure the server to allow remote connections. To do so, edit the server configuration file
/etc/postgresql/9.3/main/postgresql.conf
and follow the instructions to allow “all” as below
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
listen_addresses = '*'
Server needs to be reloaded to enable the new listening address.
To check if server is allowing remote connections and the running port, on the server run :
netstat -na
and see if
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
it means allowed, otherwise not.
B. You need explicitly add the client side host address into “/etc/postgresql/9.3/main/pg_hba.conf” as below to authorize the accessibility for the specific host.
host all all 108.7.54.145/24 md5
where “108.7.54.145” is the client host address to be allow for access. To allow client access via any machine, add
host all all 0.0.0.0/0 md5
2.8 Connecting to database via command line client Install the client via
sudo apt-get install postgresql-client
connect to the database via
psql -h server.domain.org database user
2.9. Create a database as a test
run
sudo -u postgres createdb rsas-db
2.10 Quit Postgres Ctrl+D
2.11 Reset admin password
If you forgot your admin password (just a few days after you set it as what I am now experiencing), here is a way to reset it and start all over again.
sudo nano /etc/postgresql/9.1/main/pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres md5
change md5 back to peer
restart Postgre via
sudo /etc/init.d/postgresql reload
then access the postgre console via
sudo -u postgres psql
Once inside psql, issue the SQL command:
ALTER USER postgres PASSWORD 'newpassword';
then redo the changes to /etc/postgresql/9.1/main/pg_hba.conf as instructed in 2.7
3. Installation on Ubuntu 14.04 Trusty
Follow the procedure in the following pages, the process is very precise and should proceed smoothly.
http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1310src
4. Installation on AWS
You need to appropriate set the security group and inbound policies to allow PostgreSQL client access via tcp/5432.
forum of geeks 