Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 09EE92E0100 for ; Thu, 21 Feb 2008 19:30:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 01654-07 for ; Thu, 21 Feb 2008 19:30:14 -0400 (AST) Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C0F0B2E00F2 for ; Thu, 21 Feb 2008 19:30:13 -0400 (AST) Received: from [10.224.23.50] (unknown [38.106.153.128]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 9228F564AA; Thu, 21 Feb 2008 23:30:08 +0000 (UTC) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v753) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-1--156377003; protocol="application/pkcs7-signature" Message-Id: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org> Cc: pgsql-admin@postgresql.org From: Decibel! Subject: Re: vaccuming very large table problem Date: Thu, 21 Feb 2008 17:30:03 -0600 To: zeylienospam@gmail.com X-Mailer: Apple Mail (2.753) 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: 200802/257 X-Sequence-Number: 28331 --Apple-Mail-1--156377003 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Feb 15, 2008, at 4:56 AM, if wrote: > We use postgresql as a backend to our email gateway, and keep al > emails for in database. Using postgres version 7.4.8 (yes, i know it's > old), and rather specific table schema (the application was desined > that way) -- all emails split into 2kb parts and fed up into > pg_largeobject. So, long story short, i now have a catch-22 situation > -- database using about 0.7TB and we are running out of space ;-) > I can delete some old stuff but i cannot run full vacuum to reclaim > disk space (i takes way more than full weekend) and i also cannot > dump/restore as there's no free space (2x database) > > So, with this restrictions aplied, i figured out that i can somehow > zero out all old entries in pg_largeobject or even physically delete > these files, and rebuild all neccesary indexes. > > What is the best way to do this? > IMO, dd'ing /dev/zero to this files will cause postgres to > reinitialize these empty blocks, and after this will still need to > vacuum full over 0.7TB, am i right? > And if i delete them, then start postmaster, there'll be lots of > complaining but will the latest data be saved? > > How can i delete, for instance, first 70% of data reasonably fast? You're still inserting new email, right? If so, why are you worried about reclaiming space? Just delete some stuff, let vacuum clean it up, and make sure that your FSM is big enough (easiest way to do that is to run vacuumdb -av). You'll also want to periodically reindex, especially in 7.4. And yes, upgrade. At a minimum you need to get to the lastest 7.4, which doesn't require anything special. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 --Apple-Mail-1--156377003 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGtTCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNuMIIC16ADAgECAhACpH6ZVSGg5xZh 5O/wRN/4MA0GCSqGSIb3DQEBBQUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz dWluZyBDQTAeFw0wNzEwMjkxOTE0NDdaFw0wODEwMjgxOTE0NDdaMIGqMQ4wDAYDVQQEEwVOYXNi eTETMBEGA1UEKhMKSmFtZXMgQ2FybDEZMBcGA1UEAxMQSmFtZXMgQ2FybCBOYXNieTEcMBoGCSqG SIb3DQEJARYNamltQG5hc2J5Lm5ldDEiMCAGCSqGSIb3DQEJARYTZGVjaWJlbEBkZWNpYmVsLm9y ZzEmMCQGCSqGSIb3DQEJARYXZGVjaWJlbEBkaXN0cmlidXRlZC5uZXQwggEiMA0GCSqGSIb3DQEB AQUAA4IBDwAwggEKAoIBAQDSgNcbvYYycE3FBX+z9i9zXtQxBalsoeDP92/jSG30Pq6HMlir3+lu rgZd5wBHRt/DMj+5ZfUDY9zQ7hfUio/WKQxRkXH46deV5xh9KNFf8kgxLiLKOBpesWFaXvNlnxU9 JKEpNZUnWeE8N/2mMWWs9L0Rfq0AxYa8TPfsbklALy1n9fY2yU1dNoT/s5tjRQK0UvzElimzMlOK 5PQuZ4atL9Jl6WCZb8811Qr+VUCFWz3e7HCgYDygwL+lBHlXEOLXPJ/lB8l61ugeooGjvuVdsaNf pM30pi5mwRECfOxo9zUL6uCHT6f4Dir6DsOIsTLb4N/vQjz+SGrLwtavSwRTAgMBAAGjWDBWMEYG A1UdEQQ/MD2BDWppbUBuYXNieS5uZXSBE2RlY2liZWxAZGVjaWJlbC5vcmeBF2RlY2liZWxAZGlz dHJpYnV0ZWQubmV0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEFBQADgYEApk/O70t8/38Jkp0M 9ywgSU2kn7w+Z3JN39pH3PnrLjmYV7D//2zJavR3zrZ4AeH2TDc/ioQTk1unfOYroYl+jUKD8NvU 8hbelR8pr+4hKMDx3+6plhKL0ZI9Zbw9DaJcZ3o8QK5fNGQooiz5+aCntbxdD81qG7+lkjQUUcvC wI0xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGlu ZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBD QQIQAqR+mVUhoOcWYeTv8ETf+DAJBgUrDgMCGgUAoIIBbzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcN AQcBMBwGCSqGSIb3DQEJBTEPFw0wODAyMjEyMzMwMDNaMCMGCSqGSIb3DQEJBDEWBBTEGnmzo/Vz /uUITt+vNQfesu8wBDCBhQYJKwYBBAGCNxAEMXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMc VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZy ZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE3/gwgYcGCyqGSIb3DQEJEAILMXigdjBi MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE 3/gwDQYJKoZIhvcNAQEBBQAEggEAWdmVGvwAjqiksqQ4YQD8c0Tv5WKl3GIWlTSXeiH03SrlBhQP 3CU+MdPquQ0LRq1mFmrg4N142qbkzLxGWOvoF0UYDwKCwE5RzhAr3I6+hHFPoSCuoDDBVQh6/WUs fT9h0uf/XepZBEwkLhLSxipr9Y9+b85huSrE7oTu/D4cftVR/BmyME84rNGx+toIL3UAEAk8KN69 gOjbybHoQo7KlDIgNWWFDfB0HDn13+HQsgY6Snol+6UH15wYxgcFewN8Xs5pHIo/WnUjWkN7F7rT nuL6QFN5zqnBrS9rYG3XqnJC0asbxGuNbRGmkpTe89rFPyk8lC2ggh1ZhfZ7rTtG3gAAAAAAAA== --Apple-Mail-1--156377003--