On Mon, Oct 22, 2012 at 7:52 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
