This should work ...
SELECT pg_size_pretty(pg_database_size('mydatabasename')) As fulldbsize;
From: Khangelani Gama [mailto:khangelani.g...@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-ow...@postgresql.org
[mailto:pgsql-admin-ow...@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-ow...@postgresql.org
[mailto:pgsql-admin-ow...@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:
[r...@c9903 dbsize]# pwd
/usr/src/postgresql-7.3.4/contrib/dbsize
[r...@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
[r...@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.
