amy cheng wrote: > > hi, all experts there, greetings! > > Just minutes ago, my boss found out one of the attributes in a > table is too short (varchar 64 for url), we need to make > it wider to 85 A.S.A.P. Seems that alter table can not do it. > So, I used pg_dump, (how to do it gracefully?) immediately drop the table, > shutdown the postmaster (not necessary?) and change the dumped table, and > then restart the postmaster, use pgsql to reload the data. Our database is > not large YET. So, it took ONLY 10 minutes to re-load. > > the job was done. But I feel nervous -- because I expect this will > happen again SOON. What is the "standard" procedure for postgreSQL to > do such kind of things? more specifically (maybe there are some other > ways?): > > 1) how to shutdown postmaster gracefully (I simply used > kill proID. I feel lucky that I do not need to use -9 ! ) so > that I'm sure the data is not corrupted? > 2) how to "shutdown"(drop) just one table gracefully? > 3) any simpler way (alter-table-like) to widden the attribute? > 4) single user mode? > > thanks in advance!!! > gee :) why don't you just : 0. LOCK TABLE IN EXCLUSIVE MODE 1. create a new table with the field adjusted to the needed size. 2. INSERT INTO new_table SELECT ... FROM old_table ... 3. DROP TABLE old_table 4. ALTER TABLE new_table RENAME TO old_table eventually within a transaction. Also a SELECT INTO may be a shortcut, but I didn't test that. -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/ ************
