Can PostgreSQL support integration test with some kind of throwaway overlay?

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.

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.

StackExchange Database Administrators Q#53198, answer score: 13