Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog HA Protection Configuration of ApsaraDB RDS for PostgreSQL – Maximum Protection, Availability, and Performance

HA Protection Configuration of ApsaraDB RDS for PostgreSQL – Maximum Protection, Availability, and Performance

The article explains the process of configuring the protection level of ApsaraDB RDS for PostgreSQL to ensure maximum availability and high performance.

By digoal

Background

Database primary-standby replication is one of the standard methods of open source databases to solve the problem of single point availability. For example, in ApsaraDB for RDS PostgreSQL dual-node architecture, you can create a standby node using PostgreSQL internal stream replication and switch to it when the primary node exhibits abnormal behavior.

The active-standby redo replication of the community version of PostgreSQL supports the following synchronization modes:

  • Remote_write: The standby asynchronously writes redo logs. Containing local redo logs is crucial in this mode after customers commit or rollback. At the same time, standby receives the redo logs and writes asynchronously.
  • On: The standby synchronously writes redo logs. Similar to remote_write, preserving local redo logs is crucial in this mode after any changes or rollback. Simultaneously, the standby receives and persists the redo logs.
  • Remote_apply: The standby applies to redo logs. Saving redo logs is crucial after commit or rollback by end-users in this mode. The standby receives and replays the redo logs concurrently.
  • Local: Maintaining local redo logs is vital after customers commit or roll back in this mode.
  • Off: The local redo logs must be written asynchronously after customers commit or roll back changes.
  • Synchronous_standby_names quorum based: This mode supports multiple standby nodes. A specified number of standby nodes perform the synchronization.

Parameters

#synchronous_commit = on                # synchronization level;  
                                        # off, local, remote_write, remote_apply, or on  
  
#synchronous_standby_names = '' # standby servers that provide sync rep  
                                # method to choose sync standbys, number of sync standbys,  
                                # and comma-separated list of application_name  
                                # from standby(s); '*' = all  
[FIRST] num_sync ( standby_name [, ...] )  
ANY num_sync ( standby_name [, ...] )  
standby_name [, ...]

To ensure zero loss and availability, a customer needs to select at least three nodes, or 2.5 nodes, with one node only receiving redo logs. In case of a particular node failure, there will be no data loss and availability issue with this setup.

However, the community version doesn’t support the semi-synchronization mode. Synchronization mode is useful when standby is normal, and asynchronization mode is beneficial when standby is abnormal or in jitter. This mode does not fully guarantee zero data loss, but the probability is high. Therefore, some customers prefer the two-node semi-synchronization mode after considering the costs and availability.

ApsaraDB RDS PG supports semi-synchronization by enhancing its kernel. The configuration method is relatively simple, with two parameters required as follows:

1) rds_sync_replication_timeout

The maximum time in milliseconds to wait for WAL synchronous replication. In the case of timeout, synchronous replication change to asynchronous until replication is caught up.

Modifiable scope:

[0-300000]

If the replication delays or standby exception occurs, 0 indicates waiting forever, equivalent to full synchronization in the community version. A value greater than 0 indicates the maximum wait time of commit or rollback. If the wait time exceeds this number, the mode degrades to asynchronous.

If a standby exception occurs in semi-synchronous mode and the wait time exceeds the set value, the mode degrades to asynchronous. When standby recovers and catches up, the mode changes to synchronous.

Adjust the protection level of the transaction with the following parameters:

2) synchronous_commit

It sets the current transaction's synchronization level.

Configurable range:

[off|remote_write|local]

The configuration settings are as follows:

  • remote_write: The standby asynchronously writes redo logs. Saving local redo logs is vital after commit or rollback by end-users. Concurrently, standby receives the redo logs and writes asynchronously.
  • local: Retaining local redo logs is essential in this mode following commit or rollback.
  • off: Local redo logs should be written asynchronously after commit or rollback.

Recommended configuration:

1) Testing and edge services with requirements on high performance and low data reliability.

synchronous_commit=off

2) Services with requirements on high performance and certain data reliability (a small amount of data loss is acceptable).

synchronous_commit=remote_write  
rds_sync_replication_timeout=500

3) Services that require the highest reliability to ensure zero loss even at the expense of availability.

synchronous_commit=remote_write  
rds_sync_replication_timeout=0
PostgreSQL Database HA Protection Primary-Standy Replication High Availability Database
0 0 0
Share on

Read previous post:

Solution to Ensure Availability and Reliability (rpo,rto) of PostgreSQL Two-node Stream Replication

Read next post:

PostgreSQL Multi-replica Wait Behavior, Transaction Snapshots, and Data Visibility

digoal

277 posts | 24 followers

You may also like

Comments

digoal

277 posts | 24 followers

Related Products

More Posts by digoal

See All