61

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.

Improve this question
4
  • 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, 2011 at 6:05
  • 1
    Are you restoring just the tables that have changed?
    – Jack Douglas
    Jun 21, 2011 at 8:12
  • 2
    @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, 2011 at 22:41
  • 2
    @DrColossus: yes, the dumps are sufficient to restore the previous state, but creating and applying them is very slow.
    – Zilk
    Jun 22, 2011 at 22:42

8 Answers 8

Reset to default
45

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.

Improve this answer
3
  • That's a very good idea. I hadn't thought of database templates at all. Thank you!
    – Zilk
    Jun 22, 2011 at 22:38
  • 1
    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, 2013 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, 2014 at 21:51
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.

Improve this answer
3
  • 3
    or liquibase.org
    – David Portabella
    Jun 12, 2016 at 17:12
  • liquibase does not support it like Stellar, where you may work with the database (e.g. in unit tests) and may have to rollback to some previous tagged state or time.
    – Andreas Covidiot
    Jan 16, 2018 at 9:15
  • Stellar sounds like a great idea, but is not working for me
    – Orlando
    May 2, 2018 at 19:46
5

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).

Improve this answer
1
  • Ah nevermind, I didn't see your comment that mentions you already know about virtualbox.
    – wildpeaks
    Jul 1, 2011 at 12:48
3

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

Improve this answer
1
  • 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, 2011 at 22:37
2

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
Improve this answer
1
  • 9
    This is of no use for large databases. Plus, why torture git with binary files of varying size ?
    – RolandoMySQLDBA
    Apr 22, 2016 at 17:14
1

Though I have to say the Stellar and git reset --hard is an interesting solution, I'll have an issue with bigger databases and tests, and I do use the Virtualbox etc. solutions, how ever, in bigger tests, these to become bit more "problematic" when you are using bare metal etc. solutions.

Thus I HAVE to mention ZFS as a filesystem to consider for these in future for the following reasons that @Peter Eisentraut also mentioned:

  1. Snapshots - especially when you do replication from Prod to QA/DR, you can use that same "filesystem" for the tests:
#On a replication node, rather stop, snap, restore for a "consistent" backup ;)
su -l -c "/usr/bin/m2ee stop" acw_qa
pg_ctlcluster ${=QA} stop --force
zfs destroy -R $SNAPSHOT
pg_ctlcluster ${=REPLICATION} stop --force
zfs snapshot $SNAPSHOT
pg_ctlcluster ${=REPLICATION} start

zfs destroy $CLONE
zfs clone -o mountpoint=$CLONEDIR $SNAPSHOT $CLONE
rm $CLONEDIR/$CLUSTER/recovery.conf
pg_ctlcluster ${=QA} start
su -l -c "/usr/bin/m2ee start" acw_qa

  1. to do a test, just before the test do a postgresql stop as above, zfs snapshot $SNAPSHOT start the postgresql, then to rollback, stop the postgresql, and just zfs rollback $SNAPSHOT

  2. Compression - Postgresql gets a typical 3:1 compression in my databases, so you can do lots of testing more ;)

Improve this answer
1

Following the TEMPLATE answer by Peter, these are the commands I use:

List databases:

psql -h localhost -l

Copy db:

createdb -T <db_to_be_cloned> <clone_name>

Delete db:

dropdb <db_name>

With these commands we can do snapshots:

Create a snapshot for mydb db:

createdb -h localhost -T mydb sshot-mydb-001

Restore mydb from the snapshot:

dropdb -h localhost mydb
createdb -h localhost -T sshot-mydb-001 mydb
Improve this answer
0

Yet another option that could be experimented would be to actually save a copy of the postgresql data directory, then just rewrite the existing directory with the copy when you want to restore it. It will require more space in the disk, but wil definitely be faster than restoring from a backup. I'm not sure if this would be faster than the template method, though, so it would be a good idea to make some tests, first.

Improve this answer
2
  • If you just copy the data files from a directory while the server is running, isn't there a danger of making a backup with DB writes in progress? Or does this approach assume you shut down Postgresql before doing the backup or restore?
    – Adam Parkin
    Jan 28, 2020 at 21:44
  • 1
    That supposes Postgresql is shut down during the operation.
    – Haroldo_OK
    Jan 29, 2020 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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