Migration on non-public schema in postgres fails silently

[Nikola-Milovic]

Using postgres, it seems that the only schema supported is public which is weird that it doesn't seem to be overriden by the seach_path property.

Here's output when using the following DNS config

DATABASE_URL: postgres://${PG_USER}:${PG_USER}@${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable

 ✔ Container seolitic-clickhouse-1  Running                                                                             0.0s
Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
create Plausible.IngestRepo database if it doesn't exist
Creation of Db successful!
Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
Running migrations for Elixir.Plausible.IngestRepo
Migrations successful!

But if I use

DATABASE_URL: postgres://${PG_USER}:${PG_USER}@${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable&search_path=plausible

It just fails silently

Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
** (EXIT from #PID<0.95.0>) shutdown

Any way to debug this, or is a custom schema not supported at the moment?

I know that plausible is using v14 of postgres, but I am trying to use v16, which seems to work fine for now. But there has been a change in v15 and onwards where the public schema usage has been discouraged, so I'm dropping the usage of it, and this is the only blocker.

Full compose service definition

  plausible-migrate:
    image: plausible/analytics:v2.0
    restart: always
    command: sh -c "/entrypoint.sh db createdb && /entrypoint.sh db migrate"
    environment:
       BASE_URL: http://localhost:8000
       SECRET_KEY_BASE: ${ANALYTICS_SECRET_KEY_BASE}
       DATABASE_URL: postgres://${PG_USER}:${PG_USER}@${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable&search_path=plausible
       CLICKHOUSE_DATABASE_URL: http://clickhouse:8123/plausible
    depends_on:
      - clickhouse
      - postgres
    profiles:
      - migration
    networks:
      - platform

The user used is a superuser, so permissions shouldn't be a problem. Also tried lowering the log level to debug but it provides no additional info

[ruslandoga]

I think the search_path in the URL is not quite the expected way to provide a custom schema in Ecto. I'll need to check if Ecto supports arbitrary options in URLs.

…

On Tue, 20 Feb 2024, 15:41 Nikola Milovic, ***@***.***> wrote: Using postgres, it seems that the only schema supported is public which is weird that it doesn't seem to be overriden by the seach_path property. Here's output when using the following DNS config DATABASE_URL: postgres://${PG_USER}:${PG_USER}@ ${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable ✔ Container seolitic-clickhouse-1 Running 0.0s Loading plausible.. Starting dependencies.. Starting repos.. create Plausible.Repo database if it doesn't exist create Plausible.IngestRepo database if it doesn't exist Creation of Db successful! Loading plausible.. Starting dependencies.. Starting repos.. Running migrations for Elixir.Plausible.Repo Running migrations for Elixir.Plausible.IngestRepo Migrations successful! But if I use DATABASE_URL: postgres://${PG_USER}:${PG_USER}@ ${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable&search_path=plausible It just fails silently Loading plausible.. Starting dependencies.. Starting repos.. create Plausible.Repo database if it doesn't exist ** (EXIT from #PID<0.95.0>) shutdown Any way to debug this, or is a custom schema not supported at the moment? I know that plausible is using v14 of postgres, I tried to upgrade to v16. But v15 and onwards the public schema usage has been discouraged via the new changes, so it makes sense not to use it. Full compose service definition plausible-migrate: image: plausible/analytics:v2.0 restart: always command: sh -c "/entrypoint.sh db createdb && /entrypoint.sh db migrate" environment: BASE_URL: http://localhost:8000 SECRET_KEY_BASE: ${ANALYTICS_SECRET_KEY_BASE} DATABASE_URL: postgres://${PG_USER}:${PG_USER}@${PG_HOST}:${PG_PORT}/${PG_DB}?sslmode=disable&search_path=plausible CLICKHOUSE_DATABASE_URL: http://clickhouse:8123/plausible depends_on: - clickhouse - postgres profiles: - migration networks: - platform — Reply to this email directly, view it on GitHub <#3799>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQFABUFAO3YCOQPOPC7SBQTYURHTVAVCNFSM6AAAAABDQUJRNCVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZWGI2DGMBQGE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[ruslandoga]

There is a workaround though. You can mount your own runtime.exs into the container which would set that callback.

$ docker compose exec plausible ls /app/releases/0.0.1/
consolidated        iex                 runtime.exs         start_clean.boot    vm.args
elixir              plausible.rel       start.boot          start_clean.script
env.sh              remote.vm.args      start.script        sys.config
$ docker compose exec plausible cat /app/releases/0.0.1/runtime.exs > runtime.exs
$ ls
README.md  clickhouse  docker-compose.yml  plausible-conf.env  reverse-proxy  runtime.exs  upgrade
$ vim runtime.exs

Add something like

if database_schema = System.get_env("DATABASE_SCHEMA") do
  config :plausible, Plausible.Repo,
    after_connect: {Postgrex, :query!, ["SET search_path TO #{database_schema}", []]}
end

at the very bottom and mount this modified config script into the container at /app/releases/0.0.1/runtime.exs

[Nikola-Milovic]

You are a life saver 🙏

Could you please mention this feature request somewhere, or should I create an issue? So something like this could be supported natively by plausible?

[ruslandoga]

Oops, somehow that missed me. I'll open a PR now and it would probably get into the next release candidate for v2.1.0.

[ruslandoga]

#3823

[Nikola-Milovic]

If it makes it into 2.1 it would be ideal. Fingers crossed