Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ee59f66

Browse filesBrowse files
bmomjianbmomjian
committed
doc: improve description of synchronous_commit modes
Previously it wasn't clear exactly what each of the synchronous_commit modes accomplished. This clarifies that, and adds a table describing it. Only backpatched through 9.6 since 9.5 doesn't have all the options. Reported-by: kghost0@gmail.com Discussion: https://postgr.es/m/159741195522.14321.13812604195366728976@wrigleys.postgresql.org Backpatch-through: 9.6
1 parent 7753ca4 commit ee59f66
Copy full SHA for ee59f66

File tree

Expand file treeCollapse file tree

2 files changed

+116
-32
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+116
-32
lines changed

‎doc/src/sgml/config.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/config.sgml
+114-30Lines changed: 114 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -2277,14 +2277,26 @@ include_dir 'conf.d'
22772277
</term>
22782278
<listitem>
22792279
<para>
2280-
Specifies whether transaction commit will wait for WAL records
2281-
to be written to disk before the command returns a <quote>success</>
2282-
indication to the client. Valid values are <literal>on</>,
2283-
<literal>remote_apply</>, <literal>remote_write</>, <literal>local</>,
2284-
and <literal>off</>. The default, and safe, setting
2285-
is <literal>on</>. When <literal>off</>, there can be a delay between
2286-
when success is reported to the client and when the transaction is
2287-
really guaranteed to be safe against a server crash. (The maximum
2280+
Specifies how much WAL processing must complete before
2281+
the database server returns a <quote>success</quote>
2282+
indication to the client. Valid values are
2283+
<literal>remote_apply</literal>, <literal>on</literal>
2284+
(the default), <literal>remote_write</literal>,
2285+
<literal>local</literal>, and <literal>off</literal>.
2286+
</para>
2287+
2288+
<para>
2289+
If <varname>synchronous_standby_names</varname> is empty,
2290+
the only meaningful settings are <literal>on</literal> and
2291+
<literal>off</literal>; <literal>remote_apply</literal>,
2292+
<literal>remote_write</literal> and <literal>local</literal>
2293+
all provide the same local synchronization level
2294+
as <literal>on</literal>. The local behavior of all
2295+
non-<literal>off</literal> modes is to wait for local flush of WAL
2296+
to disk. In <literal>off</literal> mode, there is no waiting,
2297+
so there can be a delay between when success is reported to the
2298+
client and when the transaction is later guaranteed to be safe
2299+
against a server crash. (The maximum
22882300
delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
22892301
<xref linkend="guc-fsync">, setting this parameter to <literal>off</>
22902302
does not create any risk of database inconsistency: an operating
@@ -2296,38 +2308,40 @@ include_dir 'conf.d'
22962308
exact certainty about the durability of a transaction. For more
22972309
discussion see <xref linkend="wal-async-commit">.
22982310
</para>
2311+
22992312
<para>
2300-
If <xref linkend="guc-synchronous-standby-names"> is non-empty, this
2301-
parameter also controls whether or not transaction commits will wait
2302-
for their WAL records to be replicated to the standby server(s).
2303-
When set to <literal>on</>, commits will wait until replies
2313+
If <xref linkend="guc-synchronous-standby-names"> is non-empty,
2314+
<varname>synchronous_commit</varname> also controls whether
2315+
transaction commits will wait for their WAL records to be
2316+
processed on the standby server(s).
2317+
</para>
2318+
2319+
<para>
2320+
When set to <literal>remote_apply</literal>, commits will wait
2321+
until replies from the current synchronous standby(s) indicate they
2322+
have received the commit record of the transaction and applied
2323+
it, so that it has become visible to queries on the standby(s),
2324+
and also written to durable storage on the standbys. This will
2325+
cause much larger commit delays than previous settings since
2326+
it waits for WAL replay. When set to <literal>on</literal>,
2327+
commits wait until replies
23042328
from the current synchronous standby(s) indicate they have received
2305-
the commit record of the transaction and flushed it to disk. This
2329+
the commit record of the transaction and flushed it to durable storage. This
23062330
ensures the transaction will not be lost unless both the primary and
23072331
all synchronous standbys suffer corruption of their database storage.
2308-
When set to <literal>remote_apply</>, commits will wait until replies
2309-
from the current synchronous standby(s) indicate they have received the
2310-
commit record of the transaction and applied it, so that it has become
2311-
visible to queries on the standby(s).
23122332
When set to <literal>remote_write</>, commits will wait until replies
23132333
from the current synchronous standby(s) indicate they have
2314-
received the commit record of the transaction and written it out to
2315-
their operating system. This setting is sufficient to
2316-
ensure data preservation even if a standby instance of
2317-
<productname>PostgreSQL</> were to crash, but not if the standby
2318-
suffers an operating-system-level crash, since the data has not
2334+
received the commit record of the transaction and written it to
2335+
their file systems. This setting ensures data preservation if a standby instance of
2336+
<productname>PostgreSQL</productname> crashes, but not if the standby
2337+
suffers an operating-system-level crash because the data has not
23192338
necessarily reached durable storage on the standby.
2320-
Finally, the setting <literal>local</> causes commits to wait for
2321-
local flush to disk, but not for replication. This is not usually
2339+
The setting <literal>local</literal> causes commits to wait for
2340+
local flush to disk, but not for replication. This is usually not
23222341
desirable when synchronous replication is in use, but is provided for
23232342
completeness.
23242343
</para>
2325-
<para>
2326-
If <varname>synchronous_standby_names</> is empty, the settings
2327-
<literal>on</>, <literal>remote_apply</>, <literal>remote_write</>
2328-
and <literal>local</> all provide the same synchronization level:
2329-
transaction commits only wait for local flush to disk.
2330-
</para>
2344+
23312345
<para>
23322346
This parameter can be changed at any time; the behavior for any
23332347
one transaction is determined by the setting in effect when it
@@ -2337,6 +2351,76 @@ include_dir 'conf.d'
23372351
asynchronously when the default is the opposite, issue <command>SET
23382352
LOCAL synchronous_commit TO OFF</> within the transaction.
23392353
</para>
2354+
2355+
<para>
2356+
<xref linkend="synchronous-commit-matrix"> summarizes the
2357+
capabilities of the <varname>synchronous_commit</varname> settings.
2358+
</para>
2359+
2360+
<table id="synchronous-commit-matrix">
2361+
<title>synchronous_commit Modes</title>
2362+
<tgroup cols="5">
2363+
<colspec colname="col1" colwidth="1.1*">
2364+
<colspec colname="col2" colwidth="1*">
2365+
<colspec colname="col3" colwidth="1*">
2366+
<colspec colname="col4" colwidth="1*">
2367+
<colspec colname="col5" colwidth="1*">
2368+
<thead>
2369+
<row>
2370+
<entry>synchronous_commit setting</entry>
2371+
<entry>local durable commit</entry>
2372+
<entry>standby durable commit after PG crash</entry>
2373+
<entry>standby durable commit after OS crash</entry>
2374+
<entry>standby query consistency</entry>
2375+
</row>
2376+
</thead>
2377+
2378+
<tbody>
2379+
2380+
<row>
2381+
<entry>remote_apply</entry>
2382+
<entry align="center">&bull;</entry>
2383+
<entry align="center">&bull;</entry>
2384+
<entry align="center">&bull;</entry>
2385+
<entry align="center">&bull;</entry>
2386+
</row>
2387+
2388+
<row>
2389+
<entry>on</entry>
2390+
<entry align="center">&bull;</entry>
2391+
<entry align="center">&bull;</entry>
2392+
<entry align="center">&bull;</entry>
2393+
<entry align="center"></entry>
2394+
</row>
2395+
2396+
<row>
2397+
<entry>remote_write</entry>
2398+
<entry align="center">&bull;</entry>
2399+
<entry align="center">&bull;</entry>
2400+
<entry align="center"></entry>
2401+
<entry align="center"></entry>
2402+
</row>
2403+
2404+
<row>
2405+
<entry>local</entry>
2406+
<entry align="center">&bull;</entry>
2407+
<entry align="center"></entry>
2408+
<entry align="center"></entry>
2409+
<entry align="center"></entry>
2410+
</row>
2411+
2412+
<row>
2413+
<entry>off</entry>
2414+
<entry align="center"></entry>
2415+
<entry align="center"></entry>
2416+
<entry align="center"></entry>
2417+
<entry align="center"></entry>
2418+
</row>
2419+
2420+
</tbody>
2421+
</tgroup>
2422+
</table>
2423+
23402424
</listitem>
23412425
</varlistentry>
23422426

‎src/include/access/xact.h

Copy file name to clipboardExpand all lines: src/include/access/xact.h
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -61,8 +61,8 @@ typedef enum
6161
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and remote
6262
* write */
6363
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote flush */
64-
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local flush and remote
65-
* apply */
64+
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local and remote flush
65+
and remote apply */
6666
} SyncCommitLevel;
6767

6868
/* Define the default setting for synchronous_commit */

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.