Re: Testing Technique when using a DB

On 03/12/2013 08:41 AM, Perry Smith wrote:

> One choice would be to create the database, use it, and then drop it for each test. I would create the database from a template that already has data taken from the production database (and probably trimmed down to a small subset of it). This requires some crafty dancing in the Rails set up since it likes to just attach to a database and run but it could be done. From first blush, this sounds like it would be really slow but may be not.

It depends on your environment (i.e. do you have isolated dev, test and
production or are you testing your code on production machines) and the
nature of your tests (functionality and bugs only or load and performance).

The speed of CREATE DATABASE foo TEMPLATE bar; depends, of course on the
size of your template but I've found it to be fast enough for test
databases (a few hundred MB takes well under a minute on an old
desktop). Try it and see.

>
>
> The other thought is perhaps there is a "snap shot" type concept. I don't see it in the list of SQL commands. A "snap shot" would do exactly what it sounds like. It would take a snap shot and save it somehow. Then a "restore to snap shot" would restore the DB back to that state.
I may be missing something here but pg_dump/pg_restore do exactly that.
Or you could get more complicated and use point-in-time recovery,
external tools like pg_barman or even, perhaps, a file-level snapshot of
the database files (when the PostgreSQL is shut down, of course) to
handle your base test starting point.

Of all the options mentioned my first inclination would be to create
your test db from a known template prior to each test run.

Cheers,
Steve