Обсуждение: db crash after power crash

Hi.

Some hours ago there was a power failure with my test machine while
active inserting data. After power back the OS done some "fsck", clean
some files (related to postgres working directory). Then I connect to
my DB and see this:

billing=# \d
ERROR:  index "pg_attribute_relid_attnum_index" is not a btree
billing=# reindex index pg_attribute_relid_attnum_index;
ERROR:  catalog is missing 1 attribute(s) for relid 2678

I searched through archives and tried to do some:

# reindexdb  -s -U root -d billing
Password:
reindexdb: reindexing of system catalogs failed: ERROR:  index
"pg_attribute_relid_attnum_index" is not a btree
# reindexdb  -U root -d billing
Password:
reindexdb: reindexing of database "billing" failed: ERROR:  index
"pg_attribute_relid_attnum_index" is not a btree

as you can see, without success. And so with vacuumdb.

That all was on my test (learning) environment... But this may occur
on production machine (who knows, anything may happens) and I want to
know how to play with such situations (or workaround them). My only
idea - to get the last 'pg_dump' file, drop database and restore it
from that file... but it is ugly idea because many recent data will be
lost. Is there other ways?

--
engineer

"Anton Maksimenkov" <anton200@gmail.com> writes:
> # reindexdb  -s -U root -d billing
> Password:
> reindexdb: reindexing of system catalogs failed: ERROR:  index
> "pg_attribute_relid_attnum_index" is not a btree

This will not work unless backend is started with -P option.
See the REINDEX reference page for details.

            regards, tom lane

May I ask - how can I do it...? My ordinary startup command (from
rc.local) like this
# su -l _postgresql -c "nohup /usr/local/bin/pg_ctl start -D
/var/postgresql/data -l /var/postgresql/logfile -o '-D
/var/postgresql/data'"

But I tried

# su -l _postgresql
$ postgres -D /var/postgresql/data/ -P billing
FATAL:  catalog is missing 1 attribute(s) for relid 2662

Anyhow, after all it is completely refuse to log me on. After shutting
down and ordinary startup the RDBMS:

$ export PGOPTIONS="-P"
$ psql -d billing
Password:
psql: FATAL:  catalog is missing 1 attribute(s) for relid 2662

$ unset PGOPTIONS
$ psql -d billing
Password:
psql: FATAL:  catalog is missing 1 attribute(s) for relid 2662

I think I broke something while experimenting with feeding -P to my
ordinary startup string...
--
engineer

"Anton Maksimenkov" <anton200@gmail.com> writes:
> But I tried

> # su -l _postgresql
> $ postgres -D /var/postgresql/data/ -P billing
> FATAL:  catalog is missing 1 attribute(s) for relid 2662

In that case I'm afraid you're stuck: it looks like it's not just the
index but the underlying catalog that's lost data.  Were you running
with fsync off by any chance?

For forensic purposes it might be interesting to dump out the
pg_attribute catalog (file 1249) with pg_filedump, just to see what sort
of corruption is there.  But I'm afraid the odds of resurrecting the
database are small.

            regards, tom lane