wiki:UsersWikiPostGIS21UbuntuPGSQL93Apt

The PostgreSQL Apt Repository now hosts installs of PostGIS, in addition to Postgresql and PGAdmin3.

The following describes how to install Postgresql 9.4, PostGIS 2.1, PGAdmin on Ubuntu versions 12.04 and 14.04.1. It has also been tested and works on Linux Mint, Lubuntu, and Xubuntu.

Run these in terminal:

Verify what you are running

sudo lsb_release -a 

Add Respository to sources.list

You'll need to replace the nickname below with what you are running

For vivid (15.04) PostgreSQL 9.4 is already latest on launchpad / regular distro so no need to add apt.postgresql.org to list

For trusty (14.04)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list'

For precise (12.04) do this

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list'

Add Keys

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install

The following will install postgresql 9.4, PostGIS, PGAdmin3, and additional supplied modules including the adminpack extension:

If you are running 14.04 (Trusty, Vivid) -- you need to do this

sudo apt-get install postgresql-9.4-postgis-2.1 pgadmin3 postgresql-contrib-9.4

For 12.04 thru 13.02, this seems to work

sudo apt-get install postgresql-9.4-postgis pgadmin3 postgresql-contrib

To get pgRouting

(instructions from http://workshop.pgrouting.org/chapters/installation.html seems to work fine with PostgreSQL Apt repo PostgreSQL 9.3 install)

sudo apt-add-repository -y ppa:georepublic/pgrouting
sudo apt-get update
# Install pgRouting package (for Ubuntu 14.04)
sudo apt-get install postgresql-9.4-pgrouting

Enable Adminpack

While in terminal, log in to the psql console as postgres user:

sudo -u postgres psql
CREATE EXTENSION adminpack;

You can also enable the PostGIS extension here (or with the GUI as described below):

CREATE EXTENSION postgis;

Exit the psql console:

\q

Open Access to Clients

You may need to edit to pg_hba.conf and/or pg_ident.conf, postgresql.conf to allow external access

sudo nano /etc/postgresql/9.4/main/pg_hba.conf

If you need external access, scroll to the bottom of the pg_hba.conf file and add a line like this (which willa llow all clients with md5 password encrypt authentication (right after the local rules):

hostssl    all             all             0.0.0.0/0               md5

Click CTRL-X to save your changes, Y to write them to the file, and enter to exit.

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.

sudo service postgresql restart 

Note: you can also do from postgres psql console with below - only for changes that don't require retart)

SELECT pg_reload_conf();

Optional: check location of configuration files:

From the psql console(see above):

SELECT name, setting FROM pg_settings where category='File Locations';

Which will output something like:

       name        |                 setting
-------------------+------------------------------------------
 config_file       | /etc/postgresql/9.4/main/postgresql.conf
 data_directory    | /var/lib/postgresql/9.4/main
 external_pid_file | /var/run/postgresql/9.4-main.pid
 hba_file          | /etc/postgresql/9.4/main/pg_hba.conf
 ident_file        | /etc/postgresql/9.4/main/pg_ident.conf

Create new PGSQL user

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

Exit terminal:

exit

Use PGAdmin to log into database server

From the Ubuntu launcher, type in PGAdmin to reveal the application

Click the Add a connection to server button on the menu in the top-left corner

Fill in the necessary credentials to log in:

- Name: nickname you want to give your connection
- host: localhost (until you change the connection settings)
- port: 5432 by default
- maintenance DB: postgres by default
- username: whatever you chose in the step above
- password: whatever you chose in the step above

Enable postgis extensions on new database

Since you installed PostgreSQL and PostGIS from the PostgreSQL apt repository, you now have all the extensions for PostgreSQL, including PostGIS

Expand the database tree in PGAdmin, and reveal the extensions node

Right-click the extensions node, and click new extension

Enable the postgis extension, as well as postgis_topology, and postgis_tiger_geocoder

Alternatively from psql console you can do

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
#if you installed pgRouting extension, you can do
CREATE EXTENSION pgrouting;

Import SHP files using shp2pgsql-gui

Another handy piece of software shp2pgsql-gui tool. This will allow you to quickly connect to your new PostGIS database and import a Shapefile.

Open terminal, and type:

sudo apt-get install postgis

(Note: this is coming from the main Ubuntu software repository, as it seems the PostgreSQL APT repository doesn't package SHP2PGSQL-GUI anymore...)

Now open the SHP2PGSQL application:

shp2pgsql-gui

Follow the on-screen prompts to load your data.

For more information, visit the Boston GIS tutorial in the section "Load Towns Data"

Last modified 5 months ago Last modified on Jan 15, 2016 10:46:50 AM