"If they had to rewrite ORACLE, they would probably end up with PostgreSQL."

That’s a bold statement—especially from someone who’s been a DBA for both Oracle and PostgreSQL for over 20 years. But before you dismiss it, hear me out. While Oracle is undoubtedly one of the best RDBMS solutions on the market, it comes with significant costs, and not just the obvious purchase or support fees.

In this post (and hopefully in upcoming ones, if I don’t run out of steam), we’ll examine the less visible expenses—specifically, the ease of development and maintenance. Today’s focus? Comparing the numerous Oracle parameters for managing a standby database to the simpler setup of PostgreSQL.

Background: Oracle Standby

Oracle boasts a powerful physical standby option—a copy of the production database primarily used as a DR (disaster recovery) solution. When your production database goes down, the standby steps in and becomes primary until you can bring the original back online.

Why DBAs Love It

• Reliability & Speed: It’s fast, stable, and battle-tested.
• Query Offloading: Newer Oracle versions let you query the standby, lightening the load on production.
• Peace of Mind: If deployed and maintained correctly, Oracle standby rarely fails.
  

PostgreSQL’s Standby: The (Free) Elephant in the Room

PostgreSQL also provides a standby feature, offering nearly the same capabilities as Oracle—yes, including hot standby queries. However, PostgreSQL comes at zero licensing cost and, many argue, with easier deployment and maintenance steps.

1. Cost-Effectiveness: No massive fees for the software or support (though enterprise support is available if needed).
2. Simplicity: Fewer parameters to tweak for a physical standby, reducing the risk of misconfiguration.
3. Reliability: With fewer chances to introduce errors, PostgreSQL standby can be as stable as Oracle’s solution.

The key point? Complex setups often lead to errors. A simpler standby system means fewer hidden traps, making PostgreSQL’s solution robust in real-world scenarios.

In the next part, we’ll delve deeper into the specifics of ORACLE and PostgreSQL standby setups.

Oracle Standby: A Parameter Overload?

Oracle’s physical standby solution is powerful, but the sheer number of parameters can be daunting. In total, there may be around 80+ different settings and files to wrangle, including:

• log_archive_dest_1 to log_archive_dest_30 (30 parameters)
• log_archive_dest_state_1 to log_archive_dest_state_30 (30 parameters)
• vdb_name, DB_UNIQUE_NAME, LOG_ARCHIVE_CONFIG, CONTROL_FILE, REMOTE_LOGIN_PASSWORDFILE, LOG_ARCHIVE_FORMAT, FAL_SERVER, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT, STANDBY_FILE_MANAGEMENT, etc.
• Password files, tnsnames.ora, listener.ora, init files, and more.

Each log_archive_dest parameter alone can have a variety of options—AFFIRM, NOAFFIRM, ALTERNATE, COMPRESSION, DB_UNIQUE_NAME, DELAY, LOCATION, SERVICE, MANDATORY, MAX_CONNECTIONS, and so on. Even Oracle’s Data Guard Broker—meant to simplify these settings—adds another layer of complexity. And if you go all-in with Grid, you end up with even more hidden parameters and potential headaches.

Hidden Costs

• Maintenance Overhead: So many parameters leave room for misconfigurations.
• Learning Curve: DBAs must master an array of Oracle tools (Data Guard, Grid, etc.).
• Rollback Pain: Once you set these parameters through Grid or Broker, reverting changes can be tough.

PostgreSQL Standby: Fewer Parameters, Similar Power

By contrast, setting up a PostgreSQL standby typically involves just a handful of parameters:

• archive_mode
• archive_command
• restore_command
• wal_level
• archive_timeout
• postgres.conf
• recovery.conf

Yet despite the lighter parameter load, PostgreSQL standby offers nearly the same functionality as Oracle’s physical standby—hot standby for read-only queries, streaming replication, and more.

The “archive_command” Example

Where Oracle has 60 parameters in the log_archive_dest_* family, PostgreSQL relies on the single archive_command to control where and how WAL (Write-Ahead Log) files are archived:

archive_command = 'gzip%p;rsync-a%p.gzpostgres@the_standby_machine:/home/postgres/wal/%f'

• %p: The full path of the new WAL file.
• %f: The file name of the new WAL file.
• Process: This command first compresses (gzip) the WAL file, then transfers it (rsync) to the remote standby machine.

Neat, right? Minimal complexity, minimal risk of error, and a straightforward approach that covers the essential tasks.

Oracle’s abundance of standby parameters grants impressive flexibility—but at the cost of added complexity. PostgreSQL, on the other hand, achieves a comparable feature set with just a few parameters, reducing the risk of misconfiguration and lowering the DBA’s burden.

What’s Next?
In the upcoming post, we’ll tackle an even more controversial topic: Which should be your document database—PostgreSQL or MongoDB? You might be surprised by the answer!