Stackgres handles the proper settings in the Postgres and Patroni configuration files. It offers some options to set different replication modes, as follows:
This section describes the involved steps and concepts of the async replication mode.
This section describes the involved steps and concepts of the sync replication mode.
This section describes the involved steps and concepts under the Strict Sync option.
This section describes the involved steps and concepts of the sync-all replication mode.
This section describes the involved steps and concepts of the strict sync all replication mode.
This section describes the involved steps and concepts of the replication groups and roles.
Let’s dive into each of these options.
When choosing a replication mode, consider the trade-offs between data durability and availability:
When in asynchronous mode, the cluster is allowed to lose some committed transactions. When the primary server fails or becomes unavailable, a sufficiently healthy standby will automatically be promoted to primary. Any transactions that have not been replicated to that standby remain in a “forked timeline” on the primary and are effectively unrecoverable (the data is still there, but recovering it requires manual effort by data recovery specialists).
Note: The amount of data loss is proportional to the replication delay at the time of failover.
An SGCluster can be configured to use synchronous replication, allowing it to confirm that all changes made by a transaction have been transferred to one or more synchronous standby servers.
When in synchronous mode, a standby will not be promoted unless it is certain that the standby contains all transactions that may have returned a successful commit status to clients (clients can change the behavior per transaction using PostgreSQL’s synchronous_commit setting). This means the system may be unavailable for writes even though some servers are available.
Important characteristics:
When it is absolutely necessary to guarantee that each write is stored durably on at least two nodes, use strict synchronous mode. This mode prevents synchronous replication from being switched off on the primary when no synchronous standby candidates are available.
Trade-off: The primary will not be available for writes (unless the Postgres transaction explicitly turns off synchronous_mode), blocking all client write requests until at least one synchronous replica comes up.
Warning: Because of the way synchronous replication is implemented in PostgreSQL, it is still possible to lose transactions even when using strict synchronous mode. If the PostgreSQL backend is cancelled while waiting to acknowledge replication (due to client timeout or backend failure), transaction changes become visible to other backends before being replicated.