2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / Oracle High Availability concepts in PostgreSQL
Simon Riggs

Oracle High Availability concepts in PostgreSQL

October 26, 2017/13 Comments/in 2ndQuadrant, Barman, PostgreSQL, Simon's PlanetPostgreSQL /by Simon Riggs

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.

Tags: high availability, migration
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
13 replies
  1. Mathieu Poussin
    Mathieu Poussin says:
    October 26, 2017 at 11:10 am

    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.

    Reply
    • Simon Riggs
      Simon Riggs says:
      October 31, 2017 at 6:19 pm

      I’m planning to work on that in 2018.

      Reply
      • Adil Arif
        Adil Arif says:
        November 1, 2017 at 5:57 am

        Wow! 🙂

        Reply
  2. BOIZOT Pierre
    BOIZOT Pierre says:
    October 26, 2017 at 7:32 pm

    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

    Reply
  3. Amer
    Amer says:
    October 26, 2017 at 9:59 pm

    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 ?

    Reply
  4. Michael Vitale
    Michael Vitale says:
    October 28, 2017 at 1:54 pm

    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!

    Reply
    • Simon Riggs
      Simon Riggs says:
      October 31, 2017 at 6:17 pm

      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.

      Reply
      • Richard B
        Richard B says:
        November 3, 2017 at 2:43 pm

        Note that Application Continuity in Oracle 12.2 now goes a long way beyond traditional TAF and can support read-write transactions (with certain caveats).

        Reply
        • Simon Riggs
          Simon Riggs says:
          November 4, 2017 at 6:54 am

          It’s all in the caveats

          Reply
    • Michael Vitale
      Michael Vitale says:
      January 10, 2018 at 3:13 pm

      Good Catch on the transaction failover stuff, Simon. True, Oracle does not provide automatic failover for in flight DML statements (INSERT, UPDATE, DELETE), but it does for SELECT statements.

      Reply
  5. Christian H.
    Christian H. says:
    November 6, 2017 at 2:51 pm

    Re various failover managers: Could you please position repmgr in relation to Patroni? (This might also be worth a separate blog post.) Thx.

    Reply
  6. Phil
    Phil says:
    November 30, 2017 at 2:50 pm

    Will write an article about that 🙂
    My general advice : DON’T USE RAC. It is vastly overused. I asked several times to RAC users : “Do you evaluate the gain of availability obtained with RAC ?” I rarely obtained answers.
    From my experience, RAC can give a global GAIN in terms of planned downtime but this gain could mostly have been obtained with Dataguard only.
    Unless you are very lucky and/or very good and/or very rich it will gives you a global LOSS in terms of unplanned downtime
    Last time I discussed with a RAC specialist, I was very skeptical about the choice of our common customer to use RAC.
    RAC specialist gave me the usual answer “You are not confident because the previous integrators were bad but with me it will be different. I asked the customer to buy new storage, I will follow the best practices and things will run”.
    The previous integrators were platinum partners of Oracle and as I expected the specialist did nothing better than them. We had more incidents causing downtime with the RAC database than with the single instances databases left on the old AIX server.
    The reasons are easy to understand but often ignored. A single Oracle Database instance is not simple but RAC completely breaks the KISS principle, it is a complex thing to deploy and to administer. You can have as many nodes as you want, Grid Infrastructure itself will remain a logical single point of failure. Every software has bugs, especially if it is complex and Grid infrastructure is no exception.
    It does not even fully protect you from hardware issues. RAC is good to detect and protect from complete hardware failures but you are more exposed to intermittent failures and performance loss than can end in a global freeze.
    If you build your own RAC, it introduces potential issues you would have never noticed. For example, performance issue on cluster network can slow down the entire thing to a point it’s not practically usable anymore. Several times I just had to shut down one node to temporarily solve a performance issue on the RAC of inexperienced customers (hint : gc* wait events were on top of the performance reports but a ping would have been enough to understand things could not go well…)
    Paradoxically, it means you need to have a perfect storage, a perfect network etc. to reach high availability with RAC. But in fact if everything is perfect… the only SPOF left will be RAC software itself in the end.
    About scalability, it’s the same thing. Oracle RAC can be good..if you are rich. Don’t expect to aggregate your previously bought cheap hardware (servers, network, disks) and to scale as you want. RAC can run great on Oracle Cloud or at least Oracle Hardware but I noticed customers are more and more reluctant to be fully dependent from Oracle. Oracle convinced the market cause it was more “open” than proprietary IBM mainframes after all.
    Many workloads can’t even parallelized that easily on a single instance and in this case RAC won’t help at all. Even if workload can nicely be parallelized, RAC global cache is obtained through network, hence you need a VERY good network.
    Also think RAC is a “share everything” thing in terms of storage, you can have multiple instances but logically you only have ONE database. Write scalability is not as great as read scalability with this approach.
    It means you can scale…if you are rich. But if you are so rich you could probably have paid for a much simpler solution based on a more powerful server with a much bigger RAM, a better storage etc. from the beginning.

    Reply
  7. Jairo
    Jairo says:
    October 19, 2018 at 2:29 am

    I agreed with Phill’s comment, when I had the opportunity to manage environments with Oracle RAC support was complex and determine the performance issues always complicated, even, the configuration is complex and many errors can appear in between. Dataguard is so far more sociable!

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

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

Search

Get in touch with us!

Recent Posts

  • Random Data
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up]
  • Full-text search since PostgreSQL 8.3
  • Random numbers
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up]

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PG Phriday: Getting RAD with Docker [Part 1] OmniDB – Now with PL/pgSQL debugger!
Scroll to top
×