Re: Best practice for altering a table

Tim,

> This sounds appealing. I have a number of foreign keys, sequences, etc. So
> do you dump the whole DB, delete the whole thing, and reload? The key
> question is whether I have to nuke the old DB completely.

Yeah. If it's a production database, I would take the more careful process
of:

1) dump database an edit files (don't forget to edit the data files to match
the new table structure! See the dump options to help with this)
2) copy the dump, and make your edits on the copy.
3) load the edited dump files onto another database or mirror server. Look
for errors during the load process, and then test the loaded database for
problems.
4) if 3) checks out OK, drop the production database and replace it with the
edited load.
5) test the production database; if anything looks wonky, dump and re-load the
unedited files.

I think you'll find that setting up a mirror server (with full versions of the
interface, etc.) is worth the trouble in terms of letting you test changes.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco