Cluster Sync on Update

This document describes how the cluster sync on update operation works and how to disable this feature.

Description

a9s PostgreSQL clusters replicate asynchronously. When the primary node stops for an update, a standby node is promoted and the remaining standby node follows it. With asynchronous replication, the promoted standby node can start its new timeline at a point behind the remaining standby node (LSN inversion). The remaining standby node can then not follow the new primary until the updated node rejoins the cluster, which causes a downtime proportional to the amount of data stored in the cluster.

To mitigate this, a9s PostgreSQL makes the cluster temporarily synchronous while the primary node is being updated:

1. Before the primary node stops for an update, and before the Switchover runs, all nodes are configured with synchronous_commit = 'remote_apply' and synchronous_standby_names = 'FIRST 1 (*)'. Every committed transaction is then applied on at least one standby node before the commit returns.
2. The operation only keeps these settings once a standby node reports that it is streaming as a synchronous standby node. If no standby node does so in time, the settings are reverted on all nodes so writes are never blocked, and the update continues as before.
3. When the updated node comes back, its pre-start reverts the settings on the other nodes before the node is cloned from the new primary node, and the cluster replicates asynchronously again.
4. Every node that starts resets its own settings, and a node that starts as the primary node also resets the other nodes. A standby node that starts first resets the other nodes before it writes to the primary node.
5. After the deployment finishes, each node resets its own settings as a safety net.

The reverts always run. A node without these settings is left as it is.

The operation is best effort. It is skipped, without failing the update, when the cluster has fewer than three nodes, when the node being updated is not the primary node, when the local PostgreSQL is not available, or when the cluster is not healthy. Each step and each reason to skip is logged.

Cluster Sync on Update's Requirements

• The cluster has at least three nodes.
• A healthy cluster has all its nodes running and following the same primary.
• Every standby node is streaming from the primary node, with a replication lag no bigger than the Switchover's maximum replication lag (1MB by default).

Configuration

Parameter	Type	Default Value	Description
postgresql-info-webservice.cluster_sync_on_update.enable	Boolean	true	Enables the cluster sync on update operation.
postgresql-info-webservice.cluster_sync_on_update.sync_standby_timeout	Duration	30s	How long to wait for a standby node to report it is streaming as a synchronous standby node before reverting the settings.
postgresql-info-webservice.cluster_sync_on_update.timeout	Duration	1m	Timeout of the clients used by the operation.

Disable The Feature

The a9s PostgreSQL cluster sync on update feature is enabled by default on all a9s PostgreSQL cluster Service Instances. At the Platform Operator's discretion, it can be disabled platform wide, for all Service Instances, by setting the postgresql-info-webservice.cluster_sync_on_update.enable property to false in your a9s PostgreSQL Data Service manifest as a Template Custom Ops Files (Inline), as shown below.

# postgresql-service.yml
...
- name: templates-uploader
  jobs:
  - name: template-uploader
    ...
    properties:
      template-uploader:
        template-custom-ops: |
          - type: replace
            path: /instance_groups/name=pg/jobs/name=postgresql-ha/properties/postgresql-info-webservice/cluster_sync_on_update?/enable?
            value: false
...

Disabling the feature does not disable the reverts. A cluster left synchronous by an earlier update is still reverted, including by the deployment that disables the feature.

info

The new value takes effect on the update after the one that deploys it: the stopping node runs the configuration that was deployed before the update started.

Known Issues

• Write latency increases while the cluster is synchronous, as every commit waits for one standby node to apply it.

• Commits wait while no standby node is streaming from the primary node: until the remaining standby node follows a newly promoted primary node, if it cannot follow it, or if it is lost while the updated node is still down. In all these cases the cluster is not followed by the majority of its nodes, so external connections to the primary node are already blocked. Connections that are allowed while the primary node is blocked will see their commits wait instead of failing.

• The cluster stays synchronous from the pre-stop of the primary node until its pre-start, which includes the time BOSH takes to update or recreate its VM. During that time, every commit on the new primary node waits until the remaining standby node has applied it, so anything that delays replay on that standby node also delays writes: a backup running on it, a long query that conflicts with replay, or a standby node that falls behind under load. Unlike the cases above, the standby node is still streaming, so external connections are not blocked and their commits wait. A query that conflicts with replay pauses it for at most max_standby_streaming_delay (30s by default), after which PostgreSQL cancels the query, failing the backup if it was one.

• When an update decreases a property synchronized by the Sync Property Update (for example max_connections) and the updated node was the primary node, the standby nodes are restarted while the cluster is synchronous. Writes stall for the duration of the restart of the only running standby node.

• A zero or negative timeout or sync_standby_timeout falls back to its default (1m and 30s). Any other failure of cluster sync on update, a configuration error included, is only logged and never fails the pre-stop of the primary node: the update continues with the cluster asynchronous, as without this feature.

• If the updated node never reaches its pre-start, for example because its VM cannot be created, the cluster stays synchronous until another node starts or a deployment finishes successfully. The same applies after downgrading to a release without this feature once a primary node has made the cluster synchronous. To revert it manually, run the following on every node:

  source /var/vcap/jobs/postgresql-ha/helpers/vars.sh
  chpst -u postgres:vcap psql postgres

  ALTER SYSTEM RESET synchronous_commit;
  ALTER SYSTEM RESET synchronous_standby_names;
  SELECT pg_reload_conf();

• Each revert of the other nodes waits up to postgresql-info-webservice.cluster_sync_on_update.timeout for a node whose VM does not answer, so a missing VM slows down the pre-start and the start of the other nodes.

• Synchronous replication settings set manually with ALTER SYSTEM are reset whenever a node starts. Settings from the deployment manifest are not affected.

• The feature also applies to a9s-pg, which runs three postgresql-ha nodes from the a9s PostgreSQL 15 release, once a9s-pg is updated to a release that includes it. This is intended: a9s-pg updates are the case the feature primarily protects.