Re: Perl script failure => Postgres 7.1.2 database corruption

Frank McKenney <frank_mckenney(at)mindspring(dot)com> writes:
> Hm. So it's likely that the only reason that site users didn't see
> the corruption while all this was going on was that they didn't
> happen to invoke that particular portion of that particular table?

Yes. In fact, since the clobbered data table was a TOAST table, they
could actually have read the affected main-table rows without noticing
a problem, so long as they didn't try to fetch the wide fields.

The "relation does not exist" messages point to some problem in the
system tables as well, but we can no longer guess much about its
extent. It could well have been something that only affected a few
tables.

> If it makes any difference (and I wish I had remembered this
> earlier), the 'summary' table contains large formatted HTML-text
> fields, on the order of 8-14K. We needed Postgres 7.1 to do this.

Right, it would have been those fields that were clobbered.

> Looking back, I suppose we could have renamed all the tables to
> bad_summary, bad_xxxx, etc. However, since I'm fairly new to SQL
> (let alone Postgres), I think I'd have been concerned that the
> corruption might "leak out" at some point and corrupt other tables.

The apparent system-table corruption might indeed have some such
behavior; it's hard to tell without more info. My inclination would
have been to rename the trashed database out of the way and leave it
there for future analysis, if you had the disk space to spare.

> Is there a nicely documented procedure for renaming/moving an entire
> database to a different account that I overlooked in my haste?

It's not well documented. The most secure procedure is to stop the
postmaster and then cp -rp or tar the whole $PGDATA tree. If you're
fairly sure that the problem is confined to one database in an
installation, you could just rename that database out of the way.
(There's no RENAME DATABASE command, but in 7.1 I think it'd work to
update the pg_database row with a new name.)

regards, tom lane