Arnd Baranowski <baranowski@oculeus.com> writes:
> Correction fsync is „On" and the wal_sync_method is set to „open_datasync“
That's what they should be.
I tried to reproduce this by selecting "Restart..." immediately after
creating/populating a table on my own MacBook running Sonoma 14.3.
After the reboot, the table was there with the expected contents.
Now, this test doesn't actually prove a heck of a lot about PG's
crash recovery, because I see in the postmaster log
2024-02-05 21:00:30.322 EST [1148] LOG: database system was shut down at 2024-02-05 20:58:46 EST
2024-02-05 21:00:30.327 EST [1144] LOG: database system is ready to accept connections
which indicates that Postgres had time to perform a clean shutdown
before the system rebooted. (That is the expected scenario for an
OS reboot, assuming that the kernel delivers us SIGTERM as it's
required to do by POSIX and then gives us enough time to nail the
windows shut, which it's not required to do.)
The facts as you've presented them indicate that (1) checkpoints
weren't working, (2) we didn't get SIGTERM at system shutdown, *and*
(3) WAL wasn't written out to disk as it's supposed to be. It's
a bit hard to credit that so many things are broken and nobody has
noticed. I'm inclined to wonder if something is wrong with your
disk drive.
It would be interesting to know what appears in the first few lines
of your postmaster log after a data-losing restart. Also, try
running with log_checkpoints = on for awhile, and see if there are
log entries claiming successful checkpoint completion.
A different line of thought is that maybe the corruption is happening
because you have two postmasters started in the same data directory.
We have interlocks that are supposed to defend against that, but it'd
be a lot easier to credit that those aren't working than that all the
rest of this stuff broke.
regards, tom lane