Restore database after drop command

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

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:

[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

* 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* 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.

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.

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

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