.NET6应用中Npgsql随机连接超时问题求助

基于PostgreSQL + Npgsql的.NET 6应用中，手动初始化连接池时遇到以下问题：

• 首次创建连接总是超时，最终仅成功创建4个连接
• 连接池中的连接闲置一段时间后自动关闭，此时首次调用数据库API失败，第二次请求恢复正常

手动初始化连接池代码 #

List<NpgsqlConnection> connections = new List<NpgsqlConnection>();

for (int i = 0; i < 5; i++)
{
    NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    try
    {
        connection.Open(); // 实际打开连接
    }
    catch (Exception e)
    {
        continue;
    }
    connections.Add(connection);
}
connections.ForEach(connection => { connection.Close(); });

超时错误日志 #

2024-01-11T19:54:53.7699503+09:00 40000374-0003-fc00-b63f-84710c7967bb [INF] Entity Framework Core "6.0.19" initialized '"DbContextClass"' using provider '"Npgsql.EntityFrameworkCore.PostgreSQL":"6.0.8"' with options: "CommandTimeout=15 " (d2805559)
2024-01-11T19:55:09.4702152+09:00 40000374-0003-fc00-b63f-84710c7967bb [ERR] An error occurred using the connection to database '"my_db"' on server '""'. (5fc3407c)
2024-01-11T19:55:09.5272113+09:00 40000374-0003-fc00-b63f-84710c7967bb [INF] A transient exception occurred during execution. The operation will be retried after 0ms."
""System.TimeoutException: The operation has timed out.
   at Npgsql.Util.NpgsqlTimeout.Check()
   at Npgsql.Util.NpgsqlTimeout.CheckAndGetTimeLeft()
   at Npgsql.Util.NpgsqlTimeout.CheckAndApply(NpgsqlConnector connector)
   at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|195_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.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.<Get>g__RentAsync|28_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)" (43386701)
System.TimeoutException: The operation has timed out.
   at Npgsql.Util.NpgsqlTimeout.Check()
   at Npgsql.Util.NpgsqlTimeout.CheckAndGetTimeLeft()
   at Npgsql.Util.NpgsqlTimeout.CheckAndApply(NpgsqlConnector connector)
   at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|195_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.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.<Get>g__RentAsync|28_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---

当前重试策略配置 #

builder.Services.AddDbContext<DbContextClass>(options =>
{
    options.UseNpgsql(connectionString,
        options =>
        {
            options.CommandTimeout(15);
            options.EnableRetryOnFailure(2);
        }
        );
});

1. 移除手动连接池初始化代码 #

Npgsql自带内置连接池，无需手动创建和管理连接。手动创建连接再关闭的操作不仅多余，还会干扰内置连接池的正常逻辑，导致首次连接超时。

内置连接池默认会自动管理连接的创建、复用和销毁，只需通过连接字符串配置池参数即可，示例：

Host=myhost;Database=mydb;Username=myuser;Password=mypass;Maximum Pool Size=5;Minimum Pool Size=5;Connection Idle Lifetime=300;

• Maximum Pool Size：连接池最大连接数（默认100）
• Minimum Pool Size：连接池保持的最小空闲连接数（默认0，设置为5可避免闲置后连接被释放）
• Connection Idle Lifetime：连接闲置多久后被销毁（默认300秒，可根据需求调整）

2. 调整超时与重试策略 #

（1）增加连接超时时间 #

当前CommandTimeout=15是命令执行超时，而非连接建立超时。需在连接字符串中添加Timeout=30（单位秒），延长连接建立的超时时间：

Host=myhost;Database=mydb;Username=myuser;Password=mypass;Timeout=30;Maximum Pool Size=5;Minimum Pool Size=5;

（2）优化EF Core重试策略 #

EnableRetryOnFailure(2)默认只重试部分 transient 错误，需明确指定包含连接超时相关错误，确保首次连接超时能被自动重试：

builder.Services.AddDbContext<DbContextClass>(options =>
{
    options.UseNpgsql(connectionString,
        npgsqlOpts =>
        {
            npgsqlOpts.CommandTimeout(15);
            npgsqlOpts.EnableRetryOnFailure(
                maxRetryCount: 2,
                maxRetryDelay: TimeSpan.FromSeconds(1),
                errorCodesToAdd: new[] { "57P01", "08006", "08001" } // 覆盖连接终止、连接失败等常见 transient 错误
            );
        });
    // 确保异步操作也能正确触发重试逻辑
    options.UseExecutionStrategy(context => new NpgsqlExecutionStrategy(context, 2, TimeSpan.FromSeconds(1)));
});

3. 验证连接池状态 #

可以通过Npgsql内置方法查看连接池状态，确认连接是否正常维护：

var poolStats = NpgsqlConnection.GetPoolStatistics(connectionString);
Console.WriteLine($"当前活跃连接数：{poolStats.ActiveConnections}");
Console.WriteLine($"当前空闲连接数：{poolStats.IdleConnections}");

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