Introduction to Greenplum

Greenplum Database is actually an array of individual database instances running on different servers or hosts, all working together to present a single database image. The master is the entry point to the Greenplum Database system. It is the database instance where users and client applications connect and execute SQL commands. The master coordinates the work amongst the other database instances in the system—the segments, which is where the data resides. Since Greenplum Database is based on PostgreSQL^?, end-users interact with Greenplum Database (through the master) using JDBC Drivers for PostgresSQL^?

JDBC Driver

Java Database Connectivity (JDBC) is an API for connecting programs written in Java to database management systems (DBMS). The API lets you encode access request statements in SQL that are then passed to the database server.

The PostgreSQL^? driver for JDBC is called pgjdbc and can be downloaded from:

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

Documentation for the JDBC driver is located at:

- http://jdbc.postgresql.org/documentation/82/index.html

It is important to choose the correct driver version based on the Java Runtime your application is based on. Below is a quick summary from postgresql website

• JDK 1.4, 1.5 - JDBC 3. This contains support for SSL and javax.sql, but does not require J2EE as it has been added to the J2SE release.
• JDK 1.6 - JDBC4. Support for JDBC4 methods is limited. The driver builds, but the majority of new methods are stubbed out.

Deployment of Postgresql Driver in Repertoire Server

1. Once you have identify the correct version of the Postgresql driver, the system administrator of Repertoire Server would need to deploy the Postgresql driver into the <RepertoireServer7>/ext folder.
2. The Repertoire Server needs to be restarted. ( Note that for using Query Builder throught Remote Designer, the postgresql driver DO NOT need to copy to the client's JRE environment.

Allowing external client connection to Greenplum Database

By default, connection to greenplum database is restricted to only localhost. Configuration files needs to be modified before a successful connection can be achieved. Failure to do so would result in the following Java exception

Caused by: org.postgresql.util.PSQLException: FATAL: missing or erroneous pg_hba.conf file
   at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:276)
   at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:95)
   at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
   at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
   at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
   at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:29)
   at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
   at org.postgresql.Driver.makeConnection(Driver.java:386)
   at org.postgresql.Driver.connect(Driver.java:260)
   at com.elixirtech.data2.datasource.jdbc.JDBCConnector$Driver.getConnection(Unknown Source)
   ... 23 more

Therefore the following configuration file changes need to be executed.

• postgresql.conf - Insert

... listen_addresses = '*' // This allow Greenplum Database to accept connection from all IP address ...

• pg_hba.conf - Insert

... host all all <your ip address>/32 md5 // This allow <your ip address> to connect to Greenplum Database. ...

• Once these configuration files are modified, the Greenplum database needs to be restarted.

Creating a JDBC Datasource to connect to Greenplum Database

Now a JDBC Datasource can be created to connect to Greenplum Database.

1. Create JDBC Datasource
2. Choose the postgres driver
3. Change the connecting URL to jdbc:postgresql://<your host>:<your port>/<your database name>
4. Connection should now be establised.

-- IrwinTan - 16 Apr 2008