Installing & Configuring PostgreSQL + PostGIS in Ubuntu 12.XX

Installing & Configuring PostgreSQL + PostGIS in Ubuntu 12.XX

Installation

Launch Terminal

Open Terminal. Use Ctrl+Alt+T key combination                         

install python-software-properties. Type the command below in the terminal

sudo apt-get install python-software-properties

Add PPA repository to my Ubuntu.

Type the command below in the terminal

sudo add-apt-repository ppa:pitti/postgresql

 

Update the System

After adding PPA, update your system apt:

Type the command below in the terminal

                sudo apt-get update

Finally install postgresql-9.X:

Type the command below;

sudo apt-get install postgresql

A successful installation

Configuring postgresql.conf to use port 5432...

update-alternatives: using /usr/share/postgresql/9.1/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.

 * Starting PostgreSQL 9.1 database server                      [ OK ]

Setting up postgresql (9.1+134~precise) ...

if you having any error, make sure you already install libpq-dev. The libpq-dev package is for compiling wrappers/clients against libpq.

Install libpq-dev package

$sudo apt-get install postgresql-9.1 libpq-dev

 

 

 

 

 

A successful installation of Libpqdev package

 

 

 

Now check it out installation is successful or…..

locate postgresql

If done!!!, Cheers ………….. Check the install version.

PostgreSQL Configuration

Change password

 su root

 su passwd postgres

PostgreSQL Console

Now let’s take look to postgres console.

Check version

$psql –V

     

Additional configurations

Login as postgres user from root

$su postgres

That’s it for postgres installation. If you want to access postgres from remote, other than localhost. You need to perform some addition setting to postgres. conf file.

First we edit the file postgresql.conf and List all file in main folder

 cd /etc/postgresql/9.1/main

ls -la

I have already back of config file, you need to make back up for further need

cp pg_hba.conf pg_hba.conf.ogn

cp postgresql.conf postgresql.conf.ogn

Make changes to pg_hba.conf (authentication methods).

Use vim pg_hba.conf to edit the file as shown below

 Restart PostgreSQL server

/etc/init.d/postgresql restart

Perfect enjoy working with PostgreSQL database server

(Optional) Install PostGIS 2.0

PostGIS add-on is required for storage of maps in the database for consumption in any GIS client (web, mobile or desktop)

Remove Existing PostGIS Packages

Before we begin, you should uninstall your existing postgis packages:

sudo dpkg --purge postgis postgresql-9.1-postgis

Add new Repository and Install PostGIS

Add a new repository and install PostGIS from there (based on this post):

sudo apt-add-repository ppa:sharpie/for-science  # To get GEOS 3.3.2 

sudo apt-add-repository ppa:sharpie/postgis-nightly

Run the installation by issuing the command

sudo apt-get update

Please note if you have logged in as root, you don't need to use the sudo  keyword as shown below. In subsequent steps we will not use the sudo keyword.

apt-get install postgresql-9.1-postgis

Create a Template PostGIS Database

Next we should create a new template database (optional but recommend). This is the database that shall be used as a clone for databases for the new GIS enabled databases

Switch to postgres user

su postgres

Execute the following commands below;

createdb -E UTF8 template_postgis2

createlang -d template_postgis2 plpgsql

psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis2'"

Execute the following commands. Remember due to version changes replace the 9.1 with the correct 9.x label on the scripts below. Repeat the same for the rest of 2.1 for 2.x

psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql

psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/spatial_ref_sys.sql

psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/rtpostgis.sql

psql -d template_postgis2 -c "GRANT ALL ON geometry_columns TO PUBLIC;"

psql -d template_postgis2 -c "GRANT ALL ON geography_columns TO PUBLIC;"

psql -d template_postgis2 -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

Test by creating a PostGIS enabled database

createdb training -T template_postgis2

Backup and Recovery of a database

Backup from a windows machine

Execute the command below.

pg_dump -U postgres -d csudp > csudp.sql

Restore from a data file in Linux

createdb -T template_postgis2 csudp

psql -d csudp < /var/lib/postgresql/csudp.sql

Check out the tables installed