Installing Postgresql and PostGIS on Centos

Sadly installing postgresql and postgis is not as straighforward as it could be on centos 6.X as the latest stable verisons of postgresql and its various extensions are not in any of the default repositories.

VM Environment

For reference, I’m using an ESXI host and and a vanilla Centos6.4 64bit client. The client was installed from the minimal image (CentOS-6.4-x86_64-minimal.iso).

 Initial Client OS Install

Set up the vm as required. My sample virtial machine is very underpowered for a database server but fine as a development machine.

•         Single Socket
•         2 CPU
•         2 GB of Ram
•         32 GB of Disk
•         1 NIC

Install VM:

1. Boot the Machine with the iso media.
2. Choose Install or upgrade an existing system (Don’t bother testing the media)
3. Set up the system as you like (language, keyboard, hostname, etc. I chose to use the whole disk and a hostname of postgis.centos.skyscanner.dev.local)
4. Let the installer install the base packages and reboot.

Set up Client Network Card:

we need to do a little configuration to get the machine onto your network.

Set up the Network Interface (NIC)

vi /etc/sysconfig/network-scripts/ifcfg-eth0

change ONBOOT=no to ONBOOT=yes
add DHCP_HOSTNAME=<hostname> where hostname is the name of your vm
save and close the file (in vi thats :wq! (write, quit)
restart network:

service network restart

Check you have an IP address..

 ip address

you should see an ip address for the eth0 NIC now

If you don’t get an IP address then you need to check your VM’s network setup - if you are using something like VMWare player or Virtualbox then by default the NIC will be NATted (Network address translated) - under a NAT or a bridged connection (the vm looks like another machine to the rest of the network) then you should get an IP address from your DHCP server.

Your configuration may differ so your milage may vary!

Update the VM

always a good idea to update to get latest security patches etc. And now that you can talk to the internet we can by typing:

yum update -y

Which fetches the latest packages and updates the system.

Set up ssh access

Things are going to be much easier if we can ssh into the machine. it’s a lot more convenient to use something like putty to interact with the vm - if only because we can cut and paste and also let other people have access.

Your Centos install probably comes with openssh preinstalled. If not you can install it with:

yum install openssh

Hopefully now you can talk to your vm with ssh on port 22. If you have set up the vm behind a firewall or network address translation you will probably need to expose some ports - check your products documentation for how to do this. For example for virtualbox you will want to forward port 22 of the client and then ssh into your new vm.

Example - my new vm is sitting behind a firewall which I’ve opened a port in. I ssh into my vm with ssh root@charliesvms -p 9004

which gives me:

SKYLAP627(/cygdrive/c/Program Files/ConEmu) $ ssh root@charliesvms -p 9004
The authenticity of host '[charliesvms]:9004 ([172.20.131.152]:9004)' can't be established.
RSA key fingerprint is 96:84:cd:ff:1d:06:fe:84:2b:e5:2f:4f:16:2f:db:84.
Are you sure you want to continue connecting (yes/no)?

say yes and enter your password. We are now in and in a much nicer environment than the vm console.

Initial Snapshot / Install VM Tools

This is a fantastic time to take a snapshot of the vm — if we screw anything up with the postgresql / postgis install we can just fall back to this snapshot.

Consult whichever vm product you are using to make a snapshot - I’m calling mine “Initial OS Install”

Its probably also a good idea to install the virtual tools that come with your vm product (vmware or virtualbox) to do that follow the instructions that come with your vm product,

On vmware what you do is ask your player to install the VmWare Tools - this will add an ISO for the tools to your client then follow this general chain of instructions (the name of the vmware tool package will likely be different for you.

mkdir /mnt/cdrom
mount /dev/cdrom /mnt/cdrom
cp /mnt/cdrom/VMwareTools-9.0.5-1065307.tar.gz ~
cd ~
tar -xvf VMwareTools-9.0.5-1065307.tar.gz
cd vmware-tools-distrib

At this point we realise that we are going to have to install perl and the kernel headers and a compiler - if you are using a more complete distro than the centos minimal install you may be able to skip this:

yum install -y gcc kernel-devel perl

And finally we can build the vmware tools:

perl vmware-install.pl --default

and we are done - time to take another snapshot!

 

Postgresql Install

We are going to modiy the repositories available on the system to include the latest postgresql standard repos.

before we begin if you are not comfortable with vi then I suggest installing something like nano. And if you do like vi then I recommend installing vim which comes with colour highlighting and is generally nicer

    yum install nano vim

Edit Existing Yum Repos

open the file base repo file:

vim /etc/yum.repos.d/CentOS-Base.repo

we need to exclude postgresql reference from base (so that when we ask yum to install postgresql it isn’t going to find the older base repository version

append the following line to both the [base] and [updates] sections

exclude=postgresql*

you should end up with something like:

# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
.....

Install Official Postgresql Base Repo

We now need to install the right PGDM RMP (PostgreSQL Global Development Group RPM) package for our OS and architecture. Go to http://yum.postgresql.org and find the right package.

before we do lets set up a folder to put rpm packages in and go there so we aren’t going to drop rpms in other parts of the file system…..

mkdir ~/rpms
cd ~/rpms

As of 2013-08-15 the latest postgresql release is 9.2 and my vm is a 64bit centos 6 vm. therefore the package I want is: CentOS6-x86_64

Download this file using curl (or wget) (wget isn’t installed by default but it is very useful) yum install wget)
 

yum install wget
wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

And then install the distribution package - this simply sets up the postgresql repository for us to later go get postgresql using yum.

rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

you should get output similar to:

warning: pgdg-centos92-9.2-6.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
        Preparing... ########################################### [100%]
1:pgdg-centos92 ########################################### [100%]

Check that things are looking good by listing the available postgresql packages

yum list postgres*

and you should see your chosen version packages…

Install PostGIS Required Repos

To install the PostGIS extensions (which are included in the base postgresql repo we installed above we are going to need to install some additional repositories for GDAL. To do this we are going to install the EPEL 6 which is a global repository for extra Packages not included by the default centos repos. (this is one reason why doing anything on Ubuntu is simply easier)

Go here to get a download url:

http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/6/x86_64/repoview/epel-release.html

and copy the link location for the package presented (called something like epel-release-6-8.noarch), For me the link is: http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm. However this will change as new releases are performed.

Get the repo:

wget http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

And install it in the same way as above.

rpm -ivh epel-release-6-8.noarch.rpm

Again you’ll get output like:

[root@postgis rpms]# rpm -ivh epel-release-6-8.noarch.rpm
warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]

now would be another excellent time to take a snapshot

 

Install postgresql-server & postgis exensions

Now that we’ve done the hard work of setting up the repos installing the basic postgresql server, postgresql contrib package and postgis packages is very easy.

yum install postgresql92-server postgis2_92 postgresql92-contrib

this will install a huge amount of stuff. Including all the GDAL libraries.

You may get a warning about install a new signature key from the EPEL (extras) package. If you do simply answer Y when prompted (like below)..

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
Importing GPG key 0x0608B895:
 Userid : EPEL (6) <epel@fedoraproject.org>
 Package: epel-release-6-8.noarch (installed)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
 Is this ok [y/N]: y

We need to initialise the service database and configuration files (like master / msdb in sql server). This only needs to be done one.

service postgresql-9.2 initdb

The db’s live in (/var/lib/pgsql/<version>/data/) by default so you can check by listing that directory

[root@postgis rpms]# ls /var/lib/pgsql/9.2/data/
base     pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc    pg_xlog
global   pg_ident.conf  pg_notify     pg_stat_tmp   pg_twophase  postgresql.conf
pg_clog  pg_log         pg_serial     pg_subtrans   PG_VERSION

We are going to be modyfying the pg_hba.conf file later (which is hideous!) so its nice to see it there.

We want postgresql to startup automatically. so we ask chkconfig to do that.

chkconfig postgresql-9.2 on

And turn on postgresql

service postgresql-9.2 start

Postgresql and PostGIS configuration

By default postgresql installs in a local host only configuration. We want to be able to connect to the database remotely. To do this we are going to have to do a few things

Turn off Software Firewall

We don’t need the software firewall — it gets in the way for tons of things and configuring it to forward ports is beyond the scope of this document! So lets turn it off:

chkconfig iptables off
chkconfig ip6tables off
service iptables stop
service ip6tables stop

Response:

[root@postgis rpms]# service iptables stop
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]
[root@postgis rpms]# service ip6tables stop
ip6tables: Flushing firewall rules:                        [  OK  ]
ip6tables: Setting chains to policy ACCEPT: filter         [  OK  ]
ip6tables: Unloading modules:                              [  OK  ]

Postgres Access Configuration

Before we go editing any system files lets change to become the postgres user that was installed with postgresql-9.2_server. This special user has control only over the postgres conf files and access to any postgresql database.

sudo -iu postgres

Edit the pg_hba.conf file

vim /var/lib/pgsql/9.2/data/pg_hba.conf

You can break things here so be careful! Most of the file is explanation and worth a read. Add a blanket accept policy for any network connection secured by a username and password pair.

Add the following lines

#Our Internal Network
host    all    all    0.0.0.0/0    md5

which will allow connections from any network (0.0.0.0/0) as long as the client can pass a user name and a MD5 hashed password pair. We will also want to run local command line tools (osm2pgsql) so we need to modify the local access as well. Change the peer and ident access policies for “local”, IPv4 local and IPv6 local to trust

You should end up with a file that looks like this:

....
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
#Our Internal Network
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
....

This is probably not the most secure way to do this but we aren’t going to be exposing this machine to the outside world — only within our network. We also need to allow connections over TCP/IP in a different configuration file. (postgresql.conf)

Edit the postgresql.conf file…carefully!

vim /var/lib/pgsql/9.2/data/postgresql.conf

Change these two lines in the CONFIGURATIONS AND AUTHENITCATION section:

#listen_addresses = 'localhost'
#port = 5432

to:

listen_addresses = '*'
port = 5432

or whatever port you prefer (5432 is the standard port)

Save the file!

We need to restart postgres after this change. To do that we need to become root again. Assuming we became the postgres user via sudo -iu postgres then we simply need to issue:

exit

and we’ll be root again. Restart the service.

service postgresql-9.2 restart

if you’ve screwed up the conf files, this is when you will see errors. If you get the following then you are probably fine.

[root@postgis rpms]# service postgresql-9.2 restart
Stopping postgresql-9.2 service:   [  OK  ]
Starting postgresql-9.2 service:   [  OK  ]

if we are going to be using the postgres user to connect to the database remotely then we will need to give the postgres user a password. The password is seperate from the postgres linux account so we need to connect to the postgresql server first. We can do that locally (via ssh console) by becoming the linux postgres user and using psql (the interactive postgresql shell)

sudo -iu postgres
psql

This puts us into the command line interactive sql shell connected to the local postgresql instance. Issue the following command - the semi-colon is not optional. (replace <PASSWORD> with your password.

ALTER USER Postgres WITH PASSWORD '<PASSWORD>';

and exit.

\q

we should now be able to connect to the database server with pgadmin or another tool. The username and password pair will be postgres / PASSWORD (where password is the password you set above)

Now would be another excellent time to snapshot

Creating a Database with PostGIS and Hstore Extensions

Making a database with the postgis and hstore extensions is a lot easier than it used to be. In the past you needed to modify files and run sql scripts. Now you should be able to simply install new extensions using pgAdmin. PostGIS is a collection of tools for interacting with geo data and hstore is an key/value data type extension.

In PGADMIN connect to the postgres server you have set up.

To Create a new database

1.     Right Click on Databases
2.         …new Database
3.         Give your database a name

To Install postgis extensions

1.     Right click on your new database
2.         New Object…
3.             New Extension
4.             type postgis in the name field

this will take a bit of time to build all the gis extensions for your database.

To Install the Hstore extension

Just like postgis the hstore extension is now easy to install. hstore is inthe postgresql92-contrib package so that needs to be installed.

1.     Right click on your new database
2.         New Object…
3.             New Extension
4.             type hstore in the name field

And you should be good to go.

if you see the following message:

A error has occurred:

ERROR: could not open extension control file "/user/pgsql-9.2/share/extension/hstore.control": No such file or directory`

Then you have forgotten to install the postgresql-contrib extensions.

in the ssh terminal type

yum install postgres92-contrib -y

Now we have a postgresql database with all the postgis and hstore extensions!

TAKE A SNAPSHOT!

References

    [1] Yum Installation for Postgres
    [2] PostGIS and Postgres on Centos 6
    [3] postgres allow remote access tcp Connection
    [4] ALTER USER syntax