Re: Does PostgreSQL check database integrity at startup?

Greetings,

* Melvin Davidson (melvin6925(at)gmail(dot)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