Replication in Managed Service for PostgreSQL

Managed Service for PostgreSQL clusters use quorum-based synchronous replication:

1. Master host is selected from among cluster hosts, while all other hosts become replicas.

2. A transaction is considered successful only if it is confirmed by the master host and the quorum of replicas. The quorum is the half of all replicas in a cluster. Replicas for the quorum are selected randomly (quorum replicas).

   If there is an odd number of replicas, the value is rounded down, except when there is only one replica. For example, in a cluster with 17 replicas, the quorum requires a minimum of eight replicas, while in a single-replica cluster, the quorum is one.

The number of quorum replicas is updated every time the number of available cluster hosts changes, e.g., when hosts are added or deleted as well as during scheduled or unscheduled maintenance. When you add a host, it is first synchronized with the master and only then can become part of the quorum.

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

Managing replicationManaging replication

To ensure data integrity, the cluster supports automatic replication management, where each replica host receives a replication stream from the master host. If you need to, you can specify the replication source manually.

In a cluster, you can combine automatic and manual management of replication streams.

Automatic management of replication streamsAutomatic management of replication streams

Once you create a PostgreSQL cluster with multiple hosts, it contains one master host and replicas. Replicas use the master host as a replication source.

Specifics of automatic replication in Managed Service for PostgreSQL:

• When the master host fails, its most recent replica becomes the new master.
• When the master changes, the replication source for all replica hosts automatically switches to the new master host.

You can disable autofailover by changing additional cluster settings. If the current master host fails, you will have to run the selection of a new master or assign this role to one of the replicas manually.

Manual management of replication streamsManual management of replication streams

When you manage replication streams manually, a cluster host other than the master will serve as the replication source for a replica.

This way, in a PostgreSQL cluster with complex topology, you can configure cascading replication during which some of the replicas use other cluster hosts as the replication stream source. The replication stream for such source hosts can be managed both automatically, using the Managed Service for PostgreSQL tools, and manually.

Write sync and read consistencyWrite sync and read consistency

Synchronization of data writes in PostgreSQL is ensured by the synchronous_commit parameter that manages syncing the write-ahead log (WAL). The default value is synchronous_commit = on. In this case, a transaction is commited only if the WAL is written to both the master disk and each quorum replica disk.

Depending on the number of replicas in the cluster, the following behavior scenarios are possible:

• In a cluster with one replica, this replica will constitute the quorum, and manual replication stream management is unavailable. If the replica fails, write transactions will await confirmation until it is restored in the cluster.
• In a cluster with two replicas, a transaction is committed if the WAL is written to the disk of the quorum replica. If it fails, the quorum will consists of the remaining replica unless it has a replication source specified. In this case, the results of queries to the master host will not change.
• In a cluster with an odd number of replicas N > 1, the quorum consists of N-1 / 2 replicas. For other replicas, you can set a replication source manually.
• In a cluster with an even number of replicas N > 2, the quorum consists of N / 2 replicas. For other replicas, you can set a replication source manually.

To ensure ongoing consistency of data reads between the master and quorum replica, set synchronous_commit = remote_apply in the cluster settings. With this parameter value, a data write is not considered successful until the quorum replica applies the changes from the WAL. In this case, the read operation performed on the master and quorum replica returns the same result.

The downside is that the write operations to the cluster will take longer. If the master and the quorum replica are located in different availability zones, the transaction confirmation latency cannot be less than the round-trip time (RTT) between data centers. This will degrade cluster performance if writing data to a single thread with the AUTOCOMMIT mode on.

To enhance performance, write data to multiple threads whenever possible, disable AUTOCOMMIT, and group queries within a transaction.

Logical decodingLogical decoding

Managed Service for PostgreSQL clusters support logical decoding to stream DB changes to external services. For instance, it may be used as a way to change data capture (CDC).

Information about DB changes is sent to a replication slot in WAL format, where it is decoded to a format readable by an external service using an output plugin.

Managed Service for PostgreSQL supports the following WAL plugins:

• test_decoding: Converts WAL data into text.
• wal2json: Converts WAL data into JSON format.
• pgoutput: Transforms data read from WAL to the logical replication protocol.

Replication slots can be created by users with the mdb_replication role.

Use casesUse cases

• Migrating a database from a third-party PostgreSQL cluster to Managed Service for PostgreSQL
• Migrating data from Yandex Managed Service for MySQL® to Managed Service for PostgreSQL using Yandex Data Transfer
• Migrating data from Managed Service for PostgreSQL to Yandex Managed Service for MySQL® using Yandex Data Transfer
• Migrating databases from Managed Service for PostgreSQL
• Asynchronously replicating data from PostgreSQL to ClickHouse®