Thread: Revert TRUNCATE CASCADE?

Hi all,


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?


There is a "pg_dumpall" backup from yesterday, and also pg_xlogs from
well before the dumpall-file until the TRUNCATE command. Unfortunately,
there is no file system backup from the xlog timeframe and as far as I
understood the documentation, a DUMP is no valid base for PITR. Time to
rework backup practices I guess...


Thank you very much for any advice,
best regards

    -hannes

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.

> There is a "pg_dumpall" backup from yesterday, and also pg_xlogs from
> well before the dumpall-file until the TRUNCATE command.
Unfortunately,
> there is no file system backup from the xlog timeframe and as far as I
> understood the documentation, a DUMP is no valid base for PITR. Time
to
> rework backup practices I guess...

I agree.

Yours,
Laurenz Albe

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