Automatic migrations EF Core Postgres Aurora DB

Question

I have an ASP.NET Core 8 Web API with EF Core using a code-first approach. I am trying to apply migrations automatically using this code:

await using var dbContext = scope.ServiceProvider.GetRequiredService<DbContext>();
await dbContext.Database.MigrateAsync();

If I run it locally on a Postgres docker container, it is working properly. But using AWS Aurora Postgres instance, when the MigrateAsync method is running, I get:

Failed executing DbCommand (10ms)
CREATE TABLE "__EFMigrationsHistory"
Unhandled exception. Npgsql.PostgresException (0x80004005): 3F000: no schema has been selected to create in
POSITION: 14

Trying manually in the database, the user has access to the public schema and running that query manually works. I have tried explicitly specifying schema in the DbContext, but still the same issue.

Any guidance?

Answer 1

You can try to configure the searchpath in your connection string to solve this problem.

May you define the following configuration in your initial connection string:

Server=XXXX; port=XXXX; user id=XXXX; password=XXXX; database=XXXX; pooling=true; SearchPath=XXXX

If you have created your schema,you need SearchPath to configure the relevant schema: SearchPath=Myschema, else you can try deleting SearchPath.