Обсуждение: Recover rows deleted
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
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
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/
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/
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/
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
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
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
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
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
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
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
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
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