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
