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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
