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 1.0.0.RC1

   Follow instructions on http://elasticsearch.org

3. Install JDBC River plugin 1.0.0.RC1.2

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

    cd $ES_HOME
    ./bin/plugin -install river-jdbc -url http://bit.ly/1dKqNJy
   

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
   

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

   [2014-01-22 23:00:06,821][INFO ][node ] [Julie Power] version[1.0.0.RC1], pid[26152], build[c6155c5/2014-01-15T17:02:32Z] [2014-01-22 23:00:06,841][INFO ][node ] [Julie Power] initializing ... [2014-01-22 23:00:06,932][INFO ][plugins ] [Julie Power] loaded [river-jdbc], 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" : {
                "url" : "jdbc:postgresql://localhost:5432/test",
                "user" : "fred",
                "password" : "secret",
                "sql" : "select * from orders"
            }
       }'

8. Check log messages

   In case the user does not exist, Elasticsearch will log a message.

9. Repeat River creation until the river runs fine.