Re: Two fsync related performance issues?

On Wed, Oct 14, 2020 at 12:53 AM Michael Banck
<michael(dot)banck(at)credativ(dot)de> wrote:
> Am Mittwoch, den 07.10.2020, 18:17 +1300 schrieb Thomas Munro:
> > ... and for comparison/discussion, here is an alternative patch that
> > figures out precisely which files need to be fsync'd using information
> > in the WAL.
>
> One question about this: Did you consider the case of a basebackup being
> copied/restored somewhere and the restore/PITR being started? Shouldn't
> Postgres then sync the whole data directory first in order to assure
> durability, or do we consider this to be on the tool that does the
> copying? Or is this not needed somehow?

To go with precise fsyncs, we'd have to say that it's the job of the
creator of the secondary copy. Unfortunately that's not a terribly
convenient thing to do (or at least the details vary).

[The devil's advocate enters the chat]

Let me ask you this: If you copy the pgdata directory of a system
that has shut down cleanly, for example with cp or rsync as described
in the manual, who will sync the files before you start up the
cluster? Not us, anyway, because SyncDataDirectory() only runs after
a crash. A checkpoint is, after all, a promise that all changes up to
some LSN are durably on disk, and we leave it up to people who are
copying files around underneath us while we're not running to worry
about what happens if you make that untrue. Now, why is a database
that crashed any different?

> My understanding is that Postgres would go through the same code path
> during PITR:
>
> if (ControlFile->state != DB_SHUTDOWNED &&
> ControlFile->state != DB_SHUTDOWNED_IN_RECOVERY)
> {
> RemoveTempXlogFiles();
> SyncDataDirectory();
>
> If I didn't miss anything, that would be a point for the syncfs patch?

Yeah.