Why Serilog PostgreSQL sink is not working through Configuration?

I have tried PostgreSQL sink for Serilog through code and it works like a charm.
Here is working code:

    static void Main(string[] args)
    {
        var connectionString = "Server=localhost; Port=5432; Database=subscriptions_log; User Id=postgres;Password=postgres";
        string tableName = "errorlog";

        IDictionary<string, ColumnWriterBase> columnWriters = new Dictionary<string, ColumnWriterBase>
        {
            {"message", new RenderedMessageColumnWriter(NpgsqlDbType.Text) },
            {"level", new LevelColumnWriter(true, NpgsqlDbType.Varchar) },
            {"raise_date", new TimestampColumnWriter(NpgsqlDbType.Timestamp) },
            {"exception", new ExceptionColumnWriter(NpgsqlDbType.Text) }
        };

        using var log = new LoggerConfiguration()
            .WriteTo.Console()
            .WriteTo.PostgreSQL(connectionString, tableName, columnWriters)
            .CreateLogger();

        log.Information("Hello, Serilog!");
    }

But when I use it through configuration file serlig creates table in the database but does not insert any rows because of "Npgsql.NpgsqlBinaryImporter.Complete()" not found.
"Here is detailed error:

Exception while emitting periodic batch from Serilog.Sinks.PostgreSQL.PostgreSQLSink:
        System.MissingMethodException:
        Method not found: 'Void Npgsql.NpgsqlBinaryImporter.Complete()'.
   at Serilog.Sinks.PostgreSQL.PostgreSQLSink.ProcessEventsByCopyCommand(IEnumerable`1 events, NpgsqlConnection connection)
   at Serilog.Sinks.PostgreSQL.PostgreSQLSink.EmitBatch(IEnumerable`1 events)
   at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.EmitBatchAsync(IEnumerable`1 events)
   at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.OnTick()

ProjectFile

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.CommandLine" Version="5.0.0"     />
    <PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables"     Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="5.0.0" />
    <PackageReference Include="Npgsql" Version="5.0.3" />
    <PackageReference Include="Serilog" Version="2.10.1-dev-01285" />
    <PackageReference Include="Serilog.Settings.Configuration" Version="3.2.0-dev-00264" />
    <PackageReference Include="Serilog.Sinks.Console" Version="4.0.0-dev-00839" />
    <PackageReference Include="Serilog.Sinks.File" Version="5.0.0-dev-00909" />
    <PackageReference Include="Serilog.Sinks.PeriodicBatching" Version="2.3.0" />
    <PackageReference Include="Serilog.Sinks.PostgreSQL" Version="2.2.0" />
    <PackageReference Include="Serilog.Sinks.PostgreSQL.Configuration" Version="1.0.0" />
  </ItemGroup>

</Project>

Config File

        {
        "Serilog": {
            "Using": [ "Serilog.Sinks.PostgreSQL.Configuration" ],
            "MinimumLevel": "Debug",
            "Enrich": [ "WithMachineName" ],
            "WriteTo": [
                {
                    "Name": "PostgreSQL",
                    "Args": {
                        "connectionString": "LogsDb",
                        "tableName": "errorlogs",
                        "needAutoCreateTable": true
                    }
                }
            ]
        },
        "ConnectionStrings": {
            "LogsDb": "Server=localhost; Port=5432; Database=subscriptions_log; User Id=postgres;Password=postgres"
        },
        "Columns": {
            "message": "RenderedMessageColumnWriter",
            "level": {
                "Name": "LevelColumnWriter",
                "Args": {
                    "renderAsText": true,
                    "dbType": "Varchar"
                }
            },
            "raise_date": "TimestampColumnWriter",
            "exception": "ExceptionColumnWriter"
        }
    }

And the Calling Method:

    static void Main(string[] args)
    {
        Serilog.Debugging.SelfLog.Enable(msg => Debug.WriteLine(msg));
        Serilog.Debugging.SelfLog.Enable(Console.Error);

        IConfiguration configuration = new ConfigurationBuilder()
            .SetBasePath(Path.GetFullPath(Path.Combine(Directory.GetCurrentDirectory(), "..", "..", "..")))
            .AddJsonFile("serilog.json", optional: false, reloadOnChange: true)
            .AddEnvironmentVariables()
            .AddCommandLine(args)
            .Build();

        using var log = new LoggerConfiguration()
            .ReadFrom.Configuration(configuration)
            .CreateLogger();

        log.Error("Hello, Serilog!");
    }

Configuration file is exactly as described here. For test purpose I tried with .net 5.0 and 3.1 with out success.
Unfortunately I can't use it through code as I am using a 3rd party library that uses serilog for logging to sql server and I have to switch to PostgrSQL only through configuration. Any help?

Solution

The reason you get this error is because Serilog.Sinks.PostgreSQL depends on Npgsql v4.0.2, but you are forcing an upgrade of Npgsql to v5.0.3 which is not compatible with the latest version of Serilog.Sinks.PostgreSQL (v2.2.0 as of this writing).

<PackageReference Include="Npgsql" Version="5.0.3" />

This is binary breaking change in Npgsql which causes the MissingMethodException because... The method expected in 4.x no longer exists in 5.x.

An immediate solution for you, is to remove the Npgsql package reference and use v4.2.0 which will be added transitively by Serilog.Sinks.PostgreSQL. Alternatively you can upgrade Npgsql to the latest 4.x version which is v4.1.8 as of this writing.

The long-term solution is to open an issue in the repo of Serilog.Sinks.PostgreSQL, so that they can release a new version that takes a dependency on Npgsql 5.x.

Update: It looks like the Serilog.Sinks.PostgreSQL is no longer maintained, and there's a fork of available here: https://github.com/SeppPenner/SerilogSinkForPostgreSQL which is up-to-date