Logstash版本：7.6.2
Elasticsearch版本：7.6.2 # ELK版本保持一致
Postgresql版本：12.1
系统版本：centos7
ELK不要以root用户启动,最好单独建立对应的用户，给该用户授权进行登录。
1.在logstash安装目录的bin目录中 创建存放conf文件、sql文件、es模板以及pg库连接jar包
mkdir pgsql
下面就是各文件的详细配置
1.logstash-pgsql.conf配置文件
input {
stdin {
}
jdbc {
#pg库的连接地址
jdbc_connection_string => "jdbc:postgresql://192.168.0.17:5432/order_goods"
#pg库的账号密码
jdbc_user => "postgres"
jdbc_password => "123456"
#地址根据自身填写
jdbc_driver_library => "/home/logstash-7.6.2/bin/pgsql/postgresql-42.2.19.jar"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "300000"
use_column_value => "true"
#根据update_time字段更新
tracking_column => "update_time"
#字段类型
tracking_column_type => "timestamp"
last_run_metadata_path => "./logstash_capital_bill_last_id"
clean_run => false
lowercase_column_names => false
statement_filepath => "/home/logstash-7.6.2/bin/pgsql/logstash-pgsql.sql"
schedule => "* * * * *"
type => "jdbc"
jdbc_default_timezone =>"Asia/Shanghai"
}
}
filter {
json {
source => "message"
remove_field => ["message"]
}
}
output {
elasticsearch {
#es对应的地址
hosts => ["http://192.168.0.17:9200"]
#索引名称
index => "user_order"
#模板地址
template => "/home/logstash-7.6.2/bin/pgsql/es-template.json"
template_name => "t-statistic-out-logstash"
template_overwrite => true
document_id => "%{id}"
}
stdout {
codec => json_lines
}
}
1.2.logstash-pgsql.sql
select id, user_id,payment,remark,update_time from order_goods
1.3.es-template.json
{
"template" : "t-statistis-out-template",
"order":1,
"settings": {
"index": {
"refresh_interval": "5s"
}
},
"mappings": {
"_default_": {
"_all" : {"enabled":false},
"dynamic_templates": [{
"message_field" : {
"match" : "message",
"match_mapping_type" : "string",
"mapping" : { "type" : "string", "index" : "not_analyzed" }
}
},{
"string_fields" : {
"match" : "*",
"match_mapping_type" : "string",
"mapping" : { "type" : "string", "index" : "not_analyzed" }
}
}],
"properties": {
"id":{
"type":"integer"
},
"user_id":{
"type":"integer"
},
"payment":{
"type":"double"
},
"remark":{
"type":"keyword"
}
}
}
},
"aliases": {}
}
1.4.jar包下载地址，根据自己的版本在网上查对应的jar包
2.1启动logstash
在logstash的bin目录输入
./logstash -f pgsql/logstash-pgsql.conf
觉得有用的话，不点个赞吗？ 都是干货啊！！！
