Re: Revert TRUNCATE CASCADE?

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