PostgreSQL 9.6 Synchronous multi-replica and Remote_apply Transaction synchronization level field analysis

Background

For financial-level scenarios, 2 replicas are usually not enough, and users may need multiple replicas.

For example, a master 4 from, requires in addition to the master, also requires 2 synchronized copies, others can be asynchronous copy.

On the other hand, when we use a database, read-write separation is a more common usage in order to expand the ability to read.

9.6 Previous versions, synchronous replication is to ensure that Xlog has been replicated to the standby, rather than already in the repository, although the apply is usually quick and may be done at the millisecond level, but there was no previous apply-level synchronization mechanism.

For example, user A to the User B's account remitted a sum of money, the same time User B in different places immediately to see a remittance over the money, this off-site, heterogeneous library of Read and write separation scene, you need the main standby transaction level of strict consistency, so 9.6 of the remote_apply is very effective.

There is also a scenario in which a backup is required to quickly activate the same state as the Sing Woo Master, and a remote_apply level of replication is required.

The above is PostgreSQL 9.6 enhancements in streaming replication.

Synchronous multi-Copy configuration method

The parameters are configured as follows

Synchronous_standby_names (String)

Support two ways of writing

Num_sync (Standby_name [, ...])

Standby_name [, ...]
Num_sync number that represents the number of replicas synchronized.

No write Num_sync format compatible with previous configuration, representing 1 replicas.

Standby_name is the application_name of the standby configuration in recovery.conf and can replace all standby with *.

Example

2 (S1, S2, S3, S4)
Indicates that 2 replicas are synchronous standby and others are asynchronous standby.

When things are committed, make sure that the Xlog is synchronized to any 2 replicas (the first two are sync nodes, and the next 2 are alternates, and that if any of the preceding two nodes go wrong, the latter will be replaced later).

Manual description

Specifies a list of standby servers that can support synchronous replication, as described in section 26.2.8.

There would be one or more active synchronous standbys;

Transactions waiting for commit is allowed to proceed after this standby servers confirm of receipt of.

The synchronous standbys is those whose names appear earlier in this list, and which are both currently connected and Streaming data in real-time

(as shown by a, streaming in the pg_stat_replication view).

The other standby servers appearing later in this list represent potential synchronous.

If any of the current synchronous standbys disconnects for whatever reason, it would be replaced immediately with the next- Highest-priority standby.

Specifying more than one standby name can allow very high availability.

This parameter specifies a list of standby servers using either of the following syntaxes:

Num_sync (Standby_name [, ...])

Standby_name [, ...]

Where Num_sync is the number of synchronous standbys this transactions need to wait for replies from, and Standby_name is The name of a standby server.

For example, a setting of 3 (S1, S2, S3, S4) makes transaction commits wait until their WAL records are received by three Higher-priority standbys chosen from standby servers, S1, S2 and S3.

The second syntax was used before PostgreSQL version 9.6 and is still supported.

It ' s the same as the ' the ' the ' the ' syntax with Num_sync equal to 1.

For example, 1 (S1, S2) and S1, S2 have the same meaning:

Either S1 or S2 is chosen as a synchronous standby.

The name of a standby server for this purpose are the Application_name setting of the standby, as set in the Primary_connin Fo the standby ' s WAL receiver.

There is no mechanism to enforce uniqueness.

In case of duplicates one of the matching standbys'll be considered as higher priority, though exactly which one is Inde Terminate.

The Special entry * matches any application_name, including the default application name of Walreceiver.

Note:each Standby_name should have the form of a valid SQL identifier, unless it is *.

can use double-quoting if necessary.

But Note this standby_names are compared to standby application names case-insensitively, whether double-quoted or not.

If No synchronous standby names are specified here, then synchronous replication are not enabled and transaction WI ll not wait for replication.

This is the default configuration.

Even when synchronous the replication is enabled, the individual transactions can be configured does not have a for replication by set Ting the Synchronous_commit parameter to or off.
Note that Standby_name only specifies the standby node for synchronization, and the transaction level also has a switch to Synchronous_commit, configured Synchronous_commit to control the synchronization level of the transaction, can be asynchronous, can also be synchronized.

For high reliability requirements, you can configure for synchronization, or you can configure asynchronous for performance.

Transaction Synchronization Level Description

The synchronization level of the transaction is configured through the parameter Synchronous_commit (enum).

If you want to configure a transaction or session-level parameter, use the following syntax

