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.

Does it have to be elastic search? postgres s full text search wont work for you? — PirateApp
– PirateApp, Commented Mar 1, 2020 at 10:47

Eric B. Ridge · Accepted Answer · 2016-05-24 21:47:45Z

24

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.

edited May 24, 2016 at 21:47

answered Jan 3, 2016 at 19:23

Eric B. Ridge

2962 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rizwan Patel Over a year ago

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

Eric B. Ridge Over a year ago

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

MrMesees Over a year ago

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 Over a year ago

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

aeupinhere · Accepted Answer · 2011-09-07 18:29:41Z

4

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

answered Sep 7, 2011 at 18:29

aeupinhere

3,0337 gold badges34 silver badges42 bronze badges

Comments

taina · Accepted Answer · 2020-10-24 15:57:57Z

1

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

answered Oct 24, 2020 at 15:57

taina

3413 silver badges6 bronze badges

Comments

Mike · Accepted Answer · 2022-02-08 17:58:38Z

0

Its possible to insert/update/delete postgres data in elasticsearch without middle ware other than the pgsql_http extension. Using triggers you can get a pretty much real-time index update.

You can also query elasticsearch and use the results within postgres to do joins etc with other tables/data in your database.

See the elasticsearch examples: https://github.com/sysadminmike/pgsql-http_examples

edited Feb 8, 2022 at 17:58

answered Feb 8, 2022 at 17:34

Mike

635 bronze badges

Collectives™ on Stack Overflow

setting up elasticSearch with Postgresql

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related