The most important thing you have to know about synchronous replication is that it is simply expensive. Do you remember our first chapter about the CAP theory, about the speed of light, and so on? Synchronous replication and its downsides are one of the core reasons why we have decided to include all this background information in this book. It is essential to understand the physical limitations of synchronous replication, otherwise you might end up in deep trouble.

When setting up synchronous replication, try to keep the following things in mind:

The application_name plays an important role in a synchronous setup. In a typical application, people use the application_name parameter for debugging purposes. It can help to track bugs, identify what an application is doing, and so on:

test=# SHOW application_name;
application_name
------------------
psql
(1 row)

test=# SET application_name TO 'whatever';
SET
test=# SHOW application_name;
application_name
------------------
 whatever
(1 row)

As you can see, it is possible to set the application_name parameter freely. The setting is valid for the session we are in, and will be gone as soon as we disconnect. The question now is: What does application_name have to do with synchronous replication?

Well, the story goes like this: If a slave connects to the master through streaming, it can optionally send an application_name as part of the primary_conninfo setting. If this application_name happens to be part of the first entry in synchronous_standby_names, the slave will be a synchronous one.

With all this information in mind, we can move forward and configure our first synchronous replication.

To show you how synchronous replication works, this chapter will include a full, working example, outlining all the relevant configuration parameters.

A couple of changes have to be made to the master. The following settings will be needed in postgresql.conf on the master:

wal_level = hot_standby
max_wal_senders = 5    # or any number
synchronous_standby_names = 'book_sample'
hot_standby = on  
# on the slave to make it readable

Then we have to adapt pg_hba.conf just as we have already seen it in the previous chapters. After that, the server can be restarted and the master is ready for action.

In the next step, we can perform a base backup just as we have done it before. We have to call pg_basebackup on the slave. Ideally, we include the transaction log already when doing the base backup (--xlog-method=stream). This allows us to fire things up quickly and without any greater risks.

We have recommended setting hot_standby on the master already; the config file will be replicated anyway, so you save yourself one trip to postgresql.conf to change this setting. Of course, this is not fine art, but an easy and pragmatic approach.

Once the base backup has been performed, we can move ahead and write a simple recovery.conf file suitable for synchronous replication:

iMac:slavehs$ cat recovery.conf
primary_conninfo = 'host=localhost
                    application_name=book_sample
port=5432'
standby_mode = on

The config file looks just like before. The only difference is that we have added the application_name to the scenery. Note, the application_name parameter must be identical to the synchronous_standby_names setting on the master.

Once we have finished writing recovery.conf, we can fire up the slave.

In our example, the slave is on the same server as the master. In this case, you have to ensure that those two instances will use different TCP ports, otherwise the instance started second will not be able to fire up. The port can be changed in postgresql.conf easily.

After those steps, the database instance can be started. The slave will check out its connection info and connect to the master. Once it has replayed all relevant transaction logs, it will be in the synchronous state; the master and the slave will hold exactly the same data from then on.

Now that we have started the database instance, we can connect to the system and see if things are working properly.

To check for replication, we can connect to the master and take a look at pg_stat_replication. For this check, we can connect to any database inside our (master) instance:

postgres=# x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 62871
usesysid         | 10
usename          | hs
application_name | book_sample
client_addr      | ::1
client_hostname  | 
client_port      | 59235
backend_start    | 2013-03-29 14:53:52.352741+01
state            | streaming
sent_location    | 0/30001E8
write_location   | 0/30001E8
flush_location   | 0/30001E8
replay_location  | 0/30001E8
sync_priority    | 1
sync_state       | sync

This system view will show exactly one line per slave attached to your master system.

You can see that the application_name parameter has been taken from the connect string passed to the master by the slave (which is in our example book_sample). As the application_name parameter matches the master's synchronous_standby_names setting, we have convinced the system to replicate synchronously; no transaction can be lost anymore because every transaction will end up on two servers instantly. The sync_state setting will tell you precisely how data is moving from the master to the slave.

At various points in this book we have already pointed out that synchronous replication is an expensive thing to do. Remember, we have to wait for a remote server and not just on the local system; the network between those two nodes is definitely not something that is going to speed things up. Writing to more than just one node is always more expensive than writing to just one node. Therefore, we definitely have to keep an eye on speed, otherwise you might face some pretty nasty surprises.

The main question you really have to ask yourself is: Do you really want to replicate all transactions synchronously? In many cases, you don't. To prove our point, let us imagine a typical scenario: A bank wants to store accounting related data as well as some logging data. We definitely don't want to lose a couple of millions just because a database node goes down. This kind of data might be worth the effort and we can replicate it synchronously. The logging data is quite different, however. It might be far too expensive to cope with the overhead of synchronous replication. So, we want to replicate this data in an asynchronous way to ensure maximum throughput.

How can we configure a system to handle important as well as not so important transactions nicely? The answer lies in a variable you have already seen earlier on in the book: The synchronous_commit variable.

In the default PostgreSQL configuration, synchronous_commit has been set to on. In this case, commits will wait until a reply from the current synchronous standby indicates it has received the commit record of the transaction and flushed it to the disk. In other words, both servers must report that the data has been written safely. Unless both servers crash at the same time, your data will survive potential problems (crashing both servers should be pretty unlikely).

Changing the way data is replicated on the fly is easy. In this chapter, we have already set up a full synchronous replication infrastructure by adjusting synchronous_standby_names (master) along with the application_name (slave) parameter. The good thing about PostgreSQL is that you can change your durability requirements on the fly:

test=# BEGIN;
BEGIN
test=# CREATE TABLE t_test (id int4);
CREATE TABLE
test=# SET synchronous_commit TO local;
SET
test=# x
Expanded display is on.
test=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 62871
usesysid         | 10
usename          | hs
application_name | book_sample
client_addr      | ::1
client_hostname  | 
client_port      | 59235
backend_start    | 2013-03-29 14:53:52.352741+01
state            | streaming
sent_location    | 0/3026258
write_location   | 0/3026258
flush_location   | 0/3026258
replay_location  | 0/3026258
sync_priority    | 1
sync_state       | sync

test=# COMMIT;
COMMIT

In this example, we have changed the durability requirements on the fly. It will make sure that this very specific transaction will not wait for the slave to flush to disk. Note, as you can see, the sync_state has not changed. Don't be fooled by what you see here; you can completely rely on the behavior outlined in this section. PostgreSQL is perfectly able to handle each transaction separately. This is a unique feature of this wonderful open source database; it puts you in control and lets you decide which kind of durability requirements are there.