PostgreSQL

Owned by Jen Bourey
Sept 19, 2014
6 min read

Below are the steps required to integrate Postgres with uPortal

Postgresql users

You may see errors like below associated with using Postgresql. Append the "-Dmaven.test.skip=true" argument when running ANY ant commands to skip the tests in order to receive a successful build.

[artifact:mvn] testVersionBadSql(org.jasig.portal.version.dao.jpa.JpaVersionDaoTest) Time elapsed: 0.019 sec <<< ERROR!
[artifact:mvn] org.springframework.dao.InvalidDataAccessResourceUsageException: user lacks privilege or object not found: UP_PERSON_ATTR; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: user lacks privilege or object not found: UP_PERSON_ATTR

 

Step 1: Configure the Database Filter     

     1. In the filters folder, locate the default local.properties file under uPortal-4.1.x/filters/local.properties and configure the Database Connection Settings

uPortal/filters/local.properties
# HSQL Configuration
environment.build.hsql.port=8887

# Database Connection Settings (Uncomment the Maven Filters section in rdbm.properties)
environment.build.hibernate.connection.driver_class=org.postgresql.Driver
environment.build.hibernate.connection.url=jdbc:postgresql://my.school.edu/uPortal
environment.build.hibernate.connection.username=user
environment.build.hibernate.connection.password=password
environment.build.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
environment.build.hibernate.connection.validationQuery=select 1 

Step 2: Add the database driver

  1. NEW: Open uportal-db/pom.xml file, uncomment the Postgres driver below and modify as needed 

  2. Add the appropriate version properties to the root pom.xml file or enter the appropriate version below

 

GroupId Name Change

Note: groupId for Postgres JDBC drivers changed for versions 9.2+ from "postgresql" to "org.postgresql"

