I’m going a bit nuts – I’m trying to create an alternate PostGres schema for an app’s tables. The idea is that Ecto can store its migrations stuff inside public, but all of our app’s tables we want in core. So here are our migrations:

Migration 1:

def change do
    execute("CREATE SCHEMA core")
    flush()
end

Migration 2:

  def up do
    create table("users", primary_key: false, prefix: "core") do
      add(:id, :binary_id, primary_key: true)
      add(:email, :string, size: 128)
      # ...  etc...
      timestamps(inserted_at: :created_at)
    end

Note the prefix core.

This works fine locally (but granted, our local dockerized instances of PostGres have very few restrictions). On prod, however, we are relying a custom module that calls Ecto.Migrator to run the migrations (because there’s no mix on prod).

Previously, this worked well (with a legacy app). But on the new app, the migrations are not running successfully.

Problem 1: the core schema does not seem to ever get created by the migration. Running the migrations chokes on migration 2 with an error like:

{:error,
 %Postgrex.Error{
   connection_id: 3411,
   message: nil,
   postgres: %{
     code: :invalid_schema_name,
     file: "namespace.c",
     line: "3023",
     message: "schema \"core\" does not exist",
     pg_code: "3F000",
     position: "14",
     routine: "get_namespace_oid",
     severity: "ERROR",
     unknown: "ERROR"
   },
   query: nil}}

Problem 2: even if I manually create the core schema (e.g. by logging into the PostGres instance), then the migrations complete successfully, but then I can’t actually query the database. Running any sort of Ecto query using the Repo just fails with the following error:

** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 1313ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

  1. Ensuring your database is available and that you can connect to it
  2. Tracking down slow queries and making sure they are running fast enough
  3. Increasing the pool_size (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
    (ecto 3.8.4) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4
    (ecto 3.8.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (resources 1.1.0) lib/resources/pg_resource.ex:206: Resources.PGResource.get_many/3

This seems similar to Creating postgres schemas (schema_prefix) but I have no solution for it. I have checked the Ecto config and it all looks correct. I mean… I can run migrations (sort of), but I can’t seem to do anything else.

Does anyone have any ideas on this? Our previous database was set up manually without migrations – but I think we should be able to get it set up with normal migrations. Do special privileges need to be granted to the db user before queries are allowed?

Thanks in advance!