Re: Does PostgreSQL check database integrity at startup?

On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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
>

*Edson's original request was for a query that shows the FILENAMEs for the
table.*
*As for "qood" query, that is entirely an opinion. The query WILL show all
files associated*

*with ALL tables. You are free to edit and reconstruct as you choose. *

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.