Обсуждение: 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
> 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.
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
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/
----- 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