On Tue, Aug 18, 2020 at 12:50:34PM +0200, Kasper Kondzielski wrote:
> Hi, thanks for the reply.
>
> To be honest I don't think it is better. Previously paragraph about
> remote_apply was after paragraph about `on` and before remote_write which
> followed natural order in terms of how strict these parameters are (i.e. how
> strong are the guarantees they provide). Because of that I think that
> remote_apply should return to its previous position.
Uh, not really --- see below.
> My original concern was about the fact that the difference between `on`,
> remote_write and remote_apply wasn't perfectly clear.
> I am not sure if I understand this difference correctly but maybe such a table
> could be helpful to me and others:
>
> +-----------------------------+-------------------------------------------+
> | | synchronous_commit |
> +-----------------------------+-----+--------------+--------------+-------+
> | operation on standby server | on | remote_apply | remote_write | local |
> +-----------------------------+-----+--------------+--------------+-------+
> | written to WAL | Yes | Yes | Yes | No |
> +-----------------------------+-----+--------------+--------------+-------+
> | commit transaction | Yes | Yes | No | No |
> +-----------------------------+-----+--------------+--------------+-------+
> | fsync | Yes | No | No | No |
> +-----------------------------+-----+--------------+--------------+-------+
>
> From which we can clearly see that only `on` option guarantees fsync, and the
> only difference between remote_write and remote_apply is the visibility of
> transaction results to the queries.
Un, 'on' does _not_ apply the WAL data, and remote_apply does do remote
fsync. If you want to go in order of severity, with the most severe
first, it is:
remote_apply
on
remote_write
local
This is seen in the C enum ordering for synchronous_commit, but in
reverse order:
typedef enum
{
SYNCHRONOUS_COMMIT_OFF, /* asynchronous commit */
SYNCHRONOUS_COMMIT_LOCAL_FLUSH, /* wait for local flush only */
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and
remote
* write */
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote
flush */
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local flush and remote
apply */
} SyncCommitLevel;
and this defines the 'on' behavior:
/* Define the default setting for synchronous_commit */
#define SYNCHRONOUS_COMMIT_ON SYNCHRONOUS_COMMIT_REMOTE_FLUSH
I will clarify this comment, and the docs, to say that remote_apply
includes remote flush.
Obviously these docs need improvement. Updated patch attached. I have
to admit I was kind of confused if remote_apply did remote fsync, but
never had the time to research it until you asked. remote_apply is so
different from the rest, and so heavy, that I put it last in its own
paragraph.
> + Finally, when set to <literal>remote_apply</literal>, commits will
> + wait until replies from the current synchronous standby(s) indicate
> + they have received the commit record of the transaction and applied
> + it, so that it has become visible to queries on the standby(s).
> + This can cause much larger commit delays than previous settings
> + since it involves WAL replay.
> 'This can cause much' - What does it mean that it can cause? Under what
> circumstances it will/won't cause it?
Uh, I think we can change this to "will cause", because I can't think of
a case where it will not.
> "since it involves WAL replay" - What is a WAL replay?
Well, there is a doc section that talks about WAL:
https://www.postgresql.org/docs/12/wal.html
and other parts of the config docs that talk about WAL.
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index 7a7177c..095caa1
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** include_dir 'conf.d'
*** 2726,2752 ****
for their WAL records to be replicated to the standby server(s).
When set to <literal>on</literal>, commits will wait until replies
from the current synchronous standby(s) indicate they have received
! the commit record of the transaction and flushed it to disk. This
ensures the transaction will not be lost unless both the primary and
all synchronous standbys suffer corruption of their database storage.
- When set to <literal>remote_apply</literal>, commits will wait until replies
- from the current synchronous standby(s) indicate they have received the
- commit record of the transaction and applied it, so that it has become
- visible to queries on the standby(s).
When set to <literal>remote_write</literal>, commits will wait until replies
from the current synchronous standby(s) indicate they have
! received the commit record of the transaction and written it out to
! their operating system. This setting is sufficient to
! ensure data preservation even if a standby instance of
! <productname>PostgreSQL</productname> were to crash, but not if the standby
! suffers an operating-system-level crash, since the data has not
necessarily reached stable storage on the standby.
! Finally, the setting <literal>local</literal> causes commits to wait for
! local flush to disk, but not for replication. This is not usually
desirable when synchronous replication is in use, but is provided for
completeness.
</para>
<para>
If <varname>synchronous_standby_names</varname> is empty, the settings
<literal>on</literal>, <literal>remote_apply</literal>, <literal>remote_write</literal>
and <literal>local</literal> all provide the same synchronization level:
--- 2726,2756 ----
for their WAL records to be replicated to the standby server(s).
When set to <literal>on</literal>, commits will wait until replies
from the current synchronous standby(s) indicate they have received
! the commit record of the transaction and flushed it to durable storage. This
ensures the transaction will not be lost unless both the primary and
all synchronous standbys suffer corruption of their database storage.
When set to <literal>remote_write</literal>, commits will wait until replies
from the current synchronous standby(s) indicate they have
! received the commit record of the transaction and written it to
! their file systems. This setting ensures data preservation if a standby instance of
! <productname>PostgreSQL</productname> crashes, but not if the standby
! suffers an operating-system-level crash because the data has not
necessarily reached stable storage on the standby.
! The setting <literal>local</literal> causes commits to wait for
! local flush to disk, but not for replication. This is usually not
desirable when synchronous replication is in use, but is provided for
completeness.
</para>
<para>
+ Finally, when set to <literal>remote_apply</literal>, commits
+ will wait until replies from the current synchronous standby(s)
+ indicate they have received the commit record of the transaction
+ and applied it, so that it has become visible to queries on the
+ standby(s), and also written to durable storage on the standbys.
+ This will cause much larger commit delays than previous settings
+ since it involves WAL replay.
+ </para>
+ <para>
If <varname>synchronous_standby_names</varname> is empty, the settings
<literal>on</literal>, <literal>remote_apply</literal>, <literal>remote_write</literal>
and <literal>local</literal> all provide the same synchronization level:
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
new file mode 100644
index df1b43a..f44b4c4
*** a/src/include/access/xact.h
--- b/src/include/access/xact.h
*************** typedef enum
*** 72,78 ****
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and remote
* write */
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote flush */
! SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local flush and remote apply */
} SyncCommitLevel;
/* Define the default setting for synchronous_commit */
--- 72,78 ----
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and remote
* write */
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote flush */
! SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local and remote flush and remote apply */
} SyncCommitLevel;
/* Define the default setting for synchronous_commit */
