<p>To understand why this works on <em>production</em> but not <em>locally</em> one must understand schemas and Postgres <code>search_path</code></p>
<ol>
<li>Schemas are like an additional layer in Postgres. A database might contain an <code>info</code> table in the standard schema <code>public</code> and another one in a different schema <code>otherschema</code></li>
<li>If you query this database for a value in the <code>info</code> table, Postgres will look into all schemas mentioned in a Postgres setting called <code>search_path</code> in the order of listing.</li>
<li>If <code>search_path</code> is set to <code>otherschema, public</code>, Postgres will first look in <code>otherschema</code> and then in <code>public</code></li>
<li>Now the default setting for <code>search_path</code> is <code>&quot;$user&quot;, public</code>, where <code>$user</code> contains the name of the user used for the DB connection. In the example from the question above, <code>search_path</code> would therefore be set to <code>craftcms, public</code>.</li>
<li>Queries SELECTing values FROM the database can, but not necessarily need to prefix table names with a schema, so both <code>SELECT * FROM info;</code> and <code>SELECT * FROM schema.info;</code> are valid queries. However when a schema is prefixed, the <code>search_path</code> will be ignored.</li>
<li>And here starts the problem with Yii2 and Craft and DDEV (or Nitro) . Yii2 is not capable of prefixing tables with a schema in its queries. That means, if the schema of the database is different than <code>public</code>, Postgres will normally not look into it for Craft's tables, even if the schema is configured in .env. Craft will believe it is not installed yet.</li>
</ol>
<p>What solutions do we have:</p>
<ul>
<li>You can use a Postgres user with a name equal to the schema</li>
<li>You can change the <code>search_path</code> to contain the schema name on a database level with the following query: <code>ALTER DATABASE mydb SET search_path = otherschema;</code>. Disadvantage: You have to redo it after a dump import. Advantage: it will not interfere with other projects.</li>
<li>You can change the <code>search_path</code> to contain the schema name on a user level: <code>ALTER ROLE someuser SET search_path = otherschema;</code> Advantage: will persist even after a dump import. Disadvantage: might interfere with other projects.</li>
</ul>

CC BY-SA 4.0
2021-09-29T14:49:09.627Z
2022-07-29T10:27:09.543Z
2022-07-29T10:27:09.543Z
Matthias Redl-Mann
<p>Due to limits in the production environment of our website, we can not use Postgres' standard schema <code>public</code>.</p>
<p>Therefore we configured something different (<code>craftcms</code>) in .env:</p>
<pre><code>DB_DRIVER=pgsql
DB_SERVER=&lt;hostname&gt;
DB_PORT=5432
DB_DATABASE=&lt;database&gt;
DB_USER=&lt;user&gt;
DB_PASSWORD=&lt;pw&gt;
DB_SCHEMA=craftcms
DB_TABLE_PREFIX=
</code></pre>
<p>Interestingly this works in our <em>production</em> environment, but not <em>locally</em> when developing with DDEV or Nitro.</p>
<p>Only difference: the <code>username</code> of the DB connection is <code>craftcms</code> on production, but locally it's <code>nitro</code>.</p>
<p>Why is that?</p>

<nitro><postgresql>
Why do Postgres schemas different from "public" sometimes not work with Craft (DDEV / Nitro)
# Why do Postgres schemas different from "public" sometimes not work with Craft (DDEV / Nitro)

(Asked by: username_0 on 2021-09-29)

Due to limits in the production environment of our website, we can not use Postgres' standard schema public.
Therefore we configured something different (craftcms) in .env:
DB_DRIVER=pgsql
DB_SERVER=
DB_PORT=5432
DB_DATABASE=
DB_USER=
DB_PASSWORD=
DB_SCHEMA=craftcms
DB_TABLE_PREFIX=

Interestingly this works in our production environment, but not locally when developing with DDEV or Nitro.
Only difference: the username of the DB connection is craftcms on production, but locally it's nitro.
Why is that?

**Tags:** nitro, postgresql


---

## 1 answers

### Answer by username_0 on 2021-09-29. Score: 1 | Accepted answer

To understand why this works on *production* but not *locally* one must understand schemas and Postgres `search_path`

1. Schemas are like an additional layer in Postgres. A database might contain an `info` table in the standard schema `public` and another one in a different schema `otherschema`
2. If you query this database for a value in the `info` table, Postgres will look into all schemas mentioned in a Postgres setting called `search_path` in the order of listing.
3. If `search_path` is set to `otherschema, public`, Postgres will first look in `otherschema` and then in `public`
4. Now the default setting for `search_path` is `"$user", public`, where `$user` contains the name of the user used for the DB connection. In the example from the question above, `search_path` would therefore be set to `craftcms, public`.
5. Queries SELECTing values FROM the database can, but not necessarily need to prefix table names with a schema, so both `SELECT * FROM info;` and `SELECT * FROM schema.info;` are valid queries. However when a schema is prefixed, the `search_path` will be ignored.
6. And here starts the problem with Yii2 and Craft and DDEV (or Nitro) . Yii2 is not capable of prefixing tables with a schema in its queries. That means, if the schema of the database is different than `public`, Postgres will normally not look into it for Craft's tables, even if the schema is configured in .env. Craft will believe it is not installed yet.

What solutions do we have:

* You can use a Postgres user with a name equal to the schema
* You can change the `search_path` to contain the schema name on a database level with the following query: `ALTER DATABASE mydb SET search_path = otherschema;`. Disadvantage: You have to redo it after a dump import. Advantage: it will not interfere with other projects.
* You can change the `search_path` to contain the schema name on a user level: `ALTER ROLE someuser SET search_path = otherschema;` Advantage: will persist even after a dump import. Disadvantage: might interfere with other projects.


---

