Thread: How to know the sizes of all tables & indexes in a database
Hi,
Postgres 7.3
Is there any way to know the size taken up by each table and indexes in postgres .
I am having a 53 GB database which contains tables , indexes, functions etc , how i can distribute these 53 GB's in database objects .
Thanks in advance
Disclaimer :- This e-mail message including any attachment may contain confidential, proprietary or legally privileged information. It should not be used by who is not the original intended recipient. If you have erroneously received this message, you are notified that you are strictly prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd and / or its subsidiary Companies accept no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of Newgen Software Technologies Ltd and / or its subsidiary Companies, as applicable.
Try:-
SELECT relname, relpages, CAST(reltuples AS numeric) ,
( (relpages*8192)/1024 ) as MB
FROM pg_class
( (relpages*8192)/1024 ) as MB
FROM pg_class
...which will give you space used on disk for all data pages currently in use per table (assuming you have 8k data pages - the default).
On 8/9/06, Sharma;G.S. <ghanshyam@newgen.co.in> wrote:
Disclaimer :- This e-mail message including any attachment may contain confidential, proprietary or legally privileged information. It should not be used by who is not the original intended recipient. If you have erroneously received this message, you are notified that you are strictly prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd and / or its subsidiary Companies accept no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of Newgen Software Technologies Ltd and / or its subsidiary Companies, as applicable.Hi,Postgres 7.3Is there any way to know the size taken up by each table and indexes in postgres .I am having a 53 GB database which contains tables , indexes, functions etc , how i can distribute these 53 GB's in database objects .Thanks in advance
Good morning, everyone.
We just upgraded from 8.0.3 (Windows-2003) to 8.1.4 (RHEL 4.0). Conversion was successful, except that
we have some functions that used dbsize.dll in Windows.
Can someone tell me if there is a compatible dbsize.so object is available, either as a binary object, or source
code? I Googled up some references for version 8.0.8 (Fedora Core 4), but nothing for 8.1.x .
Thanks for your help.
Mr. VanIngen,
There are native PostgreSQL dbsize commands in 8.1. Check out http://www.postgresql.org/docs/8.1/interactive/functions-admin.html for more details. Hopefully these will meet your needs if you don't get a better answer.
Chris
There are native PostgreSQL dbsize commands in 8.1. Check out http://www.postgresql.org/docs/8.1/interactive/functions-admin.html for more details. Hopefully these will meet your needs if you don't get a better answer.
Chris
On 8/29/06, Lane Van Ingen < lvaningen@esncc.com> wrote:
Good morning, everyone.We just upgraded from 8.0.3 (Windows-2003) to 8.1.4 (RHEL 4.0). Conversion was successful, except thatwe have some functions that used dbsize.dll in Windows.Can someone tell me if there is a compatible dbsize.so object is available, either as a binary object, or sourcecode? I Googled up some references for version 8.0.8 (Fedora Core 4), but nothing for 8.1.x .Thanks for your help.
Thank you for drawing my attention to this!
-----Original Message-----Mr. VanIngen,
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Chris Hoover
Sent: Tuesday, August 29, 2006 11:41 AM
To: Lane Van Ingen
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] dbsize.so for PostgreSQL 8.1.4 on Red Hat Enterprise Linux 4.0
There are native PostgreSQL dbsize commands in 8.1. Check out http://www.postgresql.org/docs/8.1/interactive/functions-admin.html for more details. Hopefully these will meet your needs if you don't get a better answer.
ChrisOn 8/29/06, Lane Van Ingen < lvaningen@esncc.com> wrote:Good morning, everyone.We just upgraded from 8.0.3 (Windows-2003) to 8.1.4 (RHEL 4.0). Conversion was successful, except thatwe have some functions that used dbsize.dll in Windows.Can someone tell me if there is a compatible dbsize.so object is available, either as a binary object, or sourcecode? I Googled up some references for version 8.0.8 (Fedora Core 4), but nothing for 8.1.x .Thanks for your help.
Hi all, I am installing on RHEL 4.0. It used to be that contributions like:
fuzzystrmatch
pgcrypto
pgstattuple
.. had to be enabled in 8.0.3 via SQL functions AND the Windows Installer under Windows 2003.
What is the procedure in Linux, when compiling from source? Is there a configure option that needs to be set to pick these up? Thanks for any guidance you can give.
Answered my own question, thanks ......
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Lane Van Ingen
Sent: Thursday, September 07, 2006 12:15 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Activating Contributions in 8.1.4Hi all, I am installing on RHEL 4.0. It used to be that contributions like:fuzzystrmatchpgcryptopgstattuple.. had to be enabled in 8.0.3 via SQL functions AND the Windows Installer under Windows 2003.What is the procedure in Linux, when compiling from source? Is there a configure option that needs to be set to pick these up? Thanks for any guidance you can give.