clone a postgresql database for testing cleanly

Main menu:

Topics

Recent Posts

Blog

March 2010
M T W T F S S
« Feb   Apr »
1234567
891011121314
15161718192021
22232425262728
293031  

Past Posts

Java/Java EE

JDBC

Other

clone a postgresql database for testing cleanly

March 28th, 2010 by Jeanne Boyarsky

I’m looking at writing integration tests for the back end of JavaRanch‘s JForum install.

A few “pesky” requirements/constraints

  • 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”

  1. Start up the JForum war
  2. Go to the JForum install URL and enter some information which creates the tables
  3. Run the JavaRanch customizations.

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

  1. Create an empty database
    createdb jforum_integration_test
  2. Arrive at the base schema
    1. Go the JForum installation URL
    2. Enter the information to create the tables
  3. Export the schema thus far
    pg_dump -U postgres jforum_integration_test > c:\temp\postgres.sql
  4. Provide instructions for the rest of the sql which were created by our developers.

How to import

Now for the easy part!

Importing this dump is a matter of a single command:

psql -U postgres jforum < “pathToWorkspace\JForum\javaranch-docs\deployment\file.ddl”

Lessons learned after

The next day I learned that this wasn’t enough.  We also needed some test data from the server.  I ran this a few times to get the relevant test data.

pg_dump –data-only –inserts -U user -W database –file roles  –table tableName

Conclusion

My next step will be to actually configure dbUnit against this new database and start writing tests.

Comments

Comment from Jeanne Boyarsky
Posted: March 30, 2010 at 9:22 pm

Updated to add exporting data to a text file.

Pingback from junit suite wide setup (not @BeforeClass) | Down Home Country Coding With Scott Selikoff and Jeanne Boyarsky
Posted: April 12, 2010 at 9:39 pm

[...] clone a postgresql database for testing cleanly [...]

Pingback from process builder to run multi-platform postgres import | Down Home Country Coding With Scott Selikoff and Jeanne Boyarsky
Posted: April 25, 2010 at 8:25 pm

[...] saw how to clone a postgresql database for testing cleanly in an earlier blog post. The next step is to do so from Java so it can be part of an integration [...]

Pingback from dbunit vs recreate schema | Down Home Country Coding With Scott Selikoff and Jeanne Boyarsky
Posted: May 9, 2010 at 10:04 pm

[...] I faced the decision of how to guarantee a clean database.  A previous post on the topic covers how to clone a postgresql database via the command [...]

Write a comment