How to Use pg_receivewal with Synchronous Replication to Mirror Redo Logs

By digoal

Background

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.

1) Synchronous Replication

[FIRST] num_sync ( standby_name [, ...] )  
ANY num_sync ( standby_name [, ...] )  
standby_name [, ...]

Configuration example:

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。

2) Receive WAL in Real Time

pg_receivewal [option...]

Receive redo logs information from the upstream in real time through the stream replication protocol.

3) Configure Multiple WALs

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丢失。

Summary

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.

Community

How to Use pg_receivewal with Synchronous Replication to Mirror Redo Logs

Background

1) Synchronous Replication

2) Receive WAL in Real Time

3) Configure Multiple WALs

Summary

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution