Startups : Rajesh Ramchandani's Weblog

Installing PostgreSQL in Solaris Zone

You have probably read a lot about very cool features in Solaris 10 including Containers and zones. If you haven't yet, check out Joost Pronk's blog Here

Solaris containers have several advantages and can help you with:

1. Server consolidation

2. Resource management by using Resource Pools

3. Application or system partitions to run several instances of applications isolated from each other in seperate zones.

If you are in process of migration from commerical database to any Open source databases, you may want to run both the database servers parallely on the same server until migration is completed and workload has been moved from one DB to open source DB. By running both the dabase servers' simultaneously on the same server but in different zones, you can do a thorough workload and comparative analysis.

Here's a step by step instructions for installing and configuring PostgreSQL server in a Solaris zone.

Creating the First zone (Postgresql zone)

zonedemo# mkdir -p /export/zones/postgresql
zonedemo#chmod 700 /export/zones/postgresql

Zone is created by using a default template that defines resources in a typical zone

Configuring the zone:

zonedemo#zonecfg -z postgresqlzone

postgresqlzone: No such zone configured
Use ‘create’ to begin configuring a new zone.
zonecfg:postgresqlzone>
zonecfg:postgresqlzone> create
zonecfg:postgresqlzone> set zonepath=/export/zones/postgresql
zonecfg:postgresqlzone> set autoboot=true

The virtual network interface with IP address 192.9.200.1 is configured on local network interface of the global zone

zonecfg:postgresqlzone> add net
zonecfg:postgresqlzone:net> set address=192.9.200.1/24
zonecfg:postgresqlzone:net> set physical=bge0
zonecfg:postgresqlzone:net> end

The file system for Postgresql is created in Postgresql zone on the existing file system of the global zone. Add the following statements to the zone configuration to have the file system mounted in the zone automatically when the zone boots

zonecfg:postgresqlzone> add fs
zonecfg:postgresqlzone:fs> set type=ufs
zonecfg:postgresqlzone:fs> set special=/dev/md/rdsk/d100
zonecfg:postgresqlzone:fs> set raw=/dev/md/rdsk/d100
zonecfg:postgresqlzone:fs> set dir=/postgresql
zonecfg:postgresqlzone:fs> end
zonecfg:postgresqlzone> verify
zonecfg:postgresqlzone> commit
zonecfg:postgresqlzone> exit

Zone configuration is now complete. The verify and commit command verify and write the configuration to the disk respecitvely

Installing the Zone:

Zone is ready to be installed
zonedemo#zoneadm -z postgresqlzone install
Preparing to install zone .
Creating list of files to copy from the global zone.
Copying <2574> files to the zone.
Initializing zone product registry.
Determining zone package initialization order.
Preparing to initialize <998> packages on the zone.
Initialized <998> packages on zone.
Zone is initialized.
Installation of <24> packages was skipped.
Installation of these packages generated warnings:
The file contains a log of the zone installation.

zonecfg:postgresqlzone:fs> set dir=/postgresql
zonecfg:postgresqlzone:fs> end
zonecfg:postgresqlzone> verify
zonecfg:postgresqlzone> commit
zonecfg:postgresqlzone> exit

Installation is now complete. The verify command verifies that the current configuration of zone is syntatically correct and commit command writes the in-memory configuration to the disk.

Booting the Zone:

Since the zone is just like a fresh Solaris installation, we will need to answer a few questions to configure the system. *zlogin(1M)* command is used to obtain access to the newly created zones console.

[NOTICE: Zone rebooting]

SunOS Release 5.10 Version Generic 64-bit
Copyright 1983-2005 Sun Microsystems, Inc. All rights reserved. Use is subject to license terms.
Hostname: postgresqlzoneOQOQ

postgresqlzoneOQOQ console login:root

Installing Postgresql:

Download the Solaris sparc or x86 versions of PostgreSQL binaries from *“Pervasive”*:http://www.pervasivepostgresql.com

