Running local FROST-Server (via docker-compose) does not work with 'PostgresPersistenceManagerString'

[DLade]

Using the PostgresPersistenceManagerString (instead of default PostgresPersistenceManagerLong) in the slightly modified original docker-compose.yaml results in the error function uuid_generate_v1mc() does not exist and therefore every request is broken then.

note: Even if something else appears (Migration failed ...) I started with a completely empty database using the newest images (pulled today).

Parts of 'docker-compose.yaml':

services:
  web:
    image: fraunhoferiosb/frost-server:latest
    environment:
      - ...
      - persistence_persistenceManagerImplementationClass=de.fraunhofer.iosb.ilt.sta.persistence.pgjooq.imp.PostgresPersistenceManagerString

Error:

web_1       | Migration failed for change set tablesString.xml::20171122-datastreams::scf:
web_1       |      Reason: liquibase.exception.DatabaseException: ERROR: function uuid_generate_v1mc() does not exist
web_1       |   Hint: No function matches the given name and argument types. You might need to add explicit type casts. [Failed SQL: (0) CREATE TABLE "public"."DATASTREAMS" ("ID" VARCHAR(36) DEFAULT uuid_generate_v1mc() NOT NULL, "NAME" TEXT, "DESCRIPTION" TEXT, "OBSERVATION_TYPE" TEXT, "PHENOMENON_TIME_START" TIMESTAMP WITH TIME ZONE, "PHENOMENON_TIME_END" TIMESTAMP WITH TIME ZONE, "RESULT_TIME_START" TIMESTAMP WITH TIME ZONE, "RESULT_TIME_END" TIMESTAMP WITH TIME ZONE, "SENSOR_ID" VARCHAR(36) NOT NULL, "OBS_PROPERTY_ID" VARCHAR(36) NOT NULL, "THING_ID" VARCHAR(36) NOT NULL, "UNIT_NAME" VARCHAR(255), "UNIT_SYMBOL" VARCHAR(255), "UNIT_DEFINITION" VARCHAR(255), "OBSERVED_AREA" GEOMETRY, CONSTRAINT "DATASTREAMS_PKEY" PRIMARY KEY ("ID"))]

[DLade]

This article github - membership.db/issues/7 suggests using of CREATE EXTENSION "uuid-ossp"; at the database first.

I tried it manually and it seams to work. After restarting (via 'docker-compose') the exceptions are gone and my tests are back to green.

docker ps
docker exec -it <ContainerId> bash
psql -p 5432 -U mydatabase
CREATE EXTENSION "uuid-ossp";

[hylkevds]

Yes, we should probably explicitly mention right on the setting page that you need to run that command when using the String and UUID persistence managers. Currently it's only documented in the PostgreSQL Setup page.

[DLade]

This would be correct, if I would use an own Postgres-DB but I didn't install one. I use the docker-compose.yaml given here: 'Docker deployment' (see first comment too).

I wonder why it is not just part of the Liquibase scripts.

[hylkevds]

One of the considerations was that the database user may not have the rights to install extensions. In the docker situation the user has the needed rights, but in a non-docker situation the user may not. Though I suppose that would just cause an error, like we get now too :)

[DLade]

I see the problem. So the solution must be part of docker-compose itself.

like: https://stackoverflow.com/questions/47615751/docker-compose-run-a-script-after-container-has-started https://docs.docker.com/compose/reference/run/

[DLade]

It would be nice if you deliver a fixed "docker-compose.yaml".

It might be a rare case but developers/customers who want to use strings as Id's might feel helpless about it.

[hylkevds]

If you have one, I can add it to the repository.

[ksonda]

@DLade did you ever figure this out? I'm working on the exact same thing. Its easy enough to document but I'm working with some clients that need it to be a little more plug-and-play. If you haven't I'll work on it in the next month or so.

[ksonda]

uuid-ossp seems to represent a particular problem. I have tried building my own postgis image, modifying the intialization db script to include creating the extension and it kills the container on startup. For the use case avoiding developers/customers who want to use strings as Id's might feel helpless about it., I think more explicit instructions are required. To make it somewhat more friendly, could add adminer or another lightweight db admin gui to help users add extensions without too much CLI.

[ksonda]

@hylkevds , kartoza's postgis image includes extension installation via environmental variables, so this docker-compose.yml works, if that's of interest. Not sure your policies on alternative DB images though.

version: '3.7'

services:
    web:
        container_name: frost
        image: fraunhoferiosb/frost-server:latest
        environment:
            - serviceRootUrl=http://localhost:8080/FROST-Server #change to desired URL
            - http_cors_enable=true
            - http_cors_allowed_origins=*
            - persistence_db_driver=org.postgresql.Driver
            - persistence_db_url=jdbc:postgresql://database:5432/sensorthings
            - persistence_db_username=sensorthings
            - persistence_db_password=ChangeMe
            - persistence_autoUpdateDatabase=true
            - persistence_persistenceManagerImplementationClass=de.fraunhofer.iosb.ilt.sta.persistence.pgjooq.imp.PostgresPersistenceManagerString
            - persistence_idGenerationMode=ServerAndClientGenerated
        ports:
            - 8080:8080
            - 1883:1883
        depends_on:
            - database

    database:
        container_name: frostdb
        image: kartoza/postgis:14-3.2
        environment:
            - POSTGRES_DB=sensorthings
            - POSTGRES_USER=sensorthings
            - POSTGRES_PASS=ChangeMe
            - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,postgis_raster,pgrouting,uuid-ossp
        volumes:
            - postgis_volume:/var/lib/postgresql
volumes:
    postgis_volume:

[hylkevds]

Great find! The official PostGis Image (postgis/postgis) has the option to run initialization scripts Not quite as convenient as the POSTGRES_MULTIPLE_EXTENSIONS environment variable of kartoza though!