Re: [ADMIN] Restore database after drop command

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