Postgres 8.4 on EC2 Ubuntu 10

Followed this blog

Basic Steps:

$ sudo apt-get install postgresql

and changed the password:

$ sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD '<password>';
template1=# \q

The # symbol indicates that you are using the superuser account. Next we change the password for the database and Linux user called postgres. Use the same password as above to avoid confusion.

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

Enter a new or same password as above.


Postgres & PLJava continued

ok, so progressing. Found that pljava was already part of the Postgres install on OSX. It is under the /Library/Postgres/8.4/lib/postgresql directory

Setting up the environment is proving to be a little tricky. There are many posts explaining that you need a lot of stuff. In the end I needed to add the following three lines to the postgres.conf file, which is in the /Library/Postgres/8.4/data directory. Except that this directory is off limits for users other than postgres. So I sudo to it,

sudo vi postgresql.conf

Once in, I added this at the end of the file:

custom_variable_classes = ‘pljava’
pljava.classpath = ‘/Library/PostgreSQL/8.4/lib/postgresql/pljava.jar’
dynamic_library_path = ‘$libdir:/Library/PostgreSQL/8.4/lib/postgresql’

NB.!!!  – Careful of Typos and remember the ” ‘ ”

The docos say to change the LD_LIBRARY_PATH in Linux, but a quick tour of Google showed that this is actually DYLD_LIBRARY_PATH under OSX. However this article seems to indicate that it is not needed, so I left it

Next step is to run the PLJava installer under /Library/PostgresSQL/8.4/share/pljava/pljava.sql. This set up the environment for clients like pgadmin.

Test the install by running

SELECT sqlj.install_jar(‘file:///Library/PostgreSQL/8.4/share/pljava/examples.jar’, ‘samples’,  true);

If it works the query will return – install_jar void –

next is to now test the actual java code ….

Postgres & Java

I’m looking into the PLJava package for Postgres. Looks interesting from what I have read already and may open a new level of integration. Some of the copelling reasons are from the readme….

PL/Java is designed with the objective to enable the power of Java to the database itself so that database intensive business logic can execute as close to the actual data as possible.

The PL/Java 1.2.0 release of PL/Java provides the following features.

1. Ability to write both functions and triggers using Java 1.4 or higher.
2. Standardized utilities (modeled after the SQL 2003 proposal) to install and maintain Java code in the database.
3. Standardized mappings of parameters and result. Complex types as well as sets are supported.
4. An embedded, high performance, JDBC driver utilizing the internal PostgreSQL SPI routines.
5. Metadata support for the JDBC driver. Both DatabaseMetaData and ResultSetMetaData is included.
6. The ability to return a ResultSet that origins from a query as an alternative to build a ResultSet row by row
7. Full support for PostgreSQL 8.0 savepoints and exception handling.
8. Ability to use IN, INOUT, and OUT parameters when used with PostgreSQL 8.1
9. Two language handlers, one TRUSTED (the default) and one that is not TRUSTED (language tag is javaU to conform with the defacto standard)
10. Transaction and Savepoint listeners enabling code execution when a transaction or savepoint is commited or rolled back.
11. Integration with GNU GCJ on selected platforms.

Watch this space for developments…..