Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
You say you have the database dump, isn't that sufficient? Test your system, if isomething goes wrong, use the dump to get the DB back to its original state and continue developing. – DrColossos Jun 21 '11 at 6:05
Are you restoring just the tables that have changed? – Jack Douglas Jun 21 '11 at 8:12
@Jack Douglas: I'm restoring the complete DB from the dump. The tables in question make up about 2/3 of the data, and I'd still have to worry about the correct restoration order and foreign key restraints. – Zilk Jun 22 '11 at 22:41
@DrColossus: yes, the dumps are sufficient to restore the previous state, but creating and applying them is very slow. – Zilk Jun 22 '11 at 22:42
up vote 22 down vote accepted

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 ... TEMPLATE functionality. After the test, you throw that database away. Then your speed constraint is essentially only the time to cp -R the database directory. That's about as fast as you're going to get without file system snapshot magic.

share|improve this answer
That's a very good idea. I hadn't thought of database templates at all. Thank you! – Zilk Jun 22 '11 at 22:38
This is a great solution, 5x faster than drop-restore but is has one downside: you need to drop current connections before doing this otherwise it will fail to run. – sorin Oct 8 '13 at 14:04
Update: this will not work in production because the source database will have connections to it. We need another solution. – sorin Mar 4 '14 at 21:51

Probably not the answer you are hoping for, but have you considered some lower level of snapshotting - LVM for example?

share|improve this answer
Yes, that did come to mind. Unfortunately, filesystem snapshots aren't supported by the FS I'm currently using (ext3). Another option would be to set up a VM like Virtualbox for the test runs. – Zilk Jun 22 '11 at 22:37

If your database runs in Virtualbox, you can easily save snapshots and restore snapshots of both the database state and the OS itself in a few seconds (or 1-2 minutes if you really have a lot of data in the database or the OS or very little memory allocated to the virtual machine) for free.

In your/most cases, it would be best to install a lightweight linux (than a Windows server) for running the virtual machine where the database is hosted given you mention you have little ressources available on your laptop.

On the production site, I use MediaTemple's snapshot backups to achieve the same result (but it's 20$ per backup slot and specific to that webhosting service, so that may not fit you).

share|improve this answer
Ah nevermind, I didn't see your comment that mentions you already know about virtualbox. – wildpeaks Jul 1 '11 at 12:48

Found this question when trying to do the same and ended up using git on the postgresql data directory. Discarding the changes is as easy as:

git reset --hard
share|improve this answer
This is of no use for large databases. Plus, why torture git with binary files of varying size ? – RolandoMySQLDBA Apr 22 at 17:14

Use Stellar, it's like git for databases:

Stellar allows you to quickly restore database when you are e.g. writing database migrations, switching branches or messing with SQL. PostgreSQL and MySQL (partially) are supported.

share|improve this answer
or – David Portabella Jun 12 at 17:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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