Re: Tables dissapearing

On Aug 27, 2007, at 7:57 PM, Kamil Srot wrote:

> Tom Lane wrote:
>> Kamil Srot <kamil(dot)srot(at)nlogy(dot)com> writes:
>>> Erik Jones wrote:
>>>> Have you verified that the table's files are still on disk after
>>>> it's "disappeared"?
>>> Do not have any idea how to do it... I wasn't able to access it
>>> using any DML/DDL commands... can try it on a binary backup of
>>> the damaged DB if you'll guide me...
>> Make a note now of the table's "relfilenode" value (it'll be
>> different in each database), and confirm that you see it in the
>> filesystem. After the next disappearance, see if anything's still
>> there. For background read http://www.postgresql.org/docs/8.2/
>> static/storage.html
> OK, I have the filenames noted and I do confirm, they all does
> exist now under the base in the pgsql tree...
>> Note that certain operations like TRUNCATE and CLUSTER change the
>> relfilenode, so if you're using any of those then it might get
>> harder to track where the file is.
> There is not any manipulation with the structure of the DB, so
> it'll stay the same...
>
> Thank you!

Also, I'd write a simple "ping" script to check for the table that
runs every 5 seconds or so. Have it, at the very least, write out
the timestamp of when the table disappears into a file. Better yet
would be for it to send you an alert so you can check it out right
away. With that *when* you'll know *where* in your logs to look to
see what happened at that time.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com