Installing PostGIS on Uberspace 7๐ŸŒŽ

Tue, Aug 18, 2020 4-minute read

I recently started working again on my previous flight search database project. After a couple of years of trial and error I finally had a PostgreSQL based development version with a Django REST API that was ready to go online. As the flight search is heavily making use of GIS features such as storing airports as Points, calculating the routes distances using GreatCircleMapper and many more, a PostGIS setup was the obvious choice. I used it for example to save the locations of my airports as Points and display them using leaflet.js in the Django Admin backend.

“Leaflet.js Vancouver Airport Django Admin”

A full-blown PostGIS stack exists out of a couple of different components.

At a bare minimum these are:

  • PostgreSQL
  • GEOS
  • PROJ
  • GDAL
  • PostGIS

In the following paragraph I will show you how those can be installed on Uberspace. The same techniques can also be applied in a similar fashion to other VPS.

A word of caution before we start. Even though, the requirements are fairly little, be warned that setting everything up takes a lot of time, as everything has to be compiled from scratch on a shared server. This is definitely not a plug ‘n play solution.

Install PostgreSQL

At first we need to install the latest version of PostgreSQL (in my case version 12.4) on our Uberspace server. We can do this with following commands. You should execute them one row at a time.

mkdir ~/postgres/
cd ~/postgres/
curl -O https://ftp.postgresql.org/pub/source/v12.4/postgresql-12.4.tar.gz
tar -xvzf ~/postgres/postgresql-12.4.tar.gz
cd postgresql-12.4/
./configure --prefix=$HOME/opt/postgresql/ --with-python PYTHON=/usr/bin/python3 --without-readline
make world
make install-world

Environment Settings

Please add the following lines to your ~/._profile using nano or vi:

export PATH=$HOME/opt/postgresql/bin/:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/opt/postgresql/lib
export PGPASSFILE=$HOME/.pgpass

Reload the ._profile with:

source ~/._profile

Run psql --version to verify the installation so far:

psql --version
psql (PostgreSQL) 12.4

Create a database cluster

nano ~/.pgpass

The syntax of the .pgpass file is the following:

#hostname:port:database:username:password (min 64 characters)
*:*:*:<uberspace username>:<password with 64 characters>

Change the permissions of the .pgpass file:

chmod 0600 ~/.pgpass

To use the pure password for the database cluster creation, create a temporary password file ~/.pgpass.temp, containing only your password:

nano ~/.pgpass.temp
<password with 64 characters>

Now create the database cluster using the previous temporary .pgpass.temp:

initdb --pwfile ~/.pgpass.temp --auth=md5 -E UTF8 -D ~/opt/postgresql/data/

Delete the temporary password file:

rm ~/.pgpass.temp

Configure the Unix Socket

Edit your ~/.rc using nano and add the following content:

export PGHOST=localhost
export PGPORT=5432

And reload the .rc settings:

source ~/.rc

PostgreSQL Configuration

Edit ~/opt/postgresql/data/postgresql.conf using nano and set the listen_adresses, port and unix_socket_directories:

Change the listen_addressto every IP by setting it to ‘*’ , or a specific IP(s). Either way, make sure this section is uncommented:


listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)


Uncomment the portin the config file. It should look like this:

port = 5432 

Change the unix_socket_directories to your tmp user directory. Also make sure it’s uncommented:

unix_socket_directories = '/home/<uberspace username>/tmp' 

Setup Daemon

Create a blank ~/etc/services.d/postgresql.ini using touch:

touch ~/etc/services.d/postgresql.ini

Add the following content using nano:

[program:postgresql]
command=%(ENV_HOME)s/opt/postgresql/bin/postgres -D %(ENV_HOME)s/opt/postgresql/data/
autostart=yes
autorestart=yes

After creating the configuration, tell supervisord to refresh its configuration and start the service. The status should be Running:

supervisorctl reread
supervisorctl update
supervisorctl status

Install GEOS

Change to your home directory, create a new directory and change into there:

cd
mkdir geos
cd geos/

Download the latest version of GEOS:

curl -O http://download.osgeo.org/geos/geos-3.8.1.tar.bz2

Unzip the tar file:

tar -xvf geos-3.8.1.tar.bz2

Configure, make and install GEOS:

cd geos-3.8.1/
./configure --prefix=$HOME/opt/geos/build --enable-python
make
make install
cd

Check the version:

geos-config --version
3.8.1

Install PROJ

Change to your home directory, create a new directory and change into there:

cd
mkdir proj
cd proj/

Download the latest version of PROJ:

curl -O http://download.osgeo.org/proj/proj-7.1.0RC2.tar.gz

Unzip the tar file:

tar -xvf proj-7.1.0RC2.tar.gz

Configure, make and install PROJ.4:

cd proj-7.1.0/
./configure --prefix=$HOME/opt/proj
make
make install

Install gdal

Change to your home directory, create a new directory and change into there:

cd
mkdir gdal
cd gdal/

Download the latest version of gdal:

curl -O http://download.osgeo.org/gdal/3.1.2/gdal-3.1.2.tar.gz

Unzip the tar file:

tar -xvf gdal-3.1.2.tar.gz

Configure, make and install gdal:

cd gdal-3.1.2/
export PKG_CONFIG_PATH=/home/<uberspace username>/opt/postgresql/lib/pkgconfig
./configure --prefix=$HOME/opt/gdal --with-proj=$HOME/opt/proj --with-pg=yes -with-geosconfig=$HOME/opt/geos --with-python=python3
make
make install

Check the version:

gdalinfo --version

Install PostGIS

Before we progress, take a second to check your installation:

cd
ls opt/

The listing should look similar to this:

gdal  geos  postgresql  proj

Change to your home directory, create a new directory and change into there:

cd
mkdir postgis
cd postgis/

Download the latest version of PostGIS:

curl -O http://download.osgeo.org/postgis/source/postgis-3.0.2.tar.gz

Unzip the tar file:

tar -xvf postgis-3.0.2.tar.gz

Configure, make and install postgis:

cd postgis-3.0.2/
./configure --with-projdir=$HOME/opt/proj --with-geosconfig=$HOME/opt/geos/build/bin/geos-config --with-gdalconfig=$HOME/opt/gdal/bin/gdal-config -with-pconfig=$HOME/opt/postgresql/
make
make install

Installing PostGIS in your Database

Create a new database user:

createuser <dbusername> -P

Create a new database:

createdb --encoding=UTF8 --owner=<dbusername> <databasename>

Connect to your database and enable the postgis extension:

psql <databasename> <cluster admin> -c "CREATE EXTENSION postgis;"

Housekeeping ๐Ÿงน

Remove any unnecessary files and folders:

rm -r geos/
rm -r proj/
rm -r gdal/