02 PostgreSQL

Below are the steps required to integrate Postgres with uPortal

This was written for version 2.4.2 but should work on 2.5.* and earlier version of uPortal as well.

1. Download the latest Postgres http://www.postgresql.org/.
2. Make the postgresql executable, ensuring that you use the --with-java parameter to generate the postgresql.jar.
3. Create the database according to the postgres documents. Assume the name 'portal' was chosen for the following instructions.
4. Copy the postgresql.jar to the lib directory of your portal development directory.
5. Edit rdbm.properties and uncommment the lines for postgres. Modify the URL, user and password as appropriate:

   jdbcDriver=org.postgresql.Driver
   jdbcUrl=jdbc:postgresql://localhost:5432/portal
   jdbcUser=postgres
   jdbcPassword=

6. If you are using PersonDirs.xml, edit the file and enter:

   <driver>org.postgresql.Driver</driver>
   <url>jdbc:postgresql://localhost:5432/portal</url>
   <logonid>postgres</logonid>
   <logonpassword></logonpassword>

7. Start Postgres.
8. In your portal development directory, issue the command

   ant dbtest

   If it works correct you should see something like

   % ant dbtest
   Buildfile: build.xml
   
   prepare:
   
   compile:
        [UPC:copy] Copying 2 files to C:\portal\build\WEB-INF\classes\properties
   
   dbtest:
        [UPC:echo] Invoking DbTest
        [UPC:java] Database name: 'PostgreSQL'
        [UPC:java] Database version: '7.4.5'
        [UPC:java] Driver name: 'PostgreSQL Native Driver'
        [UPC:java] Driver version: 'PostgreSQL 7.4.5 JDBC3 with SSL (build 215)'
        [UPC:java] Driver class: 'org.postgresql.Driver'
        [UPC:java] Connection URL: 'jdbc:postgresql://localhost:5432/test'
        [UPC:java] User: 'postgres'
        [UPC:java] supportsANSI92EntryLevelSQL: true
        [UPC:java] supportsANSI92FullSQL: false
        [UPC:java] supportsCoreSQLGrammar: false
        [UPC:java] supportsExtendedSQLGrammar: false
        [UPC:java] supportsTransactions: true
        [UPC:java] supportsMultipleTransactions: true
        [UPC:java] supportsOpenCursorsAcrossCommit: false
        [UPC:java] supportsOpenCursorsAcrossRollback: false
        [UPC:java] supportsOpenStatementsAcrossCommit: true
        [UPC:java] supportsOpenStatementsAcrossRollback: true
        [UPC:java] supportsStoredProcedures: false
        [UPC:java] supportsOuterJoins: true
        [UPC:java] supportsFullOuterJoins: true
        [UPC:java] supportsLimitedOuterJoins: true
        [UPC:java] supportsBatchUpdates: true
        [UPC:java] supportsColumnAliasing: true
        [UPC:java] supportsExpressionsInOrderBy: true
        [UPC:java] supportsOrderByUnrelated: true
        [UPC:java] supportsPositionedDelete: false
        [UPC:java] supportsSelectForUpdate: true
        [UPC:java] supportsUnion: true
        [UPC:java] supportsUnionAll: true
   
        [UPC:java] getMaxColumnNameLength: 63
        [UPC:java] getMaxColumnsInIndex: 32
        [UPC:java] getMaxColumnsInOrderBy: 0
        [UPC:java] getMaxColumnsInSelect: 0
        [UPC:java] getMaxColumnsInTable: 1600
        [UPC:java] getMaxConnections: 8192
        [UPC:java] getMaxCursorNameLength: 63
        [UPC:java] getMaxIndexLength: 0
        [UPC:java] getMaxRowSize: 1073741824
        [UPC:java] getMaxStatements: 1
        [UPC:java] getMaxTableNameLength: 63
        [UPC:java] getMaxTablesInSelect: 0
        [UPC:java] getMaxUserNameLength: 63
        [UPC:java] getSearchStringEscape: \\
   
        [UPC:java] Table Types: INDEX,SEQUENCE,SYSTEM INDEX,SYSTEM TABLE,SYSTEM TOAST I
   NDEX,SYSTEM TOAST TABLE,SYSTEM VIEW,TABLE,TEMPORARY INDEX,TEMPORARY TABLE,VIEW
        [UPC:java] SQL Types: bool,bytea,char,name,int8,int2,int2vector,int4,regproc,te
   xt,oid,tid,xid,cid,oidvector,SET,pg_type,pg_attribute,pg_proc,pg_class,pg_shadow
   ,pg_group,pg_database,smgr,point,lseg,path,box,polygon,line,_line,float4,float8,
   abstime,reltime,tinterval,unknown,circle,_circle,money,_money,macaddr,inet,cidr,
   _bool,_bytea,_char,_name,_int2,_int2vector,_int4,_regproc,_text,_oid,_tid,_xid,_
   ...............

9. Edit properties/dbloader.xml and verify that there is an entry just like the database version you displayed in the last step:

   <db-type-mapping>
       <db-name>PostgreSQL</db-name>
       <db-version>7.4.5</db-version>
       <driver-name>PostgreSQL Native Driver</driver-name>
       <driver-version>PostgreSQL 7.4.5 JDBC3 with SSL (build 215)</driver-version>
       <type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
       <type><generic>VARCHAR</generic><local>VARCHAR</local></type>
       <type><generic>LONGVARBINARY</generic><local>BYTEA</local></type>
       <type><generic>VARBINARY</generic><local>BYTEA</local></type>
       <type><generic>BLOB</generic><local>OID</local></type>
     </db-type-mapping>

   If there is not an exact match, then add it.

10. Execute

    ant db

    to build the database tables
11. Execute

    ant deploy

    to copy files to your servlet container
12. Start your servlet container.

If you upgrade Postgres, you should replace postgresql.jar and update entries in dbloader.xml. The jar file may
also be placed in the Tomcat common/lib directory.