Friday, November 24

Oracle High Availability concepts in PostgreSQL

Sometimes people ask about certain Oracle High Availability features and whether they exist in PostgreSQL.

In most cases, very similar features exist. The reason for the similarity is that PostgreSQL and Oracle have very similar architectures and so the mechanisms to protect data have also developed along the same lines.

For example, Oracle Data Guard is streaming replication of the transaction log, so is very similar in concept to physical streaming replication in PostgreSQL. Active Data Guard is where Oracle users can run queries on a standby server, which again is similar in concept to Hot Standby.

Oracle RAC? You don’t really need Oracle RAC. Or at least qualified Oracle experts tell me so and my own hands on experience confirms that. Not least because disaster recovery for RAC needs to be provided by Data Guard (so why not just use Data Guard). So there is no equivalaent of RAC in the world of PostgreSQL. (Phew!)

Looking for RMAN? There is a backup and recovery tool called Barman which takes advantage of the built-in features for Point in Time Recovery.

There’s more detail there also but a fully detailed explanation is something to discuss as part of technical training or a migration project. The main message is that PostgreSQL supports HA with a range of detailed features that have been in production for many years.

9 Comments

  • What would be great in PostgreSQL would be an equivalent of Oracle Flashback.

    When you compare the difficulty for example to do a PITR in PostgreSQL (and find where the table X has been dropped and then restore manually) compared to a simple “FLASHBACK TABLE xxx TO BEFORE DROP”

    That would just be amazing, I don’t know any other database system offering that.

  • BOIZOT Pierre

    Hello,
    I am a postgres, and I admin also Oracle RAC cluster.
    Stream replication and hot standby are very nice feature.
    But replication without switchover is not very useful….

    With cluster active active like grid infrastructure et RAC database option you have good solution to protect the service.
    The hight redundancy disk choice protect you against disks failure.
    And I hope BDR will be the solution to have no interruptions of service in case of crash of server.
    Or Lost of disk.

    Standby DB are a solution for Lost site in case of 🔥….

    Pierre

  • Amer

    Even though HA is available in PostgreSQL. Just some out of discussion question regarding Oracle RAC Support. Whats about Oracle RAC support for scalability, and agility for any application ?

  • Michael Vitale

    Nice DR comparison between Oracle and PostgreSQL, but HA failover seems to be missing here. Oracle RAC provides HA failover in addition to HA DR (Data Guard), whereas core PostgreSQL does not (manual failover only). Even more interesting about Oracle RAC HA failover is that it provides failover of transactions that have not even completed yet!

    • Various failover managers exist for PostgreSQL. 2ndQuadrant provides repmgr for that task.

      A quick fact-check on the failover thing shows that transactions involving INSERT, UPDATE, DELETE are not supported by the Transactional Failover mechanism you refer to. Plus it doesn’t support Temp objects or Package variables. So there’s not much for PostgreSQL to worry about – its the feature itself that’s Transparent.

Leave a Reply

Your email address will not be published. Required fields are marked *