synchronous_commit Modes

Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPwXn-0001Cf-3d for pgsql-docs@arkaria.postgresql.org; Tue, 06 Oct 2020 23:38:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kPwXl-00006C-Nh for pgsql-docs@arkaria.postgresql.org; Tue, 06 Oct 2020 23:38:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPwXl-00005z-Gl for pgsql-docs@lists.postgresql.org; Tue, 06 Oct 2020 23:38:53 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPwXi-0004nd-QC for pgsql-docs@lists.postgresql.org; Tue, 06 Oct 2020 23:38:53 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1kPwXg-0000Ke-BE; Tue, 06 Oct 2020 19:38:48 -0400 Date: Tue, 6 Oct 2020 19:38:48 -0400 From: Bruce Momjian To: Kasper Kondzielski Cc: pgsql-docs@lists.postgresql.org, jaroslaw.kijanowski@softwaremill.com Subject: Re: Request for further clarification on synchronous_commit Message-ID: <20201006233848.GE12277@momjian.us> References: <159741195522.14321.13812604195366728976@wrigleys.postgresql.org> <20200817174727.GA28474@momjian.us> <20200818145851.GB27231@momjian.us> <20200818171740.GE27231@momjian.us> <20200821201539.GA13363@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="JP+T4n/bALQSJXh8" Content-Disposition: inline In-Reply-To: <20200821201539.GA13363@momjian.us> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --JP+T4n/bALQSJXh8 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Fri, Aug 21, 2020 at 04:15:39PM -0400, Bruce Momjian wrote: > How is this for a table? > > -- local -- ------------------- standbys ------------------ > durable query durable commit durable commit > commit consistency after OS crash after PG crash > remote_apply X X X X > on X X X > remote_write X X > local X > off I have created the attached doc patch which I plan to apply to all supported versions of Postgres. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee --JP+T4n/bALQSJXh8 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="sync.diff" diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ee914740cc..2768c85a96 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2703,14 +2703,26 @@ include_dir 'conf.d' - Specifies whether transaction commit will wait for WAL records - to be written to disk before the command returns a success - indication to the client. Valid values are on, - remote_apply, remote_write, local, - and off. The default, and safe, setting - is on. When off, there can be a delay between - when success is reported to the client and when the transaction is - really guaranteed to be safe against a server crash. (The maximum + Specifies how much WAL processing must complete before + the database server returns a success + indication to the client. Valid values are + remote_apply, on + (the default), remote_write, + local, and off. + + + + If synchronous_standby_names is empty, + the only meaningful settings are on and + off; remote_apply, + remote_write and local + all provide the same local synchronization level + as on. The local behavior of all + non-off modes is to wait for local flush of WAL + to disk. In off mode, there is no waiting, + so there can be a delay between when success is reported to the + client and when the transaction is later guaranteed to be safe + against a server crash. (The maximum delay is three times .) Unlike , setting this parameter to off does not create any risk of database inconsistency: an operating @@ -2722,38 +2734,40 @@ include_dir 'conf.d' exact certainty about the durability of a transaction. For more discussion see . + - If is non-empty, this - parameter also controls whether or not transaction commits will wait - for their WAL records to be replicated to the standby server(s). - When set to on, commits will wait until replies + If is non-empty, + synchronous_commit also controls whether + transaction commits will wait for their WAL records to be + processed on the standby server(s). + + + + When set to remote_apply, 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 waits for WAL replay. When set to on, + commits 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 + 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 remote_apply, 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 remote_write, 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 - PostgreSQL were to crash, but not if the standby - suffers an operating-system-level crash, since the data has not + received the commit record of the transaction and written it to + their file systems. This setting ensures data preservation if a standby instance of + PostgreSQL crashes, but not if the standby + suffers an operating-system-level crash because the data has not necessarily reached durable storage on the standby. - Finally, the setting local causes commits to wait for - local flush to disk, but not for replication. This is not usually + The setting local 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. - - If synchronous_standby_names is empty, the settings - on, remote_apply, remote_write - and local all provide the same synchronization level: - transaction commits only wait for local flush to disk. - + This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it @@ -2763,6 +2777,76 @@ include_dir 'conf.d' asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction. + + + summarizes the + capabilities of the synchronous_commit settings. + + + + synchronous_commit Modes + + + + + + + + + synchronous_commit setting + local durable commit + standby durable commit after PG crash + standby durable commit after OS crash + standby query consistency + + + + + + + remote_apply + • + • + • + • + + + + on + • + • + • + + + + + remote_write + • + • + + + + + + local + • + + + + + + + off + + + + + + + + +

+ diff --git a/src/include/access/xact.h b/src/include/access/xact.h index df1b43a932..f44b4c4eea 100644 --- a/src/include/access/xact.h +++ b/src/include/access/xact.h @@ -72,7 +72,7 @@ typedef enum 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 */ + SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local and remote flush and remote apply */ } SyncCommitLevel; /* Define the default setting for synchronous_commit */ --JP+T4n/bALQSJXh8--