When using non-public schema for Postgres, "goose_db_version" table cannot be found

[jamesleeht]

The first migration works fine, as the search path set on my user will cause the goose_db_version table to be created in my custom schema.

However, when running the second migration, this will cause the goose_db_version to not be found.

ERROR: relation "goose_db_version" already exists (SQLSTATE 42P07)

Related issues:
#800

Related code:
https://github.com/pressly/goose/blob/master/migrate.go#L216
https://github.com/pressly/goose/blob/master/internal/dialect/dialectquery/postgres.go#L60-L66

According to the code above, it will automatically assign the public schema for the list migrations table. How do we configure goose to read the goose version table from another schema?

There is another issue listing a potential solution:
#205

However, setting the search path in the connection string does not seem to fix this issue.

[mfridman]

The code you linked only applies to the goose provider, which is not yet hooked up to the CLI.

If you're only using the CLI, you could override the goose table to something like myschema.goose_db_version. E.g.,

goose -table="myschema.goose_db_version"

By default, goose assumes the public schema.

[jamesleeht]

Ah that works! Thanks for the info @mfridman.

[mfridman]

We should probably update both the README and add a section to the docs to clarify. Thanks for flagging.