Sunday 20 April 2014

Difference between Warm, hot standby and Streaming Replication:


We all know about replication and its types in postgresql. There are basic 3 types of replication in postgresql i.e  Warm, hot standby and Streaming Replication. I used to get confused between these three and was unable to find the difference when I was in my initial stages of postgres learning. However I understand the difference later by reading the docs. So I would like to post key differences between these 3 types of replication in short(it also helps if anyone wants to find which one is configured for his/her environment).

Warm Standby:
==========
Its introduced in PostgreSQL 8.3(IIRC).

1. It is based on WAL log shipping which typically means WAL archives generated on Master will be transferred and applied at Standby side. So Warm Standby always waits for the WAL archive in which Master is currently writing and keeps throw messages like "cp: cannot stat <archive>: No such file or directory". So it is always one archive behind than Master and data loss will be max of 16MB(assuming a healthy warm standby by :-) )

2. In postgresql.conf file, you would need to change just three parameters in master; wal_level to archive, archive_mode and archive_command, however nothing in postgresql.conf file at standby side.
On Master:
wal_level = archive
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

3. In recovery.conf file, three parameters; standby_mode, restore_command and trigger_file.
4. You cannot connect to Standby, so database is not even open for read operations (or read operations are not permitted on db).

Detailed explanation and related docs are here
Hot Standby:
========
Its introduce in PostgreSQL 9.0.

1. It is also based on WAL log shipping(same as warm standby). And of-course, WALs will be transferred and applied at Standby, so one WAL behind and always waits for the WAL archive in which Master is currently writing.
2. In postgresql.conf file, you would need to change wal_level to hot_standby, archive_mode and archive_command. Since you'll likely want to use pg_basebackup you should also set max_wal_senders to at least 2 or 3. And hot_stanby = on in standby conf file.
On Master:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'
On Slave:
hot_standby = on
3. In recovery.conf file, three parameters; standby_mode, restore_command and trigger_file. 4. You can connect to Standby for read queries(you should set hot_stanby to ON in standby postgresql.conf file). 

Detailed explanation and related docs are here.
Steaming Replication: 
==============
 Its introduced in PostgreSQL 9.0.
1. XLOG records generated in the primary are periodically shipped to the standby via the network. XLOG records shipped are replayed as soon as possible without waiting until XLOG file has been filled. The combination of Hot Standby and SR would make the latest data inserted into the primary visible in the standby almost immediately. So minimal data loss(almost only open transactions will be lost if its async rep, 0 loss if it is sync rep) 2. In postgresql.conf file, this time 5 parameters, streaming related params like below:
On Master:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'
On Slave:
hot_standby=on
3. In recovery.conf file, you would need to an extra parameter including three which you add in hot/warm standby. i.e primary_conninfo, so below are four parameters:
standby_mode          = 'on'
primary_conninfo      = 'host=192.168.0.10 port=5432 user=postgres'
trigger_file = '/path_to/trigger'
restore_command = 'cp /path_to/archive/%f "%p"'
4. You can connect to Standby for read queries(you should set hot_stanby to ON in standby postgresql.conf file).
Detailed explanation and related docs are here:  && http://bajis-postgres.blogspot.in/2013/12/step-by-step-guide-to-setup-steaming.html

Kindly let me know if I miss anything.

17 comments:

  1. Hello
    I have configured Steaming Replication without archive_mode = on and archive_command = 'cp %p /path_to/archive/%f' and it works perfect!!! that if not set archive_mode = on?? Thanks

    ReplyDelete
  2. so are warm standby, hot standby and streaming replication under log shipping? I'm sorry, I'm new in postgresql..:)

    ReplyDelete
    Replies
    1. Warm standby and Hot standby come under WAL log shipping, however streaming replication provides the capability to continuously ship and apply the WAL XLOG records

      Delete
  3. Thanks baji... This article is very helpful to me...

    ReplyDelete
  4. Hi
    can you tell me difference between warm standby and hot standby? Thankx in advance

    ReplyDelete
    Replies
    1. Both are same except you can connect hot standby for read-only, but not warm standby.

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Great work, thanks for sharing =)

    ReplyDelete
  7. Hi Baji Shaik,

    I am planning For HA in Postgresql 9.3?

    I am working for mission critical project.

    We are palnning for High Availblity.At any point of time irrespective of the failure(n/w,Hw or db) one db server should be avilable for Application.

    Scenario

    A replicates to B(hot_standby)

    if A goes down i can connect to B.Mean while after some time A has come up again how we can replicate from B to A.If we bring up A how can we bring it up either as old master or new slave to the master B.

    One more clarification And also i am planning for Disaster recovery.Can it possible to enable wal archiving on both master A and slave B before starting replication.

    And can we automate this whole procedure.I mean no manual intervention is required while replicating A from B(A is master) and B from A(B is master).

    I am using OS redhat

    Postgres Version 9.3.

    Please help me in this.

    ReplyDelete
  8. How To Set Up Master Slave Replication on PostgreSQL..Error occurs in WAL level not sufficient for making an online backup
    HINT: wal_level must be set to "archive", "hot_standby".

    ReplyDelete
  9. please let me know baji cascading replication disadvantages when compare with streaming replication

    ReplyDelete
  10. I have streaming but im missing restore_command in recovery.conf everything works good so far. any gotchas?

    ReplyDelete
  11. should I use hot standby or stream replication? which is better?

    ReplyDelete
  12. Is is possible to create write tranasction is slave

    ReplyDelete