与ElasticSearch同步postgreSql数据 [英] Sync postgreSql data with ElasticSearch

问题描述

最终我想为PostgreSql中的数据提供可扩展的搜索解决方案。我的发现指出，我使用Logstash将写事件从Postgres发送到ElasticSearch，但是我还没有找到可用的解决方案。我发现的解决方案涉及使用jdbc-input在一段时间内从Postgres查询所有数据，并且删除事件不被捕获。

我认为这是一个常见的用例，所以我希望你们能够与我分享你的经验，或者给我一些指导。

解决方案

如果您还需要在DELETE上通知并删除Elasticsearch中的相应记录，则Logstash jdbc输入确实不会有帮助。您必须使用解决方案围绕binlog作为建议在这里

但是，如果您仍然想使用Logstash jdbc输入，您可以做的只是在PostgreSQL中软删除记录，即创建一个新的BOOLEAN列为了将您的记录标记为删除。在弹性搜索中会存在相同的标记，您可以在中删除​​中简单的术语查询，将其从您的搜索中排除。字段。

每当您需要执行一些清理时，您可以删除PostgreSQL和Elasticsearch中标记为的所有记录 。

Ultimately I want to have a scalable search solution for the data in PostgreSql. My finding points me towards using Logstash to ship write events from Postgres to ElasticSearch, however I have not found a usable solution. The soluions I have found involve using jdbc-input to query all data from Postgres on an interval, and the delete events are not captured.

I think this is a common use case so I hope you guys could share with me your experience, or give me some pointers to proceed.

解决方案

If you need to also be notified on DELETEs and delete the respective record in Elasticsearch, it is true that the Logstash jdbc input will not help. You'd have to use a solution working around the binlog as suggested here

However, if you still want to use the Logstash jdbc input, what you could do is simply soft-delete records in PostgreSQL, i.e. create a new BOOLEAN column in order to mark your records as deleted. The same flag would then exist in Elasticsearch and you can exclude them from your searches with a simple term query on the deleted field.

Whenever you need to perform some cleanup, you can delete all records flagged deleted in both PostgreSQL and Elasticsearch.

这篇关于与ElasticSearch同步postgreSql数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！