Subject: Re: command to check the database size ?
From: Brad Nicholson <bnichols@ca.afilias.info>
To: "Plugge, Joe R." <JRPlugge@west.com>
Cc: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: 
 <BD69807DAE0CE44CA00A8338D0FDD08302CF163AEE@oma00cexmbx03.corp.westworlds.com>
References: 
 <D78A8169F9436B4DB978300336168F3B33597EF250@SWBREXCH00.ucs-software.net>
	<BD69807DAE0CE44CA00A8338D0FDD08302CF163ADF@oma00cexmbx03.corp.westworlds.com>
	<D78A8169F9436B4DB978300336168F3B33597EF2A3@SWBREXCH00.ucs-software.net>
	<BD69807DAE0CE44CA00A8338D0FDD08302CF163AEE@oma00cexmbx03.corp.westworlds.com>
Content-Type: text/plain; charset="UTF-8"
Date: Thu, 25 Mar 2010 09:36:06 -0400
Message-Id: <1269524166.5155.40.camel@bnicholson-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable

On Thu, 2010-03-25 at 08:33 -0500, Plugge, Joe R. wrote:
> This should work =E2=80=A6
>=20
> =20
>=20
> 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.

>=20
> From: Khangelani Gama [mailto:Khangelani.Gama@ucs-software.co.za]=20
> 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 ?=20
>=20
>=20
> =20
>=20
> 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?=20
>=20
> =20
>=20
> Please advise if it=E2=80=99s possible.=20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> 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 ?=20
>=20
>=20
> =20
>=20
> Execute this against the database that you want to check the size of:
>=20
> =20
>=20
> SELECT schemaname, tablename,
>=20
> pg_size_pretty(size) AS size_pretty,
>=20
> pg_size_pretty(total_size) AS total_size_pretty
>=20
> FROM (SELECT *,
>=20
> pg_relation_size(schemaname||'.'||tablename) AS size,
>=20
> pg_total_relation_size(schemaname||'.'||tablename) AS total_size
>=20
> FROM pg_tables) AS TABLES
>=20
> WHERE schemaname=3D'public'
>=20
> ORDER BY total_size DESC;
>=20
> =20
>=20
> =20
>=20
> This will get it by table =E2=80=A6.
>=20
> =20
>=20
> 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 ?=20
>=20
>=20
> =20
>=20
> Hi=20
>=20
> =20
>=20
> =20
>=20
> Please help, I am new to postgres administration. I just need a
> command to check the size of the database.
>=20
> =20
>=20
> 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
>=20
> =20
>=20
> =20
>=20
> 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=E2=80=99t what to from from:
>=20
> =20
>=20
> [root@c9903 dbsize]# pwd
>=20
> /usr/src/postgresql-7.3.4/contrib/dbsize
>=20
> [root@c9903 dbsize]# ls -ltrh
>=20
> total 16K
>=20
> -rw-r--r--    1 70       wheel         201 Feb 23  2002 Makefile
>=20
> -rw-r--r--    1 70       wheel         253 Apr  2  2002 dbsize.sql.in
>=20
> -rw-r--r--    1 70       wheel         554 Jun 23  2002 README.dbsize
>=20
> -rw-r--r--    1 70       wheel        2.6K Sep  4  2002 dbsize.c
>=20
> [root@c9903 dbsize]#
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> I also could not find functions like pg_database_size(), pg_size_pretty()
> ,etc in this old version of postgres7.3.4.
>=20
> =20
>=20
> =20
>=20
> In summary I can=E2=80=99t find the command to find the database size fro=
m the
> main server and from the backup server.
>=20
> =20
>=20
> =20
>=20
> Please help
>=20
> =20
>=20
> =20
>=20
> Thanks
>=20
> =20
>=20
> Khangelani
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> Confidentiality Notice:http://ucs.co.za/conf.html
>=20
> =20
>=20
> =20
>=20
> =20
>=20
>                                   =20
> ______________________________________________________________________
> 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.
>=20
> =20
>=20
>                                   =20
> ______________________________________________________________________
> 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.
>=20
>=20
--=20
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.