pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
lost data, possible playback?
7+ messages / 3 participants
[nested] [flat]

* lost data, possible playback?
@ 2007-12-06 16:24  Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Palle Girgensohn @ 2007-12-06 16:24 UTC (permalink / raw)
  To: pgsql-admin; +Cc: kudo@pingpong.net

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 16:41  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2007-12-06 16:41 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-admin; kudo@pingpong.net

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 16:45  Palle Girgensohn <girgen@pingpong.net>
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Palle Girgensohn @ 2007-12-06 16:45 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin; kudo@pingpong.net



--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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 17:11  Palle Girgensohn <girgen@pingpong.net>
  parent: Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 2 replies; 7+ messages in thread

From: Palle Girgensohn @ 2007-12-06 17:11 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin; kudo@pingpong.net



--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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 17:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Palle Girgensohn <girgen@pingpong.net>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2007-12-06 17:22 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin; kudo@pingpong.net

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 17:34  Palle Girgensohn <girgen@pingpong.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Palle Girgensohn @ 2007-12-06 17:34 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin; kudo@pingpong.net



--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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: lost data, possible playback?
@ 2007-12-06 17:51  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Palle Girgensohn <girgen@pingpong.net>
  1 sibling, 0 replies; 7+ messages in thread

From: Scott Marlowe @ 2007-12-06 17:51 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-admin; kudo@pingpong.net

On Dec 6, 2007 11:11 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
> > 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?

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2007-12-06 17:51 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-06 16:24 lost data, possible playback? Palle Girgensohn <girgen@pingpong.net>
2007-12-06 16:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-06 16:45   ` Palle Girgensohn <girgen@pingpong.net>
2007-12-06 17:11     ` Palle Girgensohn <girgen@pingpong.net>
2007-12-06 17:22       ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-06 17:34         ` Palle Girgensohn <girgen@pingpong.net>
2007-12-06 17:51       ` Scott Marlowe <scott.marlowe@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox