Replication

Data in Managed Service for PostgreSQL clusters is replicated synchronously. A successful response to a write data request is returned only when the data is written to the disk on both the master host and the priority replica. In the other cluster replicas, data is replicated asynchronously.

Due to limited resources, the b1.nano, b1.micro, b2.nano, and b2.micro class hosts are not replicated.

For more information about how replication works in PostgreSQL, see the DBMS documentation.

Selecting the master and a synchronous replica

The master host and synchronous replica are selected by priority, which you can set for a specific host.

In addition, you can configure cascading replication by explicitly assigning a source of replication for each host. The hosts with the set replication source cannot:

• Become synchronous replicas.
• Participate in the selection of a new master host.
• Automatically switch to a new source of replication.

Write sync and read consistency

By default, the master and replica are kept in sync by syncing the Write-Ahead Log (WAL) (synchronous_commit = on). However, the WAL isn't applied to a synchronous replica immediately after being updated, so the synchronous replica might respond with outdated data during this lag.

If you want to ensure ongoing consistency of data reads between the master and synchronous replica, specify, in the cluster settings, the synchronous_commit = remote_write parameter. With this parameter value, a data write is not considered successful until the synchronous replica is ready to read the updated data. However, there is a disadvantage: write operations to the cluster will take longer.

For a detailed description of the synchronous_commit parameter, see the PostgreSQL documentation.