> 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? I usually create a new table, do an insert of data from a select on the old table, then rename the tables. Don't forget to recreate any indexes. I usually write it all up in a single script and run it on a test database before hand. For small tables or tables that don't under normal usage this works reasonably well. For larger more criticle tables I would be tempted to pull the ethernet plug during the changeover. Course that depends on how your system is laid out. NOTE: I don't rely on the alter table add column feature. It seems to ignore the new default value entirely (even on data added after the alteration). Also I suggest using the 'text' data type. It has no length limit other than the 8K tuple size. ************