Re: Two fsync related performance issues?

Hi,

Am Mittwoch, den 14.10.2020, 14:06 +0900 schrieb Michael Paquier:
> On Wed, Oct 14, 2020 at 02:48:18PM +1300, Thomas Munro wrote:
> > On Wed, Oct 14, 2020 at 12:53 AM Michael Banck
> > <michael(dot)banck(at)credativ(dot)de> wrote:
> > > 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).
>
> Yeah, it is safer to assume that it is the responsability of the
> backup tool to ensure that because it could be possible that a host is
> unplugged just after taking a backup, and having Postgres do this work
> at the beginning of archive recovery would not help in most cases.
> IMO this comes back to the point where we usually should not care much
> how long a backup takes as long as it is done right. Users care much
> more about how long a restore takes until consistency is reached. And
> this is in line with things that have been done via bc34223b or
> 96a7128.

I agree that the backup tool should make sure the backup is durable and
this is out of scope.

I was worried more about the restore part, right now,
https://www.postgresql.org/docs/13/continuous-archiving.html#BACKUP-PITR-RECOVERY
says this in point 4:

|Restore the database files from your file system backup. Be sure that
|they are restored with the right ownership (the database system user,
|not root!) and with the right permissions. If you are using
|tablespaces, you should verify that the symbolic links in pg_tblspc/
|were correctly restored.

There's no word of running sync afterwards or making otherwise sure that
everything is back in a durable fashion. Currently, we run fsync on all
the files on startup anyway during a PITR, but with the second patch,
that will no longer happen. Maybe that is not a problem, but if that's
the case, it's at least not clear to me.

Also, Tom seemed to imply up-thread in 3750(dot)1589826415(at)sss(dot)pgh(dot)pa(dot)us
that syncing the files was necessary, but maybe I'm reading too much into his rather short mail.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz