Re: Database possible corruption , unsolvable mystery

Eric Lauzon wrote:
> This is why our investigation brought us to the folowing questions:
>
> 1. Are postgresql data file name are hashed references to table
> name(as oracle)? [~path to data EX:/var/log/pgsql/data/[arbitraty
> numbers]/[datafile]]?

OID numbers - look in the contrib directory/package for the oid2name
utility.

> 2. If the data files are corrupted and we re-create is it possible it
> uses the same files thus creating the same issue?

No

> 3. Since we know that all the tables has that problems is there an
> internal table with undisclosed references to tables data files?  I
> hope the questions were clear.

You mean a system table that could account for your problems since it
refers to some of your tables but not others? No.

The obvious places to start are:
1. vacuum analyse verbose on the tables in question
    This should show whether there are a lot of "dead" rows
2. explain analyse on problem queries
    To see if the query plans are correct
3. SELECT * FROM pg_stat_???
    Assuming you have statistics gathering turned on, this might show
unusual table accesses.

HTH
--
   Richard Huxton
   Archonet Ltd