Serilog结合过滤表达式写入PostgreSQL失败及JSON语法错误排查

异常信息 #

Edit2:
2024-09-11T11:49:04.0440649Z 从Serilog.Sinks.PostgreSQL.PostgreSQLSink发送周期性批量日志时发生异常：Npgsql.PostgresException (0x80004005): 22P02: json类型的输入语法无效

已安装的NuGet包 #

<ItemGroup>
    <PackageReference Include="Serilog" Version="4.0.1" />
    <PackageReference Include="Serilog.AspNetCore" Version="8.0.2" />
    <PackageReference Include="Serilog.Expressions" Version="5.0.0" />
    <PackageReference Include="Serilog.Sinks.Console" Version="6.0.0" />
    <PackageReference Include="Serilog.Sinks.PostgreSQL" Version="2.3.0" />
    <PackageReference Include="Serilog.Sinks.PostgreSQL.Configuration" Version="1.0.1" />
</ItemGroup>

核心问题 #

• 直接配置WriteTo写入PostgreSQL时功能正常：

  "WriteTo": [
              {
                "Name": "PostgreSQL",
                "Args": {
                  "connectionString": "AppDbConnection",
                  "tableName": "tblAppError",
                  "needAutoCreateTable": true
                }
              }
            ]
  

• 但结合表达式过滤（无论是按日志级别还是SourceContext）时，数据库表无法自动创建，日志也无法写入。后续尝试添加文件日志，文件日志正常生成，但PostgreSQL问题依旧。

当前配置代码 #

Program.cs #

builder.Host.UseSerilog((context, config) => config.ReadFrom.Configuration(context.Configuration));

appSettings.json #

{
  "Serilog": {
    "Using": [
      "Serilog.Sinks.Console",
      "Serilog.Expressions",
      "Serilog.Sinks.PostgreSQL.Configuration"
    ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft.AspNetCore.Mvc": "Warning",
        "Microsoft.AspNetCore.Routing": "Warning",
        "Microsoft.AspNetCore.Hosting": "Warning"
      }
    },
    "WriteTo": [
      {
        "Name": "Console"
      },
      {
        "Name": "PostgreSQL",
        "Args": {
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "StartsWith(SourceContext,'SerilogWork.API.Handlers.GlobalExceptionHandler')"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "PostgreSQL",
                "Args": {
                  "connectionString": "AppDbConnection",
                  "tableName": "tblAppError",
                  "needAutoCreateTable": true
                }
              }
            ]
          }
        }
      }
    ],
    "Enrich": [
      "FromLogContext"
    ]
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "AppDbConnection": "Server=localhost;Database=templateDb;User ID=postgres;Password=***;"
  },
  "Columns": {
    "message": "RenderedMessageColumnWriter",
    "message_template": "MessageTemplateColumnWriter",
    "level": {
      "Name": "LevelColumnWriter",
      "Args": {
        "renderAsText": true,
        "dbType": "Varchar"
      }
    },
    "raise_date": "TimestampColumnWriter",
    "exception": "ExceptionColumnWriter",
    "properties": "LogEventSerializedColumnWriter",
    "props_test": {
      "Name": "PropertiesColumnWriter",
      "Args": {
        "dbType": "Varchar"
      }
    }
  }
}

后续尝试的配置（文件日志正常，PostgreSQL无效） #

"configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "SourceContext = 'SerilogWork.API.Handlers.GlobalExceptionHandler'"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "PostgreSQL",
                "Args": {
                  "connectionString": "AppDbConnection",
                  "tableName": "tblAppError",
                  "needAutoCreateTable": true
                }
              },
              {
                "Name": "File",
                "Args": {
                    "path": "Logs/log-.txt",
                    "rollingInterval": "Day",
                    "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level:u3}] {Message:lj}{NewLine}{Exception}",
                    "fileSizeLimitBytes": 10485760,
                    "rollOnFileSizeLimit": true,
                    "shared": true,
                    "flushToDiskInterval": 1
                }
            }
            ]
          }

1. 修正嵌套配置错误 #

你当前在PostgreSQL sink的Args里嵌套configureLogger是错误用法——configureLogger是Serilog.Sinks.Map这类sink的专属参数，PostgreSQL sink不支持这种嵌套逻辑。正确做法是直接给PostgreSQL sink添加过滤规则，而非嵌套子logger。

2. 正确的过滤配置方式 #

方式一：直接为PostgreSQL sink添加过滤 #

修改WriteTo节点，将过滤规则直接附加到PostgreSQL sink配置中：

"WriteTo": [
  {
    "Name": "Console"
  },
  {
    "Name": "PostgreSQL",
    "Args": {
      "connectionString": "AppDbConnection",
      "tableName": "tblAppError",
      "needAutoCreateTable": true
    },
    "Filter": [
      {
        "Name": "ByIncludingOnly",
        "Args": {
          "expression": "StartsWith(SourceContext,'SerilogWork.API.Handlers.GlobalExceptionHandler')"
        }
      }
    ]
  }
]

方式二：用Map sink实现条件路由（需额外安装Serilog.Sinks.Map包） #

如果需要更复杂的日志路由逻辑，可使用Map sink：

"WriteTo": [
  {
    "Name": "Console"
  },
  {
    "Name": "Map",
    "Args": {
      "fromSource": "SourceContext",
      "configure": [
        {
          "Name": "PostgreSQL",
          "Args": {
            "connectionString": "AppDbConnection",
            "tableName": "tblAppError",
            "needAutoCreateTable": true
          }
        }
      ],
      "when": "StartsWith($value, 'SerilogWork.API.Handlers.GlobalExceptionHandler')"
    }
  }
]

3. 修复JSON语法异常 #

你遇到的json类型的输入语法无效错误，大概率是LogEventSerializedColumnWriter序列化日志属性时生成了非法JSON。可尝试：

• 移除根级别的properties字段，改用已配置的props_test（PropertiesColumnWriter）；
• 或为LogEventSerializedColumnWriter指定合法序列化格式：

"properties": {
  "Name": "LogEventSerializedColumnWriter",
  "Args": {
    "format": "Json"
  }
}

4. 修正列映射配置位置 #

根级别的Columns配置不会被PostgreSQL sink自动读取，需将其移到sink的Args节点下，命名为columnOptions：

{
  "Name": "PostgreSQL",
  "Args": {
    "connectionString": "AppDbConnection",
    "tableName": "tblAppError",
    "needAutoCreateTable": true,
    "columnOptions": {
      "message": "RenderedMessageColumnWriter",
      "message_template": "MessageTemplateColumnWriter",
      "level": {
        "Name": "LevelColumnWriter",
        "Args": {
          "renderAsText": true,
          "dbType": "Varchar"
        }
      },
      "raise_date": "TimestampColumnWriter",
      "exception": "ExceptionColumnWriter",
      "props_test": {
        "Name": "PropertiesColumnWriter",
        "Args": {
          "dbType": "Varchar"
        }
      }
    }
  },
  "Filter": [
    {
      "Name": "ByIncludingOnly",
      "Args": {
        "expression": "StartsWith(SourceContext,'SerilogWork.API.Handlers.GlobalExceptionHandler')"
      }
    }
  ]
}

5. 验证日志触发逻辑 #

先移除过滤规则，确认PostgreSQL能正常创建表并写入日志，再逐步添加过滤条件。同时检查GlobalExceptionHandler生成的日志是否确实携带了匹配的SourceContext（注意大小写和拼写完全一致）。

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