Synchronous replication! Yay!

Posted May 2, 2011 22:17 UTC (Mon) by Cyberax (✭ supporter ✭, #52523)
Parent article: New features in PostgreSQL 9.1

Synchronous replication! Yay! At last!!!

I think I'm in love with PostgreSQL. I can finally ditch the last our DB2 servers.

Synchronous replication! Yay!

Posted May 5, 2011 17:27 UTC (Thu) by intgr (subscriber, #39733) [Link] (2 responses)

Not sure what kind of synchronous replication you're looking for, but note that PostgreSQL 9.1's synchronous replication only guarantess durability of replicated data, not query consistency between master and slaves.

In simpler terms, it guarantees that no committed data will be lost when the master crashes, but slave nodes can still return stale data after rows are modified/deleted on the master. Replication lag still exists (though to a lesser degree).

Synchronous replication! Yay!

Posted May 5, 2011 20:25 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link] (1 responses)

Ok, I'm confused.

What happens when I commit a transaction? I understand that by the time COMMIT completes, the data is replicated to all the slaves. Right?

Synchronous replication! Yay!

Posted May 6, 2011 7:30 UTC (Fri) by intgr (subscriber, #39733) [Link]

Yes, by the end of COMMIT, the WAL data from the transaction is replicated to the slaves, but changes from the WAL haven't been applied to slave databases yet -- so not visible to queries. Applying/recovering the WAL on slaves still takes place asynchronously.

In the event of a failover/promotion, PostgreSQL applies all outstanding WAL changes, so no data is lost.