Re: [GENERAL] shutdown gracefully & single user mode?
Jeff, Bryan, Teodor: thank you all so much!!! one related/furthur question: >0. LOCK TABLE IN EXCLUSIVE MODE this is what I'm concerned. Now, the "shutdown" a table gracefully is solved. How about the whole postmaster (in some other situation), how can I shut it down GRACEFULLY (i.e., I know that the data will not be corrupted because of my "shutdown". Also, how do I know that there is no user connected? BTW, It's a cgi-db). amy __ Get Your Private, Free Email at http://www.hotmail.com
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 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 @ [EMAIL PROTECTED],+(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?
> 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.
[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? thanks in advance!!! amy __ Get Your Private, Free Email at http://www.hotmail.com
