Howto quickly restore db from dump file

[zbynekwinkler]

This is what I am currently using:

#!/usr/bin/env bash

db=${1-'backup'}
dump=~/gittip-dumps/latest.dump
cores=`grep -c ^processor /proc/cpuinfo`
echo "Clearing $db..."
psql -q -c "drop schema public cascade; create schema public;" $db 2> /dev/null
echo "Restoring $dump..."
pg_restore --jobs $cores --no-acl --no-owner -h localhost -d $db $dump

While it may seem like not much there were several problems I was dealing with. pg_restore has two almost useful options - --clean and --create.

Using --clean one attempts to first drop any entity it is about to load but unfortunately complains a lot when it is not there. Any entity not in the dump file is not touched meaning that this option does to give you clean load.

Using --create looks like something one would want. But you don't. Trust me. It drops the db supplied on the command line and gets db name from the dump file and creates that one (it loads it too). Let me repeat: it operates on 2 different databases.

To speed up the restore it is run in parallel using as many cores you have (it really makes a difference). psql will not stop complaining even with -q so I send it to /dev/null. The db name can be supplied from the command line. If it is not then backup is used as the default.

If anyone wants to integrate this somewhere feel free.

[chadwhitacre]

@zwn Maybe put this in the repo with your description above as a comment at the top?

We just lost the bin directory in #1769, but we could maybe bring that back and move some other scripts into there?

[zbynekwinkler]

@whit537 I was not sure. We have gittip/utils/swaddle.py that gets installed into the env. We also have gittip.py, masspay.py and snapper.py right in the gittip dir. Then we had the bin dir. Shall we pick a direction as where to go? Is anyone else going to be using this except you and me?

[chadwhitacre]

@zwn We also have some one-off scripts that are public only as gists (untip.py, final-gift.py).

[chadwhitacre]

@zwn I think for now we should consolidate scripts in the bin directory. The swaddle script should go away (#468). The gist scripts should go in bin as well.