I am slightly worried that corruption of data files may
remain undetected in PostgreSQL.
As an experiment, I created a simple table with a primary key
index and inserted a couple of rows. The corresponding data file
is 1 page = 8K long.
Now when I stop the server, zero out the data file with
dd if=/dev/zero of=45810 bs=8192 count=1
and start the server again, the table is empty when I SELECT
from it and no errors are reported.
Only a VACUUM gives me the
WARNING: relation "test" page 0 is uninitialized --- fixing
and the file is truncated to length zero.
The next thing I tried is to randomly scribble into the 8K data
file with a hex editor at different locations.
Some of these actions provoked error messages ranging from
ERROR: invalid page header in block 0 of relation "test"
over
ERROR: could not access status of transaction 1954047348
to
LOG: server process (PID 28149) was terminated by signal 11
Frequently, though, the result was that some of the rows were
"missing", i.e. there was no error message when I SELECTed
from the table, but some of the rows were gone.
I got no errors or warnings from VACUUM either.
As far as I know there is no tool to verify the integrity of
a PostgreSQL table.
- Shouldn't there be an error, some kind of 'missing magic
number' or similar, when a table file consists of only
zeros?
- Wouldn't it be desirable to have some means to verify the
integrity of a table file or a whole database?
Yours,
Laurenz Albe
