From: | Teodor Cimpoesu <teo(at)digiro(dot)net> |
---|---|
To: | |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] shutdown gracefully & single user mode? |
Date: | 1999-09-14 16:11:38 |
Message-ID: | 37DE73BA.CAA8BD85@digiro.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)digiro(dot)net,+(401)-330.47.28
official home page ~ http://www.digiro.net/
Internet web page ~ http://internet.digiro.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Hitesh Patel | 1999-09-14 16:31:35 | max() question |
Previous Message | Jeff Hoffmann | 1999-09-14 15:21:53 | Re: [GENERAL] shutdown gracefully & single user mode? |