Thread: Shit happens

Shit happens

From
Dick Kniep
Date:
Hi list,

Happy newyear to all of you.

We use WAL files for backup purposes. Now we need to restore up to a certain date in december, we have a base backup
fromoctober 24th, but the WAL files start only at october 28th. Is there a way that we can use the WAL files to recover
dataup to half december anyway? I know it is a long shot, but we would be VERY happy if there is a way to have a
(possiblyinconsistent) database that contains the data from the base backup plus the data from the WAL files.   

Cheers,
D.Kniep

Re: Shit happens

From
Andreas Kretschmer
Date:
Dick Kniep <dick.kniep@lindix.nl> wrote:

> Hi list,
>
> Happy newyear to all of you.
>
> We use WAL files for backup purposes. Now we need to restore up to a
> certain date in december, we have a base backup from october 24th, but
> the WAL files start only at october 28th. Is there a way that we can
> use the WAL files to recover data up to half december anyway? I know

No. You can't fill the gap.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Shit happens

From
Dick Kniep
Date:
Hi list,

Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not
directlypossible to get that data, but I am quite desparate to get the data back. If one way or another the data is
(exceptfor the 4 days we really have no data for) accessible, we will write a program to recover the data into the
productiondatabase. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference
whereto find enough information to do this) I would be very happy. 


Cheers,
D. Kniep


-----Oorspronkelijk bericht-----
Van: Andreas Kretschmer <akretschmer@spamfence.net>
Verzonden: za 01-01-11 10:04:11
Aan: pgsql-general@postgresql.org;
Onderwerp: Re: [GENERAL] Shit happens

> Dick Kniep <dick.kniep@lindix.nl> wrote:
>
> > Hi list,
> >
> > Happy newyear to all of you.
> >
> > We use WAL files for backup purposes. Now we need to restore up to a
> > certain date in december, we have a base backup from october 24th, but
> > the WAL files start only at october 28th. Is there a way that we can
> > use the WAL files to recover data up to half december anyway? I know
>
> No. You can't fill the gap.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> !DSPAM:4d1eee0b160842018316037!
>
>

Re: Shit happens

From
Alban Hertroys
Date:
On 2 Jan 2011, at 13:19, Dick Kniep wrote:

> Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not
directlypossible to get that data, but I am quite desparate to get the data back. If one way or another the data is
(exceptfor the 4 days we really have no data for) accessible, we will write a program to recover the data into the
productiondatabase. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference
whereto find enough information to do this) I would be very happy. 


I suppose you did already try restoring that dump and applying the log files to it and that failed?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4d20711d11541020214904!



Re: Shit happens

From
Radosław Smogura
Date:
Hi,

I can only think, You could try to parse page files. If you didn't do VACCUM
FULL you could try to restore it, if those rows was not overwritten after
VACCUM (normal). Maybe some "internal" guys can give you answer (if e.g.
VACCUM (nomral) zeros row).

The programm to restore will bo so tricky as file system undelete, and it will
have same requirements as.

Here I found some link (I gooled "postgresql find deleted tuples"):

http://postgresql.1045698.n5.nabble.com/Restore-deleted-rows-td2005963.html

I think I saw quite interesting idea of "trash" for vaccumed tuples.

Kind regards,
Radosław Smogura
http://www.softperience.eu


Alban Hertroys <dalroi@solfertje.student.utwente.nl> Sunday 02 January 2011
13:35:29
> On 2 Jan 2011, at 13:19, Dick Kniep wrote:
> > Thanks for the clear answer. However, this is the simple answer that is
> > also in the manual. Yes I know it is not directly possible to get that
> > data, but I am quite desparate to get the data back. If one way or
> > another the data is (except for the 4 days we really have no data for)
> > accessible, we will write a program to recover the data into the
> > production database. So if anyone of you knows about a way to access the
> > actual data in the WAL file (or a reference where to find enough
> > information to do this) I would be very happy.
>
> I suppose you did already try restoring that dump and applying the log
> files to it and that failed?
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:737,4d20711d11541020214904!

Re: Shit happens

From
Dick Kniep
Date:
Hi list,

Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not
directlypossible to get that data, but I am quite desparate to get the data back. If one way or another the data is
(exceptfor the 4 days we really have no data for) accessible, we will write a program to recover the data into the
productiondatabase. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference
whereto find enough information to do this) I would be very happy. 


Cheers,
D. Kniep

-----Oorspronkelijk bericht-----
Van: Andreas Kretschmer <akretschmer@spamfence.net>
Verzonden: za 01-01-11 10:04:11
Aan: pgsql-general@postgresql.org;
Onderwerp: Re: [GENERAL] Shit happens

> Dick Kniep <dick.kniep@lindix.nl> wrote:
>
> > Hi list,
> >
> > Happy newyear to all of you.
> >
> > We use WAL files for backup purposes. Now we need to restore up to a
> > certain date in december, we have a base backup from october 24th, but
> > the WAL files start only at october 28th. Is there a way that we can
> > use the WAL files to recover data up to half december anyway? I know
>
> No. You can't fill the gap.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> !DSPAM:4d1eee0b160842018316037!
>
>

Re: Shit happens

From
Jeff Davis
Date:
On Sun, 2011-01-02 at 10:31 +0100, Dick Kniep wrote:
> Hi list,
>
> Thanks for the clear answer. However, this is the simple answer that
> is also in the manual. Yes I know it is not directly possible to get
> that data, but I am quite desparate to get the data back. If one way
> or another the data is (except for the 4 days we really have no data
> for) accessible, we will write a program to recover the data into the
> production database. So if anyone of you knows about a way to access
> the actual data in the WAL file (or a reference where to find enough
> information to do this) I would be very happy.
>

The WAL segment files will contain a lot of information (including some
full page images), which may be enough to recover some of your data.
Obviously, you'll be missing most of the data from that 4-day period,
but there is some hope.

However, pulling that data out of the WAL and making sense out of it
will be tricky, indeed (as Radosław already mentioned).

Email lists are probably not the best way to make it through this kind
of tricky recovery. You might consider contacting one of the PostgreSQL
consulting companies:

http://www.postgresql.org/support/professional_support

where someone experienced with WAL recovery can help you personally.
Some of these organizations employ people who have done extensive
development on the WAL recovery system and know it _very_ well.

Regards,
    Jeff Davis


Re: Shit happens

From
Craig Ringer
Date:
On 01/02/2011 11:19 PM, Dick Kniep wrote:
> Hi list,
>
> Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not
directlypossible to get that data, but I am quite desparate to get the data back. If one way or another the data is
(exceptfor the 4 days we really have no data for) accessible, we will write a program to recover the data into the
productiondatabase. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference
whereto find enough information to do this) I would be very happy. 

What happened to the original data? You might have more luck with that
than working with an incomplete PITR backup, depending on what went wrong.

Otherwise, you may be able to use xlog analysis tools to extract
information from the transaction logs, then attempt to manually
reconstruct your database minus the lost changes for the period you have
no records for. These tools are both rather old, and may need porting to
new versions of Pg, but will serve as a good starting point. If you're
as desperate as you sound, you may want to hire someone experienced with
Pg's source code to port the tools and enhance them if necessary to fit
your needs.

http://xlogviewer.projects.postgresql.org/

http://pgfoundry.org/projects/xlogviewer/


Tom Lane wrote the original xlogviewer code, which was then enhanced by
Diogo Biazus and later Euler Taveira de Oliveira. Tom is a core
postgresql developer. I haven't seen Diogo or Euler around on the
mailing lists, but that doesn't mean they're not involved.

--
Craig Ringer