执行dotnet ef database update时Npgsql流读取TimeoutException报错

问题场景 #

在.NET应用中使用Entity Framework Core执行dotnet ef database update命令更新PostgreSQL数据库时，抛出TimeoutException错误，完整错误信息及堆栈跟踪如下：

Build started...
Build succeeded.
Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
 ---> System.TimeoutException: Timeout during reading attempt
   at Npgsql.Internal.NpgsqlReadBuffer.g__EnsureLong|55_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
   at Npgsql.Internal.NpgsqlConnector.RawOpen(SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
   at Npgsql.Internal.NpgsqlConnector.g__OpenCore|213_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
   at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.UnpooledDataSource.Get(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.g__OpenAsync|42_0(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.GetAppliedMigrations()
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)  
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.c__DisplayClass0_0.b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Exception while reading from stream

连接字符串示例 #

{
    "ConnectionStrings": {
        "DefaultConnection" : "Host=MyIP; Port=6060; Database=test; Username=user; Password=password;"
    },
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    }
}

使用版本 #

• PostgreSQL: 15.7
• Npgsql.EntityFrameworkCore.PostgreSQL: 8.0.10
• .NET SDK Version: 8.0.403
• OS: Windows 10

已尝试的解决措施 #

• 检查连接字符串格式，确认无误
• 测试PostgreSQL服务器连通性：ping MyIP超时，但PowerShell命令Test-NetConnection返回TcpTestSucceeded: True，判断网络连通正常
• 尝试开启/关闭SSL设置
• 在连接字符串中添加CommandTimeout=300延长命令超时时间

解决方案建议 #

1. 调整连接超时参数：CommandTimeout控制的是命令执行超时，连接阶段的超时需设置Timeout参数（单位秒），在连接字符串中添加Timeout=300，覆盖默认15秒的连接超时。
2. 检查PostgreSQL服务器配置：
   • 确认postgresql.conf中的listen_addresses包含客户端IP或设置为*，允许远程连接
   • 检查pg_hba.conf是否添加了客户端IP段的访问规则，例如：host test user 你的客户端IP/32 scram-sha-256
3. 排查网络防火墙：虽然Test-NetConnection显示TCP测试成功，但可能存在防火墙在连接建立后限制数据传输，检查服务器端和客户端的防火墙规则，确保PostgreSQL端口（6060）的双向数据传输不受限
4. 启用Npgsql详细日志：在appsettings.json中添加Npgsql的Debug级日志，查看连接过程中的具体细节：

   "Logging": {
       "LogLevel": {
           "Default": "Information",
           "Microsoft.AspNetCore": "Warning",
           "Npgsql": "Debug"
       }
   }
   

5. 版本兼容性排查：尝试将Npgsql.EntityFrameworkCore.PostgreSQL降级到8.0.0或7.0.x版本，排查是否存在版本适配问题

内容的提问来源于stack exchange，提问作者Hridoy Hasan