From adarsh.sharma@orkash.com Mon Jul  25 06:38:31 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 76998B5D80A
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 03:38:31 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 82456-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 06:38:25 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp192.dfw.emailsrvr.com (smtp192.dfw.emailsrvr.com
	[67.192.241.192])
	by mail.postgresql.org (Postfix) with ESMTP id 37E7CB5D91F
	for <pgsql-admin@postgresql.org>; Mon, 25 Jul 2011 03:38:24 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp9.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id
	830C13C0159; Mon, 25 Jul 2011 02:38:23 -0400 (EDT)
X-Virus-Scanned: OK
Received: by smtp9.relay.dfw1a.emailsrvr.com (Authenticated sender:
	adarsh.sharma-AT-orkash.com) with ESMTPSA id 9B28D3C0157;
	Mon, 25 Jul 2011 02:38:22 -0400 (EDT)
Message-ID: <4E2D0F64.2040500@orkash.com>
Date: Mon, 25 Jul 2011 12:08:28 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org
Subject: Restore database after drop command
Content-Type: multipart/alternative;
	boundary="------------090107000908070602060908"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201107/179
X-Sequence-Number: 37958

This is a multi-part message in MIME format.
--------------090107000908070602060908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear all,

I am using Postgres-8.4.2 on Windows system.
I have 2 databases in my postgres database ( globedatabase (21GB), 
urldatabase).

I restore globedatabase from a .sql file on yesterday morning.I insert 
some new data in that database.
In the evening, by mistake I issued a *drop database globedatabase* command.

Today morning, I restore again the same database from backup (.sql) file.
My .sql file have data till yesterday morning but I want newly insert 
data now. Is it possible.

Is it possible to get the data back till the state before drop database 
command.

My pglog files is in the E:/data directory & Binary log is also enabled.

Please let me know if it is possible. It's urgent.


Thanks & Regards
Adarsh Sharma

--------------090107000908070602060908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Dear all,<br>
<br>
I am using Postgres-8.4.2 on Windows system.<br>
I have 2 databases in my postgres database ( globedatabase (21GB),
urldatabase).<br>
<br>
I restore globedatabase from a .sql file on yesterday morning.I insert
some new data in that database.<br>
In the evening, by mistake I issued a <b>drop database globedatabase</b>
command.<br>
<br>
Today morning, I restore again the same database from backup (.sql)
file.<br>
My .sql file have data till yesterday morning but I want newly insert
data now. Is it possible.<br>
<br>
Is it possible to get the data back till the state before drop database
command.<br>
<br>
My pglog files is in the E:/data directory &amp; Binary log is also
enabled.<br>
<br>
Please let me know if it is possible. It's urgent.<br>
<br>
<br>
Thanks &amp; Regards<br>
Adarsh Sharma
</body>
</html>

--------------090107000908070602060908--



From vibhor.aim@gmail.com Mon Jul  25 06:47:33 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id AE4ADB5D80A;
	Mon, 25 Jul 2011 03:47:33 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 44076-02; Mon, 25 Jul 2011 06:47:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f44.google.com (mail-pz0-f44.google.com
	[209.85.210.44])
	by mail.postgresql.org (Postfix) with ESMTP id 1E47FB5D803;
	Mon, 25 Jul 2011 03:47:26 -0300 (ADT)
Received: by pzk5 with SMTP id 5so24658438pzk.17
	for <multiple recipients>; Sun, 24 Jul 2011 23:47:26 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	bh=IQOrKRTvSCojs7j1CenxxJy4idjrmTxNM+UtSiWBVd0=;
	b=rmQ2lWHeSWG2swJrjq1LV+aj091uEcKkdUv5TI1i79mAZwMhDnhJ0qmG4lpkH3T+b7
	R7SwY1C0UlZTCoy+FoSBEXeO7R7WErKe/RxdntfSPQAIYdPLM1x0v2bdPpV9ci6YHq/S
	x5ULG6n/5GJtgJEzVea+k/NZ/BX/LiEYOz9oI=
