Re: Backend died while dropping index

brianb-pggeneral(at)edsamail(dot)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