Streaming replication in PostgreSQL is asynchronous by default. In order to avoid data loss upon failover, it is important to have synchronous streaming replication. A synchronous replication gives a success message to the client only when the change has been confirmed by at least one of the standby servers specified. This way, the change is never lost but it may come with a performance penalty. In this recipe, we shall look at how synchronous replication can be enabled.
Getting ready...
To avoid a huge performance overhead, you may have at least one standby sitting in the same data center as the master. This way, the change will be confirmed as received faster. We could also enable synchronous replication at the session level. It allows a user to configure which changes need to be synchronous and which don't.
It is important to set application_name appropriately on all the standby servers when setting up replication. For your reference...
