Thread: Restore database after drop command
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
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
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:
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
* 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