# Transform large file from postgresql to elasticsearch

**URL:** <https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803>\
**Category:** Logstash\
**Tags:** elastic-stack-sql\
**Created:** [December 13, 2019, 2:31pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803 "2019-12-13T14:31:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pilo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pilo/32/46015_2.png) [@pilo](https://discuss.elastic.co/u/pilo)\
**Post date:** [December 13, 2019, 2:31pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/1 "2019-12-13T14:31:34Z")

</div>

Hi everybody  
I have an issue when dealing with large table in postgresql. I have a table with about 1 millions rows, each row contains some text, about half of A4 page of length. I want to index this table into elasticsearch. But i always got `java.lang.OutOfMemoryError: Java heap space`. I increased jvm heap size to 4Gb and i cant increase more. I also add jdbc\_page\_size options to my logstash config file but it doesn't work.

> ```
> input {
> jdbc {
> # Postgres jdbc connection string to our database, mydb
> jdbc_connection_string => "jdbc:postgresql://localhost:5432/jmdb"
> # The user we wish to execute our statement as
> jdbc_user => "xxx"
> # The path to our downloaded jdbc driver
> jdbc_driver_library => "${HOME}/postgresql-42.2.8.jar"
> # The name of the driver class for Postgresql
> jdbc_driver_class => "org.postgresql.Driver"
> jdbc_password => "xxx"
> jdbc_paging_enabled => true
> jdbc_page_size => 10000
> statement_filepath => "${INDEXING_DIRECTORY}/decision_index.sql"
> type => "decision"
> }
> }
> output {
> elasticsearch {
> index => "decision"
> }
> }
> 
> ```

Someone know how to solve this situation. Or maybe a way to monitor jdbc\_page\_size to know how many jdbc\_page\_size i need to not dump java heap size ?  
Thank you alot.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [December 13, 2019, 8:38pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/2 "2019-12-13T20:38:12Z")

</div>

try something like  
select \* from table where data between 01/01/2019 and 01/31/2019

and go one month at a time.

---

<div class="post-metadata">

**Author:** ![pilo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pilo/32/46015_2.png) [@pilo](https://discuss.elastic.co/u/pilo)\
**Post date:** [December 16, 2019, 9:17am UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/3 "2019-12-16T09:17:18Z")

</div>

Thanks for your help.  
But what if i dont have data column in my table. Can i use something else like id column ?

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [December 16, 2019, 4:46pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/4 "2019-12-16T16:46:46Z")

</div>

yes you can try  
id\_column \> 123454 something like this?

---

<div class="post-metadata">

**Author:** ![pilo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pilo/32/46015_2.png) [@pilo](https://discuss.elastic.co/u/pilo)\
**Post date:** [December 17, 2019, 8:58pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/5 "2019-12-17T20:58:52Z")

</div>

I applied your method by using schedule and it works. Thank you.  
But i'm wondering are there other way than using schedule, because i want logstash to shut down after doing all the jobs. But with schedule, i can't.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [December 18, 2019, 2:49pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/6 "2019-12-18T14:49:21Z")

</div>

I had one such request, and here is what I did.  
Created bash script on one of the elk node where I don't run logstash as daemon.  
run that bash script via cron, so it run every other hour and shut down

#cat test.bash  
/usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/my\_test.conf

and in this my\_test.conf I don't have schedule so it will run right away and shutdown after it finish

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 15, 2020, 2:49pm UTC](https://discuss.elastic.co/t/transform-large-file-from-postgresql-to-elasticsearch/211803/7 "2020-01-15T14:49:47Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.