This is an excit­ing year for Postgres. Large, enterprise-minded com­pa­nies have been work­ing on new fea­tures and get­ting them com­mit­ted to the core of Postgres.

One of those fea­tures avail­able for test­ing in the first 9.1 beta release is syn­chro­nous repli­ca­tion. In only a few hun­dred lines, the source code file that imple­ments this fea­ture is incred­i­bly light­weight. But there’s some sweet and sur­pris­ing func­tion­al­ity inside.

So, what exactly is syn­chro­nous replication?

The Postgres docs say this fea­ture “extends the stan­dard level of dura­bil­ity offered by a trans­ac­tion com­mit. This level of pro­tec­tion is referred to as 2-safe repli­ca­tion in com­puter sci­ence the­ory.” (Here’s a paper that explains 1-safety, 2-safety and group-safety, if you’re into that type of thing.)

What this means is that syn­chro­nous repli­ca­tion ensures that a data­base trans­ac­tion is writ­ten to a standby sys­tem before it’s con­sid­ered com­mit­ted. A trans­ac­tion is any­thing in a BEGIN/COMMIT block, includ­ing CREATE/DROP statements.

You can then treat the two Postgres sys­tems that are part­nered as an up-to-the-latest-commit-synchronized cluster.

The sys­tem includes a sim­ple queue that iden­ti­fies which standby data­bases are part­nered and pro­motes poten­tial part­ners to sync sta­tus if an exist­ing part­ner is unable to syn­chro­nize. If a higher syn­chro­niza­tion pri­or­ity sys­tem comes back online, and the changes can still be replayed, then the part­ner­ship will change again, back to the higher pri­or­ity system.

Only two sys­tems can be part­nered at any time. Other can­di­dates for syn­chro­niza­tion wait in ‘poten­tial’ status.

Under the hood, stream­ing repli­ca­tion is what ships data from data­base sys­tem to data­base sys­tem as changes occur. You can, of course, use stream­ing repli­ca­tion with­out syn­chro­nous com­mit. Changes will then be shipped between data­bases after data is com­mit­ted locally.

A few tricky bits

Configuring repli­ca­tion con­tin­ues to get sim­pler, but there may be a few speed bumps for those not famil­iar with Postgres.

The trick­i­est aspects include: the para­me­ters which reside in recovery.conf, the pg_hba.conf and user-level per­mis­sions that are needed, as well as some unex­plained depen­dan­cies on other postgresql.conf parameters.

With pre-packaged sys­tems, uti­liz­ing the postgres user requires the addi­tion of at least one pg_hba.conf con­fig­u­ra­tion line to enable access for the post­gres user to replication.

Another issue that tripped me up was the require­ment that max_connections be the same value between mas­ter and stand­bys. It didn’t bite me until I restarted the slave sys­tems, and they wouldn’t start up! You can restart a mas­ter sys­tem to change max_connections, and the slaves will just recon­nect to it when it comes back online.

Digging in

Parameters for con­fig­ur­ing syn­chro­nous repli­ca­tion on the mas­ter sys­tem are: synchronous_commit (on/off/local), and synchronous_standby_names. The synchronous_standby_names can be either ‘*’ or a list of names used to deter­mine which stand­bys can syn­chro­nize in order of priority.

On the standby sys­tem you may con­fig­ure the fol­low­ing fea­tures in recovery.conf:
primary_conninfo,standby_mode (on/off), trigger_file (trigger file that ends recovery mode), recovery_command. Only standby_mode and primary_conninfo are required.

Here’s a con­ninfo example:

primary_conninfo = ’host=localhost port=5432 user=sdeckelmann application_name=sync1’

Your repli­ca­tion user can be any user with supe­ruser priv­i­leges. Standby sys­tems will con­nect using the replication data­base, which is a reserved word in 9.0 and higher. Typically, you’ll need to update pg_hba.conf to allow con­nec­tions to the repli­ca­tion database.

You may have noticed that I set application_name in my con­ninfo string. This can be used to explic­itly con­trol which sys­tems are engaged in syn­chro­niza­tion (per synchronous_standby_names), and name each standby data­base sys­tem in pg_stat_replication mon­i­tor­ing output.

Because syn­chro­nous repli­ca­tion was built as an exten­sion to our WAL-based repli­ca­tion sys­tem, it can oper­ate seam­lessly in con­junc­tion with hot standby. On the standby sys­tem you may also add hot_standby (on/off) to postgresql.conf, enabling hot standby *and* syn­chro­nous repli­ca­tion at the same time.

This is most likely not an ideal con­fig­u­ra­tion for high per­for­mance, so please con­sider the per­for­mance impacts care­fully before enabling.

Once everything’s up and run­ning, you can view cur­rent sta­tus through the pg_stat_replication view:

  application_name |   state   | sent_location | replay_location | sync_priority | sync_state
------------------+-----------+---------------+-----------------+---------------+-------------
 sync2            | STREAMING | 0/202BBB8     | 0/202BBB8       |             1 | SYNC
 sync1            | STREAMING | 0/202BBB8     | 0/202BBB8       |             1 | POTENTIAL
(2 rows)

Here you can clearly see the sync_stat of async and sync, as well as the sync_priority of sys­tems which are capa­ble of receiv­ing syn­chro­nous commits.

If I take sync1 offline, we see this:

 application_name |    state       | sent_location  |  replay_location  | sync_priority | sync_state
---------------+----------+------------+--------------+-----------+----------
 sync2                  | STREAMING | 0/202BBB8     | 0/202BBB8        |             1        | SYNC
(1 row)

If instead, I con­fig­ure the mas­ter sys­tem to *only* per­form syn­chro­nous com­mits with sync1 (by con­fig­ur­ing: synchronous_standby_names = 'sync1'), then once all sys­tems are online and the mas­ter is restarted, we’ll see the following:

 application_name |   state        | sent_location | replay_location  | sync_priority | sync_state
---------------+----------+------------+-------------+-----------+----------
  sync2                 | STREAMING | 0/202BB08     | 0/202BB08       |                   0 | ASYNC
  sync1                 | STREAMING | 0/202BB08     | 0/202BB08       |                   1 | SYNC

Pretty slick! This level of report­ing is some­thing that could only be par­tially coaxed out of calls to pg_controldata not too long ago in the past.

Try it out yourself

As I men­tioned ear­lier, you can down­load 9.1 Beta 1 and try syn­chro­nous repli­ca­tion out today. We depend on beta test­ing to find cor­ner cases and work out the kinks. So, your feed­back is welcome.

I’m in Ottawa meet­ing with devel­op­ers for PgCon, the yearly, inter­na­tional devel­op­ers con­fer­ence for the PostgreSQL Global Development Group. I’ll be giv­ing a talk about Emma’s jour­ney to terabyte-sized data­bases, lead­ing another excit­ing round of Lightning Talks, and facil­i­tat­ing our first ever Procedural Language Summit on Saturday.

If you’re at PgCon, there will be at least one talk demon­strat­ing sync rep, and the authors will be on hand for you to congratulate.

Also, come say hi to me and Mark Wong — we’re both here on behalf of Emma.