pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcommand to check the database size ?
8+ messages / 3 participants
[nested] [flat]
* command to check the database size ?
@ 2010-03-25 12:55 Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 1 reply; 8+ messages in thread
From: Khangelani Gama @ 2010-03-25 12:55 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
@ 2010-03-25 13:19 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
0 siblings, 1 reply; 8+ messages in thread
From: Plugge, Joe R. @ 2010-03-25 13:19 UTC (permalink / raw)
To: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
@ 2010-03-25 13:31 ` Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:33 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:35 ` Re: command to check the database size ? Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 2 replies; 8+ messages in thread
From: Khangelani Gama @ 2010-03-25 13:31 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
@ 2010-03-25 13:33 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:36 ` Re: command to check the database size ? Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 1 reply; 8+ messages in thread
From: Plugge, Joe R. @ 2010-03-25 13:33 UTC (permalink / raw)
To: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; pgsql-admin
This should work ...
SELECT pg_size_pretty(pg_database_size('mydatabasename')) As fulldbsize;
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:33 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
@ 2010-03-25 13:36 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-03-25 13:49 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
0 siblings, 1 reply; 8+ messages in thread
From: Brad Nicholson @ 2010-03-25 13:36 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; +Cc: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:33 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:36 ` Re: command to check the database size ? Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-03-25 13:49 ` Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:51 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 1 reply; 8+ messages in thread
From: Khangelani Gama @ 2010-03-25 13:49 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; Plugge, Joe R. <JRPlugge@west.com>; +Cc: pgsql-admin
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 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:33 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:36 ` Re: command to check the database size ? Brad Nicholson <bnichols@ca.afilias.info>
2010-03-25 13:49 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
@ 2010-03-25 13:51 ` Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 0 replies; 8+ messages in thread
From: Plugge, Joe R. @ 2010-03-25 13:51 UTC (permalink / raw)
To: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; Brad Nicholson <bnichols@ca.afilias.info>; +Cc: 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 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: command to check the database size ?
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Re: command to check the database size ? Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Re: command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
@ 2010-03-25 13:35 ` Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 0 replies; 8+ messages in thread
From: Brad Nicholson @ 2010-03-25 13:35 UTC (permalink / raw)
To: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; +Cc: Plugge, Joe R. <JRPlugge@west.com>; pgsql-admin
On Thu, 2010-03-25 at 15:31 +0200, Khangelani Gama wrote:
> 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.
A trivial modification to the SQL statement provided will sum up the
totals for each table and present the overall size.
And, it's worth mentioning, Postgres 7.3 is many, many years old, full
of nasty bugs. You really, really should upgrade. If you can't, make
sure your head is not on the line when it corrupts your data.
>
>
>
>
>
>
>
>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-03-25 13:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-25 12:55 command to check the database size ? Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:19 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:31 ` Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:33 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:36 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-03-25 13:49 ` Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-03-25 13:51 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-25 13:35 ` Brad Nicholson <bnichols@ca.afilias.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox