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="mdillon/postgis" export POSTGRES_IMAGE_VERSION="latest"
This will work since the mdillon/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.
But 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 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.
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.
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.
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.
- dokku-postgres: repo and documentation for the dokku-postgres plugin.
- docker-postgis: repo and documentation for the PostGIS docker image.