1. ./PPG_80R2_SOL10_SPARC.bin -console

Welcome to the InstallShield’s Console driven installation for Pervasive Postgres

Pervasive Postgres 8.0 Release 2

Pervasive Postgres is our distribution of PostgreSQL, the world’s most advanced open source database. The product is free. We also offer support, training, and
related services.

To install Pervasive Postgres on your computer, please do the following:

Press 1 for Next, 3 to Cancel or 5 to Redisplay [1]

It displays the Pervasive PostgreSQl license. Please read and accept the license to continue installing Pervasive Postgresq

Pervasive Software Inc.

Pervasive Postgres License Agreement

*....details deleted*

Please choose from the following options:
[ ] 1 – I accept the terms of the license agreement.
[X] 2 – I do not accept the terms of the license agreement.

To select an item enter its number, or 0 when you are finished: [0] 1

Enter 0 to continue or 1 to make another selection: [0] 0

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1]

Pervasive Postgres

Select the features for “pervasivepostgres” you would like to install:

pervasivepostgres To select/deselect a feature or to view its children, type its number: 1. +[x] Server Components 2. +[x] Optional Packages 3. +[x] Tools Other options: 0. Continue installing Enter command [0] 0

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] 1

/opt/pervasivepostgres

with the following features:

Server Components

Pervasive Postgres Core Initialize Database Cluster
Optional Packages JDBC Client Library ODBC PostgreSQL documentation DBD-Pg
Tools Pervasive Postgres Config Tool phpPgAdmin

for a total size:

Press ENTER to read the text [Type q to quit]

60.4 MB

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1]

Pervasive Postgres

Installing pervasivepostgres. Please wait…

0 % complete
20 % complete
30 % complete
40 % complete
50 % complete
60 % complete
70 % complete
80 % complete
90 % complete
100 % complete

Creating uninstaller…

Make libpath to ODBC libs (Only for Admins)

The InstallShield Wizard has successfully installed pervasivepostgres. Choose Finish to exit the wizard.

Press 3 to Finish or 5 to Redisplay [3] 3

Installation is completed and configuration part begins:

bash-3.00$ LD_LIBRARY_PATH=/opt/pervasivepostgres/lib:$LD_LIBRARY_PATH
bash-3.00$ export LD_LIBRARY_PATH

bash-3.00$ PGDATA=/opt/pervasivepostgres/data;export PGDATA

-bash-3.00$ pwd
/opt/pervasivepostgres/bin

-bash-3.00$ ./initdb
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /opt/pervasivepostgres/data … ok
creating directory /opt/pervasivepostgres/data/global … ok
creating directory /opt/pervasivepostgres/data/pg_xlog … ok
creating directory /opt/pervasivepostgres/data/pg_xlog/archive_status … ok
creating directory /opt/pervasivepostgres/data/pg_clog … ok
creating directory /opt/pervasivepostgres/data/pg_subtrans … ok
creating directory /opt/pervasivepostgres/data/base … ok
creating directory /opt/pervasivepostgres/data/base/1 … ok
creating directory /opt/pervasivepostgres/data/pg_tblspc … ok
selecting default max_connections … 100
selecting default shared_buffers … 1000
creating configuration files … ok
creating template1 database in /opt/pervasivepostgres/data/base/1 … ok
initializing pg_shadow … ok
enabling unlimited row size for system tables … ok
initializing pg_depend … ok
creating system views … ok
loading pg_description … ok
creating conversions … ok
setting privileges on built-in objects … ok
creating information schema … ok
vacuuming database template1 … ok
copying template1 to template0 … ok

WARNING: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

./postmaster -D /opt/pervasivepostgres/data
or ./pg_ctl -D /opt/pervasivepostgres/data -l logfile start

bash-3.00$ ./pg_ctl -l /opt/pervasivepostgres/logs/pg_log start
postmaster starting
bash-3.00$ ./pg_ctl status
pg_ctl: postmaster is running (PID: 1909)
/opt/pervasivepostgres/bin/postmaster

