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
@ teo@digiro.net,+(401)-330.47.28
official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/