X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6961BD1B438 for ; Thu, 8 Jan 2004 19:45:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44098-04 for ; Thu, 8 Jan 2004 15:44:31 -0400 (AST) Received: from mail6.bluewin.ch (mail6.bluewin.ch [195.186.4.229]) by svr1.postgresql.org (Postfix) with ESMTP id BE84AD1B44A for ; Thu, 8 Jan 2004 15:44:29 -0400 (AST) Received: from bluewin.ch (62.203.212.142) by mail6.bluewin.ch (Bluewin AG 7.0.024) id 3FBC84610062CAC9; Thu, 8 Jan 2004 19:44:18 +0000 Message-ID: <3FFDB33A.9080207@bluewin.ch> Date: Thu, 08 Jan 2004 20:44:58 +0100 From: Oli Sennhauser User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en, de-ch MIME-Version: 1.0 To: Robert Cc: pgsql-admin@postgresql.org Subject: Re: Database size with postgres 7.2 References: <3FFD9982.6050409@scs.carleton.ca> In-Reply-To: <3FFD9982.6050409@scs.carleton.ca> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms030502040705090702060705" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/65 X-Sequence-Number: 11928 This is a cryptographically signed message in MIME format. --------------ms030502040705090702060705 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Robert > I'm hoping to find a easy way to find the disk space used by each > database on a given postgres v7.2 server - I've been looking through > the docs and have seen some references to oid2name, but that doesn't > really help my situation. This script gives you the used size per object. Unfortunately only for the actual db. If it also works on 7.2: I do not know (old stuff) -- -- Amount of space per object used after vacuum -- \echo \echo 'Caution: This skript does only print usefull information' \echo ' if you run VACUUM before!' \echo --VACUUM; SELECT c1.relname AS "tablename", c2.relname AS "indexname", c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename" FROM pg_class c1, pg_class c2, pg_index i WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid UNION SELECT relname, NULL, relpages * 8, relfilenode FROM pg_class WHERE relkind = 'r' ORDER BY tablename, indexname DESC, size_kb; Does it help? Regards Oli ------------------------------------------------------- Oli Sennhauser Database-Engineer (Oracle & PostgreSQL) Rebenweg 6 CH - 8610 Uster / Switzerland Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14 e-Mail oli.sennhauser@bluewin.ch Website http://mypage.bluewin.ch/shinguz/PostgreSQL/ Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import --------------ms030502040705090702060705 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIK0jCCAmUwggHOoAMCAQICCQCsJKpLsmk5ZzANBgkqhkiG9w0BAQUF ADBwMSUwIwYDVQQDExxTd2lzc1NpZ24gUGVyc29uYWwgQ2xhc3MxIENBMSYw JAYJKoZIhvcNAQkBFhdjbGFzczEtY2FAU3dpc3NTaWduLmNvbTESMBAGA1UE ChMJU3dpc3NTaWduMQswCQYDVQQGEwJDSDAeFw0wMzEwMTYxODIxMjNaFw0w NDEwMTYxODIxMjNaMGIxNjA0BgNVBAMULVN3aXNzU2lnbiBCcm9uemUgSUQg b2xpLnNlbm5oYXVzZXJAYmx1ZXdpbi5jaDEoMCYGCSqGSIb3DQEJARYZb2xp LnNlbm5oYXVzZXJAYmx1ZXdpbi5jaDBcMA0GCSqGSIb3DQEBAQUAA0sAMEgC QQCxt+SmHgsTzgBw7MY+KYwNwfurrZWoUSqm6Vdw5Z9aQDuRgZ/+OhL6dDXK VHpsyUiG6tzmqcEC8LkIpKv20YZxAgMBAAGjWTBXMCQGA1UdEQQdMBuBGW9s aS5zZW5uaGF1c2VyQGJsdWV3aW4uY2gwDgYDVR0PAQH/BAQDAgWgMB8GA1Ud IwQYMBaAFO30uEqBiizjCfrm+jE/TLQfSf6EMA0GCSqGSIb3DQEBBQUAA4GB AIK6w1rQif2IvpmKBjZf7fSp2FryojtHgNnKAlzfnwflZc0xcyqbRCC2vo0C psbvUBlx/t0pSwb0OVuD/uzvqTs7clq3eRH7VJL8LLS2OrJv7gI3jJVRD/oP 7m0mD/qmj0boSr9ilb5WFjXAVwsgJKuv0iHcAFJTTmj8RCZmWIVhMIICZTCC Ac6gAwIBAgIJAKwkqkuyaTlnMA0GCSqGSIb3DQEBBQUAMHAxJTAjBgNVBAMT HFN3aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEW F2NsYXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24x CzAJBgNVBAYTAkNIMB4XDTAzMTAxNjE4MjEyM1oXDTA0MTAxNjE4MjEyM1ow YjE2MDQGA1UEAxQtU3dpc3NTaWduIEJyb256ZSBJRCBvbGkuc2VubmhhdXNl ckBibHVld2luLmNoMSgwJgYJKoZIhvcNAQkBFhlvbGkuc2VubmhhdXNlckBi bHVld2luLmNoMFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALG35KYeCxPOAHDs xj4pjA3B+6utlahRKqbpV3Dln1pAO5GBn/46Evp0NcpUemzJSIbq3OapwQLw uQikq/bRhnECAwEAAaNZMFcwJAYDVR0RBB0wG4EZb2xpLnNlbm5oYXVzZXJA Ymx1ZXdpbi5jaDAOBgNVHQ8BAf8EBAMCBaAwHwYDVR0jBBgwFoAU7fS4SoGK LOMJ+ub6MT9MtB9J/oQwDQYJKoZIhvcNAQEFBQADgYEAgrrDWtCJ/Yi+mYoG Nl/t9KnYWvKiO0eA2coCXN+fB+VlzTFzKptEILa+jQKmxu9QGXH+3SlLBvQ5 W4P+7O+pOztyWrd5EftUkvwstLY6sm/uAjeMlVEP+g/ubSYP+qaPRuhKv2KV vlYWNcBXCyAkq6/SIdwAUlNOaPxEJmZYhWEwggK6MIICI6ADAgECAghscFj+ 9LG+HzANBgkqhkiG9w0BAQUFADBnMRwwGgYDVQQDExNTd2lzc1NpZ24gQ2xh c3MxIENBMSYwJAYJKoZIhvcNAQkBFhdjbGFzczEtY2FAU3dpc3NTaWduLmNv bTESMBAGA1UEChMJU3dpc3NTaWduMQswCQYDVQQGEwJDSDAeFw0wMjEwMjQx MzM0MTZaFw0wNDEwMjQxMjAwMTRaMHAxJTAjBgNVBAMTHFN3aXNzU2lnbiBQ ZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBT d2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNI MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCvb8N37MzyZnhRvbgSYCRa OYbCqH5/o9yoq3kyHP50e+QJVzvRZj9vw3GeDnhl+IrjmC0oPcXqmCgnvmZ3 2AlQITCU7jAfTKHjk2GZ8cFGgFTuR0lTbv0AfgMmYXYxoV0hu4v6WpDsigNx J6q5Pt6rWEFkOLu7IvgkBZ2RKQdz5wIDAQABo2YwZDAOBgNVHQ8BAf8EBAMC AQYwEgYDVR0TAQH/BAgwBgEB/wIBADAdBgNVHQ4EFgQU7fS4SoGKLOMJ+ub6 MT9MtB9J/oQwHwYDVR0jBBgwFoAUnKEHkQJI5q/Y1/5jBjNfheKPtbMwDQYJ KoZIhvcNAQEFBQADgYEAFMJrIPCoeXZxquyk6xqqflC/W+W3y5SNVzdASOjr tTzREC1oRwI36eqwMuAkbCr7znDV8rp2OC5S/DNw360FUvro/MjL2Ajztkum V/EF6enEAMbzCjBzJM8EfYEE3XyDyy5SzOtU5VPDMHOe9lPVOZ+1v2FpVLlK RW9Fxy6EG+wwggM+MIICJqADAgECAghVy6lgNCVu5DANBgkqhkiG9w0BAQUF ADB2MQswCQYDVQQGEwJDSDESMBAGA1UEChMJU3dpc3NTaWduMTIwMAYDVQQD EylTd2lzc1NpZ24gQ0EgKFJTQSBJSyBNYXkgNiAxOTk5IDE4OjAwOjU4KTEf MB0GCSqGSIb3DQEJARYQY2FAU3dpc3NTaWduLmNvbTAeFw0wMjEwMjQxMjAw MTRaFw0wNDEwMjQxMjAwMTRaMGcxHDAaBgNVBAMTE1N3aXNzU2lnbiBDbGFz czEgQ0ExJjAkBgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBTd2lzc1NpZ24uY29t MRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNIMIGfMA0GCSqGSIb3 DQEBAQUAA4GNADCBiQKBgQDJHZUwOQhgCPyjvVNBeMocJ/hsmImGuFFIa/QQ t31ExbzSfe58xgItElgqgPbMFevbVldcf2gafVMihh3aIYPcNTsDFn9FeiVP S7zohjKFVQxxDjprzCkixPAF9vuY9IKfRUoae0rDM6T2D5a2KdgtrJTgUu+y k0VjFd1hfHW5qwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/ BAUwAwEB/zAdBgNVHQ4EFgQUnKEHkQJI5q/Y1/5jBjNfheKPtbMwHwYDVR0j BBgwFoAUltdxzTkq1PyIsYqrU3hp749HfhYwDQYJKoZIhvcNAQEFBQADggEB ADxcvW2sVFFI48Pf9Bss++/M+a3sZ56EOdOcdYHhkk1Ax/tUsLCnjscKWHwj iJo0/ZunK070iwpSIbJAGDFXERx1VZZ3unUlFLANAgddIXAbIuvij5VoGZbG 6UiFxMTbcYxO1yYM/iQVSwB7NBKqUcTdHyYyw7oPh9BIayrYTkLBy2FPKe21 CAFGa9Ek8D/RyL6YsFgLB2neXqIxdIqID+QUufzKCUGOdgbc3Tvk8im3vR8C RVBxvZhY9I8+NIQR+nBPMoVp2PnDnp5sNLNGLIdKoZJXlQ2+8+fjnB/vrjYp Tn9W3nJEgJsbFScH4k+urq8TjK+QPpamJjySIikcJG8xggK3MIICswIBATB9 MHAxJTAjBgNVBAMTHFN3aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAk BgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQK EwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNIAgkArCSqS7JpOWcwCQYFKw4DAhoF AKCCAdEwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUx DxcNMDQwMTA4MTk0NDU4WjAjBgkqhkiG9w0BCQQxFgQUY4HentYTsAPyVns8 6XhAtARU3HwwUgYJKoZIhvcNAQkPMUUwQzAKBggqhkiG9w0DBzAOBggqhkiG 9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwIC ASgwgYwGCSsGAQQBgjcQBDF/MH0wcDElMCMGA1UEAxMcU3dpc3NTaWduIFBl cnNvbmFsIENsYXNzMSBDQTEmMCQGCSqGSIb3DQEJARYXY2xhc3MxLWNhQFN3 aXNzU2lnbi5jb20xEjAQBgNVBAoTCVN3aXNzU2lnbjELMAkGA1UEBhMCQ0gC CQCsJKpLsmk5ZzCBjgYLKoZIhvcNAQkQAgsxf6B9MHAxJTAjBgNVBAMTHFN3 aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEWF2Ns YXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJ BgNVBAYTAkNIAgkArCSqS7JpOWcwDQYJKoZIhvcNAQEBBQAEQFMxNW2FbN9L kIf94AwmORn1gGI6jsp6/oynieNMr0pH3L569q5hV6CYzukPFOctbSfv2DD1 plYu0lK2KmsYapcAAAAAAAA= --------------ms030502040705090702060705--