Using PostgeSQL with Address Book Server

Before you begin making changes you need to create a new database. The following instructions assume the database name to be addressbook however you can change this to whatever you like. If you do change the database name, you will also need to change the configuration files below.

The configuration files containing the database connection parameters can be found in :

/usr/share/addressBookServerEnterpriseEdition/apache-tomcat-6.0.24/

Disable H2 from starting automatically by setting the flag to false in the configuration file

bin/setenv.sh

JAVA_OPTS="$JAVA_OPTS -Dcom.addressbookserver.ENABLE_H2=false"

conf/server.xml

In the server.xml you need to configure your database connection setting. Typically this means specifying the host and access credential you use to access your database. This has to be done twice as Address Book Server used multiple connection pool, one during standard operation and another to authenticate web users. Both connection pools should point to the same database.

...
<Resource name="jdbc/AddressBookDB" auth="Container" type="javax.sql.DataSource"
   maxActive="25" maxIdle="5" maxWait="-1"
   username="postgres" password="password" driverClassName="org.postgresql.Driver"
   url="jdbc:postgresql://localhost/addressbook"/>
<Realm className="org.apache.catalina.realm.JDBCRealm"
  driverName="org.postgresql.Driver"
  connectionURL="jdbc:postgresql://localhost/addressbook"
  connectionName="postgres" connectionPassword="password"
  userTable="WebUsers" userNameCol="userid" userCredCol="password"
  userRoleTable="UserRoles" roleNameCol="accessRole"/>
...

lib/META-INF/persistence.xml

...
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
...

After changing the server.xml file restart tomcat using the instruction : Restarting Address Book Server

Downloading JDBC Driver

You can download the JDBC driver from :  JDBC Driver. You should choose the JDBC 4 type as it is more suitable to Address Book Server. Place the downloaded JAR into the /apache-tomcat-6.0.24/lib folder

Backups on PostgreSQL

 Backup Instructions

/Library/PostgreSQL/8.4/bin/pg_dump -U addressbook AddressBookDB >/Users/admin/Desktop/Backup-`date +%d%m%Y_%H%M%S`.sql

Alternatively you can create a script file and schedule this via cron / launchd

#!/bin/bash

BackupFile=~/backups/Backup-`date +%d%m%Y_%H%M%S`.sql

/Library/PostgreSQL/8.4/bin/pg_dump -U addressbook AddressBookDB > $BackupFile

gzip $BackupFile

Restore on PostgreSQL

The following sequence of commands can be used to restore a backup file created using the script shown above.

#Switch to admin user
sudo bash
#Stop the Address Book Server 
launchctl unload /Library/LaunchDaemons/com.addressBookServer.enterprise.plist 
#Switch to the postgresql user
su - postgres
cd bin/
#Drop / delete the current database
./dropdb AddressBookDB
#Create a new database
./createdb AddressBookDB
cd ../backups/
#Copy the backup file
cp Backup-22042010_160000.sql.gz backup.sql.gz
#Delete any previous backup files
rm backup.sql
#Extract the backup file
gunzip backup.sql.gz
#Restore the backup file into the new database
../bin/psql -e AddressBookDB < backup.sql
#Exit the database user 
exit
#Restart Address Book Server
launchctl load /Library/LaunchDaemons/com.addressBookServer.enterprise.plist 

Large Scale Deployment

Default /etc/sysctl.conf after postgresql installation:

kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=8192

Suggested valued for 300 connections:

kern.sysv.shmmax=1073741824
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=1073741824

can also be set via the following command:

sudo sysctl -w kern.sysv.shmmax=1073741824
sudo sysctl -w kern.sysv.shmall=1073741824

More information available here  http://www.caktusgroup.com/blog/2009/08/13/setting-postgresqls-shmmax-in-mac-os-x-105-leopard/  http://www.postgresql.org/docs/current/static/kernel-resources.html