Re: Corrupted DB? could not open file pg_clog/####

On Sun, Jul 30, 2006 at 01:31:14AM -0400, Francisco Reyes wrote:
> Looking at archives seem to indicate missing pg_clog files is some form
> of row or page corruption.
>
> In an old thread from back in 2003 Tom Lane recommended
> (http://tinyurl.com/jushf):
>
> >>If you want to try to narrow down where the corruption is, you can
> >>experiment with commands like
> >>select ctid,* from big_table offset N limit 1;
>
> Is that still a valid suggestion?
> How do I know the possible maximun value for offset to try for each table?

It's still a reasonable suggestion. The maximum offset is the number of
rows in the table. You'll notice when the output is empty. Do you have
an idea how much data it contains?

> If I have logs turned on.. at which level will the eror show? I am only
> aware of the problem, because an application connected to postgrseql had
> the errors in it's logs, but not seeing anything in the postgresql logs
> themselves.

If you connect via psql, you can set the client_min_message to DEBUG to
get some more stuff.

> Just tried a pg_dump and got the
> could not open file "pg_clog/0000"
> error.
>
> The file pg_clog/0000 is missing.
> Looking at another thread (http://tinyurl.com/feyye) I see that the file
> can be created as 256K worth of zeroes. If I do this.. will operations
> resume normally? Is there a way to tell if any data was lost?

Well, it will stop complaining. What will happen is that any
transactions involving those transaction IDs will be assumed to have
been committed. This may be ok, but in extreme cases it could lead to
broken constraints.

However, pg_clog/0000 is the very first transaction file created. Is it
in the range of the files that do do exist? Are you sure some other
process didn't remove it accedently?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.