Streaming replication - Sync and Async, benefits of streaming replication over Log-shipping in Postgres

In my previous post here, I tried to explain how the log shipping works and its advantages. Let's see more about Streaming replication in Postgres.

It's possible to get the WAL records as soon as they are generated without waiting for the WAL Files to be filled. This reduces the lag in streaming replication. The slave connects to the master and gets the WAL records. But streaming replication implies a connection between the standby and the master server.

The master will send all the transaction to log entries directly to the standby.

By doing this standby it will have all the recent changes without waiting for the WAL Files to be archived, where streaming WAL records as they are generated. We are not dependent on the archive WAL Files to keep this standby by in sync.

Therefore, the benefits of streaming replication over Log-shipping is as follows.

 

-         The lag between the backup and the Master is smaller because the WAL entries are sent immediately after the transaction are finished on the master, without waiting for the WAL files to be archived

-      It is possible to set the replication without WAL archiving at all. Therefore, there is no need to set up network storage on another common location for the master and the standby to connect

-   Streaming replication is Async by default, This means that if a user commits a transaction on the master,  it gets the confirmation of the commit immediately and the application will only happen afterward. In case the Master crashes right after a commit and the WAL records were not sent, the data will be lost although the user has seen that the commit was successful,

When high availability is the requirement in the sense that no data loss is acceptable Streaming replication can be set to synchronous mode. After this is done, The master server will wait for the standby confirming that it has receive and process every WAL record before confirming their commit request. 

This will make the commands slightly slower of course. And, If the standby is disconnected all the transactions on the master will be blocked. It will not fail though, rather it will wait until the standby is connected again. Meantime, read-only queries will work normally.

 

The benefit of synchronous replication is that it is guaranteed that if a transaction is finished and the commit command returned, the data is replicated on the stand by the server.

The drawback is performance overhead and the dependency of the master on the standby.

Review a 2 Min Survey here 

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

All about Physical Replication and Log shipping in Postgres 

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping

"Perhaps the difference between what is miserable, and that, which is spectacular, lies in the leap of faith...  #selfmusing"         

- - Sushant Singh Rajput