While asynchronous replication is sufficient in 90 percent of cases, many people ask for synchronous replication. The idea behind synchronous replication is that a transaction is only valid if it has been accepted by at least two servers. Sounds easy? It is! To configure PostgreSQL for synchronous replication, only two settings are necessary.

The first thing to configure is the slave side. All that has to be done is to add an application_name field to primary_conninfo in recovery.conf.

A configuration setting might look like this:

primary_conninfo = 'host=master.server.com user=postgres 
  port=5432 application_name=some_name'

The slave will now register itself on the master as some_name. The master will now check its synchronous_standby_names field. The first entry matching in synchronous_standby_names will be considered synchronous; the rest will be considered asynchronous.

So, in short, here's what you have to do:

However, synchronous replication comes with two issues. The first issue is obvious; performance of short transactions will drop because network latency can be a problem.

The second issue is related to availability. The golden rule is that synchronous replication should never be done with only two servers. Remember that PostgreSQL ensures that a transaction is considered to be safe only if it has been approved by at least two servers. If you got two servers and one is lost, PostgreSQL cannot live up to its promise. The result is that PostgreSQL will not accept writes anymore until a second server is provided, which can take the data and make the system happy!

It is highly recommended to use at least three servers that are redundantly connected to each other.