Categories
Tutorial

Deploy Postgresql 9.1 and PostGis 2.1 on Cent OS 7 Server

What’s PostGIS ?

PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium.

There are a lot of software products that can use PostGIS as a database backend, such as ArcGIS, QGIS, CartoDB, MapInfo, TerraLib…

For example, it’s used by OpenStreetMap, a collaborative project that create a free editable map of the world.

1) Install CentOS 7

First, we will start by downloading the last ISO image of Cent OS 7 on the official website.

We can choose the “Minimal ISO”, which contains every necessary things for a server, but nothing more.

Then, we can begin the installation process, typically a VMs with 2 vCores, 2 GB of memory and 40 Gb Hard Disk, which is enough for the moment.

centos-p-1

Installation wizard is graphical, no particular skills required:

centos-p-2

Don’t forget to set the root password!

centos-p-3

After a reboot, we should enable the Ethernet interface (which is disabled by default) and set IP parameters.

So we could connect as “root” in a terminal, and use the NetworkManager by running the command “nmtui” :

centos-p-4

centos-p-5

centos-p-6

Now we can go the next step!

2) Installation de PostgreSQL/PostGIS

PostgreSQL is available on Yum Default Repository for CentOS 7, but you cannot choose the version.

So, we need to add the “PostgreSQL 9.3” repository:

[root@vm-centos1 ~]# rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-2.noarch.rpm

And then, you can run the installation:

[root@ vm-centos1 ~]# yum install postgresql93 postgresql93-server postgresql93-libs postgresql93-contrib postgresql93-devel

Then, we could install PostGIS, but PostGIS required dependences which are not available in the Default or PostgreSQL Repository, we need to add the EPEL .repo :

[root@ vm-centos1 ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm

And now, we can run the installation:

[root@ vm-centos1 ~]# yum install postgis2_93

3) Configuration

We need to initialize the database in PGDATA (run once)

[root@ vm-centos1 ~]# /usr/pgsql-9.3/bin/postgresql93-setup initdb
Initializing database ... OK

If you want PostgreSQL to start automatically when the OS starts, you can run:

[root@ vm-centos1 ~]# systemctl enable postgresql-9.3
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.3.service to /usr/lib/systemd/system/postgresql-9.3.service.

If you want connect to the database from another PC, you must edit this 2 configuration files:

[root@ vm-centos1 ~]# vi /var/lib/pgsql/9.3/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
g
[root@ vm-centos1 ~]# vi /var/lib/pgsql/9.3/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host    all             all             all                     md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

Then, we can start PostgreSQL service:

[root@ vm-centos1 ~]# systemctl start postgresql-9.3

4) Créer une base de test

Finally, we are going to create a test database.

We need to switch as “postgres” user:

[root@localhost ~]# su postgres
bash-4.2$

And then, create a user and a database:

[root@localhost /]# su postgres
bash-4.2$ createuser --pwprompt --encrypted gisuser
Enter password for new role:
Enter it again:
bash-4.2$ createdb --encoding=UTF8 --owner=gisuser gis.test
bash-4.2$ psql -d gis.test -f /usr/pgsql-9.3/share/contrib/postgis-2.1/postgis.sql
bash-4.2$ exit

Now, you can test the connection in the console:

centos-p-7

Or with graphical tools, such as “PgAdmin” :

centos-p-8

That’s All!