如何使用 Logstash 和 JDBC 确保 Elasticsearch 与 postgres 保持数据同步

前提：已经安装好 elasticsearch ，kibana ， Logstash 和 postgres ，并且 elasticsearch ， kibana 和 postgres 已经正常运行

1.数据库中创建表

DROP TABLE IF EXISTS es_table;
CREATE TABLE es_table (
  id integer PRIMARY KEY  NOT NULL UNIQUE,
  client_name VARCHAR(32) NOT NULL,
  modification_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2.数据库中创建函数

create or replace function upd_timestamp() returns trigger as
$$
begin
    new.modification_time = current_timestamp;
    return new;
end
$$
language plpgsql;

3.数据库中设置触发器

create trigger updated_date before update on es_table for each row execute procedure upd_timestamp();

4.数据库中插入数据

INSERT INTO es_table (id, client_name) VALUES (110, 'wys');

5.数据库中修改 client_name 字段

update es_table set client_name='wwwww' where client_name='wys'

6.通过以下语句查询数据，发现 modification_time 字段值发生了变化

select * from es_table

7.安装 Logstash ，可参考：blog.csdn.net/UbuntuTouch…

8.下载驱动，可参考：jdbc.postgresql.org/download.ht… 我的是 postgresql-42.2.14.jar ，放入 logstash-7.3.0/logstash-core/lib/jars 下面

9.在 logstash-7.3.0/config 下面新建文件 test.conf

input {
    stdin {

    }
    jdbc {
        jdbc_driver_library => '../logstash-core/lib/jars/postgresql-42.2.14.jar'
        jdbc_driver_class => 'org.postgresql.Driver'
        jdbc_connection_string => 'jdbc:postgresql://10.5.1.110:5432/address_manage'
        jdbc_user => '账号'
        jdbc_password => '密码'
        use_column_value => 'true'
        tracking_column => 'modification_time'
        tracking_column_type => 'timestamp'
        record_last_run => 'true' 
        last_run_metadata_path => 'last_run'  # 提前创建好
        clean_run => false
        jdbc_validate_connection => 'true'
        schedule => '*/5 * * * * *'
        statement => 'SELECT *  FROM es_table WHERE modification_time > :sql_last_value AND modification_time < NOW() ORDER BY modification_time ASC'
    }
}

filter {
    ruby {
            code => 'event.timestamp.time.localtime'
    }
    mutate {
            copy => { 'id' => '[@metadata][_id]'}
            remove_field => ['id', '@timestamp', '@version']
    }
}

output {
    stdout {

    }
    elasticsearch {
        hosts => ['localhost:9200']
        index => 'es_table'
        document_type => '_doc'
        user => '账号'
        password => '密码'
        document_id => '%{[@metadata][_id]}'
    }
}

10.通过 kibana 创建好索引

PUT /es_table
{
"settings":{
"analysis":{
"tokenizer":{
"sp_tokenizer":{
"type":"pinyin",
"keep_first_letter":true,
"keep_separate_first_letter":true,
"keep_full_pinyin":false,
"keep_original":false,
"limit_first_letter_length":128,
"lowercase":true
},
"qp_tokenizer":{
"type":"pinyin",
"keep_first_letter":false,
"keep_separate_first_letter":false,
"keep_full_pinyin":true,
"keep_original":false,
"limit_first_letter_length":128,
"lowercase":true
}
},
"analyzer":{
"standard_analyzer":{
"tokenizer":"standard"
},
"sp_analyzer":{
"tokenizer":"sp_tokenizer"
},
"qp_analyzer":{
"tokenizer":"qp_tokenizer"
},
"ik_analyzer":{
  "tokenizer":"ik_max_word"
},
"ik_search_analyzer":{
  "tokenizer":"ik_smart"
}
}
}
},
"mappings":{
"properties":{
"id":{
"type":"keyword"
},
"client_name":{
"type":"text",
"analyzer": "ik_analyzer"
},
"modification_time":{
"type":"keyword"
}
}
}
}

11.保存之后，执行如下命令，执行完之后，查看 es 中如果有数据导入则成功，否则失败

./bin/logstash -f ./config/test.conf