Skip site navigation (1) Skip section navigation (2)

Re: Best practice for altering a table

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tim Wilson <wilson(at)visi(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Best practice for altering a table
Date: 2002-11-11 19:24:37
Message-ID: 200211111124.37791.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Patrick HatcherDate: 2002-11-11 19:51:14
Subject: Cancelling long running query?
Previous:From: Josh BerkusDate: 2002-11-11 18:16:34
Subject: Re: Best practice for altering a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group