Received: by 10.68.58.10 with SMTP id m10mr6739155pbq.359.1311576446506;
	Sun, 24 Jul 2011 23:47:26 -0700 (PDT)
Received: from [192.168.1.2] ([122.170.49.205])
	by mx.google.com with ESMTPS id r8sm4294301pbd.92.2011.07.24.23.47.22
	(version=TLSv1/SSLv3 cipher=OTHER);
	Sun, 24 Jul 2011 23:47:24 -0700 (PDT)
Subject: Re: Restore database after drop command
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
From: Vibhor Kumar <vibhor.aim@gmail.com>
In-Reply-To: <4E2D0F64.2040500@orkash.com>
Date: Mon, 25 Jul 2011 12:17:17 +0530
Cc: Vibhor Kumar <vibhor.aim@gmail.com>, pgsql-admin@postgresql.org,
	pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
References: <4E2D0F64.2040500@orkash.com>
To: Adarsh Sharma <adarsh.sharma@orkash.com>
X-Mailer: Apple Mail (2.1084)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001
X-Spam-Level: 
X-Archive-Number: 201107/180
X-Sequence-Number: 37959


On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote:

> I restore globedatabase from a .sql file on yesterday morning.I insert =
some new data in that database.
> In the evening, by mistake I issued a drop database globedatabase =
command.
> Today morning, I restore again the same database from backup (.sql) =
file.
> My .sql file have data till yesterday morning but I want newly insert =
data now. Is it possible.
> Is it possible to get the data back till the state before drop =
database command.

No you won't be able to recover.=20

If you have Online Backup, then PITR would help you.

Thanks & Regards,
Vibhor Kumar
Blogs: http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com




From adarsh.sharma@orkash.com Mon Jul  25 07:11:08 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 91465B5D8C5
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 04:11:08 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 48633-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 07:11:01 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp192.dfw.emailsrvr.com (smtp192.dfw.emailsrvr.com
	[67.192.241.192])
	by mail.postgresql.org (Postfix) with ESMTP id A6E92B5D803
	for <pgsql-admin@postgresql.org>; Mon, 25 Jul 2011 04:11:01 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp9.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id
	6F5913C0159; Mon, 25 Jul 2011 03:11:01 -0400 (EDT)
X-Virus-Scanned: OK
Received: by smtp9.relay.dfw1a.emailsrvr.com (Authenticated sender:
	adarsh.sharma-AT-orkash.com) with ESMTPSA id D83AB3C016A;
	Mon, 25 Jul 2011 03:10:59 -0400 (EDT)
Message-ID: <4E2D1709.4020105@orkash.com>
Date: Mon, 25 Jul 2011 12:41:05 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: Vibhor Kumar <vibhor.aim@gmail.com>
CC: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Restore database after drop command
References: <4E2D0F64.2040500@orkash.com>
	<CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
In-Reply-To: <CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090508070806000107020902"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201107/181
X-Sequence-Number: 37960

This is a multi-part message in MIME format.
--------------090508070806000107020902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I go through the link, so it is impossible to get the data back.
I have following files in my pg_xlog directory :

000000010000000700000091
000000010000000700000092
000000010000000700000093
000000010000000700000094
000000010000000700000095
000000010000000700000096
000000010000000700000097
000000010000000700000098

I think I issued the drop database command 1 month ago.
 From the manual, I understand that my segment files are recycled to 
newer ones :

/The segment files are given numeric names that reflect their position 
in the abstract WAL sequence. When not using WAL archiving, the system 
normally creates just a few segment files and then "recycles" them by 
renaming no-longer-needed segment files to higher segment numbers. It's 
assumed that a segment file whose contents precede the 
checkpoint-before-last is no longer of interest and can be recycled.

