Logstash无法从AWS RDS PostgreSQL导入数据至Elasticsearch求助

环境背景 #

• 运行Ubuntu 20.04的AWS EC2实例
• Elasticsearch、Logstash、Kibana均为8.7版本，Elasticsearch与Kibana运行正常
• 尝试通过Logstash将AWS RDS PostgreSQL的contacts表数据导入Elasticsearch，进程停滞且无索引生成

Logstash配置文件 #

input {
  jdbc {
    jdbc_driver_class => "org.postgresql.Driver"
    jdbc_connection_string => "jdbc:postgresql://xxx.xxx.us-east-1.rds.amazonaws.com:5432/postgres"
    jdbc_user => "xxx"
    jdbc_password => "xxx"
    schedule => "0 0 * * *"
    statement => "SELECT * FROM contacts"
  }
}

output {
  elasticsearch {
    hosts => ["https://localhost:9200"]
    user => "elastic"
    password => "xxx"
    cacert => '/etc/logstash/config/certs/http_ca.crt'
    index => "contacts"
    document_id => "%{[id]}"
  }
}

Logstash运行日志 #

WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
[WARN ] 2023-05-08 04:22:20.425 [main] runner - NOTICE: Running Logstash as superuser is not recommended and won't be allowed in the future. Set 'allow_superuser' to 'false' to avoid startup errors in future releases.
[INFO ] 2023-05-08 04:22:20.435 [main] runner - Starting Logstash {"logstash.version"=>"8.7.1", "jruby.version"=>"jruby 9.3.10.0 (2.6.8) 2023-02-01 107b2e6697 OpenJDK 64-Bit Server VM 17.0.7+7 on 17.0.7+7 +indy +jit [x86_64-linux]"}
[INFO ] 2023-05-08 04:22:20.438 [main] runner - JVM bootstrap flags: [-Xms1g, -Xmx1g, -Djava.awt.headless=true, -Dfile.encoding=UTF-8, -Djruby.compile.invokedynamic=true, -XX:+HeapDumpOnOutOfMemoryError, -Djava.security.egd=file:/dev/urandom, -Dlog4j2.isThreadContextMapInheritable=true, -Djruby.regexp.interruptible=true, -Djdk.io.File.enableADS=true, --add-exports=jdk.compiler/com.sun.tools.javac.api=ALL-UNNAMED, --add-exports=jdk.compiler/com.sun.tools.javac.file=ALL-UNNAMED, --add-exports=jdk.compiler/com.sun.tools.javac.parser=ALL-UNNAMED, --add-exports=jdk.compiler/com.sun.tools.javac.tree=ALL-UNNAMED, --add-exports=jdk.compiler/com.sun.tools.javac.util=ALL-UNNAMED, --add-opens=java.base/java.security=ALL-UNNAMED, --add-opens=java.base/java.io=ALL-UNNAMED, --add-opens=java.base/java.nio.channels=ALL-UNNAMED, --add-opens=java.base/sun.nio.ch=ALL-UNNAMED, --add-opens=java.management/sun.management=ALL-UNNAMED]
[WARN ] 2023-05-08 04:22:20.651 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
[INFO ] 2023-05-08 04:22:21.285 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600, :ssl_enabled=>false}
[INFO ] 2023-05-08 04:22:21.906 [Converge PipelineAction::Create<main>] Reflections - Reflections took 192 ms to scan 1 urls, producing 132 keys and 462 values
[INFO ] 2023-05-08 04:22:22.684 [Converge PipelineAction::Create<main>] javapipeline - Pipeline `main` is configured with `pipeline.ecs_compatibility: v8` setting. All plugins in this pipeline will default to `ecs_compatibility => v8` unless explicitly configured otherwise.
[INFO ] 2023-05-08 04:22:22.712 [[main]-pipeline-manager] elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["https://localhost:9200"]}
[INFO ] 2023-05-08 04:22:22.867 [[main]-pipeline-manager] elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[https://elastic:xxxxxx@localhost:9200/]}}
[WARN ] 2023-05-08 04:22:23.137 [[main]-pipeline-manager] elasticsearch - Restored connection to ES instance {:url=>"https://elastic:xxxxxx@localhost:9200/"}
[INFO ] 2023-05-08 04:22:23.145 [[main]-pipeline-manager] elasticsearch - Elasticsearch version determined (8.7.1) {:es_version=>8}
[WARN ] 2023-05-08 04:22:23.145 [[main]-pipeline-manager] elasticsearch - Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>8}
[INFO ] 2023-05-08 04:22:23.159 [[main]-pipeline-manager] elasticsearch - Not eligible for data streams because config contains one or more settings that are not compatible with data streams: {"index"=>"contacts"}
[INFO ] 2023-05-08 04:22:23.159 [[main]-pipeline-manager] elasticsearch - Data streams auto configuration (`data_stream => auto` or unset) resolved to `false`
[WARN ] 2023-05-08 04:22:23.161 [[main]-pipeline-manager] elasticsearch - Elasticsearch Output configured with `ecs_compatibility => v8`, which resolved to an UNRELEASED preview of version 8.0.0 of the Elastic Common Schema. Once ECS v8 and an updated release of this plugin are publicly available, you will need to update this plugin to resolve this warning.
[INFO ] 2023-05-08 04:22:23.182 [[main]-pipeline-manager] javapipeline - Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50, "pipeline.max_inflight"=>250, "pipeline.sources"=>["/etc/logstash/conf.d/postgresql.conf"], :thread=>"#<Thread:0x2fe9a4a6@/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:134 run>"}
[INFO ] 2023-05-08 04:22:23.187 [Ruby-0-Thread-10: /usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-output-elasticsearch-11.13.1-java/lib/logstash/plugin_mixins/elasticsearch/common.rb:161] elasticsearch - Using a default mapping template {:es_version=>8, :ecs_compatibility=>:v8}
[INFO ] 2023-05-08 04:22:23.767 [[main]-pipeline-manager] javapipeline - Pipeline Java execution initialization time {"seconds"=>0.58}
[INFO ] 2023-05-08 04:22:24.308 [[main]-pipeline-manager] jdbc - ECS compatibility is enabled but `target` option was not specified. This may cause fields to be set at the top-level of the event where they are likely to clash with the Elastic Common Schema. It is recommended to set the `target` option to avoid potential schema conflicts (if your data is ECS compliant or non-conflicting, feel free to ignore this message)
[INFO ] 2023-05-08 04:22:24.309 [[main]-pipeline-manager] javapipeline - Pipeline started {"pipeline.id"=>"main"}
[INFO ] 2023-05-08 04:22:24.322 [Agent thread] agent - Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}

