Configure PostgreSQL with PostGIS

2016-01-13    Filed under linux, Tags linux postgresql database

PostgreSQL installation

Archlinux see also this guide:

sudo pacman -S postgresql
sudo -u postgres initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service


sudo zypper in postgresql postgresql-server postgresql-contrib


sudo dnf install postgresql-server postgresql-contrib
sudo systemctl enable postgresql
sudo postgresql-setup initdb
sudo systemctl start postgresql


sudo apt-get install postgresql postgresql-client
sudo apt-get install postgis \
    postgresql-9.4-postgis \
    postgresql-9.4-postgis-scripts \

Create database and users

First create a postgresql user for your shell user account. The postgresql user will have superuser permissions:

sudo -u postgres createuser -s <myshellusername>

Now, from the shell, create a user that will be used for accessing your database, for example 'django' user. Choose a password:

createuser -P django

Then create a database owned by 'django' user:

createdb --encoding=UTF8 --owner=django <mydatabase>

An alternative way to create the database is by using the psql user with the PostgresSQL shell:

sudo -u postgres psql
    CREATE DATABASE <mydatabase> OWNER django ENCODING 'utf8';

Check local connection for this new user:

psql -d <mydatabase> -U django -W

If it doesn't work it might be necessary to edit /var/lib/pgsql/data/pg_hba.conf:

host    all     all      md5

To add PostGIS support to the database:

sudo pacman -S postgis
psql <mydatabase>
    mydatabase=# ALTER USER django WITH PASSWORD 'mypassword';
    mydatabase=# CREATE EXTENSION postgis;
    mydatabase=# CREATE EXTENSION postgis_topology;

To delete a database from shell account (be careful !):

dropdb <mydatabase>

To delete database from psql:

sudo -u postgres psql
    DROP DATABASE <mydatabase>;

You can connect to psql using shell account because you must specify a database to connect to and the you can't delete it:

psql -d <mydatabase>

Restore a backup

To restore a PSQL backup you must delete the database first, then create an empty one and read the backup data to this empty database.

dropdb <mydatabase>
cat mybackup.psql | psql <mydatabase>

Create a backup

To create a PSQL backup:

pg_dump <mydatabase>  > backup.psql

Virtual environment for Python

How to create a virtual environment with the necessary system development packages to be able to compile and install with pip the most usual python packages.

First install virtualenv and virtualenvwrapper:


sudo pacman -S python-virtualenvwrapper


sudo apt-get install python3-virtualenv virtualenvwrapper
sudo apt-get install build-essential python3.4-dev
source /usr/share/virtualenvwrapper/


sudo dnf install python-virtualenvwrapper python3-virtualenv
sudo dnf install rpm-build postgresql-devel
sudo dnf install libjpeg-turbo-devel zlib-devel


sudo zypper in postgresql-contrib
sudo zypper in python3-devel gcc
sudo zypper in zlib-devel libjpeg8-devel

Now configure virtualenvwrapper. Edit ~/.zshrc or ~/.basrc:

export WORKON_HOME=$HOME/.virtualenvs
source /usr/bin/
# For Debian or Ubuntu use this instead:
# source /usr/share/virtualenvwrapper/

To create a virtualenv:

mkvirtualenv -p /usr/bin/python3 <myvirtenv>
pip install --upgrade pip
pip install ipdb
pip install psycopg2

If you have a requirements file (list of packages to install):

pip install -r requrements.txt