Deploying postgis

Questions / Help
1

I noticed that postgis has been added to flyio/postgres and will supposedly be available in the v0.0.6 release.

I’m wondering when this will be available when using fly postgres create.

  • repo clone method doesn’t work (I can’t get it to successfully deploy)
  • nor does deploy with --image flyio/postgres:14 (although I think it gives me version v0.0.9?)

I was really hoping to deploy today. :disappointed_relieved:

Current image details
  Registry   = registry-1.docker.io
  Repository = flyio/postgres-standalone
  Tag        = 14.1
  Version    = v0.0.5
  Digest     = sha256:b59c6c33c1e019b8942b0992df60bb3fd52db4811ddefc93e1d0b9aac5d9c68e

$ fly image update
Error Image is already running the latest image.
2

I got postgis to deploy with a flag not included in fly help pg create, that was mentioned in a github issue:

fly pg create --image-ref flyio/postgres:14

However, now I get a permission error when my application tries to run the migrations:

15:36:06.676 [info] execute "CREATE EXTENSION IF NOT EXISTS postgis"
  ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "postgis"
	     hint: Must be superuser to create this extension.

So I needed to switch the DATABASE_URL that was set for me when running fly postgres attach.

3

OK, so how do I add PostGIS to Postgres? Do I just create fly pg create as normal and then enable a Postgres plugin? Thanks!

4

In our case, we used a standard postgres setup routine (fly pg create) and simply enabled PostGIS via migration. Ours is a Phoenix app so it looked like this:

defmodule Outandback.Repo.Migrations.EnablePostgis do
  use Ecto.Migration

  def up do
    execute "CREATE EXTENSION IF NOT EXISTS postgis;"

    alter table(:races) do
      add :coordinates, :geometry
    end
  end

  def down do
    execute "DROP EXTENSION IF EXISTS postgis;"

    alter table(:races) do
      remove :coordinates
    end
  end
end

And as previously stated, just make sure you have sufficient privileges for the user that runs the migrations. If you don’t use migrations, you could also use fly pg connect to enable it manually.