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