回复： fsync data directory after DB crash

Yes, I saw the usage of syncfs in PG14, but it is recommended to use it on Linux 5.8 or higher. If my OS version is lower than 5.8, can I still enable it?

 

Pandora
yeyukui(at)qq(dot)com

 

------------------ 原始邮件 ------------------
发件人: "Thomas Munro"<thomas(dot)munro(at)gmail(dot)com&gt;;
发送时间: 2023年7月19日(星期三) 上午9:37
收件人: "Michael Paquier"<michael(at)paquier(dot)xyz&gt;;
抄送: "Pandora"<yeyukui(at)qq(dot)com&gt;; "pgsql-general"<pgsql-general(at)lists(dot)postgresql(dot)org&gt;;
主题: Re: fsync data directory after DB crash

On Wed, Jul 19, 2023 at 12:41 PM Michael Paquier <michael(at)paquier(dot)xyz&gt; wrote:
&gt; On Tue, Jul 18, 2023 at 04:50:25PM +0800, Pandora wrote:
&gt; &gt; I found that starting from version 9.5, PostgreSQL will do fsync on
&gt; &gt; the entire data directory after DB crash. Here's a question: if I
&gt; &gt; have FPW = on, why is this step still necessary?
&gt;
&gt; Yes, see around the call of SyncDataDirectory() in xlog.c:
&gt;&nbsp; * - There might be data which we had written, intending to fsync it, but
&gt;&nbsp; *&nbsp;&nbsp; which we had not actually fsync'd yet.&nbsp; Therefore, a power failure in
&gt;&nbsp; *&nbsp;&nbsp; the near future might cause earlier unflushed writes to be lost, even
&gt;&nbsp; *&nbsp;&nbsp; though more recent data written to disk from here on would be
&gt;&nbsp; *&nbsp;&nbsp; persisted.&nbsp; To avoid that, fsync the entire data directory.

FTR there was some discussion and experimental patches that would add
recovery_init_sync_method=none and recovery_init_sync_method=wal,
which are based on the OP's observation + an idea for how to make it
work even without FPWs enabled:

https://www.postgresql.org/message-id/flat/CA%2BhUKGKgj%2BSN6z91nVmOmTv2KYrG7VnAGdTkWdSjbOPghdtooQ%40mail.gmail.com#576caccf21cb6c3e883601fceb28d36b

Only recovery_init_sync_method=syncfs actually went in from that
thread.&nbsp; It works better for some setups (systems where opening
squillions of files just do perform a no-op fsync() is painfully
expensive).