Обсуждение: 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
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.
--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
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.