dbsnap, a git-friendly postgresql backup system

Unlike leapwm, dbsnap is designed to work for anyone using git and postgres. It's my latest and greatest attempt to solve the problem of keeping a database synced up to a git repository. I designed it to be completely trivial to use, and easy to find the correct backup for any particular point on the git commit tree. To set it up, just run

git clone https://github.com/AlexWillisson/dbsnap
cd dbsnap
sudo ./install.sh

Once installed, dbsnap will live in your $HOME/bin directory. It optionally takes one argument for the database name, and must be run inside a git repository. If you call dbsnap without an argument, it will use the current directory's name as the database name. dbsnap requires sudo to manage the permissions in /opt/dbsnap, but that's simple to remove if necessary.

dbsnap fetches the current commit ID and builds a backup of the postgres database named after the commit, builds a compressed tarball, and stores it in /opt/dbsnap/DBNAME/$DBNAME-$COMMIT.tar.gz. Eventually I'll probably write a dbrestore $COMMIT function which will look for the most recent database backup to that commit.

The net result is a /opt/dbsnap that looks something like this:

atw@lorien:~$ ls -R /opt/dbsnap
atw oom

atw-6e85f75.tar.gz atw-af77ef4.tar.gz atw-9c05b52.tar.gz atw-cc5afce.tar.gz

oom-03c4810.tar.gz oom-600f920.tar.gz oom-87327b9.tar.gz oom-d60f33a.tar.gz
oom-501845e.tar.gz oom-7a967db.tar.gz oom-b82939f.tar.gz oom-f7edce9.tar.gz

If you know that the checked out commit in a git repository had a database backup to go with it, you can restore it with this command (remember to back up your current database with pg_dump!)

pg_dump $DBNAME > $DBNAME-`date +%F`.sql
dropdb $DBNAME
createdb $DBNAME
tar -Oxf /opt/dbsnap/$DBNAME/$DBNAME-`git rev-parse --short HEAD`.tar.gz | psql $DBNAME

If the git repository's directory name exactly matches the postgres database, those commands can be simplified to:

pg_dump $DBNAME > $DBNAME-`date +%F`.sql
dropdb $DBNAME
createdb $DBNAME
tar -Oxf /opt/dbsnap/$(basename `pwd`)/$(basename `pwd`)-`git rev-parse --short HEAD`.tar.gz | psql $(basename `pwd`)

Happy coding!