lost data, possible playback?

Hi!

A user accidentally deleted some results (i.e. tuples in a postgresql
database) created this morning.

I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab out
3 o'clock this morning.

The results where created from about nine o'clock until noon, when the user
accidentally removed the results.

Would it be possible to replay the WAL logs and get the results from
pg_xlog + the dump? Or from a file system snapshot as of now (five pm) +
pg_xlog?

Regards,
Palle

No.

> Or from a file system snapshot as of now (five pm) + pg_xlog?

No.

--On torsdag, december 06, 2007 10.41.40 -0600 Scott Marlowe
<scott.marlowe@gmail.com> wrote:

> On Dec 6, 2007 10:24 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
>> Hi!
>>
>> A user accidentally deleted some results (i.e. tuples in a postgresql
>> database) created this morning.
>>
>> I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab
>> out 3 o'clock this morning.
>>
>> The results where created from about nine o'clock until noon, when the
>> user accidentally removed the results.
>>
>> Would it be possible to replay the WAL logs and get the results from
>> pg_xlog + the dump?
>
> No.
>
>> Or from a file system snapshot as of now (five pm) + pg_xlog?
>
> No.

So, I *must* have an image of the entire database, and I *must* have all
WAL files since the image snapshot was taken?

/Palle

--On torsdag, december 06, 2007 17.45.57 +0100 Palle Girgensohn
<girgen@pingpong.net> wrote:

>
>
> --On torsdag, december 06, 2007 10.41.40 -0600 Scott Marlowe
> <scott.marlowe@gmail.com> wrote:
>
>> On Dec 6, 2007 10:24 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
>>> Hi!
>>>
>>> A user accidentally deleted some results (i.e. tuples in a postgresql
>>> database) created this morning.
>>>
>>> I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab
>>> out 3 o'clock this morning.
>>>
>>> The results where created from about nine o'clock until noon, when the
>>> user accidentally removed the results.
>>>
>>> Would it be possible to replay the WAL logs and get the results from
>>> pg_xlog + the dump?
>>
>> No.
>>
>>> Or from a file system snapshot as of now (five pm) + pg_xlog?
>>
>> No.
>
> So, I *must* have an image of the entire database, and I *must* have all
> WAL files since the image snapshot was taken?

Is the real reason for the negative answer that there is a "hole" in the
pg_xlogs from 3 am - 9 am?

Palle

Palle Girgensohn <girgen@pingpong.net> writes:
> Is the real reason for the negative answer that there is a "hole" in the
> pg_xlogs from 3 am - 9 am?

That's half of it.  The other half is that what you have from 3am is a
pg_dump, not a filesystem-level snapshot of the database, and you'd need
the latter to be able to replay the WAL log against.

   regards, tom lane

--On torsdag, december 06, 2007 12.22.45 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> Is the real reason for the negative answer that there is a "hole" in the
>> pg_xlogs from 3 am - 9 am?
>
> That's half of it. The other half is that what you have from 3am is a
> pg_dump, not a filesystem-level snapshot of the database, and you'd need
> the latter to be able to replay the WAL log against.

OK, my assumptions where correct. Thanks.

/Palle

No, the problem is that you don't have a system image from before the
problem occurred.  You need a file system backup from before the
delete occurred, and a set of logs from the time the file system
backup occurred until the accidental delete occurred.