When adding your second standby node with default settings, you get the
following architecture:
pg_auto_failover architecture with two standby nodes
In this case, three nodes get set up with the same characteristics, achieving
HA for both the Postgres service and the production dataset. An important
setting for this architecture is number_sync_standbys.
The replication setting number_sync_standbys sets how many standby nodes
the primary should wait for when committing a transaction. In order to have
a good availability in your system, pg_auto_failover requires
number_sync_standbys+1 standby nodes participating in the replication
quorum: this allows any standby node to fail without impact on the system’s
ability to respect the replication quorum.
When only two nodes are registered in a group on the monitor we have a
primary and a single secondary node. Then number_sync_standbys can only
be set to zero. When adding a second standby node to a pg_auto_failover
group, then the monitor automatically increments number_sync_standbys to
one, as we see in the diagram above.
When number_sync_standbys is set to zero then pg_auto_failover
implements the Business Continuity setup as seen in
Architecture Basics: synchronous replication is then used as a way to
guarantee that failover can be implemented without data loss.
In more details:
With number_sync_standbys set to one, this architecture always
maintains two copies of the dataset: one on the current primary node
(node A in the previous diagram), and one on the standby that
acknowledges the transaction first (either node B or node C in the
diagram).
When one of the standby nodes is unavailable, the second copy of the
dataset can still be maintained thanks to the remaining standby.
When both the standby nodes are unavailable, then it’s no longer
possible to guarantee the replication quorum, and thus writes on the
primary are blocked. The Postgres primary node waits until at least one
standby node acknowledges the transactions locally committed, thus
degrading your Postgres service to read-only.
The sequence below shows how a commit is confirmed and how a healthy
failover completes when all quorum standbys are available:
It is possible to manually set number_sync_standbys to zero when
having registered two standby nodes to the monitor, overriding the
default behavior.
In that case, when the second standby node becomes unhealthy at the same
time as the first standby node, the primary node is assigned the state
Wait_primary. In that state, synchronous replication is disabled
on the primary by setting synchronous_standby_names to an empty
string. Writes are allowed on the primary, even though there’s no extra
copy of the production dataset available at this time.
Setting number_sync_standbys to zero allows data to be written even
when both standby nodes are down. In this case, a single copy of the
production data set is kept and, if the primary was then to fail, some
data will be lost. How much depends on your backup and recovery
mechanisms.
The sequence below shows the stuck-election scenario that can arise with any
number_sync_standbys>=1 setting when the primary and one quorum standby
fail at the same time, and how --allow-data-loss unblocks it:
Note
Failover when the primary and a quorum standby fail simultaneously.
If the primary and one quorum standby are lost at the same time, the
surviving standby is assigned report_lsn but will not be promoted
automatically. The monitor cannot know whether the missing standby
acknowledged the last synchronous commit, and promoting the survivor
could silently discard those transactions.
Once you have confirmed the missing node is permanently lost and you accept
the potential data loss, unblock the election with:
This parameter is used by Postgres in the synchronous_standby_names
parameter: number_sync_standby is the number of synchronous standbys for
whose replies transactions must wait.
This parameter can be set at the formation level in pg_auto_failover, meaning
that it applies to the current primary, and “follows” a failover to apply to
any new primary that might replace the current one.
To set this parameter to the value <n>, use the following command:
pg_autoctlsetformationnumber-sync-standbys<n>
The default value in pg_auto_failover is zero. When set to zero, the
Postgres parameter synchronous_standby_names can be set to either
'*' or to '':
synchronous_standby_names='*' means that any standby may
participate in the replication quorum for transactions with
synchronous_commit set to on or higher values.
pg_autofailover uses synchronous_standby_names='*' when there’s at
least one standby that is known to be healthy.
synchronous_standby_names='' (empty string) disables synchrous
commit and makes all your commits asynchronous, meaning that transaction
commits will not wait for replication. In other words, a single copy of
your production data is maintained when synchronous_standby_names is
set that way.
pg_autofailover uses synchronous_standby_names='' only when
number_sync_standbys is set to zero and there’s no standby node known
healthy by the monitor.
In order to set number_sync_standbys to a non-zero value,
pg_auto_failover requires that at least number_sync_standbys+1 standby
nodes be registered in the system.
When the first standby node is added to the pg_auto_failover monitor, the
only acceptable value for number_sync_standbys is zero. When a second
standby is added that participates in the replication quorum, then
number_sync_standbys is automatically set to one.
The command pg_autoctlsetformationnumber-sync-standbys can be used to
change the value of this parameter in a formation, even when all the nodes
are already running in production. The pg_auto_failover monitor then sets a
transition for the primary to update its local value of
synchronous_standby_names.
The replication quorum setting is a boolean and defaults to true, and can
be set per-node. Pg_auto_failover includes a given node in
synchronous_standby_names only when the replication quorum parameter has
been set to true. This means that asynchronous replication will be used for
nodes where replication-quorum is set to false.
It is possible to force asynchronous replication globally by setting
replication quorum to false on all the nodes in a formation. Remember that
failovers will happen, and thus to set your replication settings on the
current primary node too when needed: it is going to be a standby later.
To set this parameter to either true or false, use one of the following
commands:
The candidate priority setting is an integer that can be set to any value
between 0 (zero) and 100 (one hundred). The default value is 50. When the
pg_auto_failover monitor decides to orchestrate a failover, it uses each
node’s candidate priority to pick the new primary node.
When setting the candidate priority of a node down to zero, this node will
never be selected to be promoted as the new primary when a failover is
orchestrated by the monitor. The monitor will instead wait until another
node registered is healthy and in a position to be promoted.
To set this parameter to the value <n>, use the following command:
pg_autoctlsetnodecandidate-priority<n>
When nodes have the same candidate priority, the monitor then picks the
standby with the most advanced LSN position published to the monitor. When
more than one node has published the same LSN position, a random one is
chosen.
When the candidate for failover has not published the most advanced LSN
position in the WAL, pg_auto_failover orchestrates an intermediate step in the
failover mechanism. The candidate fetches the missing WAL bytes from one of the
standby with the most advanced LSN position prior to being promoted. Postgres
allows this operation thanks to cascading replication: any standby can be the
upstream node for another standby.
It is required at all times that at least two nodes have a non-zero candidate
priority in any pg_auto_failover formation. Otherwise no failover is possible.
The command pg_autoctlgetformationsettings (also known as
pg_autoctlshowsettings) can be used to obtain a summary of all the
replication settings currently in effect in a formation. Still using the
first diagram on this page, we get the following summary:
We can see that the number_sync_standbys has been used to compute the
current value of the synchronous_standby_names setting on the primary.
Because all the nodes in that example have the same default candidate
priority (50), then pg_auto_failover is using the form ANY1 with the
list of standby nodes that are currently participating in the replication
quorum.
The entries in the synchronous_standby_names list are meant to match the
application_name connection setting used in the primary_conninfo, and
the format used by pg_auto_failover there is the format string
“pgautofailover_standby_%d” where %d is replaced by the node id. This
allows keeping the same connection string to the primary when the node name
is changed (using the command pg_autoctlsetmetadata--name).
Here we can see the node id of each registered Postgres node with the
following command:
$ pg_autoctl show state
Name | Node | Host:Port | LSN | Reachable | Current State | Assigned State
-------+-------+----------------+-----------+-----------+---------------------+--------------------
node_A | 1 | localhost:5001 | 0/7002310 | yes | primary | primary
node_B | 2 | localhost:5002 | 0/7002310 | yes | secondary | secondary
node_C | 3 | localhost:5003 | 0/7002310 | yes | secondary | secondary
When setting pg_auto_failover with per formation number_sync_standby and
then per node replication quorum and candidate priority replication
settings, those properties are then used to compute the
synchronous_standby_names value on the primary node. This value is
automatically maintained on the primary by pg_auto_failover, and is updated
either when replication settings are changed or when a failover happens.
The other situation when the pg_auto_failover replication settings are used
is a candidate election when a failover happens and there is more than two
nodes registered in a group. Then the node with the highest candidate
priority is selected, as detailed above in the Candidate Priority
section.
When setting the three parameters above, it’s possible to design very
different Postgres architectures for your production needs.
pg_auto_failover architecture with three standby nodes
In this case, the system is set up with three standby nodes all set the same
way, with default parameters. The default parameters support setting
number_sync_standbys=2. This means that Postgres will maintain three
copies of the production data set at all times.
On the other hand, if two standby nodes were to fail at the same time,
despite the fact that two copies of the data are still maintained, the
Postgres service would be degraded to read-only.
With this architecture diagram, here’s the summary that we obtain:
Sample architecture with three standby nodes, one async
pg_auto_failover architecture with three standby nodes, one async
In this case, the system is set up with two standby nodes participating in the
replication quorum, allowing for number_sync_standbys=1. The system
always maintains at least two copies of the data set, one on the primary,
another on either node B or node C. Whenever we lose one of those nodes, we can
hold to the guarantee of having two copies of the data set.
Additionally, we have the standby server D which has been set up to not
participate in the replication quorum. Node D will not be found in the
synchronous_standby_names list of nodes. Also, node D is set up to
never be a candidate for failover, with candidate-priority=0.
This architecture would fit a situation with nodes A, B, and C are deployed
in the same data center or availability zone and node D in another one.
Those three nodes are set up to support the main production traffic and
implement high availability of both the Postgres service and the data set.
Node D might be set up for Business Continuity in case the first data center
is lost, or maybe for reporting needs on another application domain.
With this architecture diagram, here’s the summary that we obtain:
