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.