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

> 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.