/My archive_status folder is empty.
How would we know that which data these segment files corresponds too.

I followed below steps 1 month ago :
1. Load globdatabase through backup.sql (21 GB)file
2. Insert some data near about 3-4 tables ( KB) data.
3. Drop database globdatabase.
4. Load globdatabase through backup.sql (21GB)file

May be there is chance because we work very rarely on that system.
Now i have the backup file bt I want that 3-4 tables.


Thanks

Vibhor Kumar wrote:
> On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote:
>
>   
>> I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database.
>> In the evening, by mistake I issued a drop database globedatabase command.
>> Today morning, I restore again the same database from backup (.sql) file.
>> My .sql file have data till yesterday morning but I want newly insert data now. Is it possible.
>> Is it possible to get the data back till the state before drop database command.
>>     
>
> No you won't be able to recover. 
>
> If you have Online Backup, then PITR would help you.
>
> Thanks & Regards,
> Vibhor Kumar
> Blogs: http://vibhork.blogspot.com
> http://vibhorkumar.wordpress.com
>
>   


--------------090508070806000107020902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I go through the link, so it is impossible to get the data back.<br>
I have following files in my pg_xlog directory :<br>
<br>
000000010000000700000091
<br>
000000010000000700000092
<br>
000000010000000700000093
<br>
000000010000000700000094
<br>
000000010000000700000095
<br>
000000010000000700000096
<br>
000000010000000700000097
<br>
000000010000000700000098
<br>
<br>
I think I issued the drop database command 1 month ago. <br>
>From the manual, I understand that my segment files are recycled to
newer ones :<br>
<br>
<i>The segment files are given numeric names that reflect their
position
in the abstract WAL sequence. When not using WAL archiving, the system
normally creates just a few segment files and then <span class="QUOTE">"recycles"</span>
them by renaming no-longer-needed segment files to higher segment
numbers. It's assumed that a segment file whose contents precede the
checkpoint-before-last is no longer of interest and can be recycled.<br>
<br>
</i>My archive_status folder is empty.<br>
How would we know that which data these segment files corresponds too.<br>
<br>
I followed below steps 1 month ago :<br>
1. Load globdatabase through backup.sql (21 GB)file<br>
2. Insert some data near about 3-4 tables ( KB) data.<br>
3. Drop database globdatabase.<br>
4. Load globdatabase through backup.sql (21GB)file<br>
<br>
May be there is chance because we work very rarely on that system.<br>
Now i have the backup file bt I want that 3-4 tables.<br>
<br>
<br>
Thanks<br>
<br>
Vibhor Kumar wrote:
<blockquote cite="mid:CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com"
 type="cite">
  <pre wrap="">On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database.
In the evening, by mistake I issued a drop database globedatabase command.
Today morning, I restore again the same database from backup (.sql) file.
My .sql file have data till yesterday morning but I want newly insert data now. Is it possible.
Is it possible to get the data back till the state before drop database command.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
No you won't be able to recover. 

If you have Online Backup, then PITR would help you.

Thanks &amp; Regards,
Vibhor Kumar
Blogs: <a class="moz-txt-link-freetext" href="http://vibhork.blogspot.com">http://vibhork.blogspot.com</a>
<a class="moz-txt-link-freetext" href="http://vibhorkumar.wordpress.com">http://vibhorkumar.wordpress.com</a>

  </pre>
</blockquote>
<br>
</body>
</html>

--------------090508070806000107020902--



From craig@postnewspapers.com.au Mon Jul  25 07:47:02 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id EC517B5DC05
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 04:47:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 53407-07
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 07:46:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[173.230.139.50])
	by mail.postgresql.org (Postfix) with ESMTP id E02AFB5DC03
	for <pgsql-general@postgresql.org>;
	Mon, 25 Jul 2011 04:46:56 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 0142B10D;
	Mon, 25 Jul 2011 03:46:57 -0400 (EDT)
