Thread: replay WAL segments without a "base backup" ?

Hi all;

due to operator error at one of my client sites we're trying to restore a
deleted db.

We did a restore with a pgdump that went fine but the dump is 3weeks old. they
do have WAL segments and they claim that the WAL segments are exactly in line
with the needed transactions - from a timing perspective.  However no base
backup with pg_start_backup() and pg_stop_backup() was ever run.


Is there any way to trick postgres into thinking that it needs to play fwd
these WAL's?  Would it work to simply place them in a dir, create a
recovery.conf file pointing to that dir and restart the cluster?


Thanks in advance...

On Tue, Aug 11, 2009 at 9:05 AM, Kevin Kempter <kevink@consistentstate.com> wrote:

Doubtful.  I'm sure someone more in-the-know than I can give all the explanation needed but if my memory serves the problem is the transaction ID.  You restored a database in an existing cluster or new cluster and those transactions locked up in those WAL files either A) have already occurred or B) have not yet occurred in that cluster.

That is my understanding of it.  There may be more to it.

It'd be a neat service if such a restore were possible.
 * Have all WAL logs from the creation of database X to it's current state
 * Be able to apply those WAL logs to any cluster saying "restore only database X to time/transaction Y"

Greg

Kevin Kempter <kevink@consistentstate.com> writes:
> due to operator error at one of my client sites we're trying to restore a
> deleted db.

> We did a restore with a pgdump that went fine but the dump is 3weeks old. they
> do have WAL segments and they claim that the WAL segments are exactly in line
> with the needed transactions - from a timing perspective.  However no base
> backup with pg_start_backup() and pg_stop_backup() was ever run.

> Is there any way to trick postgres into thinking that it needs to play fwd
> these WAL's?  Would it work to simply place them in a dir, create a
> recovery.conf file pointing to that dir and restart the cluster?

No, there is exactly 0 hope here.  You would need a filesystem-level
backup to work from.  A restore from a pg_dump is not going to reproduce
any of the low-level details (such as OID assignments or tuple
placement) that WAL replay needs to have match.

            regards, tom lane

Kevin Kempter <kevink@consistentstate.com> wrote:

> We did a restore with a pgdump

> do have WAL segments

> Is there any way to trick postgres into thinking that it needs to
> play fwd these WAL's?

No.  pg_dump SELECTs data at the row level and WAL files are at the
page level.  The two techniques aren't compatible in that way.

Theoretically, if you could determine the oids of all the objects in
the old database, through examination of the WAL files, you might be
able to work through the WAL files and recover some data, but it would
be a massive amount of work.

At this point, if you have source documents, logs, or any other
peripheral source of data it is likely to be less work to recover that
way.

In case is is not already obvious, you should never consider that you
have a working backup system without testing a restore onto another
machine.

-Kevin

On Tuesday 11 August 2009 09:49:14 Tom Lane wrote:
> Kevin Kempter <kevink@consistentstate.com> writes:
> > due to operator error at one of my client sites we're trying to restore a
> > deleted db.
> >
> > We did a restore with a pgdump that went fine but the dump is 3weeks old.
> > they do have WAL segments and they claim that the WAL segments are
> > exactly in line with the needed transactions - from a timing perspective.
> >  However no base backup with pg_start_backup() and pg_stop_backup() was
> > ever run.
> >
> > Is there any way to trick postgres into thinking that it needs to play
> > fwd these WAL's?  Would it work to simply place them in a dir, create a
> > recovery.conf file pointing to that dir and restart the cluster?
>
> No, there is exactly 0 hope here.  You would need a filesystem-level
> backup to work from.  A restore from a pg_dump is not going to reproduce
> any of the low-level details (such as OID assignments or tuple
> placement) that WAL replay needs to have match.
>
>             regards, tom lane


What if we could do a pg_start_backup on the running cluster (the one that's 3
weeks behind, tar up the file system, do a pg_stop_backup and walk thru the
recovery steps?

Would there be a  way to force postgres to recognize the wal segments that
werte created earlier (which according to the client include all tx from 3
weeks ago thru today) ?

Kevin Kempter <kevink@consistentstate.com> wrote:

> Would there be a  way to force postgres to recognize the wal
> segments that werte created earlier (which according to the client
> include all tx from 3 weeks ago thru today) ?

No.

The database restored from the output of pg_dump won't have the same
object IDs, row IDs, page locations, etc.  Page images in the WAL
files won't correspond to your new database.  At all.  Period.

Making a PITR backup *now* (as you suggest) will allow you to recover
to what is in the database now, or (as long as you accumulate WAL
files) any future point in time.  It won't rearrange the pages in your
current database to resemble the pages in the lost database.

-Kevin