Re: Does PostgreSQL check database integrity at startup?

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

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

> Greetings,
>
> * Edson Carlos Ericksson Richter (richter(at)simkorp(dot)com(dot)br) wrote:
> > There should be a "catalog" that point where tables are stored in
> physical
> > files (I think, at least, because at some point PostgreSQL need to know
> from
> > where to read the data).
>
> Yes, it's pg_class. Specifically, the relfilenode.
>
> > Based on information from this catalog, would I have a tool (perhaps, a C
> > function) that check that data is really there?
>
> You could write such a function, but it wouldn't be able to be general
> purpose as a zero-byte file is, in fact, a valid file. You could just
> as easily do a 'select 1 from table limit 1;' and make sure that you get
> back a successful, single-row, result, if you wish to verify that certain
> tables in your database always have at least 1 row.
>
> Thanks!
>
> Stephen
>

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