We use an Oracle(or postgres) database and an application server to execute integration tests. To isolate each test from another one , the database schema is dropped and re-created before each test.
As you see this is a time taking process. The application uses 100+ tables. We are thinking of writing custom sql to delete unwanted data from each tables. Is there a better way to save and restore database state?
( It appears DBUnit could do this, I have not tried it yet. )
A single test involves:
We have 5000 odd tests, taking 700 hours or so. (we do it on a grid environment, finishes overnight)
Most of the tests uses small data sizes, say up to 10 MB.
Oracle Flashback allows you to restore a table at a specified time point using a simple SQL query. The documentation is available here.
I don't know if Postgre has a similar feature.