Обсуждение: Backend died while dropping index
The backend died while I was dropping an index on a table. Now I cannot access my data! Help! My table looks like create table user_usage (email varchar(100),date date); I created the index: CREATE INDEX user_usage_email_idx on user_usage(email); When I dropped the index, the backend died. Now whenever I try to do an insert I get: ERROR: mdopen: couldn't open user_usage_email_idx: No such file ordirectory How do I fix this? This is on 6.5. Thanks, Brian -- Brian Baquiran <brianb@edsamail.com> http://www.baquiran.com/ AIM: bbaquiran
brianb-pggeneral@edsamail.com wrote: > > The backend died while I was dropping an index on a table. Now I cannot > access my data! Help! > > My table looks like > > create table user_usage (email varchar(100),date date); > > I created the index: > CREATE INDEX user_usage_email_idx on user_usage(email); > > When I dropped the index, the backend died. Now whenever I try to do an > insert I get: > > ERROR: mdopen: couldn't open user_usage_email_idx: No such file > ordirectory > > How do I fix this? This is on 6.5. > > Thanks, > Brian The easiest way to deal with this, is, as the "postgres" user: 1. Change to the location of the datafiles, which, for a database, called "mydatabase" will look something like: cd /usr/local/pgsql/data/base/mydatabase 2. Create an empty file of the name of the index: touch user_usage_email_idx 3. Delete the file using psql: psql mydatabase > DROP INDEX user_usage_email_idx; 4. Recreate the index as appropriate Hope that helps, Mike Mascari
Mike Mascari <mascarm@mascari.com> writes: > brianb-pggeneral@edsamail.com wrote: >> How do I fix this? This is on 6.5. > 1. Change to the location of the datafiles, which, for a > database, called "mydatabase" will look something like: > 2. Create an empty file of the name of the index: > 3. Delete the file using psql: > 4. Recreate the index as appropriate And 5. Update to 7.0.2 at your earliest convenience, if not sooner. No smileys here. I've noticed several people lately saying they're still on 6.5 (or earlier :-() because they're "waiting for 7.0 to settle out". Wake up and smell the roses folks. 7.0 certainly has bugs, and maybe a few teething pains yet to go, but it has far fewer bugs than any prior release --- and most of the bugs it does have are undoubtedly present in prior releases as well. Holding off on an update isn't buying you anything except continued exposure to the hundreds of bugs we've swatted since 6.5. regards, tom lane
I respectfully disagree Tom, for various reasons: 1) Postgres 6.5.3 is a known quantity, and there are more people familiar with the common, critical problems, so there are more people who can help solve problems. 2) 7.0.2 undoubtedly has some new bugs and problems that are not apparent yet - bugs that could sink a commercial business that depends on Postgres (like mine) 3) In some cases (perhaps not 6.5.3, but some), the entire database must be dumped and restored, which is difficult for a commercial business to "just do it". 4) Various 3rd party applications and various exotic queries need to be verified against the new platform. 5) A variety of the common existing queries need to be re-evaluated and compared in terms of speed, since there have been several reports of 'slowness' in queries. I'm sure you're frustrated by us laggards. But frankly, my entire business depends on Postgres, and I'm not going to risk it just because there's an upgrade that may or may not fix some of my problems, and will undoubtedly create new ones. If you can get the guys from Great Bridge to send out general announcements that they have tested it, and are satisfied that it will work properly, then I will consider upgrading sooner. jb ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Mike Mascari" <mascarm@mascari.com> Cc: <brianb-pggeneral@edsamail.com>; <pgsql-general@postgresql.org> Sent: Sunday, June 18, 2000 1:12 AM Subject: Re: [GENERAL] Backend died while dropping index > Mike Mascari <mascarm@mascari.com> writes: > > brianb-pggeneral@edsamail.com wrote: > >> How do I fix this? This is on 6.5. > > > 1. Change to the location of the datafiles, which, for a > > database, called "mydatabase" will look something like: > > 2. Create an empty file of the name of the index: > > 3. Delete the file using psql: > > 4. Recreate the index as appropriate > > And > > 5. Update to 7.0.2 at your earliest convenience, if not sooner. > > No smileys here. I've noticed several people lately saying they're > still on 6.5 (or earlier :-() because they're "waiting for 7.0 to > settle out". Wake up and smell the roses folks. 7.0 certainly has > bugs, and maybe a few teething pains yet to go, but it has far fewer > bugs than any prior release --- and most of the bugs it does have > are undoubtedly present in prior releases as well. Holding off on > an update isn't buying you anything except continued exposure to the > hundreds of bugs we've swatted since 6.5. > > regards, tom lane
Вложения
> 1) Postgres 6.5.3 is a known quantity, and there are more people familiar > with > the common, critical problems, so there are more people who can help solve > problems. > 2) 7.0.2 undoubtedly has some new bugs and problems that are not > apparent yet - bugs that could sink a commercial business that depends > on Postgres (like mine) I'll throw in my two cents, your experience hopefully won't be the same as ours. This week, 6.5.3 somehow horked it's own data quite thoroughly for us. Vacuuming the table would fail miserably - even trying to *describe* the tables shut down the back end. It would complain about a tuple being too large - yet the largest tuple in the table was around 6K. Pg_dump and pg_dumpall would also promptly shut down the back end as soon as they tried to touch the table. Boy, that makes it fun. The cause (we suspect) is from one day when another programmer here did something really dumb, and filled up the disk. The docs for 7.0.2 say that it is supposed to be more reliable in situations like that, and so we decided that it was simply too dangerous to stay with a version that can mangle it's own data beyond repair. So, there are arguments on both sides of the coin. It's critical for your database to be running at all times, so you don't want to bring it down to upgrade. At the same time, the upgrade may buy you a better chance of staying up - or it might break everything. You'll have to make an educated guess, and possibly just flip a coin. And, of course, one of the prime rules of system administration is to make changes reversible. Back up the old version and the data, libraries, interfaces, etc.. If something goes terribly wrong during the upgrade, you can always switch back to the old version and be up and running in just a few minutes.... Ah - Here's one more thing, just in case it benefits anyone. A good upgrade procedure is to take a different machine (even if it's pretty low-end) that is similar in OS and hardware, and install the new version on it first. Copy the data over. *Then* test the things that are likely to break, so that you don't have any surprises. steve