Re: command to check the database size ? - Mailing list pgsql-admin

Sorry, I overlooked that you were on an ancient version of Postgres ... you really need to migrate to a newer version.

-----Original Message-----
From: Khangelani Gama [mailto:Khangelani.Gama@ucs-software.co.za] 
Sent: Thursday, March 25, 2010 8:49 AM
To: Brad Nicholson; Plugge, Joe R.
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] command to check the database size ?

Hi

I get the following :

db9879=# SELECT pg_size_pretty(pg_database_size('db9879')) As fulldbsize;

ERROR:  Function pg_database_size("unknown") does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts





Sorry the previous command gives me the following,  I actually ran it in my test server(pg 8.3) when I ran it the first
time:

db9879=# SELECT schemaname, tablename,
db9879-# pg_size_pretty(size) AS size_pretty,
db9879-# pg_size_pretty(total_size) AS total_size_pretty
db9879-# FROM (SELECT *,
db9879(# pg_relation_size(schemaname||'.'||tablename) AS size,
db9879(# pg_total_relation_size(schemaname||'.'||tablename) AS total_size
db9879(# FROM pg_tables) AS TABLES
db9879-# WHERE schemaname='public'
db9879-# ORDER BY total_size DESC;
ERROR:  Function pg_relation_size(text) does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts







Thanks

Khangelani







-----Original Message-----
From: Brad Nicholson [mailto:bnichols@ca.afilias.info]
Sent: Thursday, March 25, 2010 3:36 PM
To: Plugge, Joe R.
Cc: Khangelani Gama; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] command to check the database size ?

On Thu, 2010-03-25 at 08:33 -0500, Plugge, Joe R. wrote:
> This should work …
>
>
>
> SELECT pg_size_pretty(pg_database_size('mydatabasename')) As
> fulldbsize;

 I'm pretty sure that the pg_database_size() function did not exist in
PG 7.3.

>
> From: Khangelani Gama [mailto:Khangelani.Gama@ucs-software.co.za]
> Sent: Thursday, March 25, 2010 8:31 AM
> To: Plugge, Joe R.; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] command to check the database size ?
>
>
>
>
> Thanks Plugge, it works fine whereby it gives me the sizes for each
> table. But is there another command that will sum up and then give me
> one size for the whole database?
>
>
>
> Please advise if it’s possible.
>
>
>
>
>
>
>
>
>
>
>
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Plugge, Joe R.
> Sent: Thursday, March 25, 2010 3:19 PM
> To: Khangelani Gama; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] command to check the database size ?
>
>
>
>
> Execute this against the database that you want to check the size of:
>
>
>
> SELECT schemaname, tablename,
>
> pg_size_pretty(size) AS size_pretty,
>
> pg_size_pretty(total_size) AS total_size_pretty
>
> FROM (SELECT *,
>
> pg_relation_size(schemaname||'.'||tablename) AS size,
>
> pg_total_relation_size(schemaname||'.'||tablename) AS total_size
>
> FROM pg_tables) AS TABLES
>
> WHERE schemaname='public'
>
> ORDER BY total_size DESC;
>
>
>
>
>
> This will get it by table ….
>
>
>
> From:pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Khangelani Gama
> Sent: Thursday, March 25, 2010 7:55 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] command to check the database size ?
>
>
>
>
> Hi
>
>
>
>
>
> Please help, I am new to postgres administration. I just need a
> command to check the size of the database.
>
>
>
> I tried searching but no luck. The server is running on Postgres 7.3.4
> and O/S Linux Centos 5. The data sits on /data/postgres7.3.4/data
> directory. There is no directory
> like /usr/src/postgresql-7.3.4/contrib/dbsize
>
>
>
>
>
> But the backup server which is also running postgres 7.3.4 but on
> Redhat 9 does have /usr/src/postgresql-7.3.4/contrib/dbsize directory
> which has the following files but I don’t what to from from:
>
>
>
> [root@c9903 dbsize]# pwd
>
> /usr/src/postgresql-7.3.4/contrib/dbsize
>
> [root@c9903 dbsize]# ls -ltrh
>
> total 16K
>
> -rw-r--r--    1 70       wheel         201 Feb 23  2002 Makefile
>
> -rw-r--r--    1 70       wheel         253 Apr  2  2002 dbsize.sql.in
>
> -rw-r--r--    1 70       wheel         554 Jun 23  2002 README.dbsize
>
> -rw-r--r--    1 70       wheel        2.6K Sep  4  2002 dbsize.c
>
> [root@c9903 dbsize]#
>
>
>
>
>
>
>
> I also could not find functions like pg_database_size(), pg_size_pretty()
> ,etc in this old version of postgres7.3.4.
>
>
>
>
>
> In summary I can’t find the command to find the database size from the
> main server and from the backup server.
>
>
>
>
>
> Please help
>
>
>
>
>
> Thanks
>
>
>
> Khangelani
>
>
>
>
>
>
>
>
>
>
>
>
>
> Confidentiality Notice:http://ucs.co.za/conf.html
>
>
>
>
>
>
>
>
> ______________________________________________________________________
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of UCS
> Group and/or its subsidiaries. Any review, use or dissemination
> thereof by anyone other than the intended addressee is prohibited. If
> you are not the intended addressee please notify the writer
> immediately and destroy the e-mail. UCS Group Limited and its
> subsidiaries distance themselves from and accept no liability for
> unauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.
>
>
>
>
> ______________________________________________________________________
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of UCS
> Group and/or its subsidiaries. Any review, use or dissemination
> thereof by anyone other than the intended addressee is prohibited. If
> you are not the intended addressee please notify the writer
> immediately and destroy the e-mail. UCS Group Limited and its
> subsidiaries distance themselves from and accept no liability for
> unauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.
>
>
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof UCS Group and/or its subsidiaries.  Any review, use or dissemination thereof by anyone other than the
intendedaddressee is prohibited.  If you are not the intended addressee please notify the writer immediately and
destroythe e-mail.  UCS Group Limited and its subsidiaries distance themselves from and accept no liability for
unauthoriseduse of their e-mail facilities or e-mails sent other than strictly for business purposes.