number 9

public service announcement

I need a good database to run locally. When I Google for ‘postgreSQL OSX’ I see a lot of old posts that start with ‘download and build readline’. No wonder people still use MySQL.

If you’re on a Mac use EnterpriseDBs installer. It’s up to date and you get a reasonably sane default config

(everything is under /Library/PostgreSQL/8.3 if you want to tweak it).

Also bundles docs and pgAdmin (which seems to have become a half-decent tool  while I wasn’t looking) in /Applications/PostgreSQL (I found a JDBC driver under /Library/Extensions/Java too, although that could have come from anywhere).

There are also installers there for Linux (but you might as well use your package manager ) and Windows (no, really).

For production, I’d use something on a ‘real’ UNIX obviously - the Solaris 10 / SXCE bundled PostgreSQL 8.2 / 8.3 works great.

Glassfishv2 and PostgreSQL (open source, free) now officially wizz all over
BEA, IBM and Oracle (proprietary, hideously expensive).

Feels good to have backed a winning horse. There’s another beta out now too that I’ll be upgrading
my existing install to when I get five minutes.

In the meantime, congratulations to the GF team.

Update: roller 4 is a lot faster to setup than 3; see here.

 

Now my appserver and database are setup, I can install something.
Right on cue, Roller 3.1 shipped . Looks pretty good too.

WARmonger

I want to deploy from a WAR file, but they don’t ship a prebuilt one
(a GPL thing ?).
So I’ll make my own
(this isn’t necessary – glassfish can deploy from a webapp directory easily enough – it just feels neater to me somehow).

planb:/tmp $ wget http://www.apache.org/dist/roller/roller-3/v3.1.0/bin/apache-roller-3.1.tar.gz
planb:/tmp $ wget https://roller.dev.java.net/files/documents/190/51361/required-jars-roller-3.1.tar.gz
planb:/tmp $ tar zxvf apache-roller-3.1.tar.gz
planb:/tmp $ tar zxvf required-jars-roller-3.1.tar.gz
planb:/tmp $ cd apache-roller-3.1/webapp/roller/

Jarring up apache-roller-3.1/webapp/roller will make a WAR. I need to make a couple of tweaks first.

this is how we (configure) Roll(er)

change the salt

First, edit WEB-INF/security.xml and change the ‘salts’ .

(optional) install textile plugin

Textile support is always nice (even without live preview):

