Home Explore Blog CI



postgresql

18th chunk of `doc/src/sgml/high-availability.sgml`
1efa37696684910f1b04b8e9d3dc6692ec7be008a1a3f7ef0000000100000fa1
 shutdown is requested.  However, as
    when using asynchronous replication, the server will not fully
    shutdown until all outstanding WAL records are transferred to the currently
    connected standby servers.
   </para>

   </sect3>

   <sect3 id="synchronous-replication-multiple-standbys">
    <title>Multiple Synchronous Standbys</title>

   <para>
    Synchronous replication supports one or more synchronous standby servers;
    transactions will wait until all the standby servers which are considered
    as synchronous confirm receipt of their data. The number of synchronous
    standbys that transactions must wait for replies from is specified in
    <varname>synchronous_standby_names</varname>. This parameter also specifies
    a list of standby names and the method (<literal>FIRST</literal> and
    <literal>ANY</literal>) to choose synchronous standbys from the listed ones.
   </para>
   <para>
    The method <literal>FIRST</literal> specifies a priority-based synchronous
    replication and makes transaction commits wait until their WAL records are
    replicated to the requested number of synchronous standbys chosen based on
    their priorities. The standbys whose names appear earlier in the list are
    given higher priority and will be considered as synchronous. Other standby
    servers appearing later in this list represent potential synchronous
    standbys. If any of the current synchronous standbys disconnects for
    whatever reason, it will be replaced immediately with the
    next-highest-priority standby.
   </para>
   <para>
    An example of <varname>synchronous_standby_names</varname> for
    a priority-based multiple synchronous standbys is:
<programlisting>
synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
</programlisting>
    In this example, if four standby servers <literal>s1</literal>, <literal>s2</literal>,
    <literal>s3</literal> and <literal>s4</literal> are running, the two standbys
    <literal>s1</literal> and <literal>s2</literal> will be chosen as synchronous standbys
    because their names appear early in the list of standby names.
    <literal>s3</literal> is a potential synchronous standby and will take over
    the role of synchronous standby when either of <literal>s1</literal> or
    <literal>s2</literal> fails. <literal>s4</literal> is an asynchronous standby since
    its name is not in the list.
   </para>
   <para>
    The method <literal>ANY</literal> specifies a quorum-based synchronous
    replication and makes transaction commits wait until their WAL records
    are replicated to <emphasis>at least</emphasis> the requested number of
    synchronous standbys in the list.
   </para>
   <para>
    An example of <varname>synchronous_standby_names</varname> for
    a quorum-based multiple synchronous standbys is:
<programlisting>
synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
</programlisting>
    In this example, if four standby servers <literal>s1</literal>, <literal>s2</literal>,
    <literal>s3</literal> and <literal>s4</literal> are running, transaction commits will
    wait for replies from at least any two standbys of <literal>s1</literal>,
    <literal>s2</literal> and <literal>s3</literal>. <literal>s4</literal> is an asynchronous
    standby since its name is not in the list.
   </para>
   <para>
    The synchronous states of standby servers can be viewed using
    the <structname>pg_stat_replication</structname> view.
   </para>
   </sect3>

   <sect3 id="synchronous-replication-performance">
    <title>Planning for Performance</title>

   <para>
    Synchronous replication usually requires carefully planned and placed
    standby servers to ensure applications perform acceptably. Waiting
    doesn't utilize system resources, but transaction locks continue to be
    held until the transfer is confirmed. As a result, incautious use of
    synchronous replication will reduce performance for database
    applications because of increased response times and higher

Title: Synchronous Replication with Multiple Standbys and Performance Planning
Summary
This section describes how synchronous replication works with multiple standbys, including priority-based and quorum-based methods using the synchronous_standby_names parameter, and how to plan for performance when using synchronous replication, considering the impact of waiting for confirmation on system resources and transaction locks, and the need for careful placement of standby servers to ensure acceptable application performance.