Re: [GENERAL] shutdown gracefully & single user mode? - Mailing list pgsql-general

From Bryan White
Subject Re: [GENERAL] shutdown gracefully & single user mode?
Date
Msg-id 004f01befec4$aeb51260$22d260d1@arcamax.com
Whole thread Raw
In response to shutdown gracefully & single user mode?  ("amy cheng" <amycq@hotmail.com>)
List pgsql-general
> 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.


pgsql-general by date:

Previous
From: "amy cheng"
Date:
Subject: shutdown gracefully & single user mode?
Next
From: Jeff Hoffmann
Date:
Subject: Re: [GENERAL] shutdown gracefully & single user mode?