Postgres DB Schema not referenced when accessing web url but craft cmd works fine?

Question

I'm having a strange issue when accessing a craft instance via the web yet the craft cmd works fine. When resolving the URL it's throwing a 503 and the logs are showing an issue with fetching data from the info table, stating the relation doesn't exit, it seems the db schema env var isn't being picked up/or being used?

Error is:

2022-12-16 18:21:56 [web.INFO] [yii\db\Connection::open] Opening DB connection: pgsql:host=xxx;dbname=xxx;port=5432 {"memory":2025488} 
2022-12-16 18:21:56 [web.ERROR] [craft\base\ApplicationTrait::getIsInstalled] There was a problem fetching the info row: SQLSTATE[42P01]: Undefined table: 7 ERR
OR:  relation "info" does not exist
LINE 2: FROM "info"
             ^
The SQL being executed was: SELECT *
FROM "info"
WHERE "id"=1
LIMIT 1 {"memory":2313616}

The env vars are all in place:

# printenv | grep DB_
DB_PASSWORD=xxx
DB_PORT=5432
DB_USER=xxx
DB_SCHEMA=xxx
DB_SERVER=xxx
DB_DRIVER=pgsql
DB_DATABASE=xxx

And running the install/check cmd reports craft is installed fine.

# ./craft install/check
Craft is installed.

Any ideas? Any help appreciated!

Answer 1

please have a look at this Q&A I posted some time ago: Why do Postgres schemas different from "public" sometimes not work with Craft (DDEV / Nitro)

This will very likely explain the behavior you describe and the know-how is essential when working with postgres.

Answer 2

Seems I've sorted it by restoring the pg dump to the default public schema instead of a custom one. Something wasn't referencing the schema when exec'ing queries - even though the db connection was fine (within php-fpm) and from ./craft cmd, both referencing the same DB_ env vars. Didn't dig much deeper but attempted an install using the craft cmd which got so far then a plugin ran into the same issue above, need to see exactly where/why it's happening but that's one to inspect at a later date.