Shulou(Shulou.com)06/02 Report--

Note: some of the following are excerpts from the delayed replication of "PostgreSQL practice" PG.

Parameter: recovery_min_apply_delay

In some cases, a backup server will restore the WAL record from the primary server as soon as possible. It is useful to have a delayed copy of the data, which provides an opportunity to correct data loss errors. This parameter allows you to delay recovery for a fixed period of time, in milliseconds if no unit is specified. For example, if you set this parameter to 5min, for a transaction commit, the backup will replay the transaction only if the system clock on the standby exceeds the commit time reported by the primary server by at least 5 minutes.

It is possible that the replication delay between servers will exceed the value of this parameter, in which case the latency will not be increased. Note that the delay is calculated based on the timestamp on which the WAL is written on the primary server and the current time on the backup. Transmission delays due to network delays or cascaded replication configurations may significantly reduce the actual wait time. If the system clock on the primary server and the standby is out of sync, this can cause the application record to be restored earlier than expected. This is not a major problem, however, because the useful setting of this parameter is much greater than the deviation of typical events between servers.

The delay occurs only on the WAL record of the transaction commit. Other records will still be replayed as quickly as possible, which will not be a problem, because the MVCC visibility rules ensure that their effects will not be seen until the corresponding commit records are applied.

Once the database in recovery has reached a consistent state, a delay occurs until the backup is promoted or triggered. After that, the backup machine will end the recovery and no longer wait.

This parameter is intended to be used with a stream replication deployment, but if specified, it will be followed in all cases. Using this feature can also delay hot_standby_feedback, which can lead to the expansion of the primary server, so be careful when using both.

The construction of deferred repository is very simple, as long as you add a configuration item in recovery.conf.

Recovery_min_apply_delay = 1min # here I set a delay of 1 minute for the test.

# # default supported time units are ms, s, min, h, d, that is, milliseconds, minutes, hours and days

Note: after modification, the standby node needs to be restarted to take effect.

Then, create a table in the main library and insert a test data:

Postgres=# create table test_delay (id int4,create_time timestamp (0) without time zone)

Postgres=# insert into test_delay (id,create_time) values (1je now ())

Then, wait a minute or so to go to the delay standby node to see if the data is synchronized.

Influence of recovery_min_apply_delay parameters on synchronous replication in delayed replication scenario

In the case of synchronous replication, synchronous_commit is usually configured as on or remote_apply

On indicates that standby received the wal-> wrote to the wal log file-- and returned success to the client.

Standby indicates that standby receives the wal-> writes the wal log file-- > and applies it to standby-- before it returns success to the client.

The following tests are set for delayed replication under different parameters of synchronous_commit:

Scenario 1: synchronous_commit=on and recovery_min_apply_delay = 1min

Note:

Synchronous_commit is set in the postgresql.conf of the main library (session-level settings are supported, or the configuration file reload can be modified to take effect globally).

Recovery_min_apply_delay is set in the recovery.conf of standby.

In this scenario, we insert a piece of data into the main library, and the main database immediately returns the result of a successful or failure. Then we go to the delayed replication standby to query and find that we still need 1min to find this data.

In other words, when synchronous_commit is configured as on, it is consistent with asynchronous flow replication in the case of delayed repository provisioning.

Scenario 2: synchronous_commit=remote_apply and recovery_min_apply_delay = 1min

Note:

Synchronous_commit is set in the postgresql.conf of the main library (session-level settings are supported, or the configuration file reload can be modified to take effect globally).

Recovery_min_apply_delay is set in the recovery.conf of standby.

In this scenario, we insert a piece of data into the master database, and the master database will hang wait for the 1min (wait for the slave database to complete the apply operation) before returning the result of the successful or execution.

Then we went to the delayed replication standby to query and found that this data could be found immediately.

In other words, in the case of delayed provisioning, when synchronous_commit is configured as remote_apply, the commit of transactions above the main database will be blocked.

In a production environment where delayed slave libraries are used, be sure to avoid setting synchronous_commit=remote_apply (of course, it is rarely set to remote_apply from a performance point of view)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.