I think the big issue is that structure.sql ALSO lists all of the migrations that you’ve done, so there’s a recursive dependency here that is sort of weird if you do it inside of a migration.

You could do a sort of custom my_structure.sql that has just a big psql_dump of things and call that from your migration, but the output of mix ecto.dump is really designed to be used with mix ecto.load

In this case, the database had no migrations. I just have a structure dump, and it does properly represent the structure of the database. But I hear what you’re saying: the structure.sql is best handled by ecto.load.

ecto.load under the hood just does a psql to load the structure.sql into the database.
So if you have access to it on the server, you could call it yourself from a migration.

Just a note… PostgreSQL allows you to create schema objects within a transaction without problem:

127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:05:47 PM PDT 2023]
> BEGIN;
BEGIN
Time: 0.381 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:05:55 PM PDT 2023]
> CREATE SCHEMA my_schema_1;
CREATE SCHEMA
Time: 1.601 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:14 PM PDT 2023]
> CREATE SCHEMA my_schema_2;
CREATE SCHEMA
Time: 0.516 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:17 PM PDT 2023]
> CREATE SCHEMA my_schema_3;
CREATE SCHEMA
Time: 0.558 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:19 PM PDT 2023]
> COMMIT;
COMMIT
Time: 2.574 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:24 PM PDT 2023]
> 

New databases are not allowed to be created in an ongoing transaction:

127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:08:42 PM PDT 2023]
> BEGIN;
BEGIN
Time: 0.412 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:08:44 PM PDT 2023]
> CREATE DATABASE test_db OWNER scb;
ERROR:  CREATE DATABASE cannot run inside a transaction block
Time: 0.786 ms

FYI you can set @disable_ddl_transaction true in your ecto migration file and then this is no longer in a transaction. As @sbuttgereit notes I don’t think there is actually an issue here though.