Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

What's is the difference between streaming replication Vs hot standby vs warm standby ?

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.called file based log shipping:only records in a filled file are shipped, what's referred to as file-based log-shipping
3.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'
4. In recovery.conf file, three parameters; standby_mode, restore_command and trigger_file.
5. You cannot connect to Standby, so database is not even open for read operations (or read operations are not permitted on db).

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.called file based log shipping : only records in a filled file are shipped, what's referred to as file-based log-shipping
3. 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
4. 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).
 
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.Called Record based log shipping:XLOG records in partially-filled XLOG file are shipped too, implementing "record-based log-shipping" .
 3. 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
4. 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"'
5. You can connect to Standby for read queries(you should set hot_stanby to ON in standby postgresql.conf file).

Comments

  1. It seems to me you got hot standby and streaming replication confused. Hot Standby IS streaming replication. Note how even your settings are the same for master and slave with both hot standby and streaming replication.

    ReplyDelete

Post a Comment

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL