Synchronous Transfer

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Created page with "'''Synchronous Transfer''' provide that master make any file system level changes after confirming that the corresponding WAL record is replicated to the standby. Herewith use...")
 
Line 1: Line 1:
 
'''Synchronous Transfer''' provide that master make any file system level changes after confirming that the corresponding WAL record is replicated to the standby. Herewith user can fail-back master without fresh backup.
 
'''Synchronous Transfer''' provide that master make any file system level changes after confirming that the corresponding WAL record is replicated to the standby. Herewith user can fail-back master without fresh backup.
  
* [http://www.postgresql.org/message-id/CAF8Q-Gy7xa60HwXc0MKajjkWFEbFDWTG=gGyu1KmT+s2xcQ-bw@mail.gmail.com Discussion]
+
* [http://www.postgresql.org/message-id/CAF8Q-Gy7xa60HwXc0MKajjkWFEbFDWTG=gGyu1KmT+s2xcQ-bw@mail.gmail.com Here] is discussion about this.
  
 
=Usage=
 
=Usage=
 
==Users Overview==
 
==Users Overview==
 +
This parameter accept 'all', 'data_flush' and 'commit'. 'synchronous_commit' paramter controls whether master server waits for corresponsing WAL to be flushed to disk of standby before return COMMIT to client.
 +
*'''commit'''
 +
This value means that master wait sfor corresponding WAL to flushed to disk of standby server on commits.
 +
*'''data_flush'''
 +
This value means that master wait for replicated data page( e.g., CLOG, pg_control) before flushto disk of master server. but, master doesn't wait to for corresponding WAL to flushed to disk of standby server on commits.
 +
*'''all'''
 +
This value seems to combined 'commit'and 'data_flush'. that is, master waits for not only corresponding WAL to be flushded standby server but also all data page change to be fulushed standby server.
 +
 +
So if user sets 'all' or 'data_flush' to synchronous_transfer, user setup ''failback-safe'' standby. that is, when user fail back server, user doesn't need fresh backup.
 +
 +
In postgresql.conf, User set 'all' or 'data_flush' or 'commit' to synchronosu_transfer.
 +
 +
=Setting paramters=
 +
 +
If 2 servers connects to master server,
 +
*'''Synchronous server and also make failback safe standby server'''
 +
**synchronous_standby_names = <server name>
 +
**synchronous_transfer = all
 +
 +
*'''Asynchronous server and also make failback safe standby server'''
 +
**synchronous_transfer = data_flush
 +
 +
*'''Pure synchronous replication'''
 +
**synchronous_standby_names = <server name>
 +
**synchronous_transfer = commit
 +
 +
*'''Pure asynchronous replication'''
 +
**No setting anything
 +
 
==How to Use==
 
==How to Use==
  
 +
Until you build streaming replication, see [https://wiki.postgresql.org/index.php?title=Streaming_Replication&action=submit Streaming Replication]
 +
 +
after that,
 +
*'''1.''' Set up synchronous_transfer parameter to 'all'
 +
$EDITOER postgresql.conf
 +
synchronous_transfer = all
 +
 +
 +
 +
==Restrictions==
 +
Currenty this function, only standby server which highest priority is effective. for example, if there is 2 server connects to master server (one is SYNC, another one is ASYNC) and synchronous_transfer is set 'all'. Master server waits for only SYNC standby server because it is highest priority. even if synchronous_transfer is set 'all', there is not effective to server which is NOT highest priority.
 
=Impact for Performance=
 
=Impact for Performance=
  
 
=Todo=
 
=Todo=
 
+
*Currently this patch supports only one failback safe standby. It can either be synchronous or an asynchronous standby. We probably need to discuss whether it needs to be changed for support of multiple failsafe standby's.
  
 
[[Category:Replication]]
 
[[Category:Replication]]

Revision as of 12:31, 22 July 2013

Synchronous Transfer provide that master make any file system level changes after confirming that the corresponding WAL record is replicated to the standby. Herewith user can fail-back master without fresh backup.

  • Here is discussion about this.

Contents

Usage

Users Overview

This parameter accept 'all', 'data_flush' and 'commit'. 'synchronous_commit' paramter controls whether master server waits for corresponsing WAL to be flushed to disk of standby before return COMMIT to client.

  • commit

This value means that master wait sfor corresponding WAL to flushed to disk of standby server on commits.

  • data_flush

This value means that master wait for replicated data page( e.g., CLOG, pg_control) before flushto disk of master server. but, master doesn't wait to for corresponding WAL to flushed to disk of standby server on commits.

  • all

This value seems to combined 'commit'and 'data_flush'. that is, master waits for not only corresponding WAL to be flushded standby server but also all data page change to be fulushed standby server.

So if user sets 'all' or 'data_flush' to synchronous_transfer, user setup failback-safe standby. that is, when user fail back server, user doesn't need fresh backup.

In postgresql.conf, User set 'all' or 'data_flush' or 'commit' to synchronosu_transfer.

Setting paramters

If 2 servers connects to master server,

  • Synchronous server and also make failback safe standby server
    • synchronous_standby_names = <server name>
    • synchronous_transfer = all
  • Asynchronous server and also make failback safe standby server
    • synchronous_transfer = data_flush
  • Pure synchronous replication
    • synchronous_standby_names = <server name>
    • synchronous_transfer = commit
  • Pure asynchronous replication
    • No setting anything

How to Use

Until you build streaming replication, see Streaming Replication

after that,

  • 1. Set up synchronous_transfer parameter to 'all'
$EDITOER postgresql.conf
synchronous_transfer = all


Restrictions

Currenty this function, only standby server which highest priority is effective. for example, if there is 2 server connects to master server (one is SYNC, another one is ASYNC) and synchronous_transfer is set 'all'. Master server waits for only SYNC standby server because it is highest priority. even if synchronous_transfer is set 'all', there is not effective to server which is NOT highest priority.

Impact for Performance

Todo

  • Currently this patch supports only one failback safe standby. It can either be synchronous or an asynchronous standby. We probably need to discuss whether it needs to be changed for support of multiple failsafe standby's.
Personal tools