Thread: shutdown gracefully & single user mode?

shutdown gracefully & single user mode?

From
"amy cheng"
Date:
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!!!

amy

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

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

From
"Bryan White"
Date:
> 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.


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

From
Jeff Hoffmann
Date:
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

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

From
Teodor Cimpoesu
Date:
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@digiro.net,+(401)-330.47.28

official home page ~ http://www.digiro.net/
Internet  web page ~ http://internet.digiro.net/

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

From
"Moray McConnachie"
Date:
----- Original Message -----
From: Teodor Cimpoesu <teo@digiro.net>
Cc: <pgsql-general@postgreSQL.org>
Sent: Tuesday, September 14, 1999 5:11 PM
Subject: Re: [GENERAL] shutdown gracefully & single user mode?

I have not a lot of technical know-how on locking, but a couple of queries
present themselves from this...
Shouldn't step 0 be LOCK TABLE IN ACCESS EXCLUSIVE MODE ?
& what happens to the lock immediately after the table is dropped and before
it is recreated? At that stage if clients try to read from the table, should
they get denied access by the lock, or should they get an error saying no
such table?

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


----------------------------------------------------------------------------
----------
Moray.McConnachie@computing-services.oxford.ac.uk