Unable to load data from PostgreSQL to Elasticsearch via Logstash

Hello,

I'm trying to load data from PostgreSQL Server to Elasticsearch so as to use it in Kibana for visualization purpose. But while connecting it with Logstash, I'm not getting any index. I'm attaching my logstash configuration file for the review, and the output that I'm getting after running Logstash.

Please suggest some solution.

.conf file

input {
    jdbc {
        # Postgres jdbc connection string to our database, mydb
        jdbc_connection_string => "jdbc:postgresql://localhost:49526/<dbname>"
        # The user we wish to execute our statement as
        jdbc_user => "user"
	#password for the user
	jdbc_password => "password"
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "D:/KIBANA/postgresql-42.2.6.jar"
        # The name of the driver class for Postgresql
        jdbc_driver_class => "org.postgresql.Driver"
        # our query
        statement => "SELECT * from <dbname>"
	#sinceDB file path
	sincedb_path => "D:/KIBANA/"
    }
}
output {
	elasticsearch { protocol => http }
    stdout { codec => json_lines }
}

Following is the output that I'm getting after executing the .conf file:

D:\KIBANA\logstash-6.6.2\bin>logstash -f test7.conf
Sending Logstash logs to D:/KIBANA/logstash-6.6.2/logs which is now configured via log4j2.properties
[2019-07-03T16:57:58,779][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-07-03T16:57:58,808][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"6.6.2"}
*[2019-07-03T16:57:59,495][ERROR][logstash.agent           ] Failed to execute action* {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, input, filter, output at line 1, column 1 (byte 1) after ", :backtrace=>["D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/compiler.rb:49:in `compile_graph'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2486:in `map'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:in `initialize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline.rb:22:in `initialize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline.rb:90:in `initialize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline_action/create.rb:43:in `block in execute'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:94:in `block in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in `synchronize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:94:in `exclusive'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline_action/create.rb:39:in `execute'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:327:in `block in converge_state'"]}
[2019-07-03T16:57:59,795][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}

It is failing at the very first byte of the configuration file. You are using -f with a specific file, so the problem has to be in that file. Perhaps it has a byte order mark at the start?

1 Like

Thank you Badger. I removed the byte order mark from the file, but now it's showing that something is wrong with the configuration. Below is the configuration file that I'm using:

input {
    jdbc {
        # Postgres jdbc connection string to our database,
        jdbc_connection_string => "jdbc:postgresql://localhost:49526/<dbname>"
        # The user we wish to execute our statement as
        jdbc_user => "user"
	#password for the user
	jdbc_password => "password"
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "D:/KIBANA/postgresql-42.2.6.jar"
        # The name of the driver class for Postgresql
        jdbc_driver_class => "org.postgresql.Driver"
        # our query
        statement => "SELECT * from <dbname>"
    }
}
output {
	elasticsearch { protocol => http }
    stdout { codec => json_lines }
}

Following is the output that I'm getting after the execution:

Sending Logstash logs to D:/KIBANA/logstash-6.6.2/logs which is now configured via log4j2.properties
[2019-07-04T10:45:15,055][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-07-04T10:45:15,070][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"6.6.2"}
[2019-07-04T10:45:19,917][ERROR][logstash.outputs.elasticsearch] Unknown setting 'protocol' for elasticsearch
[2019-07-04T10:45:19,944][ERROR][logstash.agent           ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Something is wrong with your configuration.", :backtrace=>["D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/config/mixin.rb:86:in `config_init'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/outputs/base.rb:60:in `initialize'", "org/logstash/config/ir/compiler/OutputStrategyExt.java:224:in `initialize'", "org/logstash/config/ir/compiler/OutputDelegatorExt.java:48:in `initialize'", "org/logstash/config/ir/compiler/OutputDelegatorExt.java:30:in `initialize'", "org/logstash/plugins/PluginFactoryExt.java:235:in `plugin'", "org/logstash/plugins/PluginFactoryExt.java:181:in `plugin'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline.rb:71:in `plugin'", "(eval):12:in `<eval>'", "org/jruby/RubyKernel.java:994:in `eval'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline.rb:49:in `initialize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline.rb:90:in `initialize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline_action/create.rb:43:in `block in execute'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:94:in `block in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in `synchronize'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:94:in `exclusive'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/pipeline_action/create.rb:39:in `execute'", "D:/KIBANA/logstash-6.6.2/logstash-core/lib/logstash/agent.rb:327:in `block in converge_state'"]}
[2019-07-04T10:45:20,164][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}

The elasticsearch output does not have a protocol option. Remove that.

1 Like

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