On Sat, Dec 3, 2016 at 2:52 PM, Maeldron T. <maeldron@gmail.com> wrote:
> Upgrading FreeBSD from 10 to 11 might break your database. It probably won’t
> be corrupted but it will be useless until you dump-import it, and you might
> need to do manual fixing.
Sorry to hear about your arm and your database.
The collation support was changed in FreeBSD 11 (in collaboration with
Illumos and DragonflyBSD[1]). From FreeBSD's UPDATING:
Databases administrators will need to reindex their databases given
collation results will be different.
This is a problem that faces users of Postgres on all operating
systems, too, for example recently some GNU/Linux users had indexes
silently corrupted because of change to the glibc locale data for
German. One approach is to use "amcheck"[2] (proposed for future
inclusion in PostgreSQL) to check if indexes are still sane, any time
the collation definition files or libc itself might have changed.
> (Maybe database clusters should have a header that wouldn’t allow
> incompatible server versions to process the existing data. I wonder if it
> would take more than 8 bytes per server. But I guess it was not know to be
> incompatible. Even my two CIs didn’t show it.)
I had some thoughts along those lines too[3]. I thought about
checksumming libc and all relevant collation files (ie OS specific
files) so you could notice when anything that could bite you changes
(that is just some sketched out ideas, not production code). Some
have suggested that PostgreSQL should stop using OS collations and
adopt ICU[4] and then use its versioning metadata. Of course some
people already use ICU on FreeBSD because the old strcoll
implementation didn't play well with Unicode, but those users are
still presumably exposed to silent corruption when ICU changes because
AFAIK that didn't keep track of ICU versions.
[1] http://blog.etoilebsd.net/post/This_is_how_I_like_opensource
[2] https://www.postgresql.org/message-id/flat/CAM3SWZQzLMhMwmBqjzK+pRKXrNUZ4w90wYMUWfkeV8mZ3Debvw@mail.gmail.com
[3] https://github.com/macdice/check_pg_collations
[4] https://www.postgresql.org/message-id/flat/85364fde-091f-bbc0-fec2-e3ede39840a6@2ndquadrant.com
--
Thomas Munro
http://www.enterprisedb.com