X-Virus-Scanned: amavisd-new at postnewspapers.com.au.com
Received: from www.postnewspapers.com.au ([127.0.0.1])
	by localhost (www.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id 26KxmqQuUF5z; Mon, 25 Jul 2011 03:46:56 -0400 (EDT)
Received: from mail.postnewspapers.com.au (ppp171-128.static.internode.on.net
	[150.101.171.128])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 32EAF37;
	Mon, 25 Jul 2011 03:46:56 -0400 (EDT)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 2A8B3AF16C;
	Mon, 25 Jul 2011 15:46:55 +0800 (WST)
Message-ID: <4E2D1F6F.1040203@postnewspapers.com.au>
Date: Mon, 25 Jul 2011 15:46:55 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.15) Gecko/20110307 Thunderbird/3.1.9
MIME-Version: 1.0
To: Adarsh Sharma <adarsh.sharma@orkash.com>
CC: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [WAS:ADMIN] [WAS:PERFORM] Restore database after drop
 command
References: <4E2D0F64.2040500@orkash.com>
	<CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
	<4E2D1709.4020105@orkash.com>
In-Reply-To: <4E2D1709.4020105@orkash.com>
X-Enigmail-Version: 1.1.2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-3.093 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RP_MATCHES_RCVD=-1.193
X-Spam-Level: 
X-Archive-Number: 201107/690
X-Sequence-Number: 178069

> [ADMIN] [PERFORM]

First rule of mailing lists: DO NOT CROSS POST. Please stick to one
mailing list. I've replied on pgsql-general where your post started out.
Please do not reply to the posts on -admin or -perform.

My reply follows below.

On 25/07/11 15:11, Adarsh Sharma wrote:
> I go through the link, so it is impossible to get the data back.
> I have following files in my pg_xlog directory :
> 
> 000000010000000700000091
> 000000010000000700000092
> 000000010000000700000093
> 000000010000000700000094
> 000000010000000700000095
> 000000010000000700000096
> 000000010000000700000097
> 000000010000000700000098
> 
> I think I issued the drop database command 1 month ago.

.... and you're asking NOW? Even though "it's urgent"?

The first rule of data recovery: As soon as you realize something is
wrong, make a copy of everything immediately. Then stop using it.

> 
> I think I issued the drop database command 1 month ago.
> From the manual, I understand that my segment files are recycled to newer ones :

Correct. Any chance you ever had of recovering  your data is almost
certainly gone because you restored into it - probably immediately
destroying your deleted data - then kept on using the database for
another month.

If you're willing to spend a lot of money you might be able to recover
some of it using hard-drive level overwritten data forensics, but I
rather doubt it.

--
Craig Ringer



From fweimer@bfk.de Mon Jul  25 10:51:46 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 78EB1B5D80A;
	Mon, 25 Jul 2011 07:51:46 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 37771-01; Mon, 25 Jul 2011 10:51:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2])
	by mail.postgresql.org (Postfix) with ESMTP id DFF37B5D803;
	Mon, 25 Jul 2011 07:51:35 -0300 (ADT)
Received: from mx00.int.bfk.de ([10.119.110.2])
	by mx01.bfk.de with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32)
	id 1QlIl2-0006YJ-0K; Mon, 25 Jul 2011 10:51:32 +0000
Received: by bfk.de with local id 1QlIl1-0005PU-Qz;
	Mon, 25 Jul 2011 10:51:31 +0000
