_{‍If they had to rewrite ORACLE they would probably end up with PostgreSQL}

‍

In part 1, we saw a general comparison of ORACLE and PostgreSQL standby databases.

Oracle has so many parameters and options that it becomes virtually impossible to remember and set them properly . I counted about 80 different parameters settings and files involved in the process of setting up a physical standby, each comes about a dozen of options:

• 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, password files, tnsnames.ora and listener.ora, init files, and more.

Some of these parameters have so many options. For example each log_archive_dest (and there 30 of them) can be configured by a combination of the following options:

• AFFIRM and NOAFFIRM
• ALTERNATE
• COMPRESSION
• DB_UNIQUE_NAME
• DELAY
• LOCATION and SERVICE
• MANDATORY
• MAX_CONNECTIONS
• And more

One would say ORACLE has this tool for managing the standby (dataguard broker) so you don't have to mess with most of these parameters and settings. Problem is the dataguard broker comes with it’s own complexity.

And yes, I am also aware you can use GRID (which also isn't cheap) to configure a standby database. But you know how it goes, once you go GRID you can never go back. That is because GRID sets dozens of parameters and options, which the average DBA is not familiar with. Rolling back those changes can be painful.

Setting up a standby database on PostgreSQL is easier and involves only a handful of parameters:

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

But still delivers most of the functionality delivered by ORACLE.

The archive_command parameter alone replaces most of the equivalent ORACLE parameters.

While the oracle log_archive_dest* parameters (remember there are 60 of those) control the flow of a new archive, PostgreSQL controls that using a single parameter, “archive_command”.

archive_command is shell script that uses %p (represents the new archive file full path) and %f (the new archive file name) and is triggers once a new archive file is created.

Here is an example:

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

This would first zip the archive file and then send it to the remote stand by computer.

I’d say that’s neat.

Well that's all about this topic.

I hope you found this post useful.

Please share your thoughts about this.

I intend to have the next post about which should be your document database, PostgreSQL VS mongoDB ?

I know it seems I deliberately choose controversial topics (after all some say mongoDB is THE document database) but you will be surprised.

‍

Cluster Spotlight

‍

Your go-to source for all things 123cluster: platform news, step-by-step tutorials, and can’t-miss community events. Whether you’re spinning up a new cluster or fine-tuning an existing one, these resources will empower your workflow and spark new ideas.

‍