2

I am confused about the Differences BETWEEN the database replication methods mentioned in a wiki page of postgres, which is best for normal situation?

  1. warm-standby/Continuous archiving/log shipping

    offers high availability

    http://www.postgresql.org/docs/current/static/warm-standby.html

  2. hot-standby/binary-replication/Streaming Replication

    used for read-only querie

    https://wiki.postgresql.org/wiki/Hot_Standby

    https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

  3. PITR

3

Those words are often misunderstood and notions are often mixed up together because they arrived in the same version of PostgreSQL.

  • Warm-standby is simply a replica that's not open for read-only SQL statements.
  • In opposition, Hot-standby is a replica that is open for read-only SQL statements.
  • Before that, we had a "cold standby". It was simply a restored backup, ready to be started in case of the primary node went down.
  • Log-shipping is the old school technic used to send the WALs (write Ahead logs) to a replica so it can replay transactions that took place on the primary node. The file is sent once the primary node isn't writing in it anymore, so lag between primary and secondary depends on how fast the primary will need another file(you can specify it needs a new file after x minutes though).
  • Streaming replication is the upgraded technic that allows to send modified data blocks without waiting for the WAL file to be closed by the primary. It's send as soon as that block is writtentin the WAL file. So we can reduce the lag between primary and secondary nodes.
  • Continuous archiving is what allows Postgres to archive WAL files when they're not needed anymore.
  • Binary replication is a synonym for physical replication (which warm and hot standby are).It simply means both nodes are binary identical.

I hope this will help!

  • PITR (Point In Time Recovery), is a recovery (when WAL are applied to a restored backup) that is asked to stop at a precise moment. – Arkhena Jan 25 at 10:37
  • Streaming Replication improves either Warm Standby or Hot Standby by opening a network connection between the Standby and the Master database, instead of copying 16MB log files. the wiki page said this – dorbodwolf Jan 26 at 1:12
  • How is that different from what I said ? – Arkhena Jan 26 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.