Re: [GENERAL] shutdown gracefully & single user mode?

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 way i would do this is add a new column to the table that's the
right size (url2), copy the contents of url1 to url2, rename the
original table to a new table name, then select all the fields except
the original url field into a new table (called the same as the original
one, which is now named something else).  it sounds a lot more
complicated than it is.  maybe this will help:

alter table mytable add column url2 varchar(85);
update mytable set url2=url;
alter table mytable rename to mytable2;
select field1, field2, field3, url2 as url into mytable from mytable2;

probably will save some time, plus i've always though dumping tables was
a PITA.

jeff