Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 27583B5DBD2 for ; Mon, 25 Jul 2011 15:15:21 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 49484-01 for ; Mon, 25 Jul 2011 18:15:15 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (mail.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id C450FB5DBEF for ; Mon, 25 Jul 2011 15:15:14 -0300 (ADT) Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124]) by mail.gransy.com (Postfix) with ESMTP id 4B1844A21AD for ; Mon, 25 Jul 2011 20:15:13 +0200 (CEST) Received: from mail.gransy.com ([82.208.29.194]) by antispam.gransy.com (antispam.gransy.com [77.78.104.124]) (amavisd-new, port 10024) with ESMTP id lWpc30l3DbVg for ; Mon, 25 Jul 2011 20:14:33 +0200 (CEST) Received: from [192.168.1.227] (ip-78-45-133-255.net.upcbroadband.cz [78.45.133.255]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id 1EE704A2170 for ; Mon, 25 Jul 2011 20:15:13 +0200 (CEST) Message-ID: <4E2DB2AE.6010704@fuzzy.cz> Date: Mon, 25 Jul 2011 20:15:10 +0200 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:5.0) Gecko/20110624 Thunderbird/5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [ADMIN] Restore database after drop command References: <4E2D0F64.2040500@orkash.com> <4E2D1709.4020105@orkash.com> In-Reply-To: <4E2D1709.4020105@orkash.com> X-Enigmail-Version: 1.2 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201107/212 X-Sequence-Number: 44424 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