SET [Session | Local] Configuration_parameter {to | =} {value | ' Value ' | DEFAULT}
That is, users can set different levels of synchronization for different transactions depending on the actual business requirements.

The synchronization levels currently supported are as follows

When a transaction commits or rolls back, a commit/rollback redo record with a transaction end is generated, expressed in LSN in the Redo address system.

On
When a transaction commits or rollback, it waits for its redo to be primary, and synchronization standby (s) is persisted (>= its LSN).

Remote_apply,
When a transaction commits or rollback, it waits for its redo to be primary, and synchronization standby (s) is persisted (>= its LSN). And its redo has been apply (>= its LSN) at the synchronous standby (s).

Remote_write,
When a transaction commits or rollback, it waits for its redo to be persisted in primary; Its redo has called the Write interface (written to the OS, but has not yet invoked a persistent interface such as Fsync) (>= its LSN) in sync standby (s).

Local
When a transaction commits or rollback, it waits for its redo to be persisted in primary;

Off
When a transaction commits or rollback, it waits for its redo to be written to Wal buffer in primary and does not need to wait for its persistence;
Manual description

Synchronous_commit (enum)

Specifies whether transaction commit would wait for WAL records to is written to disk before the command returns a "success "Indication to the client.

Valid values are on, remote_apply, Remote_write, local, and off. The default, and safe, setting is on.

When off, there can be a delay between when success are reported to the client and then the transaction is really D to be safe against a server crash.

(The maximum delay is three times wal_writer_delay.)

Unlike Fsync, setting this parameter to off does not create any risk of database inconsistency:

An operating system or database crash might result in some recent allegedly-committed transactions being, lost the DAT Abase state'll be just the same as if those transactions had been aborted.

So, turning synchronous_commit out can be a useful alternative when performance are more important than exact certainty ABO UT the durability of a transaction.

For more discussion the section 30.3.

If Synchronous_standby_names is non-empty, this parameter also controls whether or not transaction commits would wait for T Heir WAL records to is replicated to the standby server (s).

When set to on, commits'll wait until replies from the current synchronous standby (s) indicate they have received the CO Mmit the transaction and flushed it to disk.

This ensures the transaction won't is lost unless both the primary and all synchronous standbys suffer corruption of th EIR database storage.

When set to Remote_apply, commits'll wait until replies from the current synchronous standby (s) indicate they have recei Ved the commit record of the transaction and applied it,

So this it has become visible to queries on the standby (s).

When set to Remote_write, commits'll wait until replies from the current synchronous standby (s) indicate they have recei Ved the commit record of the "transaction" and written it out to their operating system.

This setting was sufficient to ensure data preservation even if a standby instance of PostgreSQL were to crash,

But not if the standby suffers a operating-system-level crash, since the data has not necessarily reached stable storage On the standby.

Finally, the setting local causes commits to a for local flush to disk, and but not for replication.

This isn't usually desirable when synchronous replication are in use, the but is provided for completeness.

If Synchronous_standby_names is empty, the settings in, Remote_apply, Remote_write and local all provide the same Synchron ization level:

Transaction commits only wait for the local flush to disk.

This parameter can is changed at any time; The behavior for any one of the transaction is determined from the setting in effect when it commits.

It is therefore possible, and useful, to have some transactions commit synchronously and others.

For example, to make a single multistatement transaction commits asynchronously when the opposite, issue SET Local synchronous_commit to off within the transaction.
Remote_apply Transaction Synchronization Level configuration method

The synchronization level of the transaction is configured through the parameter Synchronous_commit (enum).

If you want to configure a transaction or session-level parameter, use the following syntax

SET [Session | Local] Configuration_parameter {to | =} {value | ' Value ' | DEFAULT}
Summary

1. Reliability

Combined with the Synchronous_standby_names and synchronous_commit settings, a very flexible transaction-level reliability requirement can be achieved.

2. Read-write consistency guarantee of reading and writing separation

When Synchronous_commit=remote_apply, the redo of this transaction must be in sync standby (s) already apply before the end of the client transaction is returned.

This kind of transaction, the read request that initiates after the transaction completes, in primary and the sync standby can be able to read and write completely consistent.

At the expense of the RT of the write transaction, get the full consistency of the read transaction at the multiple debit point.

3. Handoff delay

When you set the Synchronous_commit=remote_apply transaction synchronization replication level, you can reduce the switching latency.

When the standby is activated, it does not need to apply redo and can be quickly activated as the main library.