【PostgreSQL】synchronous_commit参数和Synchronous Standby Replication

下图显示了PostgreSQL的WAL在primary端生成以及发送到standby的过程。

​

PostgreSQL内部使用pg_pwrite()函数写WAL段，其实这个函数内部是调用系统的write()函数，write()并不能保证数据被刷到磁盘。为了完成刷盘，会调用另一个函数issue_xlog_fsync()，该函数基于参数wal_sync_method的设置选择哪种方式进行fsync。比如：

postgres=# show wal_sync_method;
 wal_sync_method 
-----------------
 fdatasync
(1 row)

postgres=# 

　　

上面的图显示了WAL的五个阶段：

1.本地插入WAL记录

wal记录最先是在wal buffers中生成。因为多个backend进程会同时生成wal记录，需要通过锁来保护。不同的background进程会不断地将wal buffers中的内容写入wal segment。如果synchronous_commit是off的，刷盘的过程不会立即发生，而是根据参数wal_writer_delay的设置。

postgres=# show wal_writer_delay;
 wal_writer_delay 
------------------
 200ms
(1 row)

postgres=# 

2.本地写wal和刷盘

将wal刷到本地盘通常被认为是件繁重的操作。PostgreSQL做了一些优化，避免频繁地刷盘。

3.远程写wal

wal记录被写到standby，但是这个时候还没有刷盘。数据会在页缓存中保留一段时间。除非我们强调primary和standby同时奔溃掉，否则这段时间被认为是可以提供保护的。

4.远程刷盘

在这个阶段，在standby端真正的写数据，并刷盘。

5.远程apply

在这个阶段，wal记录在standby上被重放。

 

synchronous_commit可以设置的值：

1.off

可以用值off、0、false、no来关闭synchronous_commit。顾名思义，提交确认可以在将记录刷新到磁盘之前进行。这通常称为异步提交。如果 PostgreSQL实例崩溃，最后几个异步提交可能会丢失。

2.local

wal记录被写入到本地磁盘。在本地wal写入和刷盘后，才会提交确认。

3.remote_write

wal记录被写到远程节点后，才会返回ack，这里是写入远程节点，但是没有将wal刷盘。

4.on

这是默认值，可以使用on、true、yes、1来表示。但是根据有没有standby，含义会有不同。如果有synchronous standby，primary会等待，直到远程standby已经刷盘。

5.remote_apply

接收并应用，standby上的查询已经可以看到数据。

 

如果没有synchronous standbys(synchronous_standby_names为空)，synchronous_commit设置成on、remote_apply、remote_write、local提供的是相同的同步级别：事务提交只是等待本地刷盘。

 

有人问，如果synchronous_commit = off，会有多少数据丢失？

这个问题的答案稍微有点复杂，取决于wal_write_delay的设置。默认是200ms。这就意味着每隔wal_write_delay之后，wal会被刷盘。wal写进程会间歇性醒来，调用XLogBackgroundFlush()函数。

这将检查完全填充的WAL页。如果有，它会将所有缓冲区写入。所以在良好的负载条件下，WAL writer会写入整个缓冲区。在未找到完整页面的低负载条件下，将刷新直到最后一次异步提交的所有内容。

从上一次刷盘，如果超过了wal_writer_delay，或者超过了wal_write_flush_after，wal会被刷盘到当前位置。这样可以确保事务完成，最多是两次wal_writer_delay就会被刷盘。

但是，PostgreSQL以灵活的方式写入/刷新完整缓冲区，这是为了减少在每个 WAL写入周期填充多个WAL页时在高负载下发出的写入次数。从概念上讲，这使得最坏情况延迟最多三个wal_writer_delay周期。

因此，问题的回答是：大多数情况下，丢失的数据会少于两个wal_writer_delay,最差情况下，也就是三个wal_writer_delay。

 

sychronous_commit的设置范围

可以在全局级别设置sychronous_commit。此外也可以在其它级别设置：

1.在每个事务级别

SET LOCAL synchronous_commit = 'remote_write';

注意这里的关键字，local。在事务结束或回滚后，设置会恢复成会话级别的默认设置。

2.会话级别

SET synchronous_commit = 'remote_write';

可以在建立连接的时候，设置：

"host=hostname user=postgres ... options='-c synchronous_commit=off'

3.用户级别

ALTER USER trans_user SET synchronous_commit= ON;

ALTER USER report_user SET synchronous_commit=OFF;

4.数据库级别

ALTER DATABASE reporting SET synchronous_commit=OFF;

5.实例级别​

ALTER SYSTEM SET synchronous_commit=OFF;

SHOW synchronous_commit;