[Eug-lug] Translate file name to Postgres DB name

[Ben Barrett]

from http://linuxboxadmin.com/articles/postgresql-for-mysql-users.php
"PostgreSQL uses the vacuumdb utility program to reclaim space from deleted
records and optimize indexes.  Vacuum should be scheduled to run via cron at
least once a week."
Do you use that?

You might also like this older thread:
http://archives.postgresql.org/pgsql-docs/2002-04/msg00001.php

To answer your question, from
http://developer.postgresql.org/pgdocs/postgres/functions-admin.html

The functions shown in Table
9-48<http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#F=
UNCTIONS-ADMIN-DBSIZE>calculate
the actual disk space usage of database objects.

*Table 9-48. Database Object Size Functions*
NameReturn TypeDescriptionpg_column_size(any)intNumber of bytes used to
store a particular value (possibly compressed) pg_database_size(oid) bigint=
Disk
space used by the database with the specified OID pg_database_size(name)
bigintDisk space used by the database with the specified name
pg_relation_size(oid) bigintDisk space used by the table or index with the
specified OID pg_relation_size(text) bigint Disk space used by the table or
index with the specified name. The table name can be qualified with a schema
name pg_size_pretty(bigint) textConverts a size in bytes into a
human-readable format with size units pg_tablespace_size(oid) bigintDisk
space used by the tablespace with the specified OID pg_tablespace_size(name)
bigintDisk space used by the tablespace with the specified name
pg_total_relation_size(oid) bigint Total disk space used by the table with
the specified OID, including indexes and toasted data pg_total_relation_size
(text) bigint Total disk space used by the table with the specified name,
including indexes and toasted data. The table name can be qualified with a
schema name

pg_column_size shows the space used to store any individual data value.

pg_database_size and pg_tablespace_size accept the OID or name of a database
or tablespace, and return the total disk space used therein.

pg_relation_size accepts the OID or name of a table, index or toast table,
and returns the size in bytes.

pg_size_pretty can be used to format the result of one of the other
functions in a human-readable way, using kB, MB, GB or TB as appropriate.

pg_total_relation_size accepts the OID or name of a table or toast table,
and returns the size in bytes of the data and all associated indexes and
toast tables.

What do I win?  :)

ben



On 1/31/07, Rob Hudson <rob at euglug.net> wrote:
>
> Is there a way, from within Postgresql, to view the filesize usage of
> its databases and tables?  That might be the best approach and sounds
> easier than looking at the raw data directory.
>
> On Wed Jan 31, 2007 at 02:35:16PM -0800, larry price wrote:
> > short answer: you can but it's ugly
> >
> > longer answer:
> > start looking at the pg_catalog.* relations (use \d from psql prompt)
> > as I understand it the files under $PG_DATA/base/  are named after
> > oid's for tables and relations, but there are a lot of subtleties, and
> > the whole point of a DBMS is to manage that bookkeeping for you.
> _______________________________________________
> EUGLUG mailing list
> euglug at euglug.org
> http://www.euglug.org/mailman/listinfo/euglug
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://euglug.org/pipermail/euglug/attachments/20070131/3b25c930/attac=
hment.htm