It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself.

I know that I could wrap my test in a transaction and rollback the transaction after the test run. But it would be very nice if PostgreSQL could provide some kind of global snapshoting or throwaway overlay. In an ideal case such a feature would cover all state of the database including schemas and stored procedures.

3 Answers 3


One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template.

create database test_db 
   with template = base_test_db;

Using this, everything that is in the template will be copied to the new database (including data, stored procedures, extensions).

After each test suite, you simple drop test_db and re-create it with the above statement.

This approach might not be fast enough if you do it before each test, but that highly depends on how large your test database is and how fast the harddisk of your build environment is.

  • You would drop base_test_db? Or just test_db? Not trolling but I'm a rookie with databases and to me it would seem you would want to drop test_db.
    – adam-beck
    Commented Jul 27, 2016 at 19:06
  • 1
    @adam-beck: yes, you are absolutely right. Thanks, corrected.
    – user1822
    Commented Jul 27, 2016 at 19:21

Every time I run integration tests, I build my database from scratch off scripts stored in git. In the past, whenever slowness would become a problem, we would run the server locally on developer's workstation, with the database on RAM Disk - that should be fast enough. We did that many times for our applications powered by SQL Server

I have not yet done that for PostgreSql, but I am very new to it.



"Roll back all commands that were executed after the savepoint was established. The savepoint remains valid and can be rolled back to again later, if needed.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.