So far, asynchronous replication has been covered in reasonable detail. However, asynchronous replication means that a commit on the slave is allowed to happen after the commit on the master. If a master crashes, data that has not made it to the slave yet might be lost even if replication is occurring.

Synchronous replication is here to solve the problem—if PostgreSQL replicates synchronously, a commit has to be flushed to disk by at least one replica in order to go through on the master. Therefore, synchronous replication basically reduces the odds of data loss substantially.

In PostgreSQL, configuring synchronous replication is easy. Only two things have to be done:

• Adjust the synchronous_standby_names setting in the postgresql.conf file on the master
• Add an application_name setting to the primary_conninfo parameter in the recovery.conf file in the replica

Let's get started with the postgresql.conf file on the master:

synchronous_standby_names = '' 
         # standby servers that provide sync rep 
         # number of sync standbys and comma-separated 
         # list of application_name 
         # from standby(s); '*' = all 

If we put in '*', all nodes will be considered synchronous candidates. However, in real-life scenarios, it is more likely that only a couple of nodes will be listed. Here is an example:

synchronous_standby_names = 'slave1, slave2, slave3'

Now, we have to change the recovery.conf file and add application_name:

primary_conninfo = '... application_name=slave2'

The replica will now connect to the master as slave2. The master will check its configuration and figure out that slave2 is the first one in the list that makes a viable slave. PostgreSQL will, therefore, ensure that a commit on the master will only be successful if the slave confirms that the transaction is there.

Let's now assume that slave2 goes down for some reason: PostgreSQL will try to turn one of the other two nodes into a synchronous standby. The problem is this: what if there is no other server?

In this case, PostgreSQL will wait on commit forever if a transaction is supposed to be synchronous. Yes, this is true. PostgreSQL will not continue to commit unless there are at least two viable nodes available. Remember, we have asked PostgreSQL to store data on at least two nodes—if we cannot provide enough hosts at any given point in time, it is our fault. In reality, this means that synchronous replication is best achieved with at least three nodes—one master and two slaves—as there is always a chance that one host is lost.

Talking about host failures, there is an important thing to note at this point—if a synchronous partner dies while a commit is going in, PostgreSQL will wait for it to return. Alternatively, the synchronous commit can happen with some other potential synchronous partner. The end user might not even notice that the synchronous partners change.

In some cases, storing data on just two nodes might not be enough: maybe we want to improve safety even more and store data on even more nodes. To achieve that, we can make use of the following syntax in PostgreSQL 9.6 or higher:

synchronous_standby_names = 
   '4(slave1, slave2, slave3, slave4, slave5, slave6)' 

In this case, data is supposed to end up on four out of six nodes before the commit is confirmed by the master.

Of course, this comes with a price tag—keep in mind that speed will go down if we add more and more synchronous replicas. There is no such thing as a free lunch. PostgreSQL provides a couple of ways to keep the performance overhead under control, which we'll discuss in the following section.

In PostgreSQL 10.0, even more functionality has been added:

[FIRST] num_sync ( standby_name [, ...] )
ANY num_sync ( standby_name [, ...] )
standby_name [, ...]

The ANY and FIRST keywords have been introduced. FIRST allows you to set the priorities of your servers, while ANY gives PostgreSQL a bit more flexibility when it commits a synchronous transaction.