Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 91465B5D8C5 for ; 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 ; 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 ; 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 User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Vibhor Kumar CC: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Restore database after drop command References: <4E2D0F64.2040500@orkash.com> In-Reply-To: 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 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--