planb:roller $ cd WEB-INF/lib
planb:lib $ wget https://roller.dev.java.net/files/documents/190/56103/textile-plugin-3.1.tar.gz
planb:lib $ tar zxvf textile-plugin-3.1.tar.gz
planb:lib $ mv textileplugin/* .
planb:lib $ rm -r textileplugin textile-plugin-3.1.tar.gz
planb:lib $ cd -

To enable the textile plugin, you can tweak roller-custom.properties
or download mine
(which also tells Hibernate that I’m using a postgresql database) to somewhere in your classpath:

planb:roller $ cd WEB-INF/classes
planb:classes $ wget http://files.hellooperator.net/solaris/glassfish/roller-custom.properties
planb:classes $ cd -

(optional) install extra themes

Add some bundled themes if that’s your thing (you might want to prune some out, there are 20+ themes in the bundle):

planb:roller $ cd themes
planb:themes $ wget https://roller.dev.java.net/files/documents/190/56087/opt-themes-roller-3.1.tar.gz
planb:themes $ tar zxvf opt-themes-roller-3.1.tar.gz ; rm opt-themes-roller-3.1.tar.gz
planb:themes $ cd ..

(not really optional) setup mail support

Roller depends on JavaMail for email notifications of comments, inviting people to become
authors, etc.
The relevant part of WEB-INF/web.xml is inexplicably commented out (apparently Tomcat doesn’t need this).
If you want to send any mail, uncomment the following chunk:

   planb:roller $ tail -15 WEB-INF/web.xml
   ....
       <resource-ref>
           <res-ref-name>mail/Session</res-ref-name>
           <res-type>javax.mail.Session</res-type>
           <res-auth>Container</res-auth>
       </resource-ref>
   ....

make the jar

Create the JARfile and copy it up to the appserver:

   planb:roller $ pwd
   /tmp/apache-roller-3.1/webapp/roller
   planb:roller $ jar cf ~/roller31.war .
   planb:roller $ scp ~/roller31.war  root@goldfish:

the little schema

Roller has db creation scripts for most databases (here’s one I made earlier )

planb $ scp  WEB-INF/dbscripts/postgresql/createdb.sql root@goldfish:
planb $ ssh root@goldfish
goldfish # PATH=/usr/postgres/8.2/bin/:$PATH
goldfish # psql -h elephantom.mydomain -U dbuser zonedb < createdb.sql
....creation output snipped....
goldfish #

configure glassfish

asadmin is great if you don’t like web frontends (or taking screnshots..). You can do all the setup from the CLI.

  planb $ ssh root@goldfish
  goldfish #

install a jdbc driver

PostgreSQL ones here .

   goldfish # cd /domains/rollerdisco/lib
   goldfish # wget http://jdbc.postgresql.org/download/postgresql-8.2-505.jdbc3.jar
   goldfish # svcadm restart rollerdisco

NB: I’ve put the JAR into the domain directory so it won’t be lost on server upgrades

create a connection pool

To create a pool called ‘rollerpool’ (’asadmin help create-jdbc-connection-pool’):

   goldfish # /j2ee/bin/asadmin create-jdbc-connection-pool \
   --user admin --passwordfile /domains/rollerdisco/.aspass -s \
   --datasourceclassname org.postgresql.ds.PGSimpleDataSource --restype javax.sql.DataSource \
   --steadypoolsize 4 --maxpoolsize 12 \
   --property portNumber=5432:password=sekrit:user=dbuser:serverName=elephantom:databaseName=zonedb \
   --description "Roller Connection Pool" rollerpool
  Command create-jdbc-connection-pool executed successfully.
  goldfish #

Using any datasources other than PGSimpleDatasource with my glassfish build (b33) didn’t work. I got a lot of

java.lang.Exception: Doh! Couldn't instantiate a roller class

Bug 2779 has all the gory details. There is a workaround in b46, which we’ll be upgrading to in my next gf post.

create jdbc resource

Out of the box (WEB-INF/sun-web.xml), Roller looks
for a pool called ‘jdbc/rollerdb’. So ‘tag’ our connection pool with that name by creating a JDBC datasource and roller will use it:

   goldfish # /j2ee/bin/asadmin create-jdbc-resource \
   --user admin --passwordfile /domains/rollerdisco/.aspass -s \
   --connectionpoolid rollerpool jdbc/rollerdb

create a javamail session

We need a JavaMail session with the name we specified in Rollers web.xml earlier. This one talks to a local MTA:

   goldfish # /j2ee/bin/asadmin create-javamail-resource  \
   --user admin --passwordfile /domains/rollerdisco/.aspass -s  \
   --mailhost localhost --mailuser required_although_i_dont_use_smtp_auth --fromaddress gfadmin@yourdomain.com mail/Session

deploy it

You can autodeploy the WARfile (by copying ‘yourwebapp.war’ to /domains/rollerdisco/autodeploy),
but that’ll run the webapp at /yourwebapp. Instead, I’ll use asadmin deploy :

   goldfish # /j2ee/bin/asadmin deploy \
   --user admin --passwordfile /domains/rollerdisco/.aspass -s \
   --upload --contextroot '/' /root/roller31.war

(’—upload’ physically copies the warfile into the appserver, rather than deploying from it ‘in situ’)

can you hear me now?

If you autodeployed, browse to :

http://yourserver:8080/roller31/

and register an admin user, create blogs, etc.

I setup glassfish running on port 80 so
I can just go to

http://goldfish/

One gotcha: roller creates directories under roller-data/ in the glassfish users home directory.
Just don’t wonder what it is and delete it (bad things happen, trust me).

UPDATE:

I’m an ex-tomcat user and I’ve seen JVMs bleed memory over time, so I put a resource control of around 600Mb RAM on the zone. It seems to be using about half that, and hasn’t grow in the last few weeks.

Ubuntu put their postgresql client bits where gems can’t see them.

Google turned up the answer in the past, but today I had to figure it out myself.
So for the next time, here’s how to get the client bits needed for a gem build.

planb $ sudo apt-get install  postgresql-client-8.2 libpq-dev
planb $ sudo gem install postgres -- \
--with-pgsql-include-dir=/usr/include/postgresql \
--with-pgsql-lib-dir=/usr/lib/postgresql

Rails (well, rake) calls out to dropdb and createdb to flush the test database. If you want to do TDD, make sure the rails DB user has ‘createdb’ rights and owns the 3 databases:

postgres@elephantom $ createuser -PREdS railsguy
postgres@elephantom $ createdb -O railsguy live
postgres@elephantom $ createdb -O railsguy dev
postgres@elephantom $ createdb -O railsguy test

dropdb/createdb need access to the ‘postgres’ database to operate. Until you know this,
errors like

FATAL: no pg_hba.conf entry for host “1.2.3.4”, user “railsguy”, database “postgres”, SSL off

will have you staring at database.yml until your eyes cross.

you might as well have a pg_hba.conf line like

#TYPE  DB   USER       CIDR         METHOD
host   all   railsguy    1.2.3.4/32   md5

and have done.

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.