Switching roller from Derby to PostgreSQL

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.

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.

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:

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:

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.

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:

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.