Re: Does PostgreSQL check database integrity at startup? - Mailing list pgsql-general

Greetings,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> >There should be a "catalog" that point where tables are stored in physical
> files...
>
> Here is the query that gives you that information.
>
> SELECT c.oid,
>                n.nspname as schema,
>                c.relname as table,
>               (SELECT oid FROM pg_database WHERE datname =
> current_database() ) as db_dir,
>                c.relfilenode as filename
>    FROM pg_class c
>      JOIN pg_namespace n ON (n.oid = c.relnamespace)
> WHERE relname NOT LIKE 'pg_%'
>       AND relname NOT LIKE 'information%'
>       AND relname NOT LIKE 'sql_%'
>       AND relkind = 'r'
> ORDER BY 2, relname;

This isn't a terribly good query- it's entirely valid to have 'pg_blah'
and 'informationblah' tables in user schemas.  If you'd like to filter
out the catalogs/internal schemas, filter based on schema name instead.

Also, this doesn't actually provide what Edson is asking for.  Edson's
asking for a query that uses pg_ls_dir() or some custom function which
will run 'stat' on each file and return the size, according to the OS.

Thanks!

Stephen