clone a postgresql database for testing cleanly

Multiple developers all over the word have their own local test databases filled with data in different states. The tests must work for everyone. Ideally they won’t leave data floating around either.

The tests must use PostgreSQL. While the original JForum supported multiple databases, the JavaRanch version has been scaled down to just run with the one we need. We do have some PostgreSQL specific SQL which rules out using an embedded database like HSQLDB or Derby.

Developers are using both Eclipse and IntelliJ. Tests should care about the IDE anyway, so this isn’t a big constraint.

Developers are using a variety of operating systems and languages on their operating systems. While code is in English, there can’t be assumptions as to the OS state.

Strategy

I think the best strategy is to create a second database just for testing. The JForum database would remain untouched and a jforum_integration_test database can be created for the tests. dbUnit can control the state of that special database.

The problem

Before I even start thinking about dbUnit, I did a proof of concept to ensure I could create a new database from scratch using the command line. Creating a database is the easy part. The “hard” part is that JForum doesn’t come with a schema. It comes with an installation servlet that creates the schema. While few people will be creating a schema for JForum, the technique I used applies elsewhere.

The procedure “before”

Start up the JForum war

Go to the JForum install URL and enter some information which creates the tables

Run the JavaRanch customizations.

How to clone a database for which you only have a partial script

Create an empty database

createdb jforum_integration_test

Arrive at the base schema

Go the JForum installation URL

Enter the information to create the tables

Export the schema thus far

pg_dump -U postgres jforum_integration_test > c:\temp\postgres.sql

Provide instructions for the rest of the sql which were created by our developers.