使用Terraform配置GCP Datastream实现PostgreSQL到GCS的CDC报错求助

以下是针对你遇到的三个验证错误的具体修复方案：

1. 修复*POSTGRES_BAD_WAL_CONFIG*错误 #

PostgreSQL的wal_level必须设置为logical才能支持逻辑解码，同时需要开启logical_decoding flag。修改google_sql_database_instance资源，取消注释并补充database_flags配置：

resource "google_sql_database_instance" "instance" {
  name             = "cdc-test-2"
  database_version = "POSTGRES_14"
  region           = "us-central1"
  settings {
    tier = "db-f1-micro"
    backup_configuration {
      enabled = true
    }

    ip_configuration {
      // Datastream IPs will vary by region.
      authorized_networks {
        value = "34.71.242.81"
      }
      authorized_networks {
        value = "34.72.28.29"
      }
      authorized_networks {
        value = "34.67.6.157"
      }
      authorized_networks {
        value = "34.67.234.134"
      }
      authorized_networks {
        value = "34.72.239.218"
      }
    }
  }

  deletion_protection = true
  database_flags      = [
    {
      name  = "logical_decoding"
      value = "on"
    },
    {
      name  = "wal_level"
      value = "logical"
    }
  ]
}

2. 修复*POSTGRES_REPLICATION_SLOT_DOES_NOT_EXIST*错误 #

需要在PostgreSQL实例中创建指定的复制槽replication_slot，使用pgoutput插件。提供两种实现方法：

方法1：使用PostgreSQL Provider #

先添加PostgreSQL provider配置：

provider "postgresql" {
  host            = google_sql_database_instance.instance.public_ip_address
  port            = 5432
  username        = google_sql_user.user.name
  password        = google_sql_user.user.password
  database        = google_sql_database.db.name
  sslmode         = "require"
  connect_timeout = 15
}

然后创建复制槽资源：

resource "postgresql_replication_slot" "replication_slot" {
  name   = "replication_slot"
  plugin = "pgoutput"

  depends_on = [google_sql_database_instance.instance, google_sql_user.user]
}

方法2：使用null_resource执行SQL #

如果不想引入PostgreSQL provider，可通过local-exec直接执行SQL命令：

resource "null_resource" "create_replication_slot" {
  depends_on = [google_sql_database_instance.instance, google_sql_user.user]

  provisioner "local-exec" {
    command = <<EOT
      psql "host=${google_sql_database_instance.instance.public_ip_address} port=5432 dbname=${google_sql_database.db.name} user=${google_sql_user.user.name} password=${google_sql_user.user.password} sslmode=require" -c "CREATE_REPLICATION_SLOT replication_slot WITH PLUGIN pgoutput;"
    EOT
  }
}

3. 修复*POSTGRES_PUBLICATION_DOES_NOT_EXIST*错误 #

需要创建指定的publicationpublication，包含需要捕获变更的表（示例为public schema下所有表）：

方法1：使用PostgreSQL Provider #

resource "postgresql_publication" "publication" {
  name   = "publication"
  schema = "public"
  tables = ["*"] // 可替换为具体表名，如["table1", "table2"]

  depends_on = [google_sql_database.db, postgresql_replication_slot.replication_slot]
}

方法2：使用null_resource执行SQL #

resource "null_resource" "create_publication" {
  depends_on = [google_sql_database.db, null_resource.create_replication_slot]

  provisioner "local-exec" {
    command = <<EOT
      psql "host=${google_sql_database_instance.instance.public_ip_address} port=5432 dbname=${google_sql_database.db.name} user=${google_sql_user.user.name} password=${google_sql_user.user.password} sslmode=require" -c "CREATE PUBLICATION publication FOR ALL TABLES;"
    EOT
  }
}

调整Datastream Stream的依赖关系 #

确保Stream资源在复制槽和publication创建完成后再部署，修改google_datastream_stream.default的depends_on列表：

resource "google_datastream_stream" "default" {
  depends_on = [
    google_kms_crypto_key_iam_member.key_user,
    # 根据你选择的实现方式添加依赖
    postgresql_replication_slot.replication_slot,
    postgresql_publication.publication
    # 若使用null_resource则替换为：
    # null_resource.create_replication_slot,
    # null_resource.create_publication
  ]
  // 其余原有配置保持不变
}

额外权限配置 #

确保SQL用户拥有复制和查询权限，可添加以下资源执行授权命令：

resource "null_resource" "grant_permissions" {
  depends_on = [google_sql_user.user]

  provisioner "local-exec" {
    command = <<EOT
      psql "host=${google_sql_database_instance.instance.public_ip_address} port=5432 dbname=postgres user=postgres password=<你的postgres超级用户密码> sslmode=require" -c "ALTER USER ${google_sql_user.user.name} WITH REPLICATION; GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${google_sql_user.user.name};"
    EOT
  }
}

注意：替换<你的postgres超级用户密码>为实际密码，建议通过Terraform变量安全管理。

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