From: Florian Weimer <fweimer@bfk.de>
To: Adarsh Sharma <adarsh.sharma@orkash.com>
Cc: pgsql-admin@postgresql.org,  pgsql-performance@postgresql.org
Subject: Re: Restore database after drop command
References: <4E2D0F64.2040500@orkash.com>
Date: Mon, 25 Jul 2011 10:51:31 +0000
In-Reply-To: <4E2D0F64.2040500@orkash.com> (Adarsh Sharma's message of "Mon,
	25 Jul 2011 12:08:28 +0530")
Message-ID: <8239hueiq4.fsf@mid.bfk.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-3.093 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.193
X-Spam-Level: 
X-Archive-Number: 201107/182
X-Sequence-Number: 37961

* Adarsh Sharma:

> I restore globedatabase from a .sql file on yesterday morning.I insert
> some new data in that database.
> In the evening, by mistake I issued a *drop database globedatabase* comma=
nd.
>
> Today morning, I restore again the same database from backup (.sql) file.
> My .sql file have data till yesterday morning but I want newly insert
> data now. Is it possible.
>
> Is it possible to get the data back till the state before drop
> database command.

It might have been possible if you had performed a hard shutdown
directly after discovering the mistake, by undeleting the database files
at the operating system level.  This has been made more difficult
(perhaps even impossible) by your subsequent write activity.

--=20
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstra=DFe 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99



From tv@fuzzy.cz Mon Jul  25 18:15:21 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 27583B5DBD2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 15:15:21 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 49484-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 25 Jul 2011 18:15:15 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.gransy.com (mail.gransy.com [82.208.29.194])
	by mail.postgresql.org (Postfix) with ESMTP id C450FB5DBEF
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Jul 2011 15:15:14 -0300 (ADT)
Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	by mail.gransy.com (Postfix) with ESMTP id 4B1844A21AD
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Jul 2011 20:15:13 +0200 (CEST)
Received: from mail.gransy.com ([82.208.29.194])
	by antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	(amavisd-new, port 10024)
	with ESMTP id lWpc30l3DbVg for <pgsql-performance@postgresql.org>;
	Mon, 25 Jul 2011 20:14:33 +0200 (CEST)
Received: from [192.168.1.227] (ip-78-45-133-255.net.upcbroadband.cz
	[78.45.133.255])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.gransy.com (Postfix) with ESMTPSA id 1EE704A2170
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Jul 2011 20:15:13 +0200 (CEST)
Message-ID: <4E2DB2AE.6010704@fuzzy.cz>
Date: Mon, 25 Jul 2011 20:15:10 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: [ADMIN] Restore database after drop command
References: <4E2D0F64.2040500@orkash.com>
	<CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
	<4E2D1709.4020105@orkash.com>
In-Reply-To: <4E2D1709.4020105@orkash.com>
X-Enigmail-Version: 1.2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201107/212
X-Sequence-Number: 44424

Dne 25.7.2011 09:11, Adarsh Sharma napsal(a):
> I go through the link, so it is impossible to get the data back.
> I have following files in my pg_xlog directory :
> 
> 000000010000000700000091
> 000000010000000700000092
> 000000010000000700000093
> 000000010000000700000094
> 000000010000000700000095
> 000000010000000700000096
> 000000010000000700000097
> 000000010000000700000098
> 
> How would we know that which data these segment files corresponds too.

The xlog segments are for the whole cluster, not for individual objects
(tables etc.). It's very difficult to read data from those files if you
don't have a proper base backup (copy of the data files) and all
subsequent xlog files.

> I followed below steps 1 month ago :
> 1. Load globdatabase through backup.sql (21 GB)file
> 2. Insert some data near about 3-4 tables ( KB) data.
> 3. Drop database globdatabase.
> 4. Load globdatabase through backup.sql (21GB)file
> 
> May be there is chance because we work very rarely on that system.
> Now i have the backup file bt I want that 3-4 tables.

No, there's almost no chance to do that. If your wal_level is archive or
hot_standby, then those 21GB in step (4) were written to the xlog
directory. And as you keep only 8 wal segments (128MB), the data are
long gone.

If you have wal_level=minimal, then there's a slight chance the data are
actually still in the wal segments. That depends on how the .sql backup
loads data (COPY does not write data into the wal segments). But even in
that case you don't have information that is necessary to parse the
files as you've dropped the database.

Tomas