Re: Does PostgreSQL check database integrity at startup?

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.

> 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.

> 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)

* 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)

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services