On Mon, Oct 22, 2012 at 7:52 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Hannes Erven wrote:
>> today I ran into an issue I believed to be a FAQ, but fortunately it
>> doesn't seem so as I could find any resources related to this... :-/
>>
>> A misguided click in PGADMIN executed a "TRUNCATE CASCADE" on a rather
>> central table of my schema, which resulted in most important tables
>> being emptied. Postgresql (9.0) was cleanly shut down immediately.
>>
>>
>> Is there any chance to get the data back?
>
> A dirty shutdown (-m immediate) would have been better.
>
> Basically it is work for an expert to try and salvage data like this.
>
> If (auto-)VACUUM has not run yet, maybe pg_resetxlog can do something
> for you. But keep a copy of the original cluster before you start
> messing around.
nah -- that would only apply to records that were deleted (as in, with
SQL delete). TRUNCATE has different mechanics -- it roughly
translates to: make new table like the old one, do file level delete
on the old one, and swap the new one in. That's why it's so much
faster -- only the operation itself has to be logged, not what it's
doing.
The only recourse is to restore from backup assuming you are not
interested on pursuing 'undelete' strategies in the file system which
are unlikely to work anyways.
A filesystem level backup + stream of archived xlogs would have been
ideal -- then you could have brought the system back to precisely life
as it was precisely before the accident.
merlin
