PostgreSQL 同步 Elasticsearch记录

elasticsearch安装

使用docker-compose安装elasticsearch服务

1. 首先安装docker-compose
2. 创建elasticsearch文件夹并在下面编辑docker-compose.yml

version: '2.2'
services:
  node01:
    image: docker.elastic.co/elasticsearch/elasticsearch:7.13.4
    container_name: node01
    environment:
      - node.name=node01
      - cluster.name=es-cluster
      - discovery.type=single-node
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    ulimits:
      memlock:
        soft: -1
        hard: -1
    volumes:
      - es-data01:/usr/share/elasticsearch/data
    ports:
      - 9200:9200
      - 9300:9300
    networks:
      - es-network

volumes:
  es-data01:
    driver: local
    driver_opts:
      type: none
      device: /root/elasticsearch/data
      o: bind
    #external: true

networks:
  es-network:
    driver: bridge
docker-compose up -d

数据将会持久化到/root/elasticsearch/data下。

当前配置为单节点 ， 如果以后要添加集群也很方便参考官方文档修改便是。

此处参考：https://quoeamaster.medium.com/deploying-elasticsearch-and-kibana-with-docker-86a4ac78d851

logstash安装并开启同步(CentOS 7 and JDK8)

#配置好logstash的yum源
*vim /etc/yum.repos.d/logstash.repo
[logstash-7.x]
name=Elastic repository for 7.x packages
baseurl=https://artifacts.elastic.co/packages/7.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

#安装
yum install logstash -y
#replace current version <https://jdbc.postgresql.org/download.html#current>
#安装pgsql-jdbc
curl <https://jdbc.postgresql.org/download/postgresql-{version}.jar> -o /usr/share/logstash/logstash-core/lib/jars/postgresql-jdbc.jar

#find config.support_escapes 修改
vim /etc/logstash/logstash.yml
config.support_escapes: true

#新建配置文件
vim /etc/logstash/conf.d/{config_name}.conf
#注意 currentSchema后面的参数指定数据源或者在statement里sql指定数据源
input {
  jdbc {
     jdbc_connection_string => "jdbc:postgresql://localhost:5432/db_chongqing?currentSchema=chongqing"
     jdbc_user => "postgres"
     jdbc_password => "password"
     jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/postgresql-jdbc.jar"
     jdbc_driver_class => "org.postgresql.Driver"
     statement => "SELECT * from chongqing.table"
     jdbc_paging_enabled => "true"
     jdbc_page_size => "300"
 }
}
output {
  stdout { codec => "json" }
  elasticsearch {
    hosts => ["<http://localhost:9200>"]
    index => "next_oddata_15min_f"
    document_id => "document_%{id}"
    doc_as_upsert => true
 }
}

#1. tmux查看跑入详情 启动
/usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/{config_name}.conf
#每天定时入库
crontab -e
0 7 * * * /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/next_oddata_15min_f_sync.conf >> ~/logstash.log*

参考资料：

https://medium.com/@emreceylan/how-to-sync-postgresql-data-to-elasticsearch-572af15845ad

https://gist.github.com/ErikNovak/186e6021cf30db9160c673ee3145629f

es可视化工具：https://github.com/qax-os/ElasticHD/releases