uportal-db/pom.xml
... 
<dependencies>
        <!-- Add any db drivers that are applicable to *any* of your environments -->
	    <dependency>
	        <groupId>org.hsqldb</groupId>
	        <artifactId>hsqldb</artifactId>
	        <version>${hsqldb.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <!--
         | The following db drivers should be uncommented and/or modified as needed for server 
         | deployments.  (Add all thaat are needed.)  Don't forget to add appropriate  .version 
         | properties to the root pom.xml, or simply enter the appropriate version below.
         +-->
		
	    <dependency>
            <groupId>org.postgresql</groupId>  <!-- The groupId is just postgresql for versions prior to 9.2 -->
	        <artifactId>postgresql</artifactId>
	        <version>${postgres.version}</version>
	        <scope>compile</scope>
	    </dependency>
 
		<!--
	    <dependency>
	        <groupId>com.ibm.db2</groupId>
	        <artifactId>db2-jdbc</artifactId>
	        <version>${db2.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>${mssql.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6_g</artifactId>
            <version>${oracle.version}</version>
        </dependency>
        <dependency>
            <groupId>org.sybase</groupId>
            <artifactId>sybase-jconnect</artifactId>
            <version>${sybase.version}</version>
        </dependency>
	    -->
    </dependencies>

Step 3: Test the Configuration

Start Postgres and then in your portal development directory, issue the command:

ant dbtest -Dmaven.test.skip=true

If it works correctly you should see something like

Buildfile: build.xml

dbtest:

install-parent-pom:

     [java] INFO  [58:56.133] Initializing ExecutorService  'uPortalTaskScheduler'
     [java] INFO  [58:57.174] Building JPA container EntityManagerFactory for persistence unit 'RawEventsDb'
     [java] INFO  [58:58.194] Building JPA container EntityManagerFactory for persistence unit 'AggrEventsDb'
     [java] INFO  [58:58.745] Building JPA container EntityManagerFactory for persistence unit 'PortalDb'
     [java] INFO  [59:01.356] Adding destruction callback singleton for bean 'scopedTarget.sessionAttributesOverridesMap'
     [java] INFO  [59:03.800] Created new lazily initialized GenericApplicationContext for the portal in 10617ms
     [java] INFO  [59:04.294] JDBC DataSources
     [java] INFO  [59:04.295] 	rawEventsJdbcOperations
     [java] INFO  [59:04.295] Looking up bean 'PortalDB.metadata' in ApplicationContext due to context not yet being initialized
     [java] INFO  [59:04.302] 		Database name:    'PostgreSQL'
     [java] INFO  [59:04.302] 		Database version: '9.3.1' (9.3)
     [java] INFO  [59:04.302] 		Driver name:      'PostgreSQL Native Driver'
     [java] INFO  [59:04.302] 		Driver version:   'PostgreSQL 9.0 JDBC4 (build 801)' (9.0)
     [java] INFO  [59:04.302] 		Driver class:     'PostgreSQL Native Driver'
     [java] INFO  [59:04.302] 		supportsANSI92EntryLevelSQL: true
     [java] INFO  [59:04.302] 		supportsANSI92FullSQL:       false
     [java] INFO  [59:04.302] 		supportsCoreSQLGrammar:      false
     [java] INFO  [59:04.302] 		supportsExtendedSQLGrammar:  false
     [java] INFO  [59:04.302] 
     [java] INFO  [59:04.302] 		supportsTransactions:         true
     [java] INFO  [59:04.302] 		supportsMultipleTransactions: true
     [java] INFO  [59:04.302] 		supportsOpenCursorsAcrossCommit:      false
     [java] INFO  [59:04.302] 		supportsOpenCursorsAcrossRollback:    false
     [java] INFO  [59:04.302] 		supportsOpenStatementsAcrossCommit:   true
     [java] INFO  [59:04.302] 		supportsOpenStatementsAcrossRollback: true
     [java] INFO  [59:04.302] 
     [java] INFO  [59:04.302] 		supportsStoredProcedures:     true
     [java] INFO  [59:04.302] 		supportsOuterJoins:           true
     [java] INFO  [59:04.302] 		supportsFullOuterJoins:       true
     [java] INFO  [59:04.302] 		supportsLimitedOuterJoins:    true
     [java] INFO  [59:04.302] 		supportsBatchUpdates:         true
     [java] INFO  [59:04.302] 		supportsColumnAliasing:       true
     [java] INFO  [59:04.302] 		supportsExpressionsInOrderBy: true
     [java] INFO  [59:04.303] 
...
     [java] INFO  [59:04.303] 		Table Types: INDEX,SEQUENCE,SYSTEM INDEX,SYSTEM TABLE,SYSTEM TOAST INDEX,SYSTEM TOAST TABLE,SYSTEM VIEW,TABLE,TEMPORARY INDEX,TEMPORARY SEQUENCE,TEMPORARY TABLE,TEMPORARY VIEW,TYPE,VIEW
     [java] INFO  [59:04.307] 		SQL Types:   bool,bytea,char,name,int8,bigserial,int2,int2vector,int4,serial,regproc,text,oid,tid,xid,cid,oidvector,pg_type,pg_attribute,pg_proc,pg_class,json,xml,_xml,_json,pg_node_tree,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,_cid,_oidvector,_bpchar,_varchar,_int8,_point,_lseg,_path,_box,_float4,_float8,_abstime,_reltime,_tinterval,_polygon,aclitem,_aclitem,_macaddr,_inet,_cidr,_cstring,bpchar,varchar,date,time,timestamp,_timestamp,_date,_time,timestamptz,_timestamptz,interval,_interval,_numeric,timetz,_timetz,bit,_bit,varbit,_varbit,numeric,refcursor,_refcursor,regprocedure,regoper,regoperator,regclass,regtype,_regprocedure,_regoper,_regoperator,_regclass,_regtype,uuid,_uuid,tsvector,gtsvector,tsquery,regconfig,regdictionary,_tsvector,_gtsvector,_tsquery,_regconfig,_regdictionary,txid_snapshot,_txid_snapshot,int4range,_int4range,numrange,_numrange,tsrange,_tsrange,tstzrange,_tstzrange,daterange,_daterange,int8range,_int8range,record,_record,cstring,any,anyarray,void,trigger,event_trigger,language_handler,internal,opaque,anyelement,anynonarray,anyenum,fdw_handler,anyrange,pg_attrdef,pg_constraint,pg_inherits,pg_index,pg_operator,pg_opfamily,pg_opclass,pg_am,pg_amop,pg_amproc,pg_language,pg_largeobject_metadata,pg_largeobject,pg_aggregate,pg_statistic,pg_rewrite,pg_trigger,pg_event_trigger,pg_description,pg_cast,pg_enum,pg_namespace,pg_conversion,pg_depend,pg_database,pg_db_role_setting,pg_tablespace,pg_pltemplate,pg_authid,pg_auth_members,pg_shdepend,pg_shdescription,pg_ts_config,pg_ts_config_map,pg_ts_dict,pg_ts_parser,pg_ts_template,pg_extension,pg_foreign_data_wrapper,pg_foreign_server,pg_user_mapping,pg_foreign_table,pg_default_acl,pg_seclabel,pg_shseclabel,pg_collation,pg_range,pg_roles,pg_shadow,pg_group,pg_user,pg_rules,pg_views,pg_tables,pg_matviews,pg_indexes,pg_stats,pg_locks,pg_cursors,pg_available_extensions,pg_available_extension_versions,pg_prepared_xacts,pg_prepared_statements,pg_seclabels,pg_settings,pg_timezone_abbrevs,pg_timezone_names,pg_stat_all_tables,pg_stat_xact_all_tables,pg_stat_sys_tables,pg_stat_xact_sys_tables,pg_stat_user_tables,pg_stat_xact_user_tables,pg_statio_all_tables,pg_statio_sys_tables,pg_statio_user_tables,pg_stat_all_indexes,pg_stat_sys_indexes,pg_stat_user_indexes,pg_statio_all_indexes,pg_statio_sys_indexes,pg_statio_user_indexes,pg_statio_all_sequences,pg_statio_sys_sequences,pg_statio_user_sequences,pg_stat_activity,pg_stat_replication,pg_stat_database,pg_stat_database_conflicts,pg_stat_user_functions,pg_stat_xact_user_functions,pg_stat_bgwriter,pg_user_mappings,cardinal_number,character_data,sql_identifier,information_schema_catalog_name,time_stamp,yes_or_no,applicable_roles,administrable_role_authorizations,attributes,character_sets,check_constraint_routine_usage,check_constraints,collations,collation_character_set_applicability,column_domain_usage,column_privileges,column_udt_usage,columns,constraint_column_usage,constraint_table_usage,domain_constraints,domain_udt_usage,domains,enabled_roles,key_column_usage,parameters,referential_constraints,role_column_grants,routine_privileges,role_routine_grants,routines,schemata,sequences,sql_features,sql_implementation_info,sql_languages,sql_packages,sql_parts,sql_sizing,sql_sizing_profiles,table_constraints,table_privileges,role_table_grants,tables,triggered_update_columns,triggers,udt_privileges,role_udt_grants,usage_privileges,role_usage_grants,user_defined_types,view_column_usage,view_routine_usage,view_table_usage,views,data_type_privileges,element_types,_pg_foreign_table_columns,column_options,_pg_foreign_data_wrappers,foreign_data_wrapper_options,foreign_data_wrappers,_pg_foreign_servers,foreign_server_options,foreign_servers,_pg_foreign_tables,foreign_table_options,foreign_tables,_pg_user_mappings,user_mapping_options,user_mappings,admins,_admins,announcement,_announcement,announcement_attachment,_announcement_attachment,audience,_audience,authors,_authors,moderators,_moderators,subscription,_subscription,topic,_topic,calendar_configuration,_calendar_configuration,calendar_parameter,_calendar_parameter,calendar_preference,_calendar_preference,calendar_role,_calendar_role,calendar_store,_calendar_store,news_configuration,_news_configuration,news_definition,_news_definition,news_parameter,_news_parameter,news_preference,_news_preference,news_role,_news_role,news_set,_news_set,scm_attachment,_scm_attachment,hibernate_sequence,up_user,_up_user,up_user_locale,_up_user_locale,up_permission,_up_permission,up_user_layout,_up_user_layout,up_layout_struct,_up_layout_struct,up_layout_param,_up_layout_param,up_sequence,_up_sequence,up_user_profile,_up_user_profile,up_group,_up_group,up_entity_type,_up_entity_type,up_group_membership,_up_group_membership,up_entity_cache_invalidation,_up_entity_cache_invalidation,up_entity_lock,_up_entity_lock,up_entity_prop,_up_entity_prop,up_dlm_evaluator,_up_dlm_evaluator,up_dlm_evaluator_paren,_up_dlm_evaluator_paren,up_jgroups_auth,_up_jgroups_auth,up_jgroups_ping,_up_jgroups_ping,up_message,_up_message,up_mutex,_up_mutex,up_permission_activity,_up_permission_activity,up_permission_owner,_up_permission_owner,up_person_attr,_up_person_attr,up_person_attr_values,_up_person_attr_values,up_person_dir,_up_person_dir,up_portal_cookies,_up_portal_cookies,up_portlet_cookies,_up_portlet_cookies,up_portlet_def,_up_portlet_def,up_portlet_def_mdata,_up_portlet_def_mdata,up_portlet_def_param,_up_portlet_def_param,up_portlet_ent,_up_portlet_ent,up_portlet_ent__states,_up_portlet_ent__states,up_portlet_pref,_up_portlet_pref,up_portlet_prefs,_up_portlet_prefs,up_portlet_pref_values,_up_portlet_pref_values,up_portlet_type,_up_portlet_type,up_ss_desc,_up_ss_desc,up_ss_desc_lay_attr,_up_ss_desc_lay_attr,up_ss_desc_lay_attr_elms,_up_ss_desc_lay_attr_elms,up_ss_desc_output_prop,_up_ss_desc_output_prop,up_ss_desc_param,_up_ss_desc_param,up_ss_user_pref,_up_ss_user_pref,up_ss_user_pref_lay_attr,_up_ss_user_pref_lay_attr,up_ss_user_pref_lay_attr_val,_up_ss_user_pref_lay_attr_val,up_ss_user_pref_output_prop,_up_ss_user_pref_output_prop,up_ss_user_pref_param,_up_ss_user_pref_param,up_user_fragment_subscription,_up_user_fragment_subscription,up_version,_up_version,up_dlm_evaluator_seq,up_message_seq,up_mutex_seq,up_permission_activity_seq,up_permission_owner_seq,up_person_attr_seq,up_person_dir_seq,up_portal_cookies_seq,up_portlet_cookies_seq,up_portlet_def_param_seq,up_portlet_def_seq,up_portlet_ent_seq,up_portlet_prefs_seq,up_portlet_pref_seq,up_portlet_type_seq,up_ss_desc_lay_attr_seq,up_ss_desc_output_prop_seq,up_ss_desc_param_seq,up_ss_desc_seq,up_ss_user_pref_lay_attr_seq,up_ss_user_pref_seq,up_user_fragment_sub_seq,up_version_seq,up_raw_events,_up_raw_events,up_raw_events_seq,up_academic_term_detail,_up_academic_term_detail,up_aggr_group_mapping,_up_aggr_group_mapping,up_aggr_portlet_mapping,_up_aggr_portlet_mapping,up_aggr_tab_mapping,_up_aggr_tab_mapping,up_concurrent_user_aggr,_up_concurrent_user_aggr,up_date_dimension,_up_date_dimension,up_event_aggr_conf_groups,_up_event_aggr_conf_groups,up_event_aggr_conf_groups_exc,_up_event_aggr_conf_groups_exc,up_event_aggr_conf_groups_inc,_up_event_aggr_conf_groups_inc,up_event_aggr_conf_intrvl,_up_event_aggr_conf_intrvl,up_event_aggr_conf_intrvl_exc,_up_event_aggr_conf_intrvl_exc,up_event_aggr_conf_intrvl_inc,_up_event_aggr_conf_intrvl_inc,up_event_aggr_status,_up_event_aggr_status,up_event_session,_up_event_session,up_event_session_groups,_up_event_session_groups,up_login_event_aggr,_up_login_event_aggr,up_portlet_exec_aggr,_up_portlet_exec_aggr,up_portlet_layout_aggr,_up_portlet_layout_aggr,up_quarter_detail,_up_quarter_detail,up_search_req_aggr,_up_search_req_aggr,up_tab_render_aggr,_up_tab_render_aggr,up_time_dimension,_up_time_dimension,up_unique_str,_up_unique_str,up_unique_str_segment,_up_unique_str_segment,up_unique_str_segment__uids,_up_unique_str_segment__uids,up_academic_term_detail_seq,up_aggr_group_mapping_seq,up_aggr_portlet_mapping_seq,up_aggr_tab_mapping_seq,up_concurrent_user_aggr_seq,up_date_dimension_seq,up_event_aggr_conf_groups_seq,up_event_aggr_conf_intrvl_seq,up_event_aggr_status_seq,up_event_session_seq,up_login_event_aggr_seq,up_portlet_exec_aggr_seq,up_quarter_detail_seq,up_tab_render_aggr_seq,up_time_dimension_seq,up_unique_str_segment_seq,up_unique_str_seq,hibernate_sequences,_hibernate_sequences
     [java] INFO  [59:04.307] 
     [java] INFO  [59:04.307] 	aggrEventsJdbcOperations
...
     [java] INFO  [59:04.326] Hibernate Dialects
     [java] INFO  [59:04.326] 	AggrEventsDb: org.hibernate.dialect.PostgreSQLDialect
     [java] INFO  [59:04.327] 
     [java] INFO  [59:04.327] 	RawEventsDb: org.hibernate.dialect.PostgreSQLDialect
     [java] INFO  [59:04.327] 
     [java] INFO  [59:04.327] 	PortalDb: org.hibernate.dialect.PostgreSQLDialect
     [java] INFO  [59:04.327] 
     [java] INFO  [59:04.403] Closing JPA EntityManagerFactory for persistence unit 'PortalDb'
     [java] INFO  [59:04.414] Closing JPA EntityManagerFactory for persistence unit 'AggrEventsDb'
     [java] INFO  [59:04.421] Closing JPA EntityManagerFactory for persistence unit 'RawEventsDb'
     [java] INFO  [59:04.425] Shutting down ExecutorService 'uPortalTaskScheduler'

BUILD SUCCESSFUL

 

Step 4: Build and Deploy 

Following a successful test, you can execute the command below to build the database tables and copy files to your servlet container. 

Note: Executing the command "ant clean initportal" will drop and recreate the database tables and all existing data will be lost. This will result in a clean uPortal database structure. If you want to keep the contents of your existing database, use "ant clean deploy-war -Dmaven.test.skip=true"

ant clean initportal -Dmaven.test.skip=true

Step 5: Restart Tomcat

 

 

Having problems with these instructions?

Please send us feedback at uportal-user@lists.ja-sig.org