Install PostgreSQL 9.2 + PostGIS 2 on Centos 6.7

Install the wget utility and the nano text editor

[[email protected] ~]# yum install wget nano

Installing PostgreSQL

Add the repository

[[email protected] ~]# wget http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-7.noarch.rpm
[[email protected] ~]# rpm -ivh pgdg-centos92-9.2-7.noarch.rpm

Editing the configuration file of the CentOS-Base.repo repository in order to install PostgreSQL from the added repository in the future

[[email protected] ~]# nano /etc/yum.repos.d/CentOS-Base.repo
[base]
exclude=postgresql*

[updates]
exclude=postgresql*

Install PostgreSQL 9.2, initialize it and add it to startup

[[email protected] ~]# yum install postgresql92 postgresql92-server
[[email protected] ~]# service postgresql-9.2 initdb
[[email protected] ~]# chkconfig postgresql-9.2 on

Edit the pg_hba.conf file and change the identification type to ident (identification by the allowed username)

[[email protected] ~]# nano /var/lib/pgsql/9.2/data/pg_hba.conf
local all all ident

Log in as user postgres and launch psql shell

[[email protected] ~]# su postgres
bash-4.1$ psql

Set the postgres user to a password

postgres=# ALTER ROLE postgres WITH PASSWORD 'postgres';

We leave

postgres=# q
bash-4.1$ exit

Let’s edit the pg_hba.conf file again and change the identification type to md5. We also add our subnet so that we can connect to PostgreSQL through the PgAdmin client

[[email protected] ~]# nano /var/lib/pgsql/9.2/data/pg_hba.conf
local all all md5
host all all 127.0.0.1/32 md5
host all all 192.168.1.1/24 md5

Editing postgresql.conf

[[email protected] ~]# nano /var/lib/pgsql/9.2/data/postgresql.conf
listen_addresses="*"

Without this change, remotely connecting to PostgreSQL will fail.

We restart the service

[[email protected] ~]# service postgresql-9.2 restart

Editing the firewall rules, opening the standard PostgreSQL port. Restart iptables

[[email protected] ~]# nano /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

[[email protected] ~]# service iptables restart

Add EPEL repository so that dependencies are tightened during PostGIS installation

[[email protected] ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Installing PostGIS

Install PostGIS and additional extensions

[[email protected] ~]# yum install postgis2_92 postgresql92-contrib

Create a new database

[[email protected] ~]# su - postgres
-bash-4.1$ createdb postgis
-bash-4.1$ psql postgis

Install additional extensions and check

postgis=# CREATE EXTENSION postgis;
postgis=# SELECT PostGIS_full_version();

Adding topology support and installing additional extensions

postgis=# CREATE EXTENSION postgis_topology;
postgis=# CREATE EXTENSION fuzzystrmatch;
postgis=# CREATE EXTENSION postgis_tiger_geocoder;