First of all, I'm a developer, not a DBA or sysadmin; please be gentle 🙂
I'm working on an application workflow where a single user action will trigger complex changes in the database – creating hundreds of records in some tables, updating hundreds of records in others, etc. All in all, about 12 tables (out of ~100) are touched by this action. Due to the complexity, it's very hard for me to manually revert all of the changes before I can run another test. During most of my development time, I can simply insert a "ROLLBACK" statement near the end of the workflow, but when I get close to commiting my changes, I need to test the real thing.
I have a local copy of the production database to work with. In my case, dumping and restoring between tests is faster than writing a script to undo all the changes. It's faster, but it's still slowing me down a lot (the restore takes around 20 minutes on my ageing laptop). Is there any way I can save a snapshot of the current state of the database, and then quickly restore it?
I'm guaranteed to be the only user on the system, and I have root access. The database dump is ~100MB when tar'ed and gzip'ed. PostgreSQL version is 8.3.
Thanks in advance for any helpful ideas.
Best Answer
You could use file-system level snapshots, but that is often pretty cumbersome, needs special file systems, and is not always available, especially on aging laptops. ;-)
How about you create your base state as a database, and then create a new database from it for your test run, using the
CREATE DATABASE ... TEMPLATEfunctionality. After the test, you throw that database away. Then your speed constraint is essentially only the time tocp -Rthe database directory. That's about as fast as you're going to get without file system snapshot magic.