Search code examples
c#.netpostgresqlentity-frameworknpgsql

NpgsqlException while connecting to Postgres: ERROR: 3D000: odyssey: c16b9035a1f78: route for 'template1.xxx' is not found

I have faced a problem while connecting to a cloud Postgres database. It throws an error:

System.Data.Entity.Core.ProviderIncompatibleException: An error occurred accessing the database. 
This usually means that the connection to the database failed. 
Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application's config file.
 System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string.
Npgsql.NpgsqlException: ERROR: 3D000: odyssey: c16b9035a1f78: route for 'template1.my-admin' is not found

External connections are allowed in the database. The user my-admin has been created, with full access to the database.

Connection to the database is made from a server running on Windows Server 2008 R2. The cloud service certificate has been added to Trusted Root Certificates, Intermediate Certificate Authorities.

The connection string is as follows (.Net 4.8, Entity Framework):

<add name="ApplicationContext" providerName="Npgsql" connectionString="Host=xyz.mdb.yandexcloud.net;Port=6432;Database=my-db;Username=my-admin;Password=XYZ;SSLMode=Require;"/>

Have no idea how to fix this error. Will appreciate any help.

Solution

  • I am posting this solution for those who are having trouble with legacy projects.

    The following error message

    The provider did not return a ProviderManifestToken string.
Npgsql.NpgsqlException: ERROR: 3D000: odyssey: c16b9035a1f78: route for 'template1' is not found

    is shown because the Npgsql uses by default the template1 database as a template when EF6 creates or deletes a database. There is a discussion on that, explaining that it is a sort of old bug: https://github.com/npgsql/npgsql/issues/804

    One of the solutions suggested by the Npgsql team during that discussion was to create a template1 database and give the user full rights to it.

    Unfortunately, that was not my case, so, I decided to update EF, and Npgsql and specify the EF Template Database and the EF Admin Database parameters in the connection string to stop using the template1 database.

    Finally, I updated the libraries listed below:

    • EntityFramework v. 6.2.0 -> v. 6.5.1
    • Npgsql replaced with EntityFramework6.Npgsql v.6.4.3
    • Npgsql v.2.2.7 -> v.6.0.11
    • Net 4.5.2 -> Net 4.8

    Please note, that I've chosen the Npgsql version 6.0.11 because of their known bug and the latest Npgsql version did not work because of that: ProviderIncompatibleException with Npgsql, Postgresql 16.1 and .NET Framework 4.8

    In the end, my connection string was as the following and worked well:

    <add name="ApplicationContext" providerName="Npgsql" connectionString="Host=xyz.mdb.yandexcloud.net;Port=6432;Database=my-db;Username=my-admin;Password=XYZ;EF Template Database=my-db;EF Admin Database=my-db;" />

    Hope this post will save someone's day in the future.