Working together with PostgreSQL, Glassfish, Liferay on Fedora Linux

Prerequisites

• Download and unzip liferay-portal-sql-5.1.1.zip
• Download liferay-portal-glassfish-linux-5.1.1.jar

PostgreSQL

Installation

Login as root:

yum install postgresql postgresql-server

To make the service start on system startup:

chkconfig postgresql on

To initialize a database cluster:

service postgresql initdb

Remote connections

To allow remote connections from PostgreSQL server via TCP/IP networking, change the setting of local and host of /var/lib/pgsql/data/pg_hba.conf

local	all				trust
host	all	all	0.0.0.0/0	trust

Change listen_addresses to value asterisk: /var/lib/pgsql/data/postgresql.conf

listen_addresses='*'

Start database

service postgresql start

Configuration

Create user

psql -U postgres -c "CREATE USER your_login WITH PASSWORD 'your_password' CREATEDB" 

Create database:

psql -U your_login -d template1 -c "CREATE DATABASE lportal ENCODING='UNICODE';"

Delete first 3 lines of liferay-portal-sql-5.1.1/create/create-postgresql.sql

drop database lportal;
create database lportal encoding = 'UNICODE';
\c lportal;

Fix create-postgresql.sql:

1. Replace all \n with \\n inside the sql statement "insert into Layout ( ... );"
2. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'.

Reference: http://support.liferay.com/browse/LEP-7436

Load sample sql script

psql -U your_login -d lportal -c "set datestyle='ISO, DMY';" -f create-postgresql.sql -o output.txt

JDK

Download jdk-6u7-linux-i586.bin from http://java.sun.com/javase/downloads/index.jsp

Install JDK

Login as root and install as below steps

mv jdk-6u7-linux-i586.bin /opt/
cd /opt/
chmod +x jdk-6u7-linux-i586.bin
./jdk-6u7-linux-i586.bin

Add JAVA startup environment

Create a text file /etc/profile.d/java.sh and add the following lines:

export JAVA_HOME=/opt/jdk1.6.0_07
export PATH=$JAVA_HOME/bin:$PATH

Set up Java environment:

source /etc/profile.d/java.sh
alternatives --install /usr/bin/java java /opt/jdk1.6.0_07/bin/java 2
alternatives --config java

Glassfish

Install ANT first:

yum install ant

Install Liferay Glassfish bundle:

java -Xmx256m -jar liferay-portal-glassfish-linux-5.1.1.jar -console
cd glassfish
ant -f setup.xml

Start Glassfish:

cd glassfish
./bin/asadmin start-domain

Liferay

LiferayPool

• Use your favorite browser to access http://<your_glassfish_address>:4848
• Login into admin console, default login is admin, password is adminadmin
• Goto Resources - JDBC - Connection Pools - LiferayPool
• Modify and save setting of JDBC connection pool as below:

Datasource Classname	org.postgresql.ds.PGSimpleDataSource
Resource Type		javax.sql.DataSource
DatabaseName		lportal
User			your_login
Password		your_password
serverName		your_database_address
PortNumber		your_database_port

Pooled Data Source (Optional)

To take advantage of the performance benefits of connection pooling, configure the pool as described below

Datasource Classname	org.postgresql.ds.PGConnectionPoolDataSource
Resource Type		javax.sql.ConnectionPoolDataSource
DatabaseName		lportal
User			your_login
Password		your_password
serverName		your_database_address
PortNumber		your_database_port

Under JAVA 6 or higher you must also run the following command, replacing <POOL_NAME> for the appropriate value. Not doing so will cause connection failures arising from a misdectection of the PostgreSQL driver as JDBC 4 compliant (it is not).

asadmin set domain.resources.jdbc-connection-pool.<POOL_NAME>.property.JDBC30DataSource=true

Screenshots

Restart Glassfish

./asadmin stop-domain domain1
./asadmin start-domain domain1

Done!

Appendix

Password

To require the client to supply an unencrypted password for authentication. This method authorizes users that can connect to the PostgreSQL database server with the need for a password.

su - postgres
psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'" -d template1

vi /var/lib/pgsql/data/pg_hba.conf

local	all				password
host	all	all	0.0.0.0/0	password

Restart PostgreSQL:

service postgresql restart

Firewall

By default PostgreSQL listens on TCP port 5432, to allow iptables incoming request:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 5432 -j ACCEPT

pgAdmin

pgAdmin III is a database design and management application for use with PostgreSQL.

Installation on Fedora:

yum install pgadmin3

References

Database Configuration

http://www.postgresql.org/docs/manuals