补充说明 #

• 原无法连接Elasticsearch，添加cacert参数并复制Elasticsearch的http_ca.crt到Logstash目录后连接正常，不确定该操作是否必要

排查与解决步骤 #

1. 安装PostgreSQL JDBC驱动 #

Logstash的jdbc插件不会自动包含PostgreSQL驱动，需手动操作：

• 下载与RDS PostgreSQL版本匹配的JDBC驱动（例如postgresql-42.5.4.jar）
• 将驱动文件放到/usr/share/logstash/logstash-core/lib/jars/目录
• 重启Logstash服务

2. 临时调整同步计划验证配置 #

当前schedule => "0 0 * * *"是每日凌晨执行，启动后不会立即同步。可临时改为schedule => "* * * * *"（每分钟执行一次），或去掉schedule参数手动触发运行，观察是否能拉取数据。

3. 验证RDS网络连通性 #

在EC2实例上执行以下命令测试：

# 测试端口连通性
telnet xxx.xxx.us-east-1.rds.amazonaws.com 5432
# 用psql客户端测试数据库连接
psql -h xxx.xxx.us-east-1.rds.amazonaws.com -U xxx -d postgres -p 5432

确保EC2安全组允许出站5432端口到RDS安全组，同时RDS安全组允许EC2实例的入站5432连接。

4. 检查权限问题 #

• 确保Logstash进程对/etc/logstash/config/certs/http_ca.crt有读取权限，可执行chmod 644 /etc/logstash/config/certs/http_ca.crt
• 避免以root用户运行Logstash，创建专用用户并调整目录权限

5. 开启调试日志定位问题 #

启动Logstash时添加--log.level debug参数，查看详细日志输出，定位是否存在JDBC连接失败、数据读取错误或Elasticsearch写入失败的细节。

6. 验证Elasticsearch写入权限 #

在Kibana Dev Tools中执行以下命令，确认elastic用户能创建索引：

PUT /contacts

若创建失败，需调整Elasticsearch的角色权限。

关于cacert参数的说明 #

Elasticsearch启用HTTPS时，Logstash输出到ES必须指定CA证书验证服务器身份，因此添加cacert参数是必要操作，无需担心。

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