setting up elasticSearch with Postgresql

Question

where do I find a howto to set up elasticSearch using Postgres?

My field sizes will be about 350mb, yes, MB, each in size. I have a text output of all of the US Code and all decisions from all the courts, the Statutes at Large, pretty much everything you would find in a library, and I need to be able to do full text searches and return the exact point in the field to the app to return the exact page in PDF form. Postgres can easily handle the datastore, but I've never used elasticSearch and have no idea of how it integrates into the indexing, etc.

Solution 1

As of 2015, there's ZomboDB (https://github.com/zombodb/zombodb). As the author, I'm a bit biased, but it's quite powerful. ;)

It's a Postgres extension and Elasticsearch plugin that allows you to "CREATE INDEX"s that use a remote Elasticsearch cluster, and it exposes a fairly powerful query language for performing full-text searches.

Because it's an actual index in Postgres, the ES cluster is automatically synchronized as you INSERT/UPDATE/DELETE records. As such, there's no need for asynchronous synchronization processes.

Additionally, because it's an actual index, it is transaction-safe, which means concurrent Postgres sessions will only see results that are consistent with their current transaction.

Here's a link to ZomboDB's tutorial. It should give you an idea of how easy ZomboDB is to use.

Solution 2

There is an application that you can use to import SQL Server, Oracle, Postgresql MySQL, etc. in to an ElasticSearch index.

http://code.google.com/p/ogr2elasticsearch/

Please let me know if you have any trouble building or using it. ~Adam

Solution 3

You can explore using pgsync.

PGSync is an open-source middleware (written in python) for syncing data from Postgres to Elasticsearch effortlessly. It allows you to keep Postgres as your source of truth and expose structured denormalized documents in Elasticsearch.

Githib link: https://github.com/toluaina/pgsync

Comments

[PirateApp]

Does it have to be elastic search? postgres s full text search wont work for you?

[Rizwan Patel]

how to bulk import from postgres to elasticsearch on windows using zombodb

[Eric B. Ridge]

Sorry, I don't monitor StackOverflow. ZomboDB doesn't support Windows. Pull Requests are welcome at github.com/zombodb/zombodb

[MrMesees]

What about only bringing in records based on rules to a single elasticsearch index / repository? At work we're doing this with Rails and it's AWFUL (30 minutes to index) even using preload (set based ops). Problem is that it's two Postgres logical databases which cannot be joined (something something shared nothing bull-crap).

[EgoPingvina]

@EricB.Ridge can I use postgresql adater for EF Core with ZomboDB, or I should write all sql manuallly?