pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRecover rows deleted
13+ messages / 6 participants
[nested] [flat]
* Recover rows deleted
@ 2012-05-28 18:24 Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 2 replies; 13+ messages in thread
From: Alejandro Carrillo @ 2012-05-28 18:24 UTC (permalink / raw)
To: pgsql-performance
Hi,
¿How I can recover a row delete of a table that wasn't vacuummed?
I have PostgreSQL 9.1 in Windows XP/7.
Thanks
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-05-29 20:53 Jeff Davis <pgsql@j-davis.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
1 sibling, 1 reply; 13+ messages in thread
From: Jeff Davis @ 2012-05-29 20:53 UTC (permalink / raw)
To: Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: pgsql-performance
On Mon, 2012-05-28 at 19:24 +0100, Alejandro Carrillo wrote:
> Hi,
>
>
> ¿How I can recover a row delete of a table that wasn't vacuummed?
> I have PostgreSQL 9.1 in Windows XP/7.
The first thing to do is shut down postgresql and take a full backup of
the data directory, including any archived WAL you might have (files in
pg_xlog). Make sure this is done first.
Next, do you have any backups? If you have a base backup from before the
delete, and all the WAL files from the time of the base backup until
now, then you can try point-in-time recovery to the point right before
the data loss:
http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
If not, are we talking about a single row, or many rows? If it's a
single row you might be able to do some manual steps, like examining the
pages to recover the data.
Another option is to try pg_resetxlog (make sure you have a safe backup
first!):
http://www.postgresql.org/docs/9.1/static/app-pgresetxlog.html
And try setting the current transaction ID to just before the delete
ran. Then you may be able to use pg_dump or otherwise export the deleted
rows.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-05-29 21:33 hubert depesz lubaczewski <depesz@depesz.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
1 sibling, 1 reply; 13+ messages in thread
From: hubert depesz lubaczewski @ 2012-05-29 21:33 UTC (permalink / raw)
To: Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: pgsql-performance
On Mon, May 28, 2012 at 07:24:13PM +0100, Alejandro Carrillo wrote:
> ¿How I can recover a row delete of a table that wasn't vacuummed?
> I have PostgreSQL 9.1 in Windows XP/7.
http://www.depesz.com/2012/04/04/lets-talk-dirty/
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-05-29 22:16 Alejandro Carrillo <fasterzip@yahoo.es>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alejandro Carrillo @ 2012-05-29 22:16 UTC (permalink / raw)
To: depesz@depesz.com <depesz@depesz.com>; +Cc: pgsql-performance
Hi friend,
Your function doesn't compile in Windows.
Please change it.
Thanks
>________________________________
> De: hubert depesz lubaczewski <depesz@depesz.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>Enviado: Martes 29 de Mayo de 2012 16:33
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On Mon, May 28, 2012 at 07:24:13PM +0100, Alejandro Carrillo wrote:
>> ¿How I can recover a row delete of a table that wasn't vacuummed?
>> I have PostgreSQL 9.1 in Windows XP/7.
>
>http://www.depesz.com/2012/04/04/lets-talk-dirty/
>
>Best regards,
>
>depesz
>
>--
>The best thing about modern society is how easy it is to avoid contact with it.
> http://depesz.com/
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-05-29 22:34 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 0 replies; 13+ messages in thread
From: Alejandro Carrillo @ 2012-05-29 22:34 UTC (permalink / raw)
To: depesz@depesz.com <depesz@depesz.com>; +Cc: pgsql-performance
Hi,
Excuse me if I was a bit rude, was not my intention. What happens is that I think the code should be someone to do cross-platform. So many could use it.
Do you know another implementation that be cross-platform?
Thanks.
>________________________________
> De: hubert depesz lubaczewski <depesz@depesz.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>Enviado: Martes 29 de Mayo de 2012 17:21
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On Tue, May 29, 2012 at 11:16:56PM +0100, Alejandro Carrillo wrote:
>> Hi friend,
>>
>> Your function doesn't compile in Windows.
>> Please change it.
>
>My function? I just wrote about a module written by someone else - this
>is clearly stated in the first line of the blogpost.
>
>And I doubt he will be interested in changing it so that it will work on
>windows - we're not using it for anything, sorry.
>
>Best regards,
>
>depesz
>
>--
>The best thing about modern society is how easy it is to avoid contact with it.
> http://depesz.com/
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 16:14 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alejandro Carrillo @ 2012-06-04 16:14 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-performance
Anybody knows a function that let's recover a record (row) deleted in Windows?
Thanks
>________________________________
> De: Jeff Davis <pgsql@j-davis.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>Enviado: Martes 29 de Mayo de 2012 15:53
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On Mon, 2012-05-28 at 19:24 +0100, Alejandro Carrillo wrote:
>> Hi,
>>
>>
>> ¿How I can recover a row delete of a table that wasn't vacuummed?
>> I have PostgreSQL 9.1 in Windows XP/7.
>
>The first thing to do is shut down postgresql and take a full backup of
>the data directory, including any archived WAL you might have (files in
>pg_xlog). Make sure this is done first.
>
>Next, do you have any backups? If you have a base backup from before the
>delete, and all the WAL files from the time of the base backup until
>now, then you can try point-in-time recovery to the point right before
>the data loss:
>
>http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
>
>If not, are we talking about a single row, or many rows? If it's a
>single row you might be able to do some manual steps, like examining the
>pages to recover the data.
>
>Another option is to try pg_resetxlog (make sure you have a safe backup
>first!):
>
>http://www.postgresql.org/docs/9.1/static/app-pgresetxlog.html
>
>And try setting the current transaction ID to just before the delete
>ran. Then you may be able to use pg_dump or otherwise export the deleted
>rows.
>
>Regards,
> Jeff Davis
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 16:43 Shaun Thomas <sthomas@optionshouse.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 1 reply; 13+ messages in thread
From: Shaun Thomas @ 2012-06-04 16:43 UTC (permalink / raw)
To: Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: pgsql-performance
On 06/04/2012 11:14 AM, Alejandro Carrillo wrote:
> Anybody knows a function that let's recover a record (row) deleted in
> Windows?
Sorry Alejandro, I'm pretty sure no database anywhere has a function
like that. If there were, I'd certainly like to see it! Generally you
avoid situations like this by using transactions. If you do accidentally
delete a row, that's what backups are for.
Again, sorry to be the bearer of bad news.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 16:47 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Shaun Thomas <sthomas@optionshouse.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alejandro Carrillo @ 2012-06-04 16:47 UTC (permalink / raw)
To: sthomas@optionshouse.com <sthomas@optionshouse.com>; +Cc: pgsql-performance
In linux exists (https://github.com/omniti-labs/pgtreats/blob/master/contrib/pg_dirtyread/pg_dirtyread.c)
But I can't compile in Windows :(
Anybody could compile in Windows?
>________________________________
> De: Shaun Thomas <sthomas@optionshouse.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>Enviado: Lunes 4 de junio de 2012 11:43
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On 06/04/2012 11:14 AM, Alejandro Carrillo wrote:
>
>> Anybody knows a function that let's recover a record (row) deleted in
>> Windows?
>
>Sorry Alejandro, I'm pretty sure no database anywhere has a function like that. If there were, I'd certainly like to see it! Generally you avoid situations like this by using transactions. If you do accidentally delete a row, that's what backups are for.
>
>Again, sorry to be the bearer of bad news.
>
>-- Shaun Thomas
>OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
>312-444-8534
>sthomas@optionshouse.com
>
>______________________________________________
>
>See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
>-- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 17:04 Merlin Moncure <mmoncure@gmail.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2012-06-04 17:04 UTC (permalink / raw)
To: Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: sthomas@optionshouse.com <sthomas@optionshouse.com>; pgsql-performance
On Mon, Jun 4, 2012 at 11:47 AM, Alejandro Carrillo <fasterzip@yahoo.es> wrote:
> In linux exists
> (https://github.com/omniti-labs/pgtreats/blob/master/contrib/pg_dirtyread/pg_dirtyread.c)
> But I can't compile in Windows :(
> Anybody could compile in Windows?
There are no linux specific calls in there that I can see-- it should
be a matter of compiling and installing it. Do you have a compiler?
What issues are you having with compiling? It might just be a matter
of setting up postgres build environment.
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 17:46 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alejandro Carrillo @ 2012-06-04 17:46 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: sthomas@optionshouse.com <sthomas@optionshouse.com>; pgsql-performance
How I can compile in Windows? I tried to compile using Dev-C++ 4.9 and show a warning:
Compilador: Default compiler
Building Makefile: "C:\Documents and Settings\Administrador\Escritorio\pg_dirtyread\Makefile.win"
Ejecutando make clean
rm -f pg_dirtyread.o pg_dirtyread.a
gcc.exe -c pg_dirtyread.c -o pg_dirtyread.o -I"C:/Dev-Cpp/include" -I"C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server" -I"C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/libpq" -I"C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include" -I"C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/port/win32" -DBUILDING_DLL=1 -DHAVE_LONG_INT_64=1
In file included from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/c.h:851,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/postgres.h:47,
from pg_dirtyread.c:34:
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/port.h:191: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/port.h:195: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/port.h:200: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/port.h:204: warning: `gnu_printf' is an unrecognized format function type
In file included from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/postgres.h:48,
from pg_dirtyread.c:34:
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:127: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:133: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:141: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:141: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:147: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:153: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:159: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:167: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:167: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:173: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:179: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:206: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:216: warning: `gnu_printf' is an unrecognized format function type
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/utils/elog.h:375: warning: `gnu_printf' is an unrecognized format function type
In file included from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/access/xlog.h:16,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/access/heapam.h:20,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/nodes/execnodes.h:18,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/executor/execdesc.h:18,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/executor/executor.h:17,
from C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/funcapi.h:21,
from pg_dirtyread.c:35:
C:/postgresql-9.1.0-1-windows-binaries listo/pgsql/include/server/lib/stringinfo.h:98: warning: `gnu_printf' is an unrecognized format function type
ar r pg_dirtyread.a pg_dirtyread.o
ar: creating pg_dirtyread.a
ranlib pg_dirtyread.a
Ejecución Terminada
I use the sources of binary downloaded of http://www.enterprisedb.com/products-services-training/pgbindownload.
What I doing bad?
Thanks
>________________________________
> De: Merlin Moncure <mmoncure@gmail.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>CC: "sthomas@optionshouse.com" <sthomas@optionshouse.com>; "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>Enviado: Lunes 4 de junio de 2012 12:04
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On Mon, Jun 4, 2012 at 11:47 AM, Alejandro Carrillo <fasterzip@yahoo.es> wrote:
>> In linux exists
>> (https://github.com/omniti-labs/pgtreats/blob/master/contrib/pg_dirtyread/pg_dirtyread.c)
>> But I can't compile in Windows :(
>> Anybody could compile in Windows?
>
>
>There are no linux specific calls in there that I can see-- it should
>be a matter of compiling and installing it. Do you have a compiler?
>What issues are you having with compiling? It might just be a matter
>of setting up postgres build environment.
>
>merlin
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 18:23 Merlin Moncure <mmoncure@gmail.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2012-06-04 18:23 UTC (permalink / raw)
To: Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: sthomas@optionshouse.com <sthomas@optionshouse.com>; pgsql-performance
On Mon, Jun 4, 2012 at 12:46 PM, Alejandro Carrillo <fasterzip@yahoo.es> wrote:
> How I can compile in Windows? I tried to compile using Dev-C++ 4.9 and show
It's probably going to take some extra effort to compile backend
libraries with that compiler. The two supported compiling
environments on windows are mingw and visual studio. (that said, it's
probably doable if you're willing to expend the effort and have some
experience porting software).
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 18:37 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alejandro Carrillo @ 2012-06-04 18:37 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: sthomas@optionshouse.com <sthomas@optionshouse.com>; pgsql-performance
ok, How I can compile in Windows using Visual Studio and Mingw?
>________________________________
> De: Merlin Moncure <mmoncure@gmail.com>
>Para: Alejandro Carrillo <fasterzip@yahoo.es>
>CC: "sthomas@optionshouse.com" <sthomas@optionshouse.com>; "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>Enviado: Lunes 4 de junio de 2012 13:23
>Asunto: Re: [PERFORM] Recover rows deleted
>
>On Mon, Jun 4, 2012 at 12:46 PM, Alejandro Carrillo <fasterzip@yahoo.es> wrote:
>> How I can compile in Windows? I tried to compile using Dev-C++ 4.9 and show
>
>It's probably going to take some extra effort to compile backend
>libraries with that compiler. The two supported compiling
>environments on windows are mingw and visual studio. (that said, it's
>probably doable if you're willing to expend the effort and have some
>experience porting software).
>
>merlin
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Recover rows deleted
@ 2012-06-04 18:46 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 0 replies; 13+ messages in thread
From: Kevin Grittner @ 2012-06-04 18:46 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Alejandro Carrillo <fasterzip@yahoo.es>; +Cc: sthomas@optionshouse.com <sthomas@optionshouse.com>; pgsql-performance
Alejandro Carrillo <fasterzip@yahoo.es> wrote:
> How I can compile in Windows using Visual Studio and Mingw?
http://www.postgresql.org/docs/current/interactive/install-windows.html
-Kevin
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2012-06-04 18:46 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-28 18:24 Recover rows deleted Alejandro Carrillo <fasterzip@yahoo.es>
2012-05-29 20:53 ` Jeff Davis <pgsql@j-davis.com>
2012-06-04 16:14 ` Alejandro Carrillo <fasterzip@yahoo.es>
2012-06-04 16:43 ` Shaun Thomas <sthomas@optionshouse.com>
2012-06-04 16:47 ` Alejandro Carrillo <fasterzip@yahoo.es>
2012-06-04 17:04 ` Merlin Moncure <mmoncure@gmail.com>
2012-06-04 17:46 ` Alejandro Carrillo <fasterzip@yahoo.es>
2012-06-04 18:23 ` Merlin Moncure <mmoncure@gmail.com>
2012-06-04 18:37 ` Alejandro Carrillo <fasterzip@yahoo.es>
2012-06-04 18:46 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-05-29 21:33 ` hubert depesz lubaczewski <depesz@depesz.com>
2012-05-29 22:16 ` Alejandro Carrillo <fasterzip@yahoo.es>
2012-05-29 22:34 ` Alejandro Carrillo <fasterzip@yahoo.es>
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