Re: Request to share information regarding postgresql pg_xlog file.

Yogesh Sharma <Yogesh1(dot)Sharma(at)nectechnologies(dot)in> writes:
> We are using below postgresql rpm.
> postgresql-8.1.18-2.1

As already noted, that version is *way* obsolete, and full of known bugs.
It's irresponsible to be storing data you care about in such a version.
Having said that ...

> In our system, below error is found and occurring is very frequent.
> CONTEXT: writing block 53 of relation 1663/16385/280951
> ERROR: could not open relation 1663/16385/280951: No such file or directory

Evidently, the bgwriter is trying to flush out a dirty buffer belonging to
a table that isn't there on-disk. I'm not sure I believe the other
respondents suggesting that the filesystem lost the file, especially not
if you're only seeing complaints about this one block in this one
relation. You could check by seeing whether any pg_class rows have
relfilenode 280951 in whichever DB has OID 16385. If not, then this
is just a minor bug that somehow a dirty buffer didn't get flushed before
its table was dropped.

While you don't really care about the data in that buffer in such a case,
the bgwriter doesn't know that. The trick is to get past that and
complete a checkpoint. You could try just touch-ing the missing file so
that there's something for the bgwriter to write the data into.

If that doesn't work, TBH, I'd suggest kill -9'ing the bgwriter and
letting the thing recover from WAL. Given that you've built up a
whole lot of WAL since the last successful checkpoint, that will
take quite a while, so it's a last resort ... but it ought to work.

regards, tom lane