number 9

I need a database to do anything useful with glassfish .
Here are my PostgreSQL install notes.

the install

I chose postgresql 8.2 as part of my SXCE install .
If you don’t have it already, you need to:

globalzone # cd /cdrom/Solaris_11/Product
globalzone # pkgadd -d . SUNWpostgr-82-client SUNWpostgr-82-contrib \
SUNWpostgr-82-docs SUNWpostgr-82-libs SUNWpostgr-82-server \
SUNWpostgr-82-server-data-root SUNWpostgr-82-tcl

I might as well run it in a zone (partly to keep things tidy in case I screw up).
With sparse zones, I only need to install packages in the global zone and all zones can use them (one set of packages to maintain == happy sysadmin).

I’ll use the zone cloning script
I mentioned the other day, and slap on some resource caps while I’m at it :

globalzone # /zones/bangoneout.sh elephantom 1.2.3.4/24
globalzone # zonecfg -z elephantom "set max-lwps=300; add capped-memory; set physical=400M; set swap=512M; end; exit;"

ZFS snapshots make backing up the DB a lot easier, so I’ll give the zone a chunk of my zpool to manage:

globalzone # zfs create tank/delegated/elephantom
globalzone # zfs set mountpoint=none tank/delegated/elephantom
globalzone # zfs set quota=5G tank/delegated/elephantom
globalzone # zonecfg -z elephantom 'add dataset; set name=tank/delegated/elephantom; end'

From here on, we treat the zone as we would any other server:

globalzone # zoneadm -z elephantom reboot
globalzone # zlogin elephantom
[Connected to zone 'elephantom' pts/2]
elephantom #

creating the database

PostgreSQL integrates nicely with Solaris -
there’s RBAC support (a ‘PostgreSQL administration’ profile for DBA tasks),
DTrace providers and SMF integration in recent SXCE builds:

elephantom # svcs postgresql
disabled       12:21:40 svc:/application/database/postgresql:version_81
disabled       12:21:40 svc:/application/database/postgresql:version_82

I’ll make a ZFS filesystem and tell the version_82 instance to use it:

elephantom # zfs create tank/delegated/elephantom/data
elephantom # zfs set mountpoint=/data tank/delegated/elephantom/data
elephantom # chown postgres:postgres /data
elephantom # svccfg -s postgresql:version_82 'setprop postgresql/data = /data'
elephantom # svcadm refresh version_82

The rest of the install is the same as any UNIX.

Install the database as usual:

elephantom # su - postgres
$ /usr/postgres/8.2/bin/initdb /data
....snip usual initdb messages.....
$ exit
elephantom #

It’s probably a good idea to take a snapshot now, before we tweak stuff.
Note we can do this from within the zone since we :

elephantom # zfs snapshot vera/delegated/ganesh/data@pristine

The default config ( /data/postgresql.conf ) needs a few tweaks. I set:

   wal_sync_method = fsync
   full_page_writes = off
   listen_addresses = '*'
   # logfle is /data/server.log
   log_connections = on
   log_disconnections = on
   log_hostname = on

and edited /data/pg_hba.conf
to allow access from my glassfish zone
(all inter-zone traffic goes over loopback, so there’s no need to change your firewall).

Now start the server via SMF:

elephantom # svcadm enable postgresql:version_82

and create the user and the db:

elephantom # su - postgres
$ PATH=/usr/postgres/8.2/bin:$PATH
$ createuser -PREDS dbuser
Enter password for new role:
Enter it again:
CREATE ROLE
$ createdb -O dbuser zonedb
CREATE DATABASE
$ exit

Finally, I’ll check I can login from the glassfish zone:

glassfishzone # /usr/postgres/8.2/bin/psql -h elephantom.mydomain -U dbuser zonedb
Password for user dbuser:
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
zonedb=> \q
glassfishzone #

I stuck a 200Mb memory cap on the zone
- the above config seems quite happy in there.