PostgreSQL 9.6 在可靠性方面再出殺手锏.

通過流復制功能增強,提供多種可靠性模式可供用戶根據需求進行選擇,在可靠性和性能方面用戶可以自由發揮.

最強模式滿足金融級的可靠性要求.如何做到的呢?

PG允許多個同步流復制standby節點,用戶在事務提交時,必要等待多個同步的standby apply xlog,從而保證數據的多副本一致性.

具體的增強如下

.1. 事務提交掩護級別增強如下

支持5個事務提交掩護級別,確保事務提交時,XLOG的幾種狀態.

synchronous_commit =

on, remote_apply, remote_write, local, off

on 表示當地事務產生的xlog已flush到磁盤,同時sync standby(s)的xlog也已flush到磁盤.

remote_apply, 表示當地事務產生的xlog已flush到磁盤,同時sync standby(s)的xlog已回放.

remote_write, 表示當地事務產生的xlog已flush到磁盤,同時sync standby(s)的xlog 已write到OS dirty page.

local, 表示當地事務產生的xlog已flush到磁盤.

off, 表現

.2. 同步流復制掩護級別增強

支持設置同步節點數,例如用戶有4個standby,包括主節點共5個副本.

用戶要求3副本一致,則num_sync設置為2即可,確保至少有2個standby與主節點一致.

synchronous_standby_names參數配置的兩種寫法：

num_sync為同步standby節點數, 以及standby name.

num_sync ( standby_name [, ...] )

未設置掩護的standby節點數, 則默認為1個同步standby.

standby_name [, ...]

http://www.postgresql.org/docs/9.6/static/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES

synchronous_standby_names (string)

Specifies a list of standby servers that can support synchronous replication, as described in Section 25.2.8. There will be one or more active synchronous standbys; transactions waiting for commit will be allowed to proceed after these standby servers confirm receipt of their data. The synchronous standbys will be those whose names appear earlier in this list, and that are both currently connected and streaming data in real-time (as shown by a state of streaming in the pg_stat_replication view). Other standby servers appearing later in this list represent potential synchronous standbys. If any of the current synchronous standbys disconnects for whatever reason, it will be replaced immediately with the next-highest-priority standby. Specifying more than one standby name can allow very high availability.

This parameter specifies a list of standby servers using either of the following syntaxes:

num_sync ( standby_name [, ...] )

standby_name [, ...]

where num_sync is the number of synchronous standbys that transactions need to wait for replies from, and standby_name is the name of a standby server. For example, a setting of 3 (s1, s2, s3, s4) makes transaction commits wait until their WAL records are received by three higher-priority standbys chosen from standby servers s1, s2, s3 and s4.

The second syntax was used before PostgreSQL version 9.6 and is still supported. It's the same as the first syntax with num_sync equal to 1. For example, 1 (s1, s2) and s1, s2 have the same meaning: either s1 or s2 is chosen as a synchronous standby.

The name of a standby server for this purpose is the application_name setting of the standby, as set in the primary_conninfo of the standby's WAL receiver. There is no mechanism to enforce uniqueness. In case of duplicates one of the matching standbys will be considered as higher priority, though exactly which one is indeterminate. The special entry * matches any application_name, including the default application name of walreceiver.

Note: Each standby_name should have the form of a valid SQL identifier, unless it is *. You can use double-quoting if necessary. But note that standby_names are compared to standby application names case-insensitively, whether double-quoted or not.

If no synchronous standby names are specified here, then synchronous replication is not enabled and transaction commits will not wait for replication. This is the default configuration. Even when synchronous replication is enabled, individual transactions can be configured not to wait for replication by setting the synchronous_commit parameter to local or off.

This parameter can only be set in the postgresql.conf file or on the server command line.

http://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-WAL-LEVEL

synchronous_commit (enum)

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 delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see Section 29.3.

If synchronous_standby_names 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 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 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 necessarily reached stable 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 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 commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction.

更多技術深度內容,請存眷云棲社區微信公眾號：yunqiinsight.