A first look at Amazon Aurora with PostgreSQL compatibility – Benefits and Drawbacks – Part III

In the previous parts of this article series, we started to look at Amazon AWS and the Relational Database Service (Amazon RDS) that it offers to customers around the world. We learned that Amazon’s own Aurora database came along in 2014, and this initially had MySQL compatibility and was optimized to use cloud storage.

But we also noted the disadvantages of the Aurora database “read replicas” so far as application non-transparency and read write non-scalability were concerned, as compared to the Oracle Exadata Cloud and its Real Application Cluster (RAC) database. The latter has true application transparency and read write scalability due to its advanced active-active database technology.

We also talked about the AWS Database Migration Service (DMS) announced in late 2015. This was a migration service set up to facilitate moving on-premises databases such as Oracle, SQL Server, MySQL, MariaDB, or PostgreSQL databases to either the same database platform on the AWS cloud, or a different database platform – such as Microsoft SQL Server to MySQL, or perhaps Oracle to Amazon Aurora. A Schema Conversion Tool was also provided by AWS to convert the on-premises schemas and stored procedures for use on the new platform. However, the schema conversion tool fell short on converting any database application code, such as the PL/SQL packages/procedures/functions in Oracle. All the tool would do was to extract the SQL statements in the code. Almost always, this added to the migration costs since the application code in the database would need to be rewritten.

The initial version of Amazon Aurora was MySQL compatible. Amazon created a PostgreSQL compatible version of Aurora in late 2016. PostgreSQL has its own PL/SQL compatible language called as PL/pgSQL. We looked at different features of open source PostgreSQL, such as its ACID nature, its stored procedures that can be written in 12 languages, its partitioning, datatypes and User Defined Functions ((UDFs). We noted the lack of packages as are present in Oracle. And of course, there is no Real Application Cluster (RAC) database option as in Oracle, nor is there any Flashback feature.

Regarding Backup and Recovery in PostgreSQL, point-in -ime recovery (PITR) is possible. You can set up online logical backups via the pg_dumpall utility, or online binary backups using pg_basebackup or operating system commands like cp or tar.

Savepoints work in the same way as in Oracle and a transaction can be rolled back to a set savepoint. Two-phase commit is possible and is used mainly by external transaction managers.  There is an Audit Extension (pgAudit), which uses the standard PostgreSQL logging facility, whereby session or object audit logging can be set up.

Now, we can take a look at PostgreSQL replication. This is used to create standby databases to which the primary database can failover in the case of a disaster at the primary site, or it can also be used for backup purposes. Replication is of two main types. Either logical replication can be set up, or binary replication, which can either be synchronous or asynchronous.

However, the replication procedure too often relies on scripts. In the old days of Oracle standbys, DBAs used scripts to manually ship logs to standby databases and apply them. That changed with Oracle Data Guard, the Data Guard broker, and Oracle Enterprise Manager to set up and monitor standbys, which made it much more robust.

Recently there was an outage incident at Gitlab that is described here. Apparently the administrator, using manual replication scripts to re-initiate a standby, deleted the entire production directory by mistake. Restoring from the backups could not work as well for various reasons, but we do not want to talk about the failure of the backups. What we are concerned about is the mitigation of risks.

The Gitlab link mentioned frankly that, “the replication procedure is super fragile, prone to error, relies on a handful of random shell scripts, and is badly documented.” Post crisis, another PostgreSQL expert analyzed the human error and recommended that a tool like repmgr be used in the future.

If you have a first look at Repmgr, it is a utility used for PostgreSQL replication setup and failover. It appears to be a tool like the Oracle Data Guard broker, except that in the case of PostgreSQL, it is a third-party tool.

It seems to work in the same way as the Oracle Data Guard Broker to reinitiate the standby at a higher level, monitors the replication process and allows DBAs to issue switch-overs and fail-overs to the standby database. In the case of Oracle, the DG Broker can be used out of the box, so the chance for this kind of manual error to have occurred in the first place, would have been reduced.

Oracle has better Database Administration utilities, such as Data Guard and RMAN, supplied out of the box when you install the Oracle binaries. PostgreSQL has no such utilities like Data Guard, so Gitlab relied on scripts and the command line for their replication i.e. their standby database. When working on the command line to re-initiate the standby, the administrator made this manual error of deleting the entire production directory. It was then that the PostgreSQL expert pointed out that a third-party tool, repmgr, which seems to work in the same way as the Oracle Data Guard Broker to reinitiate the standby at a higher level, could have avoided this issue. The point is that repmgr is a third-party tool in the case of PostgreSQL, lessening the chances that it is used, and therefore raising the risks that such manual errors happen in real life. Data Guard is out of the box.

Oracle Data Guard is part of the installed Oracle binaries for many years and because it is out-of-the-box, database administrators can easily use it when setting up a standby. In the past, Oracle DBAs also used to use manual scripts to carry over redo logs and apply them manually, but that was in the days of Oracle 7 in the 1990s. An Oracle Technical Network (OTN) Data Guard article I wrote here in 2013 has more information on the history and use of Data Guard.

The next part of this article series is here.

(Disclaimer: The views expressed in this article are the author’s own views and do not reflect the views of Amazon Web Services (AWS) or Oracle Corporation. The author has written this article as an independent, unbiased cloud architect/consultant.)