On Mon, Oct 25, 2021 at 10:50 PM K. R. <iijima.yun@koumakan.jp> wrote:
> On 25.10.2021 0:52, Peter Geoghegan wrote:
> > On Sun, Oct 24, 2021 at 4:40 AM PG Bug reporting form
> > <noreply@postgresql.org> wrote:
> >> PostgreSQL version: 14.0
> >> Operating system: FreeBSD 13.0-RELEASE
> > PostgreSQL 14 is the first version where the default wal_sync_method
> > is fdatasync() on FreeBSD -- though only with FreeBSD 13. See commit
> > f900a79e. Perhaps that has something to do with the problem seen here.
> To clarify: I had been running PostgreSQL 13.3 since the 18th of May
> (upgraded from 13.2) until the upgrade to 14.0.
>
> The REL_13_3 tag seems to include the change from f900a79e (matching the
> commit message: ‘Like commit 576477e73c4, which did the same for Linux,
> back-patch to all supported releases.’) and both the 13.3 package from
> my package manager's cache and the 13.4 package available from FreeBSD
> repositories at the moment uses fdatasync() as well:
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.
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).