Re: 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 ….

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.