Mike Broers <mbroers@gmail.com> wrote:
> Thanks for the response. fsync and full_page_writes are both on.
> [ corruption appeared following power loss on the machine hosing
> the VM running PostgreSQL ]
That leaves three possibilities:
(1) fsync doesn't actually guarantee persistence in your stack.
(2) There is a hardware problem which has not been recognized.
(3) There is a so-far unrecognized bug in PostgreSQL.
Based on my personal experience, those are listed in descending
order of probability. I seem to recall reports of some VM for
which an fsync did not force data all the way to persistent
storage, but I don't recall which one. You might want to talk to
your service provider about what guarantees they make in this
regard.
> Is there something else I can run to confirm we are more or less
> ok at the database level after the pg_dumpall or is there no way
> to be sure and a fresh initdb is required.
Given that you had persistence options in their default state of
"on", and the corruption appeared after a power failure in a VM
environment, I would guess that the damage is probably limited.
That said, damage from this sort of event can remain hidden and
cause data loss later. Unfortunately we do not yet have a
consistency checker that can root out such problems. If you can
arrange a maintenance window to dump and load to a fresh initdb,
that would eliminate the possibility that some hidden corruption is
lurking. If that is not possible, running VACUUM FREEZE ANALYZE
will reduce the number of things that can go wrong, without
requiring down time.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company