Sorry for some similar posts – I’m narrowing the problems. I’m trying to get my PostGres database setup using Ecto migrations. This includes creating a separate PostGres schema for the app’s data. I don’t mind if Ecto creates its migration tables in the public schema, I want the app to keep its data in a different schema, e.g. core.
I’ve tried this a couple ways, but nothing seems to work. In my 1st migration, I have tried:
execute("CREATE SCHEMA core")
flush() # <-- I tried adding this too
Repo.query!("CREATE SCHEMA core")
But nothing seems to take – mix ecto.migrate crashes with an error.
** (Postgrex.Error) ERROR 3F000 (invalid_schema_name) schema "core" does not exist
What am I missing? If I log into the database using a standalone client, I can execute these queries using the same username/password credentials.
This post seemed similar: Creating postgres schemas (schema_prefix)
but It seemed to be more about where Ecto put its migration tables rather than where the app stored its data.
Likewise Ecto migration isn't creating schema in postgres seemed relevant, but my command is inside the change or up function… it should be working.
Am I the only one locking down my database by defining multiple PostGres schemas? If others are doing this, how are you doing it? Are you doing it manually? Or are you able to run these commands via a migration?
