# Logstash to ES with postgres

**URL:** <https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080>\
**Category:** Logstash\
**Created:** [March 22, 2016, 8:20am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080 "2016-03-22T08:20:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jpuch](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@Jpuch](https://discuss.elastic.co/u/Jpuch)\
**Post date:** [March 22, 2016, 8:20am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/1 "2016-03-22T08:20:13Z")

</div>

Hi there,

I'm trying to set up an ELK system to have a better overview over some data.  
I have to flash code scanne which fill up a PostgreSQL database (Date - Hour - ID ect)

So i installed ELK follywing a random tuto btu i'm stuck on sending data to ES by logstash :

firstly i created an index in elastic search :  
**curl -X PUT [http://localhost:9200/production](http://localhost:9200/production) -d '{"index.mapper.dynamic" : true}'**

**curl -X PUT [http://localhost:9200/production/\_mapping/prod](http://localhost:9200/production/_mapping/prod) -d '**  
**{**  
\*\* "prod":{\*\*  
\*\* "properties": {\*\*  
\*\* "idtraca\_datas":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "date\_code":{\*\*  
\*\* "index" : "analyzed",\*\*  
\*\* "type" : "date"\*\*  
\*\* },\*\*  
\*\* "heure\_data":{\*\*  
\*\* "index" : "analyzed",\*\*  
\*\* "type" : "date"\*\*  
\*\* },\*\*  
\*\* "code\_produit":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "num\_lot":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "code\_lu":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "id\_code":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "ligne":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* },\*\*  
\*\* "orientation":{\*\*  
\*\* "index" : "not\_analyzed",\*\*  
\*\* "type" : "string"\*\*  
\*\* }\*\*  
\*\* }\*\*  
\*\* }\*\*  
**}'**

Then, with this logstash conf :

**# file: ES\_logstash.conf**  
**input {**  
\*\* jdbc {\*\*  
\*\* jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5432/production"\*\*  
\*\* jdbc\_user =\> "test"\*\*  
\*\* jdbc\_password =\> " **"**  
\*\* jdbc\_validate\_connection =\> true\*\*  
\*\* jdbc\_driver\_library =\> "/home/test/Documents/postgresql-9.4.1208.jre7.jar"\*\*  
\*\* jdbc\_driver\_class =\> "org.postgresql.Driver"\*\*  
\*\* statement =\> 'SELECT \* from "Traca\_Datas" limit 1'\*\*  
\*\* }\*\*  
**}**

**output {**  
\*\* elasticsearch {\*\*  
\*\* hosts =\> "localhost:9200"\*\*  
\*\* document\_id =\> "%{uid}"\*\*  
\*\* index =\> "production"\*\*  
\*\* document\_type =\> "prod"\*\*  
\*\* }\*\*  
**}**

I got this error message :

**{:timestamp=\>"2016-03-22T09:16:13.539000+0100", :message=\>"Failed action. ", :status=\>400, :action=\>["index", {:\_id=\>"%{uid}", :\_index=\>"production", :\_type=\>"prod", :\_routing=\>nil}, #\<LogStash::Event:0x65783cd8 @metadata\_accessors=#\<LogStash::Util::Accessors:0x3653c1ed @store={}, @lut={}\>, @cancelled=false, @data={"idtraca\_datas"=\>15, "date\_code"=\>#\<Date: 2013-07-29 ((2456503j,0s,0n),+0s,2299161j)\>, "heure\_data"=\>"2016-03-22T17:22:23.020Z", "id\_code"=\>"Code 128", "code\_lu"=\>"00082", "ligne"=\>"L1", "orientation"=\>169, "code\_produit"=\>"74123", "num\_lot"=\>"74123", "@version"=\>"1", "@timestamp"=\>"2016-03-22T08:16:10.687Z"}, @metadata={}, @accessors=#\<LogStash::Util::Accessors:0x172c38ef @store={"idtraca\_datas"=\>15, "date\_code"=\>#\<Date: 2013-07-29 ((2456503j,0s,0n),+0s,2299161j)\>, "heure\_data"=\>"2016-03-22T17:22:23.020Z", "id\_code"=\>"Code 128", "code\_lu"=\>"00082", "ligne"=\>"L1", "orientation"=\>169, "code\_produit"=\>"74123", "num\_lot"=\>"74123", "@version"=\>"1", "@timestamp"=\>"2016-03-22T08:16:10.687Z"}, @lut={"uid"=\>[{"idtraca\_datas"=\>15, "date\_code"=\>#\<Date: 2013-07-29 ((2456503j,0s,0n),+0s,2299161j)\>, "heure\_data"=\>"2016-03-22T17:22:23.020Z", "id\_code"=\>"Code 128", "code\_lu"=\>"00082", "ligne"=\>"L1", "orientation"=\>169, "code\_produit"=\>"74123", "num\_lot"=\>"74123", "@version"=\>"1", "@timestamp"=\>"2016-03-22T08:16:10.687Z"}, "uid"]}\>\>], :response=\>{"index"=\>{"\_index"=\>"production", "\_type"=\>"prod", "\_id"=\>"%{uid}", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [date\_code]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: "2013-07-29 00:00:00 +0200" is malformed at " 00:00:00 +0200""}}}}, :level=\>:warn}**

I have an invalid format on the date, but how can i resolve it ? and if you guys found other mistake thanks for telling me.

Thanks for readin and for your help.

Josselin

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [March 22, 2016, 8:29am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/2 "2016-03-22T08:29:10Z")

</div>

I'd put a date filter in normalise it and stop this, take a look at [https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html](https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html)

---

<div class="post-metadata">

**Author:** ![Jpuch](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@Jpuch](https://discuss.elastic.co/u/Jpuch)\
**Post date:** [March 22, 2016, 10:14am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/3 "2016-03-22T10:14:38Z")

</div>

Ok now my trasfert from logstash to ES work but i only have the last entry of my database.  
In the statement of logstash.conf i put the entire table "select \* from "Traca\_Datas" but i only have the last ligne of the table in kibana.

**idtraca\_datas:251186date\_code:March 7th 2016, 00:00:00.000heure\_data:March 22nd 2016, 12:45:37.330id\_code:Code 128code\_lu:115940875492ligne:L1orientation:179code\_produit:79650u2num\_lot:594000@version:1@timestamp:March 22nd 2016, 11:07:37.727\_id:%{uid}\_type:prod\_index:production\_score:1**

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 22, 2016, 10:17am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/4 "2016-03-22T10:17:33Z")

</div>

> [@Jpuch](#):
>
> \_id:%{uid}

It looks like Logstash can not find a `uid` field to pick the ID from and therefore ends up setting the format string as the Id on all records, causing each to update the previous. Check your output and verify that you have successfully parsed this field and that it is all lower case.

It is usually easier to debug these type of issues if you send the output to stdout using the ruby debug codec until you have verified that the document has the structure you expect.

---

<div class="post-metadata">

**Author:** ![Jpuch](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@Jpuch](https://discuss.elastic.co/u/Jpuch)\
**Post date:** [March 23, 2016, 9:22am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/5 "2016-03-23T09:22:46Z")

</div>

Hi there,

Sorry but i've got a new problem.

Still got my postgres database with this Inside :

![](https://us1.discourse-cdn.com/elastic/original/2X/d/d74890c4519bdeb49d38e3a9a67a957a11ed8275.JPG)

this is my ES index :

> curl -X PUT [http://localhost:9200/ligneserac/\_mapping/serac](http://localhost:9200/ligneserac/_mapping/serac) -d '  
> {  
> "serac":{  
> "properties": {  
> "id":{  
> "index" : "not\_analyzed",  
> "type" : "string"  
> },  
> "scan\_date":{  
> "type" : "date",  
> "format" : "yyyy-MM-dd'T'HH:mm:ssZZ"  
> },  
> "scan\_cab":{  
> "index" : "not\_analyzed",  
> "type" : "string"  
> },  
> "line\_id":{  
> "index" : "not\_analyzed",  
> "type" : "string"  
> },  
> "nom\_product":{  
> "index" : "not\_analyzed",  
> "type" : "string"  
> },  
> "num\_lot":{  
> "index" : "not\_analyzed",  
> "type" : "string"  
> }  
> }  
> }  
> }'

The problem is :  
when i don't add a date format - ma data seem to not be transfered to ES  
when i use this format : "yyyy-MM-dd'T'HH:mm:ssZZ" - i have this error :

 ![](https://us1.discourse-cdn.com/elastic/original/2X/8/8fd7dfaefaf47b7b4efe6a1a527e98bdf467412e.JPG)

When i use the same format but without the 'T' : "yyyy-MM-dd HH:mm:ssZZ" - i got this error on each entry of my database :

 ![](https://us1.discourse-cdn.com/elastic/original/2X/a/a7574117939878cd90f7d94f9bb51495e2663a8a.JPG)

The only solution i found is to create the index withotu the time based event but i need it so 😕

Thanks for reading,

Josselin

---

<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:** [July 6, 2017, 5:05am UTC](https://discuss.elastic.co/t/logstash-to-es-with-postgres/45080/6 "2017-07-06T05:05:45Z")

</div>