I have done some minimal validation of PostgreSQL server inside a Solaris zone. So far I haven’t found any issues and will continue with more rigorous testing.

Posted at 01:50PM Jul 13, 2005 by rrajesh in Open Source  |  Comments[4]

PostgreSQL with Sun Java System Application Server 8.1

Recently we tested out Open source databases (Postgresql and MySQL) for their JDBC Compliance and their capabilities to work with J2EE compliant Application servers such as Sun Java System Application server 8.1. Dave Cramer, the lead JDBC developer for PostgreSQL and Lance Obermeyer of Pervasive Software Inc. offered their engineering support and implemented the missing functions, data conversion routines and also fixed several bugs to be able to pass the JDBC compliance and also other tests (CMP/EJB tests) that any backend must pass in order to be Compliant to work with J2EE certified Application servers. All the changes and fixes that were done in the driver aren't part of the community release yet, but will be soon in near future. Also, we needed support for Stored procedures, so we had to use Postgresql 8.1-dev development release.

In order to make PostgreSQL work with Sun Java System Application server 8.1, you need to create a .tpersistence.properties file under $J2EE_HOME/domains//config directory and add the following:
database.postgresql.QUOTE_CHAR_END=
database.postgresql.QUOTE_CHAR_START=
database.postgresql.RTRIM=
database.postgresql.RTRIM_POST=
database.postgresql.SQRT=SQRT
database.postgresql.ABS=ABS

Creating Connection Pool

To create Connection Pools for Postgresql, under Admin server console:

1. Under Resources, select JDBC and on right hand side frame, select Connection Pools

2. Click on New to add create a new connection pool

3. Provide some name such as postgresql-pool and select Datasource type as javax.sql.ConnectionPoolDataSource

4. Click Next and enter the Datasource Classname as org.postgresql.ds.PGSimpleDataSource

5. Add the following properties to the connection pool:

DatabaseName <your-db-name>

port <your-selected-port-number> default port is 5432

user <DB-User-Name>

Password <DB-User-Password>

ServerName <DB-Server-Name>

Save the settings and click on Ping. If DB server is running and connection can be made, Ping will succeed. Please make sure you have copied the Postgresql jdbc driver in $J2EE_HOME/domains/<domain-name>/lib/ext directory.

Here's an sample domain.xml from an working installation:

<jdbc-connection-pool connection-validation-method="auto-commit" datasource-lassname="org.postgresql.ds.PGSimpleDataSource" fail-all-connections="false"  idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="false"  max-pool-size="96" max-wait-time-in-millis="60000"  name="cts-postgresql-pool" pool-resize-quantity="2" res-type="javax.sql.DataSource  "  steady-pool-size="32">
        <property name="DatabaseName" value="ctsinout"/>       
        <property name="port" value="5432"/>
        <property name="user" value="postgres"/>
        <property name="ServerName" value="magnolia"/>
        <property name="password" value="postgres"/>
        <property name="prepareThreshold" value="3"/>                Use Server Side Stored procedures        
        <property name="loglevel" value="2"/>                                DEBUG Level log
        <property name="protocolVersion" value="2"/>    
        <property name="ssl" value="true"/>  
</jdbc-connection-pool>

Restart the domains using "asadmin stop-domain and asadmin start-domain " or use Web based Admin server for the application server. Please note that this list of entries in the .tpersistence.properties file was sufficient for our work with the testsuite, however there may be more entries required depending on the sql usage.

You can read Lance Andersen's blogs for using Derby with Sun java System Application server at:
Derby with Sun Java Application Server

and Jonathan Bruce's blog for using MySQL with Sun Java System Application Server at:
MySQL with Sun Java Application Server

If you use Postgresql or are planning to use in near future and have questions about PostgreSQl on Solaris platform or questions, issues with Sun Java System application server with PostgreSQL (or MySQL), please feel free to contact me.

Posted at 07:24PM Jun 27, 2005 by rrajesh in Open Source  |  Comments[17]