Snippet Name: Postgres_Schemas_in_Docker

Tags:

Created Date: 2025-06-16 13:12:40
Last Modified Date: 2025-05-31 10:31:49

Dependencies:

DBA Notes:

Sample Output:

Title: Postgres Schemas in Docker
Author: user
on my linux mint machine, i created a docker container for postgres. i created  a database named adventureworks, and selected the public scheam, then created ten other schemas. now those schemas appear in all databases. what am i doing wrong

Author: ChatGPT
User is working with a PostgreSQL Docker container on a Linux Mint machine, where they created an "adventureworks" database and ten additional schemas that are appearing in all databases.

Author: tool
Model set context updated.

Author: ChatGPT
It sounds like the schemas you created might have been placed in the `postgres` database or a shared template like `template1`. When new databases are created, they typically inherit from `template1` unless you specify otherwise. Here's how to check and correct the issue:

1. **Check the current database where schemas were created**:
   Ensure the schemas were created in the correct database. If they were created in `postgres` or `template1`, that's why they appear in all new databases.

   Run this query to see where the schemas are located:
   ```sql
   SELECT schema_name, catalog_name 
   FROM information_schema.schemata 
   WHERE schema_name NOT IN ('pg_catalog', 'information_schema');
   ```

2. **Check if `template1` was modified**:
   If the schemas were accidentally added to `template1`, every new database will inherit them. You can check if `template1` has those schemas:
   ```sql
   \c template1
   \dn
   ```

3. **Fix by removing schemas from `template1`**:
   If they are in `template1`, you can drop the schemas from there:
   ```sql
   DROP SCHEMA schema_name CASCADE;
   ```

4. **Recreate the `adventureworks` database without modifying `template1`**:
   If you want a clean slate, recreate your `adventureworks` database without relying on `template1`:
   ```sql
   CREATE DATABASE adventureworks TEMPLATE template0;
   ```

This way, only the schemas you define in `adventureworks` will appear there.

Author: user
i get an error ERROR: cross-database references are not implemented: "template1.person.addresstype"

Author: ChatGPT
The error you're seeing, "cross-database references are not implemented," likely occurs because you're trying to access a schema or object in one database from another, which PostgreSQL doesn't support directly.

### How to Resolve:

1. **Ensure you're connected to the correct database**:
   When manipulating schemas, make sure you're connected to the correct database (in your case, likely `template1` if you want to drop schemas there, or `adventureworks` if that's where you're managing your objects).

   To switch to `template1`, for example:
   ```sql
   \c template1
   ```

   Then check the schemas:
   ```sql
   \dn
   ```

2. **Drop the schema from the correct database**:
   If you find that the schema exists in `template1`, you can drop it like this:
   ```sql
   DROP SCHEMA schema_name CASCADE;
   ```

3. **Avoid cross-database references**:
   In PostgreSQL, you can't directly reference schemas or objects from one database while connected to another. You need to switch to the correct database before performing any operations on its objects.

   If you see references like `"template1.person.addresstype"`, ensure you're working with the right database at all times.

Would you like to check which schemas currently exist in both `template1` and your `adventureworks` database?