9 Quorum-based Synchronous Replication

PDF page 60

In PostgreSQL 9.5 and earlier, only one instance was available for synchronous replication. In PostgreSQL 9.6, synchronous replication can be performed on multiple instances. PostgreSQL 10 implements Quorum-based synchronous replication, which arbitrarily selects an instance to perform synchronous replication. The synchronous replication environment is set by the synchronous_standby_names configuration parameter as before.

Syntax 8 - Up to PostgreSQL 9.5

synchronous_standby_names = application_name, application_name, …

Syntax 9 - PostgreSQL 9.6

synchronous_standby_names
=
num_sync
(application_name,
application_name, …)

Syntax 10 - PostgreSQL 10

synchronous_standby_names = FIRST | ANY  num_sync (application_name,
application_name, …)

Specifying FIRST or omitting it will have the same behavior as PostgreSQL 9.6. Priorities are determined in the order described in the parameter application_name, and synchronous replication is performed for the number of instances specified by num_sync. If ANY is specified, it will not depend on the order of the instances specified in parameter application_name, and will determine the completion of synchronous replication when WAL is transferred to the slave instance specified by num_sync. If ANY is specified for the configuration parameter synchronous_standby_names, “quorum” is output in the sync_state column of the pg_stat_replication catalog.

PDF page 61

postgres=> SHOW synchronous_standby_names ;
        synchronous_standby_names
--------------------------------------
 any 2 (standby1, standby2, standby3)
(1 row)
postgres=> SELECT application_name,sync_state, sync_priority
FROM pg_stat_replication ;
 application_name | sync_state | sync_priority
------------------+------------+---------------
 standby1         | quorum     |             1
 standby2         | quorum     |             1
 standby3         | quorum     |             1
(3 rows)