Re: BUG #17245: Index corruption involving deduplicated entries - Mailing list pgsql-bugs

On 25.10.2021 23:49, Thomas Munro wrote:
> For the record, commit f900a79e was about preserving pre-existing
> behaviour dating back a few years (ie avoiding a change).  The history
> is like this:
>
> On ancient FreeBSD releases, PostgreSQL would default to wal_sync_method=fsync.
>
> If built on FreeBSD 11.1+ (July 2017), it would select
> wal_sync_method=fdatasync as the default, because the fdatasync()
> system call was added and would be detected by the configure script.
>
> If built on FreeBSD 13.0+ (April 2021), it would traditionally have
> selected wal_sync_method=open_datasync, because the O_DSYNC flag was
> added to <fcntl.h>.  But commit f900a79e changed PostgreSQL 13.3+
> (April 2021) and sibling releases to prefer wal_sync_method=fdatasync
> still, despite the presence of O_DSYNC.

Thank you, this confirms the assumptions I had after reading through the 
sources of different releases and testing the --describe-config output 
from old packages in my package manager's cache.

> Given that this system upgraded PostgreSQL 13.2->13.3 on the 18th of
> May, and assuming it had installed FreeBSD 13.0 and PostgreSQL 13.2
> around the time of FreeBSD 13.0's release on the 13th of April, it
> would have been running with wal_sync_method=open_datasync (assuming
> default used) in that 5 week window.  I could talk about what exact
> effects that would have if I knew which file system we're talking
> about here, but it doesn't seems to be relevant to this case, given
> that "The database in question was restored from a pg_dumpall backup
> _three weeks ago_ and I'm told there have been no crashes or even
> unclean restarts since then" (my emphasis).


In case this might be important later: everything is on ZFS (upgraded 
from FreeBSD's old zfs port to openzfs, as imported in FreeBSD 13.0).

Log entries related to the upgrade:
Oct  5 09:55:27 db pkg[7101]: postgresql13-server-13.3_1 deinstalled
Oct  5 09:55:41 db pkg[7144]: postgresql14-server-14.0 installed

This was preceded by pg_dumpall and followed by initdb with the exact 
same parameters (using unchanged rc.conf 
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=en_GB.UTF-8") and 
the import of the dump using psql. I do not use pg_upgrade since 
different versions of PostgreSQL cannot be installed at the same time in 
FreeBSD using the ports/packages system, and I do not find dump/restore 
much a chore (plus it solves issues with possible locale changes).

There have been no crashes since; there was one reload (pg_hba.conf 
edits) and several restarts (to snapshot the file structure with the 
corrupted index, plus another enabling WAL archiving today in the morning).

I have postgresql-rum installed, if this matters; it is used by a 
Pleroma instance in a separate database.

-- 
K. R.