# Trying to send postgresql query from Logstash to ElasticSearch
**URL:** https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113
**Category:** Logstash
**Created:** [November 2, 2017, 3:02am UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113 "2017-11-02T03:02:19Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@a\_dom](https://discuss.elastic.co/u/a_dom)
#### Post date: [November 2, 2017, 3:02am UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113/1 "2017-11-02T03:02:19Z")
Hi I'm following this tutorial ([https://www.elastic.co/blog/logstash-jdbc-input-plugin](https://www.elastic.co/blog/logstash-jdbc-input-plugin)) and I managed to recreate example 1 using my own table. But example 2 give me the following error and stops.
./bin/logstash -f logstash-simple.conf
Sending Logstash's logs to / **/** /logstash-5.6.3/logs which is now configured via log4j2.properties
[2017-11-01T19:48:29,266][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"fb\_apache", :directory=\>"/ **/** /logstash-5.6.3/modules/fb\_apache/configuration"}
[2017-11-01T19:48:29,276][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"netflow", :directory=\>"/ **/** /logstash-5.6.3/modules/netflow/configuration"}
[2017-11-01T19:48:29,930][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=\>{:removed=\>[], :added=\>[[http://localhost:9200/](http://localhost:9200/)]}}
[2017-11-01T19:48:29,932][INFO][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck\_url=\>[http://localhost:9200/](http://localhost:9200/), :path=\>"/"}
[2017-11-01T19:48:30,106][WARN][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=\>"[http://localhost:9200/](http://localhost:9200/)"}
[2017-11-01T19:48:30,168][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=\>nil}
[2017-11-01T19:48:30,172][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage\_template=\>{"template"=\>"logstash-_", "version"=\>50001, "settings"=\>{"index.refresh\_interval"=\>"5s"}, "mappings"=\>{"default"=\>{"\_all"=\>{"enabled"=\>true, "norms"=\>false}, "dynamic\_templates"=\>[{"message\_field"=\>{"path\_match"=\>"message", "match\_mapping\_type"=\>"string", "mapping"=\>{"type"=\>"text", "norms"=\>false}}}, {"string\_fields"=\>{"match"=\>"_", "match\_mapping\_type"=\>"string", "mapping"=\>{"type"=\>"text", "norms"=\>false, "fields"=\>{"keyword"=\>{"type"=\>"keyword", "ignore\_above"=\>256}}}}}], "properties"=\>{"@timestamp"=\>{"type"=\>"date", "include\_in\_all"=\>false}, "@version"=\>{"type"=\>"keyword", "include\_in\_all"=\>false}, "geoip"=\>{"dynamic"=\>true, "properties"=\>{"ip"=\>{"type"=\>"ip"}, "location"=\>{"type"=\>"geo\_point"}, "latitude"=\>{"type"=\>"half\_float"}, "longitude"=\>{"type"=\>"half\_float"}}}}}}}}
[2017-11-01T19:48:30,191][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=\>"LogStash::Outputs::ElasticSearch", :hosts=\>["[//localhost:9200](https://localhost:9200)"]}
[2017-11-01T19:48:30,195][INFO][logstash.pipeline] Starting pipeline {"id"=\>"main", "pipeline.workers"=\>4, "pipeline.batch.size"=\>125, "pipeline.batch.delay"=\>5, "pipeline.max\_inflight"=\>500}
[2017-11-01T19:48:30,318][INFO][logstash.pipeline] Pipeline main started
[2017-11-01T19:48:30,395][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600}
[2017-11-01T19:48:32,283][INFO][logstash.inputs.jdbc] (0.572000s) SELECT \* from contact where money \< 80000
[2017-11-01T19:48:33,354][WARN][logstash.agent] stopping pipeline {:id=\>"main"}
My config file is the following:
# file: simple-out.conf
input {
jdbc {
# Postgres jdbc connection string to our database, mydb
jdbc\_connection\_string =\> "jdbc:postgresql://_ **:5432/**"
# The user we wish to execute our statement as
jdbc\_user =\> "_**_"
jdbc\_password =\> "_**_"
# The path to our downloaded jdbc driver
jdbc\_driver\_library =\> "/_ **_/_** /postgresql-42.1.4.jar"
# The name of the driver class for Postgresql
jdbc\_driver\_class =\> "org.postgresql.Driver"
# our query
statement =\> "SELECT \* from contact where money \< 80000"
}
}
output {
elasticsearch {
index =\> "contact"
document\_type =\> "appr"
document\_id =\> "%{app\_id}"
hosts =\> ["localhost:9200"]
}
}
I'm very new to Elastic stack, could you please help me?
Thank you
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 2, 2017, 7:22am UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113/2 "2017-11-02T07:22:48Z")
What error? Logstash ran the query and then shut down gracefully.
---
### Author:  [@a\_dom](https://discuss.elastic.co/u/a_dom)
#### Post date: [November 2, 2017, 5:56pm UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113/3 "2017-11-02T17:56:43Z")
My output when using the stdout is the following:
$ ./bin/logstash -f logstash-simple.conf
Sending Logstash's logs to /Users/**/logstash-5.6.3/logs which is now configured via log4j2.properties
#output {
[2017-11-02T10:30:42,925][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"fb\_apache", :directory=\>"/Users/**/logstash-5.6.3/modules/fb\_apache/configuration"}
[2017-11-02T10:30:42,931][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"netflow", :directory=\>"/Users/\*\*/logstash-5.6.3/modules/netflow/configuration"}
[2017-11-02T10:30:43,163][INFO][logstash.pipeline] Starting pipeline {"id"=\>"main", "pipeline.workers"=\>4, "pipeline.batch.size"=\>125, "pipeline.batch.delay"=\>5, "pipeline.max\_inflight"=\>500}
[2017-11-02T10:30:43,294][INFO][logstash.pipeline] Pipeline main started
[2017-11-02T10:30:43,377][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600}
[2017-11-02T10:30:45,144][INFO][logstash.inputs.jdbc] (0.116000s) SELECT \* from contact where money \< 80000 limit 10
{"@version":"1","index":3216,"@timestamp":"2017-11-02T17:30:45.170Z","money":68033.4310594501,"number":5460026}
{"@version":"1","index":4835,"@timestamp":"2017-11-02T17:30:45.171Z","money":53538.41957209386,"number":7143299}
{"@version":"1","index":6052,"@timestamp":"2017-11-02T17:30:45.172Z","money":44350.55210621592,"number":2932773}
{"@version":"1","index":22989,"@timestamp":"2017-11-02T17:30:45.172Z","money":79892.37130223823,"number":588222}
{"@version":"1","index":33264,"@timestamp":"2017-11-02T17:30:45.173Z","money":73328.9295691638,"number":6032738}
{"@version":"1","index":33418,"@timestamp":"2017-11-02T17:30:45.173Z","money":49627.51134069792,"number":5421704}
{"@version":"1","index":37885,"@timestamp":"2017-11-02T17:30:45.174Z","money":70076.97784429585,"number":636201}
{"@version":"1","index":38237,"@timestamp":"2017-11-02T17:30:45.175Z","money":79949.64912025326,"number":716658}
{"@version":"1","index":49968,"@timestamp":"2017-11-02T17:30:45.175Z","money":75552.05490521724,"number":397120}
{"@version":"1","index":50016,"@timestamp":"2017-11-02T17:30:45.176Z","money":63148.863764502734,"number":417760}
[2017-11-02T10:30:46,313][WARN][logstash.agent] stopping pipeline {:id=\>"main"}
But when sending it to ElasticSearch and try:
GET contact/appr/1?
I get the following response:
{
"\_index": "contact",
"\_type": "appr",
"\_id": "1",
"found": false
}
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 3, 2017, 6:30am UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113/4 "2017-11-03T06:30:37Z")
You're setting the document id to the contents of the `app_id` field, but the log you posted above indicates that there is no `app_id` field in the documents.
Instead of getting a particular document, what if you search for all documents in the index?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 1, 2017, 6:42am UTC](https://discuss.elastic.co/t/trying-to-send-postgresql-query-from-logstash-to-elasticsearch/106113/5 "2017-12-01T06:42:44Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.