From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: [ADMIN] Restore database after drop command
Date: Mon, 25 Jul 2011 20:15:10 +0200
Message-ID: <4E2DB2AE.6010704@fuzzy.cz> (raw)
In-Reply-To: <4E2D1709.4020105@orkash.com>
References: <4E2D0F64.2040500@orkash.com>
<CA41459F-81E3-43EB-83B2-83362BB71858@gmail.com>
<4E2D1709.4020105@orkash.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: [ADMIN] Restore database after drop command
In-Reply-To: <4E2DB2AE.6010704@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox