Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 7FB40C955BB for ; Mon, 4 Jun 2012 13:14:26 -0300 (ADT) Received: from nm12-vm0.bullet.mail.ukl.yahoo.com ([217.146.183.246]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SbZvE-0007Ht-Ci for pgsql-performance@postgresql.org; Mon, 04 Jun 2012 16:14:25 +0000 Received: from [217.146.183.183] by nm12.bullet.mail.ukl.yahoo.com with NNFMP; 04 Jun 2012 16:14:11 -0000 Received: from [217.146.183.72] by tm14.bullet.mail.ukl.yahoo.com with NNFMP; 04 Jun 2012 16:14:11 -0000 Received: from [127.0.0.1] by omp1033.mail.ukl.yahoo.com with NNFMP; 04 Jun 2012 16:14:11 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 499372.98413.bm@omp1033.mail.ukl.yahoo.com Received: (qmail 91831 invoked by uid 60001); 4 Jun 2012 16:14:11 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.es; s=s1024; t=1338826451; bh=XUF6bdmKHLRA9meB12/IWF6BKCyVhoekEQz7+GC56jc=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=1IUDVlfo4Y+o05fxGh7csvCID/A+I7d9//6xLEbef/wWwv+oLymIC+vSw8AeKp7xqMoYV9Mku3y1eQoY9cjemHSFyKpnnl0hKw38r0FsR4KTxXG4V4U1oBVXEd2spRvY+wQj0NKFNoaXjYFTwG5oyStdPD9wMdpkiX/N1a2w5Ng= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.es; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=e/VjdT1Cf+joNMDoB9fTz1J93euOSWk6KgdMwdpDA1hx8sCPjikqpHOzNn6Gc+wTRGU3ZDb/jEfmysJJZU66cb44QmJdrqDhT5MAAgDf1RVtAc6M/kYq/kCO67XcT2iB3D5dda3BOF0hqc9p/vqXkCgQg8+eSd8dYnn0D5rAyLQ=; X-YMail-OSG: jhatMjcVM1mBa6tMEHFaC.Ccz9gdwVD6_fODZaVUBUPmh6l NHE11_b0qW99BcR3DjWPFmzlhIvzJjf3w5dBUXB0m5oy2j87p.EJkAoy_WQR gt6SItgg32qUl50XaFD8gp0Mn7kHH2eGN9sciDwPEHgGzDf.kEFJFgUvHvYZ MHrcGIvcbNT8xYA3YRnaoMKEiJBlxdBTav0sD7D6vaCPCPBRv0CzUmgBygOe etCCPrCPnoRcgCZ0YvVuICBbdw0g3Hds5zSkBHPDyVDaOZbSeyUXGoStE6_u lwJhV8NbM5rvVLDCZapzggLHF393dHrSqCv1Ui3KJja.gAl1aYM_b11L4N6a MqOJ28T8URvYCBhBy7ckBRdOFF7yCToogAG4SvEwzFfdozP3c73g4DQdM6Oa Ko8tGMuwD6ea4YiF.EMckmMVVW0KI8N2gLr4- Received: from [190.85.40.123] by web171001.mail.ukl.yahoo.com via HTTP; Mon, 04 Jun 2012 17:14:11 BST X-Mailer: YahooMailWebService/0.8.118.349524 References: <1338229453.91030.YahooMailNeo@web171005.mail.ukl.yahoo.com> <1338324800.28651.5.camel@sussancws0025> Message-ID: <1338826451.78107.YahooMailNeo@web171001.mail.ukl.yahoo.com> Date: Mon, 4 Jun 2012 17:14:11 +0100 (BST) From: Alejandro Carrillo Reply-To: Alejandro Carrillo Subject: Re: Recover rows deleted To: Jeff Davis Cc: "pgsql-performance@postgresql.org" In-Reply-To: <1338324800.28651.5.camel@sussancws0025> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-181811056-568244329-1338826451=:78107" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201206/9 X-Sequence-Number: 47035 ---181811056-568244329-1338826451=:78107 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Anybody knows a function that let's recover a record (row) deleted in Windo= ws?=0A=0AThanks=0A=0A=0A=0A=0A>________________________________=0A> De: Jef= f Davis =0A>Para: Alejandro Carrillo =0A>CC: "pgsql-performance@postgresql.org" =0A>Enviado: Martes 29 de Mayo de 2012 15:53=0A>Asunto: Re: [PERFORM] = Recover rows deleted=0A> =0A>On Mon, 2012-05-28 at 19:24 +0100, Alejandro C= arrillo wrote:=0A>> Hi,=0A>> =0A>> =0A>> =BFHow I can recover a row delete = of a table that wasn't vacuummed?=0A>> I have PostgreSQL 9.1 in Windows XP/= 7.=0A>=0A>The first thing to do is shut down postgresql and take a full bac= kup of=0A>the data directory, including any archived WAL you might have (fi= les in=0A>pg_xlog). Make sure this is done first.=0A>=0A>Next, do you have = any backups? If you have a base backup from before the=0A>delete, and all t= he WAL files from the time of the base backup until=0A>now, then you can tr= y point-in-time recovery to the point right before=0A>the data loss:=0A>=0A= >http://www.postgresql.org/docs/9.1/static/continuous-archiving.html=0A>=0A= >If not, are we talking about a single row, or many rows? If it's a=0A>sing= le row you might be able to do some manual steps, like examining the=0A>pag= es to recover the data.=0A>=0A>Another option is to try pg_resetxlog (make = sure you have a safe backup=0A>first!):=0A>=0A>http://www.postgresql.org/do= cs/9.1/static/app-pgresetxlog.html=0A>=0A>And try setting the current trans= action ID to just before the delete=0A>ran. Then you may be able to use pg_= dump or otherwise export the deleted=0A>rows.=0A>=0A>Regards,=0A>=A0=A0=A0 = Jeff Davis=0A>=0A>=0A>-- =0A>Sent via pgsql-performance mailing list (pgsql= -performance@postgresql.org)=0A>To make changes to your subscription:=0A>ht= tp://www.postgresql.org/mailpref/pgsql-performance=0A>=0A>=0A> ---181811056-568244329-1338826451=:78107 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Anybody kn= ows a function that let's recover a record (row) deleted in Windows?=

Thanks
De: Jeff Davis <pgsql@j-davis.com>= ;
Para: Alejandro Carr= illo <fasterzip@yahoo.es>
C= C: "pgsql-performance@postgresql.org" <pgsql-performance@post= gresql.org>
Enviado: Martes 29 d= e Mayo de 2012 15:53
Asunto: Re: [PERFORM] Recover rows deleted

On Mon, 20= 12-05-28 at 19:24 +0100, Alejandro Carrillo wrote:
> Hi,
>
= >
> =BFHow I can recover a row delete of a table that wasn't vacu= ummed?
> I have PostgreSQL 9.1 in Windows XP/7.

The first thin= g to do is shut down postgresql and take a full backup of
the data direc= tory, 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 ti= me 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-arc= hiving.html

If not, are we talking about a single row, or many r= ows? If it's a
single row you might be able to do some manual steps, lik= e examining the
pages to recover the data.

Another option is to t= ry pg_resetxlog (make sure you have a safe backup
first!):

http://www.postgresql.org/docs/9.1/static/app-pgresetxlog.html<= /a>

And try setting the current transaction ID to just before the de= lete
ran. Then you may be able to use pg_dump or otherwise export the de= leted
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-performa= nce


---181811056-568244329-1338826451=:78107--