Step by step recipe for setting up the river with PostgreSQL

JDBC River and PostgreSQL

1. Install PostgreSQL

   Example: PostgreSQL .dmg (Version 9.1.5) for Mac OS X from http://www.enterprisedb.com/products-services-training/pgdownload

   Filename: postgresql-9.1.5-1-osx.dmg

2. Install Elasticsearch

   Follow instructions on http://elasticsearch.org

3. Install JDBC River plugin

   Check for the latest JDBC river version under http://github.com/jprante/elasticsearch-river/jdbc - current version is 1.3.2

    cd $ES_HOME
    ./bin/plugin -install jprante/elasticsearch-river-jdbc/1.3.2
   

4. Download PostgreSQL JDBC driver

   Check http://jdbc.postgresql.org/download.html

   Current version is JDBC4 Postgresql Driver, Version 9.1-902

   Filname postgresql-9.1-902.jdbc4.jar

5. Copy driver into river folder

   The reason is to include the JDBC driver into the Java classpath.

    cp postgresql-9.1-902.jdbc4.jar $ES_HOME/plugins/river-jdbc/
   

6. Start Elasticsearch

   Just in the foreground to follow log messages on the console.

    cd $ES_HOME
    ./bin/elasticsearch -f
   

   Check if the river is installed correctly, Elasticsearch announces it in the second line logged. It must show loaded [jdbc-river].

    [2012-09-08 22:32:26,390][INFO ][node                     ] [Williams, Eric] {0.19.9}[14632]: initializing ...
    [2012-09-08 22:32:26,400][INFO ][plugins                  ] [Williams, Eric] loaded [jdbc-river], sites []
   

7. Create JDBC river

   This is just a basic example to a database test with user fred and password secret. The easiest method is using curl for a river creation via the REST interface. Use the port configured during PostgreSQL installation. The default is 5432.

   curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
              "type" : "jdbc",
              "jdbc" : {
                  "driver" : "org.postgresql.Driver",
                  "url" : "jdbc:postgresql://localhost:5432/test",
                  "user" : "fred",
                  "password" : "secret"
              },
              "index" : {
                  "index" : "jdbc",
                  "type" : "jdbc"
              }
          }'
   

8. Check log messages

   In case the user does not exist, Elasticsearch will log a message like this:

   [2012-09-08 22:32:32,485][INFO ][river.jdbc               ] [Williams, Eric] [jdbc][my_jdbc_river] starting JDBC connector: URL [jdbc:postgresql://localhost:5432/test], driver [org.postgresql.Driver], sql [null], river table [false], indexing to [jdbc]/[jdbc], poll [5m]
   [2012-09-08 22:32:32,549][ERROR][river.jdbc               ] FATAL: password authentication failed for user "fred"
   org.postgresql.util.PSQLException: FATAL: password authentication failed for user "fred"
           at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
           at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:106)
           at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
           at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:123)
           at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:28)
           at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:20)
           at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
           at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:22)
           at org.postgresql.Driver.makeConnection(Driver.java:391)
           at org.postgresql.Driver.connect(Driver.java:265)
           at java.sql.DriverManager.getConnection(DriverManager.java:582)
           at java.sql.DriverManager.getConnection(DriverManager.java:185)
           at org.elasticsearch.river.jdbc.SQLService.getConnection(SQLService.java:103)
           at org.elasticsearch.river.jdbc.JDBCRiver$JDBCConnector.run(JDBCRiver.java:194)
           at java.lang.Thread.run(Thread.java:680)
   

   Even though PostgreSQL refused the access - the river can connect to PostgreSQL successfully!

9. Repeat River creation until your configuration is optimal.