Postgres with postgis

Running create extension if not exists postgis; on fly postgres gives me:

[58P01] FatalError: could not open extension control file "/usr/share/postgresql/12/extension/postgis.control": No such file or directory

Any chance to get support for this? It’s the only blocker we have to move everything over.

Edit: I suppose one wrinkle is versioning. We’re using 2.5 as a requirement from another library, so being able to specify that would be important.

It’s possible a really generic solution would be to allow a generic install script that can be run post-bootstrap of the postgres instances, which would allow all sorts of minor customization at least until a more standardized route was created.

For reference, I was able to do this by ssh’ing into both instances, running apt install postgis postgresql-12-postgis-2.5. Pretty sure this won’t last across next update, but works for now.

Oh good question. This is one of the reasons we made our “postgres application” open source, you can actually clone the repo, add the extension, adjust fly.toml, and deploy with flyctl deploy -a <your-postgres-cluster-name>.

We’ll even accept pull requests!

1 Like

Excellent, I think a quick notice in docs linking to that repo would have pointed me there, didn’t realize it was 100% standard app I think due to the fact it has its own cli area.

I ended up hitting some snags getting this running so I thought I would write down what worked for me to get postgis working.

One note: if you go with the default VM size of shared-cpu-1x - 256, running CREATE EXTENSION postgis causes the db to run out of memory and you see this as a network disconnect. I spent ages thinking the issue was something more sinister!

  1. flyctl postgres create
  2. if you’re using shared-cpu-1x - 256, bump the memory: flyctl scale memory 1024
  3. Clone the postgres-ha repo
  4. Edit the toml.yaml file to use your app name
  5. Modify the Dockerfile to add postgis extensions (example Dockerfile)
  6. Run flyctl deploy
3 Likes