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|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