Data Pile

Against Digital Amnesia

Switching Roller From Derby to PostgreSQL

Since Roller seems to have problems with the Glassfish built-in Derby database, especially when it comes to Full preview (you might end up with an HTTP 404 error code if you try to preview your post), you might want to switch your Roller installation to PostgreSQL.

This article will explain, how you can switch the installed Roller instance from Glassfish’s built-in derby database to the PostgreSQL database.

Quick install

For the people who don’t care if anything goes wrong or just don’t like to type at all, here is a litte script, which might help you save a few keystrokes. Just be aware of the fact that you might have to change a few variables inside the script.

Manual installation

This describes basically what the script in the quick installation tries to achive by being run.

Prerequisites

As with the roller install itself, you need of course a few things before you can start. In this case you need of course the PostgreSQL database itself and a JDBC driver to attach roller to the database.

The installation of the database depends on the type of operating system you are using. I describe the setup using a Debian based system, since this is so nice and easy :).

Get the database driver

To enable Glassfish to talk to your postgres installation, you have to supply it some code. Glassfish likes Java the best and you can get some JDBC drivers from here, but specifically the postgresql-8.3-604.jdbc3.jar is of interest to us.

So go get the JDBC driver and save it in /opt/roller/glassfish/domains/roller/lib

Install database

For a debian based system just enter the following command as user root (or prefix it with sudo on ubuntu). You might of course have a newer version of postgresql available with you current installation.

1
  root@sigusr1:$ aptitude install postgresql-8.1

After the installation finishes, you might want to create the database roller will be accessing later and also create a user for use with roller. This can be achieved by entering the following sequence of commands into a terminal of your choice.

Be aware of the fact that you have to execute the commands as user postgres.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@sigusr1:$ 
	       # Get root
	       su -

	       # Get postgres user
	       su - postgres

	       # create database
	       createdb rollerdb

	       # create roller user
	       createuser roller
	       # Answers
	       # Shall the new role be a superuser? (y/n) n
	       # Shall the new role be allowed to create databases? (y/n) n
	       # Shall the new role be allowed to create more new roles? (y/n) n

	       # Enter postgres prompt
	       psql

When you are in the prompt which will look something like this:

1
2
3
4
5
6
7
8
9
Welcome to psql 8.3.4, 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

postgres=#

Enter the following SQL statements which change the passwords for the roller
and postgres user accordingly:

1
2
alter user roller with password 'roller';
alter user postgres with password 'postgres';

You are now finished using the postgres user. You may want to continue as user roller with the following commands.

If you followed the Setup Roller Weblogger 4.1 On Glassfish V2 tutorial, you do have to do an additional step to get rid of the previously assigned JDBC connection; that is deleting the JDBC connection pool. Aside from that we simply create a JDBC connection pool and the according resource. Once that is done you might want to start configuring your roller installation.

1
2
3
4
5
6
7
8
9
10
11
12
root@sigusr1:$ 
 	       #if you followed glassfish ... (cleans jdbc resource too)
 	       ./bin/asadmin delete-jdbc-connection-pool --cascade rollerpool

 	       ./bin/asadmin create-jdbc-connection-pool
 	       --datasourceclassname org.postgresql.ds.PGSimpleDataSource \
 	       --restype javax.sql.DataSource \
 	       --property portNumber=5432:password=roller:user=roller:serverName=localhost:databaseName=rollerdb \
 	       rollerpool

 	       ./bin/asadmin ping-connection-pool rollerpool
  	       ./bin/asadmin create-jdbc-resource --connectionpoolid=rollerpool jdbc/rollerdb

Once that is finished, you should restart Glassfish in order to make changes effective. This is done through the following command:

1
2
root@sigusr1:$ su - roller -c "cd glassfish; ./bin/asadmin stop-domain roller" && \
su - roller -c "cd glassfish; ./bin/asadmin start-domain roller"

When you have finished all that, you now should be able to point you browser to http://localhost:8080/blogs and start configuring your Roller installation. Have fun.