Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Replication synchronous_commit Options

Allowed values up to Postgres version 9.5 were – „on“, „remote_write“, „local“ and „off“ and now with 9.6 „remote_apply“ will join the party. Short descriptions of all of these in plain-text could be something like that of below. NB! Latter 3 values are effective only in synchronous streaming replication mode, and fall back to „on“ value when no replicas have been listed in the „synchronous_standby_names“ parameter.

* on – Transaction commit always waits until the data is really flushed to the transaction log (aka WAL or XLOG) making sure the transaction is really persisted. In synchronous streaming replication mode also the replica needs to do the same.
* off – Commit can be acknowledged to the calling client before the transaction is actually flushed to the transaction log, making it possible to lose some recent (<1s by default) allegedly-committed transactions in case of a server crash. Synchronous replication is ignored. This is one of the most known Postgres performance tweaks.
* local –  Forces the „on“ behavior (guaranteed data flush) only on the master node. Usually used as user set session parameter for speeding up non-critical data imports on master for example.
* remote_write – Provides a weaker guarantee than „on“, transaction counts as commited if master does a guaranteed flush and the replica just gets a write confirmation from the operating system (prone to replica corruption if replica machine crashes before hitting storage).
* remote_apply – Provides the strongest replica consistency – commit doesn’t return before replica flushes and also applies the data. Clients could either query the master or the slave, they would have exactly the same view on the data.


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL