Re: Advice for "hot-swapping" databases

Kynn Jones wrote:
> Hi. I'm trying to automate the updating of a database. This entails
> creating the new database from scratch (which takes a long time), under a
> different name, say mydb_tmp, and once this new database is ready, doing a
> "hot swap", i.e. renaming the existing database to something like
> mydb_20080613 and renaming the newly created database mydb.
> I have automated everything up to the creation of mydb_tmp. (This is done
> with an unholy mix of Perl and shell scripts.) I'm trying to figure out the
> best way to perform the hot swap.

One suggestion I would make is that as long as your database schema
hasn't changed, you should be able to keep a copy of the 'empty'
database around and then rather then build it from scratch, simply drop
the backup in place. I've done this very recently when testing our
Slony setup. I create the database structure (initdb, createdb, load
schema), then, after shutting down the postmaster, create a tarball of
the whole directory. This has saved me a good bit of time when having
to drop databases and then recreate the empty ones.

I'm by no means an expert, so others, please feel free to shoot holes in
this solution if it is not viable.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin