Thread: Postgres Database size
Hi all, how to find the size of a particular database in postgres... please do reply Regards J Mageshwaran DBA ********** DISCLAIMER ********** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail & notify us immediately at admin@sifycorp.com Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! .
Mageshwaran schrieb: > Hi all, > > how to find the size of a particular database in postgres... You look there: http://www.postgresql.org/docs/8.2/static/storage-file-layout.html and use OS commands to calculate space used on disk (e.g. du) > > please do reply Well, thats a mailinglist, why wouldn't we reply? ;) > > > Regards > J Mageshwaran > DBA > > > ********** DISCLAIMER ********** > Information contained and transmitted by this E-MAIL is proprietary to > Sify Limited and is intended for use only by the individual or entity to > which it is addressed, and may contain information that is privileged, > confidential or exempt from disclosure under applicable law. If this is > a forwarded message, the content of this E-MAIL may not have been sent > with the authority of the Company. If you are not the intended > recipient, an agent of the intended recipient or a person responsible > for delivering the information to the named recipient, you are notified > that any use, distribution, transmission, printing, copying or > dissemination of this information in any way or in any manner is > strictly prohibited. If you have received this communication in error, > please delete this mail & notify us immediately at admin@sifycorp.com > > > Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel > for latest updates, expert columns, schedule, desktop scorecard, photo > galleries and more! > > Watch the hottest videos from Bollywood, Fashion, News and more only on > www.sifymax.com > > For the Expert view of the ICC World Cup log on to www.sify.com/khel. > Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, > Expert Columns by Gavaskar, Web chat with Dhoni and more! . > You should configure your mail program to avoid such excessive footer especially with silly disclaimers at least when you post to mailing lists or other sane people. Regards Tino Wildenhain
For small and moderate size databases, I find that the simplest way to estimate the database size is to do: du -ks /var/lib/postgresql/data ...create a new database, restore a backup of the database in whose size you're interested in du -ks /var/lib/postgresql/data Compare the first and second "du" output and you get possibly the exact size of the database. Cheers, t.n.a.
On Sun, 2007-03-18 at 00:40 +0530, Mageshwaran wrote:
> how to find the size of a particular database in postgres...
The old way was to use du or similar. Recent versions (I believe >=8.1,
but check the release notes to be sure) provide several useful functions
for this:
pg_column_size
pg_database_size
pg_relation_size
pg_size_pretty
pg_tablespace_size
pg_total_relation_size
For example:
rkh@csb-dev=> select datname,pg_size_pretty(pg_database_size(oid)) from
pg_database ;
datname | pg_size_pretty
-----------------------------+----------------
postgres | 3905 kB
csb | 113 GB
template0 | 3840 kB
csb-dev | 124 GB
csb-dev-snapshot-2007-03-08 | 123 GB
csb_02_02_2007 | 121 GB
template1 | 3840 kB
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.
I have been looking for such a function. Having Just upgraded to 8.2,
this function is a very welcome addition to my arsenal of tools.
Many thanks!
- Naz.
Reece Hart wrote:
On Sun, 2007-03-18 at 00:40 +0530, Mageshwaran wrote:
how to find the size of a particular database in postgres...
The old way was to use du or similar. Recent versions (I believe >=8.1,
but check the release notes to be sure) provide several useful functions
for this:
pg_column_size
pg_database_size
pg_relation_size
pg_size_pretty
pg_tablespace_size
pg_total_relation_size
For example:
rkh@csb-dev=> select datname,pg_size_pretty(pg_database_size(oid)) from
pg_database ;
datname | pg_size_pretty
-----------------------------+----------------
postgres | 3905 kB
csb | 113 GB
template0 | 3840 kB
csb-dev | 124 GB
csb-dev-snapshot-2007-03-08 | 123 GB
csb_02_02_2007 | 121 GB
template1 | 3840 kB