Re: help with data recovery from injected UPDATE

Thanks for the replies.

Tom Lane wrote:
> This being 8.1, if you haven't turned on autovacuum there is some chance
> of that.

Unfortunately, autovacuum was on. I don't recall ever turning it on,
but this database is over two years old; it's possible that I blindly
followed advice from pgAdmin or something way back when.

Merlin Moncure wrote:
> does postgresql 8.1 have last_vacuum time in the pg_stat_all_tables
> coiumn?

I did not see a column called last_vacuum in the pg_stat_all_tables table.

> postgresql  8.1 supports pitr archiving.  you can
> do continuous backups and restore the database to just before the bad
> data.

I'm learning about this now. I'm cautiously optimistic, as my pg_xlog
directory contains some files whose timestamp is near the time of the
'incident'.

By "backup" do you mean the contents of a pg_dump? The most recent
dump was two months ago. I'm worried that my log files might not go
far enough back in time to restore the table from the most recent
dump.

Both Tom's and Merlin's suggestions carry a significant learning
curve. I'll do what I can in the coming days and post to the list if
anything noteworthy happens.

> plus, there is no way you are escaping the obligatory 'where are your
> backups?'. :-).

It's a classic story. I'm volunteering about one day per month for
this project, learning SQL as I go. Priority was always given to the
"get it working" tasks and never the "make it safe" tasks. I had/have
grandiose plans to rewrite the whole system properly after I graduate.
Unfortunately, the inevitable corruption didn't wait that long.

Cheers.

-Gus