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

Alvaro,

* Alvaro Herrera (alvherre@alvh.no-ip.org) wrote:
> Stephen Frost wrote:
>
> > It's entirely unclear to me what the problem being complained about in
> > this thread actually is.
>
> As Edson explained, a relfilenode in the standby server is zero bytes
> long when it is not that size in the primary server, and it corresponds
> to a persistent table.  I don't have any satisfactory explanation for
> that.

Somehow I missed the discussion of it being zero bytes for a long time
after it's non-zero on the primary (I take the to main that it's longer
than whatever checkpoint timeout is set to..?).  That does sound like
there might be an actual issue/bug here.

> > Given the thread subject, if someone actually wanted to do a database
> > integrity check before startup, they could use pgBackRest to perform a
> > backup with a CRC-enabled database and at least verify that all of the
> > checksums are valid.
>
> That's not a complete solution, because a zero-byte file does not
> contain any CRC.  CRCs may detect some problems, but they will not
> detect this particular kind of corruption.

I agree that we can certainly have bugs which will cause corruption
that's not detectable by our CRCs.  Validating CRCs is just checking for
one kind of corruption; using amcheck would provide another level,
though it's unclear if that would help in this specific case if it's
really just files on the standby (but not the primary) being zero-length
long after they should be written out.

> > We could possibly look into adding some set of additional checks for
> > files which can't actually be zero-byte, perhaps..  I know we have some
> > other one-off checks already.
>
> Some possibilities, from the realm of haven't-had-coffee-yet-after-lunch:
>
> * the length of the FSM fork can let you infer something about the
> length that the main fork ought to have.  Maybe the VM fork too? not sure.
> (Easy to check: just some math on the size of the FSM/VM forks)

I was just chatting with David over lunch specifically about checking
that the number of forks was valid and looking at their lengths also
makes sense, but the VM is only created/updated based on VACUUM runs, so
checking that is a bit tricky.  David has ideas about how to handle
various races when it comes to forks (in particular by building a
manifest once and then seeing if anything in the relation has changed
since the manifest was built- if so, assume that WAL has the necessary
info and if not then raise a flag) but it's all a bit tricky when the
system is running.  We might be able to do more checks if we're serious
about supporting pre-startup checks though.  We were also thinking about
having a set of catalog tables that must not be zero and checking for
those.

> * the largest block number in any item pointer in any index of a table
> can tell you what's the latest page that should appear in the table.
> (Expensive: need to scan the indexes completely)

This is something that I'd expect amcheck to handle; I'm not sure it
makes sense to invent something else.  David and I have chatted a little
bit a while ago about trying to incorporate amcheck but it's rather
complicated and not something we plan to do in the near-term.  Still, it
does seem like it'd be a nice capability to have.  I wish it was
possible to do off-line though.  Of course, a user could do a restore
and then run amcheck on the result themselves.

Thanks!

Stephen