ASP.NET Core中Serilog无法将日志写入PostgreSQL问题求助

问题概述 #

在ASP.NET Core Web API中使用Serilog时，控制台能正常输出API请求日志，但日志无法写入PostgreSQL数据库，需要将API请求事件按列（如Method、URL等）存储到数据库表中。

核心问题排查与修复步骤 #

1. 移除Program.cs中过早的Logger关闭操作 #

你在Program.cs中刚创建Logger就调用了Log.CloseAndFlush()，这会直接终止Logger生命周期，后续所有日志都无法被处理。同时，正确的Serilog集成方式应使用builder.Host.UseSerilog()托管Logger。

修正后的Program.cs关键代码：

using Web_API.Models;
using Microsoft.EntityFrameworkCore;
using Serilog;

var builder = WebApplication.CreateBuilder(args);

// 集成Serilog，直接复用builder的配置
builder.Host.UseSerilog((context, config) =>
    config.ReadFrom.Configuration(context.Configuration)
);

// 注册DbContext
builder.Services.AddDbContext<AppDBContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("ConnS"))
);

// 注册APILogger（依赖注入用）
builder.Services.AddScoped<APILogger>();

var app = builder.Build();

// 其他中间件配置...

app.Run();

2. 补全appsettings.json中PostgreSQL连接字符串 #

你的Serilog配置里PostgreSQL的connectionString为空，必须填入实际连接字符串；同时PostgreSQL无nvarchar类型，需改为varchar，并配置属性与数据库列的映射。

修正后的Serilog配置片段：

"Serilog":{
  "Using":["Serilog.Sinks.PostgreSQL"],
  "MinimumLevel": "Information",
  "WriteTo":[
    {
      "Name": "Console",
      "Args": {
        "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff} [{Level:u3}] {Message:lj} {CustomDateTimeColumn}"
      }
    },
    {
      "Name": "PostgreSQL",
      "Args": {
         "connectionString": "Server=localhost; Port=5432; Database=DB; User ID=postgres; Password=12345678;",
         "sinkOptionsSection": {
            "tableName": "logs",
            "schemaName" :  "public",
            "autoCreateSqlTable": false
          },
          "columnOptionsSection": {
             "customColumns" : [
                {
                  "ColumnName": "api_requestname",
                  "DataType": "varchar(255)",
                  "AllowNull": true,
                  "PropertyName": "api_requestname" // 映射LogContext中的属性名
                },
                {
                  "ColumnName": "api_method",
                  "DataType": "varchar(10)",
                  "AllowNull": true,
                  "PropertyName": "api_method"
                },
                {
                  "ColumnName": "api_url",
                  "DataType": "varchar(255)",
                  "AllowNull": true,
                  "PropertyName": "api_url"
                },
                // 其他需要映射的列
             ]
          }
      }
    }
  ],
  "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ],
  "Properties": { "Application": "Web_API" }
}

3. 重构APILogger，使用全局配置的Logger #

你在APILogger中创建了独立的Logger实例，与配置文件定义的Logger无关，且未配置列映射。正确做法是通过依赖注入获取全局Logger。

重构后的APILogger.cs：

using Serilog;
using Serilog.Context;

namespace Web_API.Models
{
    public class APILogger
    {
        private readonly ILogger<APILogger> _logger;

        // 构造函数注入全局Logger
        public APILogger(ILogger<APILogger> logger)
        {
            _logger = logger;
        }

        public void LogApiRequest(string apiRequestName, DateTime apiRequestDate, string apiMethod, string apiUrl, string apiBody, string apiStatusCode, string apiResponseMessage, DateTime apiResponseDate)
        {
            using (LogContext.PushProperty("api_requestname", apiRequestName))
            using (LogContext.PushProperty("api_requestdate", apiRequestDate))
            using (LogContext.PushProperty("api_method", apiMethod))
            using (LogContext.PushProperty("api_url", apiUrl))
            using (LogContext.PushProperty("api_body", apiBody))
            using (LogContext.PushProperty("api_statuscode", apiStatusCode))
            using (LogContext.PushProperty("api_responsemessage", apiResponseMessage))
            using (LogContext.PushProperty("api_responsedate", apiResponseDate))
            {
                _logger.Information("API request processed");
            }
        }
    }
}

4. 修正API接口中LogContext的作用域 #

你的API接口中using (LogContext.PushProperty(...))未加{}，导致作用域立即结束，try/catch块内的日志无法捕获属性。

修正后的API接口代码：

[HttpGet]
public async Task<IActionResult> GetAirlineData()
{
    DateTime reqDT = DateTime.Now;
    string reqMethod = HttpContext.Request.Method;
    string reqPath = HttpContext.Request.Path;
    
    // 正确读取Request.Body并重置流位置
    string reqBody = string.Empty;
    using (var reader = new StreamReader(HttpContext.Request.Body))
    {
        reqBody = await reader.ReadToEndAsync();
        HttpContext.Request.Body.Position = 0;
    }

    // 用大括号包裹LogContext作用域，覆盖整个try/catch
    using (LogContext.PushProperty("api_requestname", "Anonymous"))
    using (LogContext.PushProperty("api_requestdate", reqDT))
    using (LogContext.PushProperty("api_method", reqMethod))
    using (LogContext.PushProperty("api_url", reqPath))
    using (LogContext.PushProperty("api_body", reqBody))
    {
        try
        {
            var data = _dbContext.Airline;
                
            int statusCode = HttpContext.Response.StatusCode;
            string resMessage = "Success";
            DateTime resDT = DateTime.Now;

            using (LogContext.PushProperty("api_statuscode", statusCode.ToString()))
            using (LogContext.PushProperty("api_responsemessage", resMessage))
            using (LogContext.PushProperty("api_responsedate", resDT))
            {
                _logger.LogInformation("API request processed successfully");
            }

            return Ok(Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented));
        }
        catch (Exception ex)
        {
            int statusCode = HttpContext.Response.StatusCode;
            string resMessage = ex.Message;
            DateTime resDT = DateTime.Now;

            using (LogContext.PushProperty("api_statuscode", statusCode.ToString()))
            using (LogContext.PushProperty("api_responsemessage", resMessage))
            using (LogContext.PushProperty("api_responsedate", resDT))
            {
                _logger.LogError(ex, "API request error");
            }
            return BadRequest(ex.Message);
        }
    }
}

5. 验证数据库表结构 #

确保PostgreSQL的public.logs表包含所有配置的自定义列，类型与配置一致：

• 列名：api_requestname、api_method、api_url等
• 类型：varchar(255)、varchar(10)等
• 确认postgres用户拥有表的写入权限

内容的提问来源于stack exchange，提问作者Nawapol R.