Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EA7B4650668 for ; Mon, 14 Jul 2008 12:29:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98999-06-2 for ; Mon, 14 Jul 2008 12:29:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.dfki.de (corp-206.dfki.uni-sb.de [134.96.188.26]) by postgresql.org (Postfix) with ESMTP id 6EA4065067D for ; Mon, 14 Jul 2008 12:29:12 -0300 (ADT) Received: from mail.dfki.de (localhost.dfki.uni-sb.de [127.0.0.1]) by localhost (Postfix) with ESMTP id 9A2F1E47DA; Mon, 14 Jul 2008 17:29:08 +0200 (CEST) Received: from [IPv6:::1] (dave.dfki.uni-sb.de [134.96.184.220]) by mail.dfki.de (Postfix) with ESMTP id 8525BE47D4; Mon, 14 Jul 2008 17:29:08 +0200 (CEST) Cc: pgsql-admin@postgresql.org Message-Id: <792684B3-32A7-4F9F-ACA2-A682BF35F9E0@activemath.org> From: Paul Libbrecht To: Tom Lane In-Reply-To: <8259.1216046847@sss.pgh.pa.us> Content-Type: multipart/signed; boundary=Apple-Mail-43--628536437; micalg=sha1; protocol="application/pkcs7-signature" Mime-Version: 1.0 (Apple Message framework v926) Subject: Re: accidental drop table recoverable? Date: Mon, 14 Jul 2008 17:29:08 +0200 References: <53CDF98F-0AE1-49ED-A2A3-C3B1E2AF2E2E@activemath.org> <8259.1216046847@sss.pgh.pa.us> X-Mailer: Apple Mail (2.926) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/122 X-Sequence-Number: 29732 --Apple-Mail-43--628536437 Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Le 14-juil.-08 =E0 16:47, Tom Lane a =E9crit : > You can't really "rollback" a DROP TABLE --- that corresponds directly > to a filesystem remove() call, and no amount of fooling around with =20= > the > database state will undo that. That is dark. I read yesterday night that actually a vacuum was advised everyday =20 since otherwise there was no actual deletion. So you are telling me =20 that, however, drop-table does really go to deletion right away? I'm running 7.4.5 btw. > If you have filesystem tools that will resurrect the deleted files for > you, you could probably put them back into the database. My =20 > inclination > would be not to try to "roll back" anything, but create new tables =20 > with > the identical column sets to the old ones (but no indexes) this can be done easily. But the filesystem resurrect I am doubting of. I'll hunt. thanks! paul > and then rename the recovered files into place to match the new =20 > tables' > relfilenode values. > After which, a dump and reload would be prudent to > make sure everything's really kosher. (Actually, copying the data =20 > into > newly created tables should be enough for that.) sure!= --Apple-Mail-43--628536437 Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIFzjCCAocw ggHwoAMCAQICEHpkMpnMryEmArSVLnJkS28wDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MTIxOTE0MzI0NFoXDTA4MTIxODE0MzI0 NFowcDEaMBgGA1UEBBMRTGliYnJlY2h0IEdvdXJkZXQxDTALBgNVBCoTBFBhdWwxHzAdBgNVBAMT FlBhdWwgTGliYnJlY2h0IEdvdXJkZXQxIjAgBgkqhkiG9w0BCQEWE3BhdWxAYWN0aXZlbWF0aC5v cmcwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMEY+jsbvfvg6uVnITE8Pd4RONXZvqxbynLR rgmnc5pCsv3UfvWjDZfbkX3Nb2OE4j2HSwY6Uhj2SOjh25ibMg2buR6ubgYXHvCbMngeoA2jDjvb dloET7Q0hynIMA281OfxTbEZzIlq55/xr0v8yKX3DyOXqnokERhOtay7RaCfAgMBAAGjMDAuMB4G A1UdEQQXMBWBE3BhdWxAYWN0aXZlbWF0aC5vcmcwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQUF AAOBgQB52LXV/dsJaWBm8ScjEEn2/ecdVmKkQvggQFxsMHxCoIV70+OIoLYaJoO15NpdLuN7EPLE /vBBjoDp2A5KdxBAO8icv4LTOD3btE2oIcCz3SUffQtNTuUvFh9Z9OLGByLNRduw6RVm947pSNBG G+Qqd5XWQUxGci8HcsOQAOsLXjCCAz8wggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJ BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgG A1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMg RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3 DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3 MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5 KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzAN BgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAxKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9 fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7dyfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+ uxg+B79AgAJk16emu59l0cUqVIUPSAR/p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMB Af8ECDAGAQH/AgEAMEMGA1UdHwQ8MDowOKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3 dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3JsMAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgG A1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgwDQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcP f6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH 2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYITq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8x ggKPMIICiwIBATB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAo UHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQ emQymcyvISYCtJUucmRLbzAJBgUrDgMCGgUAoIIBbzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcB MBwGCSqGSIb3DQEJBTEPFw0wODA3MTQxNTI5MDhaMCMGCSqGSIb3DQEJBDEWBBQu+g674BUHeMEz bJo4+AGotEZ5/zCBhQYJKwYBBAGCNxAEMXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECEHpkMpnMryEmArSVLnJkS28wgYcGCyqGSIb3DQEJEAILMXigdjBiMQsw CQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UE AxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEHpkMpnMryEmArSVLnJkS28w DQYJKoZIhvcNAQEBBQAEgYAxrrKkmbwYR5ZAlTpDUsrXyJj00XriSneiQOm/JulGBYqgATjRarFx HoWF91se9ozjzs6wjFGafuSmWiAVrlwlLw8zMCsSMt6eZV7DXuxC5AG+iMUInbh0fihGBfE2xQnp o9W/GpnCjKHD50rDdPSae4o5MuvwiWec3vtRPpnj8wAAAAAAAA== --Apple-Mail-43--628536437--