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

From Melvin Davidson
Subject Re: Does PostgreSQL check database integrity at startup?
Date
Msg-id CANu8Fiwv9WFAWdhiTKYTjd7W7tUum7yDn=k-eVwo3E1G+m8WPA@mail.gmail.com
Whole thread Raw
In response to Re: Does PostgreSQL check database integrity at startup?  (Stephen Frost <sfrost@snowman.net>)
Responses Re: Does PostgreSQL check database integrity at startup?
List pgsql-general
>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@snowman.net> wrote:
Greetings,

* Edson Carlos Ericksson Richter (richter@simkorp.com.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.

pgsql-general by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Does PostgreSQL check database integrity at startup?
Next
From: Stephen Frost
Date:
Subject: Re: Does PostgreSQL check database integrity at startup?