Debezium+Kafka+DotNet Core开发时PostgreSQL报GENERATED语法错误

问题描述 #

基于Debezium、Kafka和DotNet Core开发应用，Kafka、PostgreSQL、Zookeeper及Connector已通过Docker正常运行。执行命令 dotnet ef database update --project Kafka.Consumer.Api --verbose 时触发PostgreSQL语法错误，提示syntax error at or near "GENERATED"，对应的建表SQL使用了GENERATED BY DEFAULT AS IDENTITY语法，怀疑是PostgreSQL版本不兼容导致，需确认是否要修改Npgsql（v6.0.2）NuGet包版本，或有其他解决办法。

报错详情 #

Failed executing DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='20']
    CREATE TABLE "Products" (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY (START WITH 100),
        "Name" text NOT NULL,
        "Price" double precision NOT NULL,
        CONSTRAINT "PK_Products" PRIMARY KEY ("Id")
    );
    Disposing transaction.
    Closing connection to database 'HelloWorld' on server 'tcp://127.0.0.1:5431'.
    Closed connection to database 'HelloWorld' on server ''.
    'KafkaConsumerDbContext' disposed.
    Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERATED"

    POSITION: 44
       at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
       at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
       at Npgsql.NpgsqlDataReader.NextResult()
       at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
       at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
       at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
       at Npgsql.NpgsqlCommand.ExecuteNonQuery()
       at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
       at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
       at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
       at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.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.<.ctor>b__0()
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
      Exception data:
        Severity: ERROR
        SqlState: 42601
        MessageText: syntax error at or near "GENERATED"
        Position: 44
        File: scan.l
        Line: 1127
        Routine: scanner_yyerror
    42601: syntax error at or near "GENERATED"

    POSITION: 44

Docker-Compose配置 #

version: '3.7'

services:

  postgres:
    image: debezium/postgres
    container_name: postgres
    networks: 
      - broker-kafka
    environment:
      POSTGRES_PASSWORD: fatih
      POSTGRES_USER: admin
    ports:
      - 5431:5432
      
  zookeeper:
    image: confluentinc/cp-zookeeper:latest
    container_name: zookeeper
    networks: 
      - broker-kafka
    ports:
      - 2181:2181
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000

  kafka:
    image: confluentinc/cp-kafka:latest
    container_name: kafka
    networks: 
      - broker-kafka
    depends_on:
      - zookeeper
    ports:
      - 9092:9092
    environment:
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:29092,PLAINTEXT_HOST://localhost:9092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_LOG_CLEANER_DELETE_RETENTION_MS: 5000
      KAFKA_BROKER_ID: 1
      KAFKA_MIN_INSYNC_REPLICAS: 1

  connector:
    image: debezium/connect:latest
    container_name: kafka_connect_with_debezium
    networks: 
      - broker-kafka
    ports:
      - "8083:8083"
    environment:
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: my_connect_configs
      OFFSET_STORAGE_TOPIC: my_connect_offsets
      BOOTSTRAP_SERVERS: kafka:29092
    depends_on:
      - zookeeper
      - kafka

  kafdrop:
    image: obsidiandynamics/kafdrop:latest
    container_name: kafdrop
    networks: 
      - broker-kafka
    depends_on:
      - kafka
    ports:
      - 9000:9000
    environment:
      KAFKA_BROKERCONNECT: kafka:29092

networks: 
  broker-kafka:
    driver: bridge  

问题根源 #

GENERATED BY DEFAULT AS IDENTITY是PostgreSQL 10及以上版本才支持的语法，当前使用的debezium/postgres镜像默认版本低于10，无法识别该语法。

解决办法 #

方案1：升级PostgreSQL镜像版本 #

修改docker-compose中postgres服务的镜像，指定PostgreSQL 10+的Debezium镜像，示例：

postgres:
  image: debezium/postgres:16
  # 其余配置保持不变

Debezium提供了对应不同PostgreSQL版本的镜像（如debezium/postgres:10、debezium/postgres:14等），可根据需求选择适配版本。

方案2：降级Npgsql包并配置EF Core使用旧版序列语法 #

若不想升级PostgreSQL，可将Npgsql NuGet包降级到适配旧版PostgreSQL的版本（如v3.x-v5.x），同时在DbContext中配置使用序列替代IDENTITY：
在OnModelCreating方法中配置实体主键：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .Property(p => p.Id)
        .UseSequence(); // 使用序列替代IDENTITY
}

或全局配置：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql(connectionString, o => o.UseIdentityColumns(false));
}

此配置会让EF Core生成旧版PostgreSQL支持的SERIAL语法，而非GENERATED AS IDENTITY。

方案3：手动修改迁移文件 #

若已生成迁移文件，可直接修改迁移中的建表语句，将GENERATED BY DEFAULT AS IDENTITY替换为旧版SERIAL语法：
将：

"Id" integer GENERATED BY DEFAULT AS IDENTITY (START WITH 100),

替换为：

"Id" SERIAL,

修改完成后重新执行dotnet ef database update命令。

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