Re: BUG #18009: Postgres Recovery not happening

Hi Thomas,

Thank you for your immediate response, This is not on the default file
system of AIX ( JFS2 ), but on a specific special purpose file system.
looks like ( open_datasync ) O_DSYNC is causing the issue which seems to
be not honoured on this file system. Yeah abrupt shutdown can be treated as
power loss.

I used wal_sync_method=fdatasync, ( although i am not sure the problem
vanished or not, because it is not getting reproduced ) but what i observe
is there is an immediate explicit sync calls to the files present in
../pg_wal/ directory post write call completions.

2023-07-04 03:36:04.259 CDT|64a3d9b4.8701bc|LOG: checkpoint starting: time
2023-07-04 03:36:06.263 CDT|64a3d9b4.8701bc|LOG: checkpoint complete:
wrote 21 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled;
write=1.925 s, sync=0.053 s, total=2.005 s; sync files=20, longest=0.019 s,
average=0.003 s; distance=32 kB, estimate=32 kB

I can see between these two time interval, write caches are cleared. With
wal_sync_method=fdatasync tunable, Is it safe to assume all the Postgres DB
writes during checkpointing are called via explicit call to OS level
sync() or fsync(), irrespective of O_DSYNC during open?.

Thanks
Vamshi.

On Mon, 3 Jul 2023 at 03:57, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Sat, Jul 1, 2023 at 2:29 AM PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> > Operating system: AIX
>
> > I verified in the OS side, we are not observing explicit fsync() call
> post
> > writing to this file "000000010000000000000003". I suspect this because
> > the writes are present in the VMM page cache and not getting synced up to
> > the disk. Post restart of my node, DB is not coming up.
>
> We don't usually call fsync() for WAL files (except when initially
> creating them), we use various methods controlled by the setting
> wal_sync_method[1] and on AIX we default to open_datasync (that means
> we open the WAL with O_DSYNC and then we expect pwrite() to return
> only after the data is durably on disk). Have you changed that
> setting? When you say "abrupt shutdown", do you mean power loss?
> Perhaps you could investigate what O_DSYNC does with respect to write
> caches on your system and what your disk controllers etc promise about
> power loss. Can you reproduce this problem with a fresh cluster, and
> does it go away if you use wal_sync_method=fdatasync?
>
> It doesn't seem that likely to me that expensive AIX systems would
> fail at sensible volatile cache management, so that's a long shot, but
> we know that some other systems can fail in that way (eg Windows on
> consumer storage), and I'm pretty sure they can fail exactly as you
> described because the control file is fsync'd while the WAL is only
> written to volatile drive caches.
>
> [1]
> https://www.postgresql.org/docs/15/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS
>

--
Thanks
Vamshi.