Redo with write-ahead logging (WAL) is the redo log of the database. Before committing a transaction, make sure that the redo logs corresponding to the transaction modifications have persisted. However, why not persist the modified data directly, but redo the log? The main reason is that the transaction-based modifications to the database — including additions, data modifications, and indexed data blocks — are usually discrete. When the database was designed, the medium for storing data was a hard disk drive (HDD), so the performance of discrete write operations was inefficient. To solve this problem, the redo log is designed with sequential I/O. In addition, the redo log is also more convenient for replicating modifications to a standby database.
The "D" in the ACID indicates the durability of the redo log data corresponding to the modifications of the database at the end of the transaction. Accordingly, redo logs are critical. Commercial databases support writing multiple redo logs. For example, by creating a redo log group, Oracle supports:
Add the mirrored groups: alter database add logfile group 1 ('/oracle/SAL/origlogB/log_g11m1.dbf', '/oracle/SAL/mirrlogB/log_g11m2.dbf') size 100 M; alter database add logfile group 2 ('/oracle/SAL/origlogA/log_g12m1.dbf', '/oracle/SAL/mirrlogA/log_g12m2.dbf') size 100 M;
Currently, PostgreSQL does not support mirror redo logs, but there are some alternatives:
1) Use multiple block devices and a file system similar to Zettabyte file system (ZFS) to build a mirror.
2) Use pg_receivewal with synchronous replication.
This article introduces the second method that stores multiple WALs by combining the synchronous replication of PostgreSQL with pg_receivewal.
[FIRST] num_sync ( standby_name [, ...] ) ANY num_sync ( standby_name [, ...] ) standby_name [, ...]
standby1: recovery.conf > 'conn info -> application_name=s1' standby2: recovery.conf > 'conn info -> application_name=s2' standby3: recovery.conf > 'conn info -> application_name=s3' standby4: recovery.conf > 'conn info -> application_name=s4' primary: synchronous_standby_names : FIRST 3 (s1, s2, s3, s4) 表示前三个为sync standby，后面的都是potential standby，前面不足3个时，接下来的potential standby自动升级为sync standby。 ANY 3 (s1, s2, s3, s4) 表示任意3个都可以为sync standby。
Receive redo logs information from the upstream in real time through the stream replication protocol.
For example, a local WAL and a remote WAL must be written simultaneously. The configuration is as follows:
synchronous_standby_names='ANY 1 (s1, s2)' synchronous_commit=remote_write
Two different remote pg_receivewal are configured at the same time. However, when the transaction is submitted, redo logs must be locally persisted according to the preceding configuration, and at least one receiver should receive redo logs at the same time.
When the local WAL fails, select the one that receives the most from the two receiver targets and configure it using the following method to achieve zero loss.
restore_command = 'cp /walarchive/%f %p || cp /walarchive/%f.partial %p' .partial表示这个wal文件还没有写完，只有部分内容。如果是归档，没有写完的wal是不会归档的，而wal receiver可以实时接收wal，做到0丢失。
pg_receivewal achieves WAL mirror by receiving a client for WAL based on PostgreSQL synchronous replication. By doing so, transactions will not be lost even if the local WALs are corrupted.
digoal - September 1, 2021
digoal - April 26, 2021
Alibaba Clouder - August 26, 2021
ApsaraDB - September 19, 2022
Alibaba Clouder - December 2, 2016
Alibaba Clouder - November 14, 2017
Fully managed and less trouble database servicesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
Leverage cloud-native database solutions dedicated for FinTech.Learn More
More Posts by digoal