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 29E79D1B466 for ; Fri, 9 Jan 2004 09:27:35 +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 86178-01 for ; Fri, 9 Jan 2004 05:26:46 -0400 (AST) Received: from mail3.bluewin.ch (mail3.bluewin.ch [195.186.1.75]) by svr1.postgresql.org (Postfix) with ESMTP id 67E79D1B482 for ; Fri, 9 Jan 2004 05:26:44 -0400 (AST) Received: from bluewin.ch (81.62.90.202) by mail3.bluewin.ch (Bluewin AG 7.0.024) id 3FBC7F9D006341DB; Fri, 9 Jan 2004 09:26:33 +0000 Message-ID: <3FFE73F9.80208@bluewin.ch> Date: Fri, 09 Jan 2004 10:27:21 +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> <3FFDB33A.9080207@bluewin.ch> <3FFDEE28.908@scs.carleton.ca> In-Reply-To: <3FFDEE28.908@scs.carleton.ca> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms060601090903040401030404" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/73 X-Sequence-Number: 11936 This is a cryptographically signed message in MIME format. --------------ms060601090903040401030404 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Robert > Thanks for the good start, it worked without a hitch in 7.2. :) So > that query lists the size (in kilobytes?) all of the relations and the > index's of the relations as well? As you can see in the result header it gives the size in kb. Caution 1: The script is written a little dirty. I just multiply??? blocksize with 8. Because standard blocksize is actually 8 kb. In future or probably depending of your OS it can give some wrong values if blocksize is not 8kb anymore or only. I do not (yet) know how to find out actual block size dynamically. Caution 2: As you can see in the comment you have to VACUUM the database before running the script. Otherwise the values are completely wrong!!! Why for each user a separate db? Why do you not work with schemas? In my opinion it eases maintenance (and also uses less ressources) > 1, to show the disk usage of each table of a given database Hmmm, if this makes sense to only show the space of tables? Typically user asked me the total amount for a user/schema or an application, etc. and not for tables only. > 2, show the total disk usage for each database (ie db in the > pg_database table.) select sum(relpages)*8 AS "size_kb" from pg_class; > The only problem is, this shows all of the pg_* tables in each > database - is there any way to not show these relations? Caution: I did not clear what happens with "global objects". See: Chapter 43. System Catalogs ... A few catalogs are physically shared across all databases in a cluster; these are marked in the descriptions of the individual catalogs. In my opinion pg_class and all dependant views etc are per db and not per cluster. An so I do NOT see a simple solution to show something like: select dbname, sum(replpages)*8 from pg_databases, pg_class, where pg_database.oid = pg_class_db... But I might be wrong. > The second problem looks like it's going to be more difficult: I think > I'm going to have to open a connection to each DB seperatly to poll > for the sizes of the relations within each, again I'm going to run > into the same problem of needing to only count up the space taken up > by the relations in the current database, and not the extra pg_* ones. > Is there any way to determine which records in pg_class are related to > a pg_database record? Hmm... Looks like I could use a ERD of the > postgres system tables... If you find something to this point I would be verry interested in. 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 --------------ms060601090903040401030404 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 DxcNMDQwMTA5MDkyNzIxWjAjBgkqhkiG9w0BCQQxFgQUPixKyRdpvBrmkJ5l jmmv5pTzSmwwUgYJKoZIhvcNAQkPMUUwQzAKBggqhkiG9w0DBzAOBggqhkiG 9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwIC ASgwgYwGCSsGAQQBgjcQBDF/MH0wcDElMCMGA1UEAxMcU3dpc3NTaWduIFBl cnNvbmFsIENsYXNzMSBDQTEmMCQGCSqGSIb3DQEJARYXY2xhc3MxLWNhQFN3 aXNzU2lnbi5jb20xEjAQBgNVBAoTCVN3aXNzU2lnbjELMAkGA1UEBhMCQ0gC CQCsJKpLsmk5ZzCBjgYLKoZIhvcNAQkQAgsxf6B9MHAxJTAjBgNVBAMTHFN3 aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEWF2Ns YXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJ BgNVBAYTAkNIAgkArCSqS7JpOWcwDQYJKoZIhvcNAQEBBQAEQG4FsL2b/yb1 T9lBXlyGXq2YVPeVLDzSBuDCp2RjPfKEN5RDeWbi9t0NuuCEj7xkj6loEXeu k7+g/+D+VCbSHlcAAAAAAAA= --------------ms060601090903040401030404--