Postgis on Dokku. Old blog post, was placed in private repo, posting it here so that others can find it.

dokku-postgis.md

title	date	tags	layout	author	subtitle
Setting up PostGIS with Dokku	2017-09-22 00:00:00 UTC	GIS PostGIS Dokku	post	Johnnie Hård	Note to self on how to set up a PostGIS datbase on a Dokku host.

How to host PostGIS with Dokku. And how to work against the postgis datbase from the local machine with QGIS.

Assumes a functioning Dokku installation. Can be created on Digitalocean with their 1-click-apps.

Installing postgres plugin

First install the postgres dokku plugin. This is the base for the PostGIS setup.

sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

Create a PostGIS service

To use another image than the default postgres image when initializing the service, set these environment variables:

export POSTGRES_IMAGE="postgis/postgis" 
export POSTGRES_IMAGE_VERSION="latest"

This will work since the postgis/postgis image is made to be compatible with the default postgres image.

Now we can create our postgis service:

dokku postgres:create mypostgis

This will create and start the service, but since we don't yet have the image it will be pulled first. After that is done we can verify that it's running with dokku postgres:list which will list all postgres services.

Connect to service

To run commands on the database from the dokku host we can do dokku postgres:connect to connect with psql inside the docker container.

Do this and run

CREATE EXTENSION postgis;

To make sure postgis is activated.

Connect from localhost

I prefer working with the database from my local machine as if it was running locally. This allows me to view and style the data with QGIS.

To do that we will use port forwarding over ssh.

Expose port

Expose a port of our docker container with dokku postgres:expose mypostgis 5432.

This will expose the port from the docker container internally on the host machine.

Port forward

Set up port forwarding to the to the remote with

ssh -L 5432:localhost:5432 user:yourdokkuhost

This will make localhost:5432 on your local machine point to our postgres service on the remote host. Trippy.

Postgres password

To get the postgres password run dokku postgres:info mypostgis. The Dsn entry will have the postgres user password postgres://user:password@host:port. It will be a long random string. You will need this later to authenticate against the database.

Upload/view data

Now you can upload and view you data just as if the postgres instance was running on your local machine.

Link database to other apps

The Postgres service can be linked with other applications on the Dokku host with dokku postgres:link mypostgis <app>. This will set the environment variable DATABASE_URL=postgres://postgres:SOME_PASSWORD@dokku-postgres-lolipop:5432/mypostgis which can be used by your server side applications for accessing the database.

Links

• dokku-postgres: repo and documentation for the dokku-postgres plugin.
• docker-postgis: repo and documentation for the PostGIS docker image.