« Back to Custom Deployments

PostgreSQL with Glassfish

Working together with PostgreSQL, Glassfish, Liferay on Fedora Linux

Prerequisites #

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

3 Attachments
65059 Views
Average (1 Vote)
The average rating is 4.0 stars out of 5.
Comments
Threaded Replies Author Date
Hi Great Guide :) I've put together similar... Garry Donnelly April 17, 2010 7:43 PM
Excellent guide, but I still can't get Liferay... Ryan Lubben September 18, 2010 4:58 PM
OK, I finally figured it out. All I had to do... Ryan Lubben September 19, 2010 9:51 AM

Hi

Great Guide emoticon

I've put together similar instructions for installing the latest version of Liferay on Glassfish with MySql. All seems to be running well so I posted the details on my blog here: http://www.greatwhiteit.com/web/guest/home/-/blogs/how-to-install-liferay-on-the­-glassfish-applicaiton-server-over-ssh

Thanks
-Garry
Posted on 4/17/10 7:43 PM.
Excellent guide, but I still can't get Liferay to connect to my PostgreSQL database.

I'm trying to install Liferay 6.0.5 on Glassfish 3.0.1. I did notice that the LifrayPool didn't exist on the Glassfish server so I had to create it from scratch.

Otherwise, I followed the instructions to a T. Please help.
Posted on 9/18/10 4:58 PM.
OK, I finally figured it out. All I had to do was create a portal-ext.properties file in my portal root directory and specify the connection pool there.

jdbc.default.jndi.name=jdbc/LiferayPool

Now I'm connected to my database and everything is running smoothly.
Posted on 9/19/10 9:51 AM in reply to Ryan Lubben.