PostgreSQL 9.6 攻克金融级多副本可靠性问题

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. Its 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 standbys 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.

PostgreSQL 双节点流复制如何同时保证可用性、可靠性(rpo,rto) - （半同步，自动降级方法实践） PostgreSQL , 同步 , 半同步 , 流复制 两节点HA架构，如何做到跨机房RPO=0(可靠性维度)？同时RTO可控(可用性维度)？ 半同步是一个不错的选择。 1、当只挂掉一个节点时，可以保证RPO=0。如下： 主 - 从（挂） 主（挂） - 从 2、当一个节点挂掉后，在另一个节点恢复并开启同步模式前，如果在此期间（
PostgreSQL 12 preview - 可靠性提升 - data_sync_retry 消除os层write back failed status不可靠的问题 PostgreSQL , data_sync_retry , write back , retry , failed status 有些OS系统，对fsync的二次调用不敏感，因为OS层可能有自己的CACHE，如果使用了buffer write，并且出现write back failed的情况，有些OS可能在下次fsync时并不能正确的反馈fsync的可靠性与否。（因为这个B
WAL是自动被启用的。除了做一些设置满足存放WAL日志的磁盘空间需求以及一些必要的调节以外（参阅第 30.4 节），对管理员没有什么其他要求。 当每个新记录被写入时，WAL记录被追加到WAL日志中。
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 30 章 可靠性和预写式日志_30.4. WAL配置 30.4. WAL配置 有几个WAL相关的配置参数会影响数据库性能。本节将解释它们的使用。关于服务器配置参数的设置的一般信息请参考第 19 章。 检查点是在事务序列中的点，这种点保证被更新的堆和索引数据文件的所有信息在该检查点之前已被写入。
异步提交是一个允许事务能更快完成的选项，代价是在数据库崩溃时最近的事务会丢失。在很多应用中这是一个可接受的交换。 如前一节所述，事务提交通常是同步的：服务器等到事务的WAL记录被刷写到持久存储之后才向客户端返回成功指示。
预写式日志（WAL）是保证数据完整性的一种标准方法。对其详尽的描述几乎可以在所有（如果不是全部）有关事务处理的书中找到。简单来说，WAL的中心概念是数据文件（存储着表和索引）的修改必须在这些动作被日志记录之后才被写入，即在描述这些改变的日志记录被刷到持久存储以后。
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 30 章 可靠性和预写式日志_30.1. 可靠性 30.1. 可靠性 可靠性是任何严肃的数据库系统的重要属性，PostgreSQL尽一切可能来保证可靠的操作。可靠的操作的一个方面是，被一个提交事务记录的所有数据应该被存储在一个非易失的区域， 这样就不会因为失去电力、操作系统失败以及硬件失败（当然，除了非易失区域自身失效之外）等原因导致的数据丢失。