Thread: Advice for "hot-swapping" databases

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.

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

If the databases are on the same machine, I could see creating a template
database and then use:

createdb -T templateName newDbName

It will create the new db exactly like the template.  I think createdb does
this by default using the database template1 as the template.

My databases are all on different machines, so I use perl/psql to create
them.  I have a sql directory that has a separate sql file for each table
and function I want to create and the perl script 'cat's them to psql in the
correct order.

Woody